Author Topic: One sheet to rule them all...  (Read 48118 times)

urmie

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: One sheet to rule them all...
« Reply #150 on: February 04, 2017, 07:20:13 PM »
So I just started using this sheet, want to start tracking for 2017 and filled in Jan 2017's column and adjusted the FI labels.
I went to mortgage and put $0 for 'Start Amount of Table' because I don't own a home hence no mortgage.
Now all of the NW tab has "#VALUE" in the colored columns, why is this so dependent on the mortgage tab?

I populated the white areas of the NW tab for 12/1/2016 with my last balances.
So income/expense/401K/IRA/ROTH, etc.
NetWorth/Liquid and all the colored columns still remain "#VALUE".

How should this be filled out for those of us who don't own a home?
What am I missing here?

erutio

  • Stubble
  • **
  • Posts: 117
Re: One sheet to rule them all...
« Reply #151 on: February 06, 2017, 11:35:22 AM »
thanks for all the work IndyP and wienerdog!

Jan 2017 over now so I've started to really start to use and learn this spreadsheet.
I have one question though: How do I account for expenses that are annual, that came up in January?  I'm putting 'FALSE' on 'Use all-time?' because I dont have historical expenses recorded.  However, a few things like property taxes and child pre-school expenses came up in January, and I would like to leave as line items in the budget (can even be in the third box), but if I enter those values in, they get calculated as monthly expenses and my time to FI shoots way up.  Whats the best way to account for annual expenses?  Thanks.

VladTheImpaler

  • Stubble
  • **
  • Posts: 217
Re: One sheet to rule them all...
« Reply #152 on: February 08, 2017, 01:16:41 AM »
thanks for all the work IndyP and wienerdog!

Jan 2017 over now so I've started to really start to use and learn this spreadsheet.
I have one question though: How do I account for expenses that are annual, that came up in January?  I'm putting 'FALSE' on 'Use all-time?' because I dont have historical expenses recorded.  However, a few things like property taxes and child pre-school expenses came up in January, and I would like to leave as line items in the budget (can even be in the third box), but if I enter those values in, they get calculated as monthly expenses and my time to FI shoots way up.  Whats the best way to account for annual expenses?  Thanks.

I pay my auto insurance in a lump sum every Jan, so I divided the amount by 12 and put that value in each month. It spreads the annual expense over the year. This keeps my "years til FI" number more accurate. The downside is that is skews my monthly savings rate on the individual month sheets.

Ultimately, the "Years till FI" number will be most accurate at the end of the year when you have tracked and entered all 12 months of expenses because it will average out.
"Everyone has different tastes and desires. These differences make the world orderly. If everyone wanted the same things, we would all be struggling against each other to acquire what little was available. Diversity is the source of harmony in human relationships."

-Harry Browne, How I Found Freedom in an Unfree World

dandypandys

  • Bristles
  • ***
  • Posts: 293
  • Age: 44
  • Location: USA
Re: One sheet to rule them all...
« Reply #153 on: February 08, 2017, 09:05:52 AM »
Hello again!
I have this working now --- yay
Just doing the investment tab today.. is something wrong with it?
Line 23 seems a little messed up in E, F and I and is affecting what I enter.. maybe the formula is in the wrong place ?

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #154 on: February 11, 2017, 01:11:23 PM »
So I just started using this sheet, want to start tracking for 2017 and filled in Jan 2017's column and adjusted the FI labels.
I went to mortgage and put $0 for 'Start Amount of Table' because I don't own a home hence no mortgage.
Now all of the NW tab has "#VALUE" in the colored columns, why is this so dependent on the mortgage tab?

I populated the white areas of the NW tab for 12/1/2016 with my last balances.
So income/expense/401K/IRA/ROTH, etc.
NetWorth/Liquid and all the colored columns still remain "#VALUE".

How should this be filled out for those of us who don't own a home?
What am I missing here?

If you don't own a home just put in $0 for the appraised value.  Put $0 for amount financed, 1 year and 1%.  Fill in $0 for Escrow and Extra Principal. Start month of table can be 1 and put $0 for amount financed.  That will give the other sheets a $0 dollar amount for the formulas to grab.

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #155 on: February 11, 2017, 01:23:23 PM »
thanks for all the work IndyP and wienerdog!

Jan 2017 over now so I've started to really start to use and learn this spreadsheet.
I have one question though: How do I account for expenses that are annual, that came up in January?  I'm putting 'FALSE' on 'Use all-time?' because I dont have historical expenses recorded.  However, a few things like property taxes and child pre-school expenses came up in January, and I would like to leave as line items in the budget (can even be in the third box), but if I enter those values in, they get calculated as monthly expenses and my time to FI shoots way up.  Whats the best way to account for annual expenses?  Thanks.

Do you have Use All-time set to TRUE on the FI sheet?  I think that takes care of that but you might need a years worth of data before it behaves better.

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #156 on: February 11, 2017, 01:28:56 PM »
Hello again!
I have this working now --- yay
Just doing the investment tab today.. is something wrong with it?
Line 23 seems a little messed up in E, F and I and is affecting what I enter.. maybe the formula is in the wrong place ?

Glad you got it working!

Line 23 - 27 is a little different as it is an example of target retirement fund.  What it attempts to do is calculate what portion of fund is in each category so it gets represented up top correctly so you can see your asset ratio.

Do you have a target retirement fund or is it something else?

dandypandys

  • Bristles
  • ***
  • Posts: 293
  • Age: 44
  • Location: USA
Re: One sheet to rule them all...
« Reply #157 on: February 11, 2017, 01:31:31 PM »
I just have 3 different stocks/ bonds, in 3 different accounts, no target funds..
So do i just skip this line - if i delete it it goes haywire and sets everything else at zero.
Thanks!

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #158 on: February 11, 2017, 02:08:39 PM »
I just have 3 different stocks/ bonds, in 3 different accounts, no target funds..
So do i just skip this line - if i delete it it goes haywire and sets everything else at zero.
Thanks!

Go to line 30 and left click the 30 to highlight the row. Copy that row and then go click line 24 to highlight it.  Under edit paste special click values only.  Now click select row 23 then edit then select Delete row 23.   Then also delete row 24, 25, 26,27.

See if that works for you.

dandypandys

  • Bristles
  • ***
  • Posts: 293
  • Age: 44
  • Location: USA
Re: One sheet to rule them all...
« Reply #159 on: February 11, 2017, 02:39:02 PM »
Thank you thankyou, seems to work.
One last Q, sorry,
but i have one rogue fund that has no ticker, a stable asset.. do i just manually put it in there somehow?

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #160 on: February 11, 2017, 03:20:50 PM »
Thank you thankyou, seems to work.
One last Q, sorry,
but i have one rogue fund that has no ticker, a stable asset.. do i just manually put it in there somehow?

Yes just manually put it in.  Make sure the column A has the correct label as that is how the formulas up top calculate your asset ratio.  Under column C you need to click on that box and look up top in the formula bar.  You will see this

=GOOGLEFINANCE(B30,"price")

Just delete the formula and enter the price of the stock.  Now you will update column C and column D manually each month.  You will give it the price of the stock and how many shares you own then column E will get calculated the same for the total value of that stock.

dandypandys

  • Bristles
  • ***
  • Posts: 293
  • Age: 44
  • Location: USA
Re: One sheet to rule them all...
« Reply #161 on: February 11, 2017, 03:27:28 PM »
Thank you so much!!! You have been a great help and really appreciate it. Looks RAD!

wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #162 on: February 11, 2017, 03:49:11 PM »
Thank you so much!!! You have been a great help and really appreciate it. Looks RAD!

You're welcome.  You'll be a spreadsheet pro before long!

BabyShark

  • Stubble
  • **
  • Posts: 135
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #163 on: February 13, 2017, 02:48:04 PM »
More dumb questions.  What is WR income?  on the dash, it's the blue line, which I understand to be my investment income, but I cannot for the life of me figure out what WR stands for.

erutio

  • Stubble
  • **
  • Posts: 117
Re: One sheet to rule them all...
« Reply #164 on: February 14, 2017, 02:29:02 PM »
More dumb questions.  What is WR income?  on the dash, it's the blue line, which I understand to be my investment income, but I cannot for the life of me figure out what WR stands for.

Withdrawal rate.  Based on what you put down for your % withdrawal rate and total investment.

BabyShark

  • Stubble
  • **
  • Posts: 135
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #165 on: February 14, 2017, 06:58:05 PM »
More dumb questions.  What is WR income?  on the dash, it's the blue line, which I understand to be my investment income, but I cannot for the life of me figure out what WR stands for.

Withdrawal rate.  Based on what you put down for your % withdrawal rate and total investment.

Ahhhhhhh. Thank you!

urmie

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: One sheet to rule them all...
« Reply #166 on: February 26, 2017, 03:22:24 PM »
So I just started using this sheet, want to start tracking for 2017 and filled in Jan 2017's column and adjusted the FI labels.
I went to mortgage and put $0 for 'Start Amount of Table' because I don't own a home hence no mortgage.
Now all of the NW tab has "#VALUE" in the colored columns, why is this so dependent on the mortgage tab?

I populated the white areas of the NW tab for 12/1/2016 with my last balances.
So income/expense/401K/IRA/ROTH, etc.
NetWorth/Liquid and all the colored columns still remain "#VALUE".

How should this be filled out for those of us who don't own a home?
What am I missing here?

If you don't own a home just put in $0 for the appraised value.  Put $0 for amount financed, 1 year and 1%.  Fill in $0 for Escrow and Extra Principal. Start month of table can be 1 and put $0 for amount financed.  That will give the other sheets a $0 dollar amount for the formulas to grab.

Thanks, I did that (put 1/1/2017 as the start date), now in the NW tab Net Worth/Liquid/WR Income tabs just show "#VALUE!". I filled out the white area for following tabs: Income/Expenses, 401K, TIRA, Roth IRA, Taxable, then in Home Equity area, I just put in my current values for Checking and Savings accounts and then in Loan tab, put 0. 2/1/2017 has the current value of all my accounts.

However, the "Current Net Worth" (blue at the top) shows $426 in E column and $342 in F column. Huh? Why so low? Am I not doing something right?

Also in the monthly tabs, what are we supposed to put for the purple "Payroll Taxes"??
Where do I get this from? Taxes (% of gross) is showing "270829.79%". That can't be right.



wienerdog

  • Bristles
  • ***
  • Posts: 353
Re: One sheet to rule them all...
« Reply #167 on: February 26, 2017, 03:29:17 PM »
The taxes you should be able to get from your paycheck stub.  They should be entered down at the bottom of each month under B69 - B74 and C, D etc depending on how many times you get paid.

cantgrowone

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: One sheet to rule them all...
« Reply #168 on: March 21, 2017, 01:21:40 PM »
This is awesome!

I use YNAB to keep track of my everyday expenses so I have made this spreadsheet to fit our lifestyle. I'm assuming 'Use Budget' = true ignores every month sheet in the FI calculations. I cannot find any equations that use the month sheets; except rows > 61.

On sheet 'FI' I ignore every row after 61, since I use YNAB to track where our money goes. I entered our month expense from last year on cell C13 so it will calculate our monthly/yearly expenses. After entering our income/expense for this year; we have 14.85 years to FI. This matches up to the FIRE Calcs I used. Here's to hoping I can make it until 45 in the workforce.

Edit: I cannot wait till next year to see my progress :)

Zarakava

  • 5 O'Clock Shadow
  • *
  • Posts: 15
Re: One sheet to rule them all...
« Reply #169 on: Today at 07:09:28 AM »
I'm a little confused about this - I think mostly because I'm trying to start halfway through the year. I think what I'd do is at the beginning of April, put all my values in the NW tab, fill out the FI tab, blank out Jan-Mar, set Use Budget to true, and go from there?