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

NinetyFour

  • CMTO 2023 Attendees
  • Walrus Stache
  • *
  • Posts: 6875
  • Age: 62
  • Location: Southwestern US
Re: One sheet to rule them all...
« Reply #50 on: January 05, 2016, 03:39:52 PM »
Following.

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #51 on: January 18, 2016, 06:20:45 PM »
I updated the alternate version of the FI Tracker for 2016 and added a couple more items.  Link to sheet:  FI Tracker V2

Now on the NW page I provided a column for Income and Expenses for each month to track "All-time" values.  You will need to update those two columns manually at the end of each month when you get all your expenses in for the month.  You can pull the  number from the FI page under B57 for income and B60 for expenses in January.  Each month will update there at the end of the month if you keep each month's tab updated.

I also added a way to make bringing in Mint transactions easier.  If you follow the directions on the Mint tab let me know if I need to make something more clear.  I would like to make things a little more automated but for now this will have to do.  Just a simple way to bring in all your expenses for the month with a few cut and pastes if you use Mint and have them sorted into the categories used on the monthly expenses tabs.

There is also a new TRUE/FALSE value on the FI page. It will use your All-time values (average of all your months logged on the NW tab) for you Financial Independence calculation.  If you want to use your average for the current year just set to FALSE.  Note you will have to wait until Feb 1 to give the sheet one month completed to calculate right when set to FALSE.

Thanks again for Indy for providing the original.  If you need help getting this working for you please let me know or if you find something wrong or that could be improved let me know.

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #52 on: January 19, 2016, 06:31:29 AM »
I updated the alternate version of the FI Tracker for 2016...

Great work! I'm going to add a link to your v2 sheet in the main thread. If this keeps up, we may retire my original and just link to yours. You clearly are iterating and tweaking more than I am.


lafemmex

  • 5 O'Clock Shadow
  • *
  • Posts: 16
Re: One sheet to rule them all...
« Reply #53 on: February 15, 2016, 02:08:11 AM »
Just found this amazing spreadsheet!  Thank you again for all of the hard work that's gone into creating it!  Can't wait to start putting in my numbers:-)

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #54 on: February 15, 2016, 08:15:12 AM »
Just found this amazing spreadsheet!  Thank you again for all of the hard work that's gone into creating it!  Can't wait to start putting in my numbers:-)

Let us know if something isn't clear or give us ideas on how to improve.  Everyone can help make it better!  I have found the Mint import could be better as credits get calculated in right now.  I only track my credit card so only refunds and payments get calculated.  So far it is easy just to delete those lines to make the numbers correct but someone using a bank account with more deposits could be a little more rough.

exoteric

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: One sheet to rule them all...
« Reply #55 on: March 11, 2016, 09:57:21 AM »
Could you create a sheet of instructions similar to the FI V1? I've been scratching my head trying to figure it out, but it's a bit overwhelming.. Thanks!

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #56 on: March 16, 2016, 09:45:39 AM »
Sorry been off of the mainland for 3 weeks and trying to get caught up.  I have a cam position sensor to change this weekend but I will see if I can put an instruction sheet together.  The setup at first can take a little bit but once you get it setup to your needs it runs pretty smoothly besides updating your expenses at the end of the month and all of your account values.

dandypandys

  • Pencil Stache
  • ****
  • Posts: 545
  • Age: 47
  • Location: USA
Re: One sheet to rule them all...
« Reply #57 on: March 17, 2016, 03:08:49 PM »
I would love instructions too.. unless V1 instructions still work for V2 ?
i have it saved, but i don't know how to use spreadsheets, like at all.. :/
Thanks for making it, it looks RAD
« Last Edit: March 21, 2016, 04:50:49 PM by dandypandys »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #58 on: March 24, 2016, 07:43:15 PM »
I put a rough draft of the directions for the V2 sheet to help some get started.  I put this together quick and without any checking so there probably are mistakes.  Please look the instructions over and give me some feedback on what is clear and what is not.  Spelling and grammar need checked also.  The descriptions highlighted in yellow have not been updated yet.  When I can get some more spare time I will describe the basics of each sheet.  The Mint and Mortgage sheet also didn't get updated very good in the instructions as I just ran out of time tonight.

dandypandys

  • Pencil Stache
  • ****
  • Posts: 545
  • Age: 47
  • Location: USA
Re: One sheet to rule them all...
« Reply #59 on: March 24, 2016, 07:47:17 PM »
thanks:)

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #60 on: March 24, 2016, 08:03:44 PM »
thanks:)

You're the guinea pig.  Let me know what I screwed up.  LOL

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #61 on: March 25, 2016, 08:47:59 AM »
One small update I found on V2.  The taxes percentage was calculated from net income not the gross.  The formula in cell C13 has been updated on all the month sheets.  You can change it manually in your sheets or paste from here:

Code: [Select]
=IF(OR(B78>0,B105>0),C14/B78+B105,0)
The text in A13 was updated from Taxes to Taxes (% of gross)

You could also copy A13 to C13 and paste in each one of the months sheet at A13

mlr2016

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: One sheet to rule them all...
« Reply #62 on: May 04, 2016, 01:19:51 PM »
Is anyone experiencing issues with the FI Tracker V2 on Google docs?  For the past few days, the file has failed to load for me.  I was thinking that somehow my file became corrupt, but I even tried opening the template a few minutes ago with no luck.  Any insight or help is appreciated? 

mlr2016

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: One sheet to rule them all...
« Reply #63 on: May 05, 2016, 07:38:36 AM »
Is anyone experiencing issues with the FI Tracker V2 on Google docs?  For the past few days, the file has failed to load for me.  I was thinking that somehow my file became corrupt, but I even tried opening the template a few minutes ago with no luck.  Any insight or help is appreciated?

Never mind, I seemed to have located the problem.  The issue occurred when I was opening up the document through IE on my work computer.  However, when I went home I opened it through Chrome and all was well.  So, I'm guessing there's a browser setting that may need to be updated in IE for it to open correctly.  Purely guessing at this point, I'm just glad to know it's working correctly.  This is definitely one of the more robust and useful spreadsheets I use to track my finances, thanks wienerdog!

exoteric

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: One sheet to rule them all...
« Reply #64 on: May 11, 2016, 09:48:15 AM »
I'm trying to think ahead to 2017, and I'm wondering if there is a way to keep the historical spending data. From what I can figure, when you update the sheet, the NW numbers will remain, but the month to month spending will be scrubbed. But for the sake of knowing the averages spent, I kinda want to keep those numbers somehow.

What's the best way to do this?

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #65 on: May 11, 2016, 08:29:37 PM »
I just added this in my version as I didn't really think about that last year.  When 2016 came I just saved a new spreadsheet to start over and updated the date on the Jan tab and the rest update automatically.  On the NW (Net Worth) tab there are now two columns for each month to record those.  Income is column D and Expenses is column E.  You have to manually update those each month. They will lag behind one month as the spreadsheet updates NW at the beginning of the month and you don't really know your expenses and income until the end.  They can be pulled from the month tab or row 57 and 60 on the FI tab.  Notice the calculation lags behind one month compared to investment income.

Income and expense are then graphed on the Dash tab for all the months that you have entered.  As new years come you just create a copy of the sheet and update the date and all your old expenses and income will accumulate on the NW tab with all your account balances for each month.

I would like to point out that on the FI tab there is a TRUE / FALSE input that says Use all-time.  If it is true it uses your average income and expenses at the top of the NW tab for the FI calculation.  (D1 and E1).  If it is set to FALSE it uses your average for the current year.

I hope all that makes sense.  If you have any other questions or ideas for improvement I will take them.

The Southern Stache

  • 5 O'Clock Shadow
  • *
  • Posts: 7
  • Age: 46
  • Location: Georgia
    • The Southern Stache
Re: One sheet to rule them all...
« Reply #66 on: May 15, 2016, 09:31:17 AM »
Thanks Indypendent.  I have been keeping my NW history in excel, but the format isn't as graphically appealing as what you have here.  I started a blog (after almost a year of procrastination) and part of it is to share my FI journey, wanting to focus mainly on monthly graphics versus long paragraphs of boring explanations, thinking this would be a more enjoyable and interesting presentation for the readers.  Instead of spending a lot of time to build this on my own, which I am sure I could do, I am VERY happy you shared this tool so I don't have to.  If you are interested, I hope to have my first monthly financial summary posted within the next month and will send you the link.

Good lookin' out.

-Stephen

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #67 on: May 23, 2016, 08:05:19 AM »
FI Tracker v3 has been posted! Weinerdog has updated the template to include automated tracking of investments, along with minor tweaks.

Take a look at try it out!



*Edit: Fixed broken in-line image. D'oh!
« Last Edit: May 31, 2016, 12:39:54 PM by IndyPendent »

dandypandys

  • Pencil Stache
  • ****
  • Posts: 545
  • Age: 47
  • Location: USA
Re: One sheet to rule them all...
« Reply #68 on: May 23, 2016, 01:49:15 PM »
wow cool!

tomboalogo

  • 5 O'Clock Shadow
  • *
  • Posts: 9
  • Age: 63
  • Location: Ontario-Canada
Re: One sheet to rule them all...
« Reply #69 on: June 04, 2016, 08:38:01 AM »
Hey thanks for the sheet, just discovered it.

One thing that bugged me in the initial setup is the copy/paste of the labels. I'm 'fixing' mine by the following:

1) Setup labels as in the instructions.
2) Instead of copy/paste, I change the first label of the Jan tab to '=FI!$A13' (no quotes). This automagically copies the label text from the FI tab to the Jan tab.
3) I then click on the square when A17 is selected and drag down to fill the rest.
4) Rinse and repeat for the other month tabs.

This way if I decide to change labels on FI tab, everything gets updated on it's own. I used this quite a bit in my own Asset Allocation spreadsheet (which morphed into budgeting and predicting FI but thats another story)

Again, thanks for the sheet and if you think that the above is useful ....

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #70 on: June 04, 2016, 08:50:31 AM »
Perfect tomboalogo!  I have always wanted to make it easier for others to setup to start with but mine has been setup so I don't necessary find creative ways to do it.  Thanks for the tip I'll update the master later tonight so it is easier for others.  Updated!

I did one small update on the FI page.  It now calculates Years of Expenses and the Goal.  If you want to update your FI sheet just copy D6 to E7 and paste in your FI sheet at D6.
« Last Edit: June 04, 2016, 09:10:37 AM by wienerdog »

tomboalogo

  • 5 O'Clock Shadow
  • *
  • Posts: 9
  • Age: 63
  • Location: Ontario-Canada
Re: One sheet to rule them all...
« Reply #71 on: June 05, 2016, 06:51:40 AM »
Thanks, I just hate doing redundant 'grunt' work.

Was wondering the easiest way to indicate my paid off mortgage (last month-yay!), while still showing it in Net Worth? I found I had to set the start date in the Mortgage tab to start next month to get the equity right.

This mornings coffee in the home office is using the sheet to decide if I'm already FI or not.

Thanks again

mynewchoice

  • 5 O'Clock Shadow
  • *
  • Posts: 96
Re: One sheet to rule them all...
« Reply #72 on: June 22, 2016, 05:30:37 PM »
First and foremost, Indy and WD, thank you both so much for building this spreadsheet and sharing it with all of us!

Now, I am a new mustachian--so new that this is my first post--and I am starting by taking this spreadsheet and adding in my data from my other various spreadsheets that I have been maintaining for quite some time.  I have a very disjointed set of spreadsheets and I think this will be a great addition, possibly replacement, to those and get me more focused on my FI date.

As I have started entering my payroll information, I had a question about how to handle bonus payouts.  Is it best to record that in as an additional pay period?  It has the standard taxes deducted but not any of the 401k, HSA, etc. so I wasn't sure if that would skew anything.  I thought about putting it in as Paycheck #2 (as we are a single income family) but wasn't sure if that would skew anything else that is calculated.  It would be great to hear how others have entered their bonuses into the spreadsheet.

Thanks,
mnc

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #73 on: June 23, 2016, 04:44:39 PM »
Doing it as an additional pay period would work.  Just leave 401k, HSA etc as zero and it will all calculate fine and enter your taxes removed.  If you do it that way it still show your taxes % of gross correctly.  You could also just put the actual take home amount of the bonus under income 3, 4, 5 etc.  Just name it bonus so you know what it was for that month.  It just would throw off you taxes paid as a percent some but that number isn't used anywhere anyway.  You could also use paycheck #2.

The paychecks just subtracts taxes and gets your net take home amount.  It then adds back in the 401k, 401k match, HSA etc and uses that for your adjusted income up top.  One month when I did a side job I just put the $500 under 3rd income amount as the saving rate is calculated off the total of that column.

Let me know if you got suggestions or need any more help.  We can all make the sheet better.

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #74 on: June 24, 2016, 08:02:54 AM »
Doing it as an additional pay period would work.  Just leave 401k, HSA etc as zero and it will all calculate fine and enter your taxes removed. 

+1, This is how I handle bonuses.

mynewchoice

  • 5 O'Clock Shadow
  • *
  • Posts: 96
Re: One sheet to rule them all...
« Reply #75 on: June 24, 2016, 09:33:32 AM »
Thank you guys!  I went ahead with how you guys have suggested with it being an extra pay period, and figured that I could adjust if needed once I heard back.  I had some challenges with the mortgage tab as I have an ARM and it required a little tinkering as my payments were not aligning to how it is calculated, nor were my results aligned to other calculators.  Not sure what is going on there but it is working well enough for me now.

On the FI tab, I had to unfortunately add a number of line items and that required some tinkering with all of the monthly tabs to keep them synced up.  That alone was a sign that adopting the MMM ways will help me trim some fat out of that budget.

Overall I am absolutely loving this spreadsheet, and it has really opened my eyes on what I need to do to get to FI.  Thank you both again.

tralfamadorian

  • Handlebar Stache
  • *****
  • Posts: 1217
Re: One sheet to rule them all...
« Reply #76 on: June 24, 2016, 06:58:29 PM »
Hi guys!  I wanted to take a sec to thank all you who contributed to make this spreadsheet.  It's fantastic! 

I have had it saved for a while but hadn't taken the time to fill it out until today because I knew I would be making some significant changes.  I am a combo RE and stocks investor, am self employed with a seasonable income and plan on having slightly different expenses when I FI then I do now.  The spreadsheet was really easy to work with and modify- much more painless than I anticipated.  You all are awesome!

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #77 on: June 25, 2016, 04:18:15 PM »
Hi guys!  I wanted to take a sec to thank all you who contributed to make this spreadsheet.  It's fantastic! 
[...]
You all are awesome!

I'm so glad it's useful!

jumpingoff

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: One sheet to rule them all...
« Reply #78 on: June 27, 2016, 11:04:09 AM »
Thanks for this.  Been working on it for a couple of days.  Lots to get my head around and organize but you have to start somewhere.  Luckily my wife and I are natural savers and live pretty lean already so the transition should be pretty painless.

*Edit - Question: 

On the Jan tab it says "Once you get Jan setup how you like it copy range A17 to E11.  Then paste that in each following month at A17."   Is that range correct?  It seems like E11 is a typo.  What should the range be?  I assume it should be A17 - A33, yes?
« Last Edit: June 27, 2016, 03:51:44 PM by jumpingoff »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #79 on: June 27, 2016, 03:45:16 PM »
*Edit - Questions: 

On the Jan tab it says "Once you get Jan setup how you like it copy range A17 to E11.  Then paste that in each following month at A17."   Is that range correct?  It seems like E11 is a typo.  What should the range be?


Ignore that.  Just setup the categories in the FI and they will replicate everywhere else thanks to user input.  Those instructions were there before the update.  But you are right the E11 is a typo.

IndyPendent

  • Stubble
  • **
  • Posts: 100
One sheet to rule them all...
« Reply #80 on: June 29, 2016, 02:22:58 PM »
Question for you guys on where to add this in the sheet.

My employer gives us 3,000 a year into an HSA.

Not stupid questions! You could either put the HSA contribution as an additional income line item or, as I do, leave HSA contributions  off the monthly sheets and just put employer contributions in the net worth sheet. I miss out on a bit of savings rate that way but I don't care that much.

Second, I highly recommend sticking to a format until Jan 1. Each year I make a new fresh sheet and move the net worth items over. Changing versions in the middle of the year would be manual and a PITA.


Sent from my iPhone using Tapatalk
« Last Edit: June 29, 2016, 05:32:09 PM by IndyPendent »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #81 on: June 29, 2016, 06:11:17 PM »
The way the HSA is setup now is it does 'contribute' from your check but with a couple changes to your spreadsheet you can fix that.  If you put a positive $125 under your HSA category at B65, C65 etc in the check section on the months that you want to put the additional amount.  That will add to your paycheck total.  The only problem is when you look at the amount under the Investment box it will be negative up there.  In B83 you need to adjust the formula =sum(B65:E65)*-1 just remove the '*-1' and it will make the amount positive up in the Investment box.

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #82 on: July 02, 2016, 02:55:58 PM »
I made a couple slight updates.  If you want to update your sheets it should be easy enough.  The FI calculations could always been done using liquid assets or total net worth depending on if you select TRUE or FALSE in the FI sheet under Include Equity.  Now I am tracking liquid assets in the NW tab so they can be graphed over time.  Basically the Liquid is the total of all accounts minus the Home Equity.

If you want to update your sheet select Row C at the very top to highlight it all in your NW sheet.  Go to Insert up top in the menu and select Column Left.  This will insert a new column.  Now go to the FI V3 Sheets Master and select Row C at top again to highlight it.  Up top in the menu go to Edit then select Copy.  Now go back to your NW sheet and make sure Row C is still selected and go to the menu and select Edit and Paste.

Now to update the graph to plot Liquid go to your Dash sheet. Click on the Networth graph that will make a little down arrow show up in the upper right corner.  Click on the down arrow and select Advanced edit...  Click the Chart types tab up top and make sure the data series box at the top matches this:  NW!A2:C134  Then click the third chart over under the Line charts.  It should be the combo chart.  Select Update at the bottom and you should be up to date!

LateToTheParty

  • Stubble
  • **
  • Posts: 121
  • Location: Oregon
Re: One sheet to rule them all...
« Reply #83 on: July 03, 2016, 03:17:20 PM »
Hello there,
Thanks for sharing this body of work Indy.  I am new to google docs. Can you post the link to v3?

Monica21

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #84 on: July 03, 2016, 03:45:40 PM »
I'm very new to FI and what needs to be done to get there, and I stumbled across this spreadsheet while tinkering around with others and it seems to suit me the best. So, forgive any super basic questions, either about FI or about the sheet itself. (I'm more comfortable with Excel and not sure about the limitations of Google Sheets.)

- I can't tell how Annual Savings and Rate is calculated. I can tell that it's (in part) from the Net Worth sheet, but aside from that I don't know how to use it, since the number and percentage it's returning is wrong. Right now I'm only putting aside a small amount in savings and my Roth since that's all I can afford.

- I will have money from a pension plan upon retirement, but it's something my employer pays into. I can't pay into it. Is there a way to account for it?

- Will my Years to FI drop as my Net Worth increases? Because I'm paying off a house, a car, and student loans, so my Net Worth is in the negative right now.

- I know that there is Mint integration, but is it necessary? I want to be able to enter the numbers manually, but I don't know if it affects any other calculations.

- I changed the Discretionary Expenses fields to include just the debt I'm paying off, but it's included in years to FI. Is it smart to leave it in, since I don't plan to retire before the debt is paid off, and just be able to breath a sigh of relief when I see the debt removed and the drop in FI? (I think I just answered my own question, but another perspective would be nice.)

- I'm not that familiar with Google Sheets, but is there a way to integrate a snowball or avalanche debt payoff into this? This kind of goes with my question above, and I've got a payoff plan from undebt.it but don't know how to see how it would affect years to FI.

Any help would be appreciated.
« Last Edit: July 03, 2016, 03:47:37 PM by Monica21 »

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #85 on: July 03, 2016, 03:56:18 PM »
Hello there,
Thanks for sharing this body of work Indy.  I am new to google docs. Can you post the link to v3?

No problem!

https://docs.google.com/spreadsheets/d/1T6RgJnzZo1bdGHA0a5JBQqqSSEPX2xDrCT5zioWr9z8/edit?usp=sharing


Sent from my iPhone using Tapatalk

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #86 on: July 03, 2016, 05:11:54 PM »
- I can't tell how Annual Savings and Rate is calculated. I can tell that it's (in part) from the Net Worth sheet, but aside from that I don't know how to use it, since the number and percentage it's returning is wrong. Right now I'm only putting aside a small amount in savings and my Roth since that's all I can afford.

Annual savings is all calculated on the FI sheet at B4 - B6 and it depends on if you 'Use All Time' is True or False.  If it is True it uses your average income and spending from the NW sheet (E1 and F1).  If it is False it uses the average of the data that is auto populated by date down in the Totals section on the FI sheet (B57 etc).  This data is pulled from each months sheet as the month finishes so you should update the data on the month sheet at the end of the month for it to be correct.  If it is set to False you  are only calculating from the current year's data.

- I will have money from a pension plan upon retirement, but it's something my employer pays into. I can't pay into it. Is there a way to account for it?

That is a good question.  Does the pension value change over time?  What you could do is make it a column in the NW sheet just like an investment account.  If the value of the pension changes as times goes on update it and it should reduce your FI time.  How does the pension work does it pay you a certain amount at a certain time?


- Will my Years to FI drop as my Net Worth increases? Because I'm paying off a house, a car, and student loans, so my Net Worth is in the negative right now.

It will but I am not sure how it will handle negative numbers.  I have never tested that.  I don't consider my mortgage debt so it isn't calculated in my NW.  The NW can either include the home equity or not depending on if you select True or False.  Some people plan on downsizing and selling their house and get something much cheaper so equity would count for them.  I plan on staying where I am at so I don't include equity as NW as it isn't liquid and can't help me in FI.  So on the FI sheet I have Include Equity set to False.

The way I account for the mortgage is it is an expense that gets calculated in my expenses.  If I spend $900 just in mortgage payment then I need enough money in FI to cover that expense so it is calculated in.  In my case if everything goes to plan I will only have to carry the mortgage for 5 years in FI then it will be paid off.  So that will make my expenses go down and help with making sure my withdraw rate is successful.

If you are paying off cars and other loans you might not include those in the NW column and just put them each month as an expense.  Once they go away your savings rate will go up.  Your goal is to spend as little as possible so having no car payment is what you want.  If you calculate it as an expense you can see in the FI sheet how many years that cost is costing you towards FI.  For instance I still have cable and it shows that it costs me 0.57 years towards fire.  I could be independent 0.57 years earlier if I dropped cable or something lower as more than likely I would keep internet.

- I know that there is Mint integration, but is it necessary? I want to be able to enter the numbers manually, but I don't know if it affects any other calculations.

Nope if you don't use it just delete the sheet and enter manually.  The way I operate I don't really use cash and put on my daily spending on an Amazon rewards cards.  Mint makes it easy to categorize the expenses, total them and then just cut and paste them into the sheets expense for the month.  I don't even link mint to my bank accounts as the only spending done from them is a few bills and mortgage.  I just enter those manually in each month's sheet.

- I changed the Discretionary Expenses fields to include just the debt I'm paying off, but it's included in years to FI. Is it smart to leave it in, since I don't plan to retire before the debt is paid off, and just be able to breath a sigh of relief when I see the debt removed and the drop in FI? (I think I just answered my own question, but another perspective would be nice.)

You did answer your own question!  Keep it in there let the "expense" show you how many years towards FI that it contributes in years.  It is a good way to remind you not to go take on that new car loan.  Just like I keep my mortgage payment in there as an expense that I plan to carry for 5 years in FI.  Insurance and property taxes of course aren't going away so I have to plan on having my stash cover those at my calculated withdraw rate.

- I'm not that familiar with Google Sheets, but is there a way to integrate a snowball or avalanche debt payoff into this? This kind of goes with my question above, and I've got a payoff plan from undebt.it but don't know how to see how it would affect years to FI.

Any help would be appreciated.

I can't think of one.  You can put the debts under the NW tab and just watch your NW become more positive (even if it is just getting less negative as you mentioned).  I just never really had debt besides a mortgage as I pay off credit card accounts each month so I never really designed the sheets to handle negative NW.  I am sure it will screw up some calculations.  Maybe I'll have to think about it to see if I can make the FI calculation work with a negative NW.  Let me know if you come up with any ideas as I am all for input from others.

ysette9

  • Walrus Stache
  • *******
  • Posts: 8930
  • Age: 2020
  • Location: Bay Area at heart living in the PNW
Re: One sheet to rule them all...
« Reply #87 on: July 04, 2016, 05:02:25 PM »
Just downloaded to play with. At first glance this looks like an awesome interface; thank you for putting this together and providing it to the world!

Little typo: Sheet Instructions, Cell A8, Is "Hopefully there is enough rows...." Should be "Hopefully there are enough..."

Other little typo: Sheet Jan (and other months), Cell A104, Is "Paycheck #1", Should be "Paycheck #2"

Cheers!
« Last Edit: July 04, 2016, 05:06:31 PM by ysette9 »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #88 on: July 04, 2016, 05:20:47 PM »
Should be updated.  Thanks for the catches!

Monica21

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #89 on: July 05, 2016, 01:36:09 PM »
Thank you for the detailed reply!

Annual savings is all calculated on the FI sheet at B4 - B6 and it depends on if you 'Use All Time' is True or False.  If it is True it uses your average income and spending from the NW sheet (E1 and F1).  If it is False it uses the average of the data that is auto populated by date down in the Totals section on the FI sheet (B57 etc).  This data is pulled from each months sheet as the month finishes so you should update the data on the month sheet at the end of the month for it to be correct.  If it is set to False you  are only calculating from the current year's data.

If it's pulling from the NW sheet, and your Net Worth is negative then I guess I have to wonder how much it throws off the Years to FI calculation. Because if I trust the calculation now, I can just subtract the years I've got left in Debt from the Necessary Expenses and it makes it much more palatable. But I'm still not sure if that's an accurate estimate.

Quote
That is a good question.  Does the pension value change over time?  What you could do is make it a column in the NW sheet just like an investment account.  If the value of the pension changes as times goes on update it and it should reduce your FI time.  How does the pension work does it pay you a certain amount at a certain time?

The pension value  is a set amount based on a number of factors, so it shouldn't change. The only thing that would add to it is if I plan to stay in my job longer, and I don't because I want a higher paying job. But I don't know how to count it as not an investment but somehow include the monthly estimate.

Quote
It will but I am not sure how it will handle negative numbers.  I have never tested that.  I don't consider my mortgage debt so it isn't calculated in my NW.  The NW can either include the home equity or not depending on if you select True or False.  Some people plan on downsizing and selling their house and get something much cheaper so equity would count for them.  I plan on staying where I am at so I don't include equity as NW as it isn't liquid and can't help me in FI.  So on the FI sheet I have Include Equity set to False.

I'm not calculating my mortgage in my net worth and I have it set to False on the FI sheet. And the more I try to look at the nuts and bolts the more confused I get. The monthly tabs has the Savings Rate pull the Unallocated Cash field, and I'm not sure why. I'm using the Unallocated Cash field as a Flex amount, and not necessarily what I would put in savings. It looks like the sheet is assuming that I would put all Unallocated Cash into Savings instead of using the Investments.

Quote
If you are paying off cars and other loans you might not include those in the NW column and just put them each month as an expense.  Once they go away your savings rate will go up.  Your goal is to spend as little as possible so having no car payment is what you want.  If you calculate it as an expense you can see in the FI sheet how many years that cost is costing you towards FI.  For instance I still have cable and it shows that it costs me 0.57 years towards fire.  I could be independent 0.57 years earlier if I dropped cable or something lower as more than likely I would keep internet.

I like seeing the increase in Net Worth, but I understand the logic behind this.

Quote
Nope if you don't use it just delete the sheet and enter manually.  The way I operate I don't really use cash and put on my daily spending on an Amazon rewards cards.  Mint makes it easy to categorize the expenses, total them and then just cut and paste them into the sheets expense for the month.  I don't even link mint to my bank accounts as the only spending done from them is a few bills and mortgage.  I just enter those manually in each month's sheet.

Okay, because I have the necessary expenses, debt payoff, and savings, and then the rest is my Flex fund, which is just a cash withdrawal I make. And when that's gone it's gone.

Quote
I can't think of one.  You can put the debts under the NW tab and just watch your NW become more positive (even if it is just getting less negative as you mentioned).  I just never really had debt besides a mortgage as I pay off credit card accounts each month so I never really designed the sheets to handle negative NW.  I am sure it will screw up some calculations.  Maybe I'll have to think about it to see if I can make the FI calculation work with a negative NW.  Let me know if you come up with any ideas as I am all for input from others.

The only way I can think to do something like this is to look at the plan and if the snowball is X years long, then subtract X years from current FI, but I don't know if that would be accurate.

Landlady

  • Stubble
  • **
  • Posts: 248
  • Location: WA
Re: One sheet to rule them all...
« Reply #90 on: September 09, 2016, 06:33:32 PM »
What a beautiful sheet. Thank you so much for sharing.
Any tips for how I can make this work for me if I have two mortgages? (I have two rental properties)

TheFirstMan

  • 5 O'Clock Shadow
  • *
  • Posts: 51
Re: One sheet to rule them all...
« Reply #91 on: September 15, 2016, 08:45:19 AM »
Two quick questions!

1. What is "Adj income + deductions 1"? I have my "Adjusted Pay" from down below on the monthly sheets. But what do I add back to it, the the stuff like Medical/Dental or stuff like Federal/OASDI?

2. I can't enter my 5/1/16 refi as the start date on the mortgage tab--it takes the dial on the dashboard to 0. Is there some underlying equation that can only accept a date up to earlier in 2016?

Thanks! Fun stuff!
« Last Edit: September 15, 2016, 08:46:58 AM by TheFirstMan »

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #92 on: September 15, 2016, 07:20:21 PM »
What a beautiful sheet. Thank you so much for sharing.
Any tips for how I can make this work for me if I have two mortgages? (I have two rental properties)

It would take some major tear up but you could do it.  The best way is maybe to insert 2 columns in the NW sheet and just manually put in your equity on the rentals.   That would be the quick way.

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #93 on: September 15, 2016, 07:30:53 PM »
1. What is "Adj income + deductions 1"? I have my "Adjusted Pay" from down below on the monthly sheets. But what do I add back to it, the the stuff like Medical/Dental or stuff like Federal/OASDI?

2. I can't enter my 5/1/16 refi as the start date on the mortgage tab--it takes the dial on the dashboard to 0. Is there some underlying equation that can only accept a date up to earlier in 2016?

1. It takes your adjusted income and adds back in any 401k, 401k match, IRA, HSA and medical.  You need to make sure to include your medical as an expense under C20.  Basically it leaves out any taxes and other deductions that are "gone".  This number in Adj income + deductions 1 is calculated automatically.  You only need to fill the stuff in down bellow under paycheck #1.

2.  Since you are starting in 2016 it screws the sheets up from Jan - Apr (there is no mortgage data in mort sheet for those months).  Just enter 0 in K14 for the Jan - Apr sheets and you should be good.  Next year when you create the new year sheet remember to paste the formula from May back to Jan - Apr so the calculations work again. 

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #94 on: September 15, 2016, 07:53:16 PM »
What a beautiful sheet. Thank you so much for sharing.
Any tips for how I can make this work for me if I have two mortgages? (I have two rental properties)

You might also get a hold of kellyincville from a few posts above.  It looks like they modified the sheet to work with rentals.  Maybe they will share their mods with you.

tralfamadorian

  • Handlebar Stache
  • *****
  • Posts: 1217
Re: One sheet to rule them all...
« Reply #95 on: September 16, 2016, 07:24:27 PM »
...(I have two rental properties)...

Here are the changes I made to accommodate my multiple properties-

1- I copied the mortgage tab so there was a tab for each property I owned.  I also changed the tab label to be the name of that property and added a few additional data calcs for my own use- cash invested, cash on cash return and current estimated cash out equity.

2- On the monthly income tabs I changed Paycheck #2 to be Rental Income and Pay Period 1, 2, etc to be each rental property's monthly cash flow.

3- On the NW tab, I copied the income column and made a rental income column right next to it.  I copied the home equity column the same way, then changed the sheet that the added column referenced to to the corresponding property's mortgage tab.

4- On the FI tab, I split into goal retirement income into monthly real estate cash flow and 4% withdrawal from stock investments.

5- On the dashboard tab, I copied the odometer and had the second one pull data from the real estate cash flow data from FI.  I also copied the mortgage graph to mirror the number of rental properties and like on the NW tab, changed the sheet it was pulling data from to the property I wanted it to reference. 

I think the only place I got stuck was that I wanted both a chart of stock NW and a chart of NW that included property equity too.  I copied the NW chart but had to bypass the Include Equity toggle on the FI tab for the include equity chart. 

That's basically it.  Let me know if I can clarify anything.

dlawson

  • 5 O'Clock Shadow
  • *
  • Posts: 59
  • Age: 34
  • Location: Southern California
Re: One sheet to rule them all...
« Reply #96 on: December 06, 2016, 04:30:27 PM »
I'm loving this tool so far! Great work everyone. One quick question:

I use Mint and am trying to get the spending automation set up. When I use the Mint tab and download the transactions for the month, Mint spits out all my Income transactions (Paycheck, Miscellaneous, etc) and transactions related to automated savings and paying off my credit cards at the end of the month (Transfer, Credit Card Payment, etc). These are then scooped up in the Total From CSV field and leaves it significantly higher than Total Calculated.

Anyone know of any workarounds for this? Can I exclude income and/or certain types of transactions (like Transfer) from the download parameters when I pull the CSV?

wienerdog

  • Pencil Stache
  • ****
  • Posts: 587
Re: One sheet to rule them all...
« Reply #97 on: December 06, 2016, 06:51:50 PM »
It would take a little doing as I know that is weak point.  At this time I don't use mint with my bank account and only use cards so I only see the credit card payments that screw up the total.  I usually just delete those lines but I only have like two a month unless something gets credited back to my card.  One thing don't right click and delete as it deletes a line in the formula.  Eventually the formula won't look at enough lines.  I usually just highlight the credit lines and use the delete key on the keyboard to keep it blank. 

Let me think about it and maybe I'll have some time over the holiday to puts a change together.  Probably should put an AND in each if statement to make sure it isn't a credit.  I think it does spit out a column that says credit or debt doesn't it?  It might be easier to write a script to process it as the formulas I think are getting kinda long.

dlawson

  • 5 O'Clock Shadow
  • *
  • Posts: 59
  • Age: 34
  • Location: Southern California
Re: One sheet to rule them all...
« Reply #98 on: December 07, 2016, 09:46:00 AM »
Yes, there's a transaction type column with debit/credit listed. But excluding that would still leave the debit half of the credit card payment, transfer, etc.

For now, it's easy to just sort by Category and then remove any Transfers, CC Payments, Income, etc. before copying the list into the FI sheet. Even with that bit of extra work it's still way faster than adding all my expenses manually every month! No worries if you don't find a solution (or have time for it).

IndyPendent

  • Stubble
  • **
  • Posts: 100
Re: One sheet to rule them all...
« Reply #99 on: December 09, 2016, 04:18:14 PM »
Just posting to say that I'm a curmudgeonly old man who manually totals, copies, and pastes my categories each month from Mint because I actually like doing it. None of this automated stuff.

:D