The Money Mustache Community
General Discussion => Share Your Badassity => Topic started by: IndyPendent on June 12, 2015, 07:17:07 PM
-
In a few older threads (this one (http://forum.mrmoneymustache.com/welcome-to-the-forum/share-your-financial-spreadsheets!/) and this one (http://forum.mrmoneymustache.com/share-your-badassity/anybody-feel-like-sharing-their-%27badass%27-spreadsheet/)), there were financial spreadsheets shared and lots of great nerd-ery ensued. In one thread (http://forum.mrmoneymustache.com/mustachianism-around-the-web/obnoxiously-good-with-money/) in particular, I shared my personal finance dashboard and got so much interest I've scrubbed the numbers and provided instructions.
For your viewing pleasure, I've attached a sample of the dashboard.
(http://i.imgur.com/BIMq9kx.png)
The master copy can be found here.
FI Tracker v3 (https://docs.google.com/spreadsheets/d/1T6RgJnzZo1bdGHA0a5JBQqqSSEPX2xDrCT5zioWr9z8/edit?usp=sharing)
If you want to use this for yourself, go to File->Make a Copy. If you have questions, post them here! I hope some of you gain some value from this.
Edit 23-May-2016: As this thread has grown, fellow forum member and Google Sheets nerd Weinerdog has taken the original sheet and made some very useful tweaks and optimizations. His FI Tracker v3 (and prior to that, v2) is a branch that allows copy/paste from Mint, reduces visual clutter, and provides more automation than the original. I think it's a better version, quite frankly. It was such an improvement that it's now the recommended link above.
Links to v2 (https://docs.google.com/spreadsheets/d/1v6ZoC_TssTs-YNFHM0u6s9pv466i2rFGVrK_2zcXAfA/edit#gid=768252897) and v1 (https://docs.google.com/spreadsheets/d/1sQI_pmv1qtQTvWX8oj-aTB7GIRUGxAYKloEFvy-nViU/edit?usp=sharing) for historical purposes.
-
That is a sweet spreadsheet!! I really like the dashboard but it must be a pain in the butt entering all that data.
I keep track of my net worth as well but not an awesome spreadsheet like yours. Mine is super simple and just keeps track of current month and last month for comparison reasons.
-
Yep, it's work to enter but the result is worth the effort! It won't be for everyone, for sure.
Sent from my iPhone using Tapatalk
-
Thanks! This is way better than mine.
-
Thanks very much! I have changed the colours around in the net worth dial on the dashboard, as I think the greatest net worth should be green, and the least red.
-
Thanks very much! I have changed the colours around in the net worth dial on the dashboard, as I think the greatest net worth should be green, and the least red.
Great idea! I didn't really pay attention to the colors, but you're right. Green definitely belongs toward the end of the progress. :)
Edit: I've now updated the master copy to flip the colors. Nice suggestion! I also added a couple of additional details about the savings rates conditional formatting.
-
Indy thanks for the great spreadsheet! I have found it very useful and well put together. I have been tinkering with it a little and made a few changes. If you don't mind I will share the edited Home Payoff sheet that calculates the loan from the terms. I followed Indy's lead and left boxes that you fill in non colored. I put a little bit of instructions in as I had a loan in time that I knew the value and I didn't want all the previous months cluttering the sheet up. The instructions explain it good enough I hope. You can just as well start from month one.
The loan balance and equity should auto update at the beginning of the month. All you need to do is put the extra payment in if you pay additional principal each month. You can put that value in ahead of time if you pay a fixed amount. I did some other updates where it will pull the value for principal and the additional payment into each month's tab to save you the labor. I can share those changes but I want to smooth them out a little more.
Anyway thanks to Indy for sharing this great spreadsheet as I just made a few minor tweaks. If you want me to take the sheet down let me know and I will delete it.
https://docs.google.com/spreadsheets/d/1lDEIs0ODvVqOW9FtB8o2Jvott4jF2ms2PJbXREBguHk/edit?usp=sharing
-
Indy thanks for the great spreadsheet!
[...]
Anyway thanks to Indy for sharing this great spreadsheet as I just made a few minor tweaks. If you want me to take the sheet down let me know and I will delete it.
[...]
Right on! No need to remove it, I think it's great if folks find your version useful. A couple of notes:
1) Cell E8 is static, which for me was a bit confusing until I copy/pasted the formula from another cell. The values down-line were borked until I did that.
2) Cell C1 should (presumably) be white, as the user should update that periodically
3) If you really wanted to be fancy-pants, you could have an amortization schedule calculator factored into your monthly amounts--technically the principle and interest amounts will change monthly, especially when you are making large extra payments.
The original is definitely more basic--I check my payoff amount manually each month and update the value. For me, updating the payoff amount is actually a nice little shot of dopamine and a reward for my frugality! :)
I've added a link to your fancy-pants version in the original template, in case others want your version--nice work!
-
Indy,
Thanks for the feedback. I tried to cut down on the confusion with the instructions on the right. After I looked at it again I figured there was a better way to do a certain point in time or start the table somewhere that is not at the beginning of the loan. I updated the spreadsheet with your feedback. I think it is more complete now and I took in account for the added payment in the principal calculation.
Thanks again for the original and the great feedback!
-
I'm not the most tech savy. How do I get a copy of it downloaded?
-
I'm not the most tech savy. How do I get a copy of it downloaded?
Hummer you should have a google account and be logged in. The link in the first post will direct you to the shared copy of the sheet which you can't edit. Under the File menu option select "make a copy..." and you will save a copy to your google drive that you can edit.
-
Figured it out. Thanks
-
For some reason, I don't have a pie chart or a needle on my dashboard page. I don't think I accidentally deleted anything.
-
Awesome resource! Pinned :)
-
For some reason, I don't have a pie chart or a needle on my dashboard page. I don't think I accidentally deleted anything.
What web browser are you using?
Sent from my iPhone using Tapatalk
-
Chrome but I'm not using a browser. After I made a copy, I downloaded it as an excel worksheet. I've been using it from my desktop.
-
I don't think excel has the needle graph type. It does, however, have pie charts.
-
Shoot. That never even occurred to me. I assumed the master copy was an excell spreadsheet. Now I see it is a google spreadsheet.
-
Oh my.... Indy, I think I love you. I am just starting off spreadsheeting my finances in full for the first time and am only the most basic of Excel users, so this is going to save me a ton of trouble. Thank you!!
-
Love it! Finally cleaned it up with all my digits. And incorporated my 'annual snapshot of planned expenses' sheet into it from my old spreadsheet. Now I got everything I need umbrella'd into one spreadsheet. Thanks!
-
Thanks for this. It's freaking awesome!
-
Thanks for this. It's freaking awesome!
You, and everyone else, are certainly welcome! I would love to hear how people are using this and how we could potentially improve the template!
-
@Indy THANK YOU!
I only have open office so I hope I can make it work. I really would love to have an all encompassing spreadsheet with color graphics. Hope I can figure it out. Spreadsheets are not my strength, glad you gave Hummer the instructions for the google download.
-
Awesome! Following and will surely enjoy.
-
@Rosy, I've not tested any versions in Open Office, but I would be interested in your feedback. What works and what doesn't?
-
Interesting sheet. Not sure if I'll use it or not versus what I'm doing now, but thanks for sharing :)
Edit: I suppose I should ask - is there a faster way to "reset" the sheets by clearing out data other than manually going through and deleting cell values?
-
Probably the best would be to edit Jan how you like it. (expenses, investment accounts etc) Then copy that one to duplicate it and rename for the rest of the months.
Edit: Actually I am not sure if that won't break something else as I haven't done it that way. I just modified the section I wanted on June. Then copied each section to each month.
-
Probably the best would be to edit Jan how you like it. (expenses, investment accounts etc) Then copy that one to duplicate it and rename for the rest of the months.
Edit: Actually I am not sure if that won't break something else as I haven't done it that way. I just modified the section I wanted on June. Then copied each section to each month.
Yep, that's pretty much how I do it too.
-
1. For each month, I created a new row that captures employer 401K match (F34, G34, H34, I34) and then added that sum to the formula in F11. Is there any reason to not do this?
2. I also created a net income row (B6 and C6=-(F37+F38+F39)) since I think of my budgets in terms of take-home on a day-to-day basis.
Thanks for putting this together !
-
1. For each month, I created a new row that captures employer 401K match (F34, G34, H34, I34) and then added that sum to the formula in F11. Is there any reason to not do this?
I did the same. I then take the total and use it under C12 and I labeled that row 401k match. I then added it to the sum under C14 as I consider that spent on investments.
One other thing I plan to do is to automatically populate all the spending areas toward the bottom left. Right now I just log a daily spreadsheet of every item I spend and categorize it. At the end of the month I will just copy that sheet to a blank on the FI Tracker and then have a script total each category and place it in the proper slot for the month.
I have also been looking at certain apps that makes entering the expenses easier with an export feature. Might have to roll my own eventually. Just want it simple where you enter the amount and select the category. It enters the current date or lets you change to the date the expense occurred.
Can't thank Indy enough for providing an excellent base to customize.
-
Nice work on the spreadsheet! I'm playing with it now.
-
I got another turn of Indy's spreadsheet if anyone is interested in taking it for a spin. Best if used in Google Sheets as that is all I have used. I gave his first spreadsheet a little twist and added the Mad Fientist's FI sheet in as I found that a good tool to look at what expenses cost in time and calculate when FI should arrive. Of course I put my little spin on some parts of each but most credit goes to Indy and Mad Fientist. (Not sure if he/she posts here?) http://www.madfientist.com/ (http://www.madfientist.com/)
I am open for feedback if anyone sees something wrong or something isn't clear. I removed Indy's dashboard sheet and the investment sheet as those need cleaned up yet. When I get time I will add them back in. I am also open to feedback if you got an idea to make it better. I tried to include plenty of different types of accounts and plenty of expenses so you can use what what you need. Just leave them blank or if you want shorten the sheet up by deleting the rows you don't need under the expenses that should work. (You will have to do it on each monthly sheet and the FI sheet so they all match. You will only use the delete row feature and the formulas should all move up. Be sure to keep the totals at the bottom of each expense bundle.)
A bunch of fields update automatically or don't appear until after the first of the month. Your expenses won't get averaged into the FI calculation until the first of the next month but the way it calculates it should take a running average for you over the year. At the end of the year you should be able to save it as a new sheet and update just the Jan 1, 2015 date to Jan 1, 2016 and the rest of the monthly sheets and the FI sheet will update for you.
At the first of the month if you update the accounts under the NW sheet then it should update your total net worth for you. On the FI sheet there are two True and False fields. In the Mad Fientist's FI sheet he/she included home equity in the calculation. To me that didn't seem right since it isn't liquid unless your plan is to move and to use that cash then. I put an easy way to go back and forth so not sure if that is useful to everyone? The equity gets pulled automatically from the mortgage page each month. If you set it to False it won't calculate the principal into the monthly savings rate. You can make true or false the field that uses the budget for the FI calculation. You then can adjust the budget numbers on the FI sheet and set it to true to see how the FI time differs from your real expenses or costs compared to a proposed budget.
Link to sheet: FI Tracker V2 (https://docs.google.com/spreadsheets/d/1v6ZoC_TssTs-YNFHM0u6s9pv466i2rFGVrK_2zcXAfA/edit?usp=sharing)
ToDos yet:
Get Indy's Dashboard back in
- Get Indy's investment sheet back in
- Make it easier for people to start out by replicating Jan to all the months. (Cut and paste isn't bad now? Any feedback on this?)
- Get a way to automatically update expenses each month from an export from programs like Mint
Edit: For updates to the sheets.
Most of the dash is back. Just need allocation pie charts once investment sheet is in.[/list]
-
I forgot one other item I would like to get added in is to track over time on how much dividend/interest income starts to come from the investments. Of course the ones in tax advantaged accounts would be easy as you just need to update totals but ones in taxed accounts will need to have some kind of taxes taken out of them. Maybe just tracking the totals so you know where they are at and more than likely the dividends are reinvested so they would get reflected in each accounts update at the beginning of the month.
-
I'm going to try the first spreadsheet out on my next NW update. Hopefully I can get it to work as I am a bit tech deficient, and don't have much (any) experience with Google Docs. Thanks!
-
Just tagging this thread, so I won't lose it. Thanks for the great tool!
-
I got another turn of Indy's spreadsheet if anyone is interested
[...]
Excellent work! I like a lot that you've done--you've cleaned up a lot of the things I had on my to-do list like simplifying the copy/paste work month to month, auto-referencing the mortgage sheet for equity, etc.
A couple of comments based off my experience with my version:
1) In the mortgage sheet, you auto-calculate the payoff balance, but I don't see any place where you take amortization into account. If I'm paying extra on the principle, doesn't that impact the principle to interest ratio on the remainder of the loan? Meaning, wouldn't I have a great proportion of principle in my next payments that is unaccounted for in the formula? This has been my experience, but perhaps I'm missing something. That's why I kept that sheet simple and didn't try to auto-calculate the balance. Instead I just check my balance (by phoning the bank) once per month and update it.
2) Nice catch on the second paycheck deduction table. Clearly I am a one income household. :)
3) I like your shortened months on the tabs.
4) Years to FI is a great stat!
5) YTD total savings is great too.
These are all really good tweaks to the sheet. I'm wondering if many of them don't belong in the master copy in some form of merge.
-
Thanks for the feedback Indy. See replies below:
1) Yea when you mentioned that the last time I didn't calculate it right. I didn't understand how it exactly worked and I wasn't using the current value of the loan left for each interest payment. (Not an accountant! LOL) I have calculated all that in now and double checked it with a financial calculator and it all calculates correct. Took me a bit but hey I understand it now. :>) If you want to play around I added another sheet that shows the complete table instead of only showing up to the current month. You can put different values in and that data won't be used anywhere else in the spreadsheet so don't worry about screwing anything up. Just a calculator to see what different values do.
What I did in the principal formula is the upper right hand table calculates principal and interest for the loan values you enter. That value stays constant over the life of the loan. For the current principal value I take the month before's loan balance and multiply it by the interest rate and divide that by 12. That gives me the interest for that month. I then subtract that from the principal and interest value in the upper right table to get the principal. If you then add additional principal it gets added to the principal and then that value is subtracted from last months loan value to get the current months loan value.
I hope that I explained that correctly and thanks for the feedback as the very first version I did was wrong.
2) I am a single income household also but figured it would be helpful for others! I moved it to the bottom as I didn't think people would update it that much and it should make it easier to insert or delete rows in the expenses since nothing is to the right.
3) Thank you.
4 & 5) Credit for these should go to Mad Fientist as that is all their doing. I just incorporated it into your sheet as I thought it was helpful and a great tool towards calculating when FI will hit. I probably should contact them to make sure they are okay with using it. Anyone know them or do they post here?
Probably should wait for some more feedback to see if it is easy for others to use? I am trying to make it easier so it isn't so much effort at the start but so far I think it is worth the trouble if you just take a little time and update the expenses that work for you. Then over time the actual expense can get entered. Thanks again for sharing the original!
-
4 & 5) Credit for these should go to Mad Fientist as that is all their doing. I just incorporated it into your sheet as I thought it was helpful and a great tool towards calculating when FI will hit. I probably should contact them to make sure they are okay with using it. Anyone know them or do they post here?
He gives it away for free on his site, so I don't see him having a problem with it especially since you gave him a shout out. I used the Mad Fientist sheet as the skeleton for my FI Tracker. I thought my previous sheet was great until I found his site. And seeing the sheet Indy created has given me even more ideas for things I would like to add to mine.
Keep up the great work guys!
-
Can I ask a quick question?
I've been stashing money into my discretionary fund and family emergency fund and they are saving goals in the spreadsheet.
Now I need to tap into those stash for some ad-hoc expenses. How do I account for those expenses in my monthly expenses spreadsheet?
-
Can I ask a quick question?
[...] How do I account for those expenses in my monthly expenses spreadsheet?
If you are referring to the net worth asset columns, whenever I have something that I have to use my emergency fund for, I simply deduct it from that asset in the net worth row for that month.
You can also make a note in the monthly spreadsheet in the miscellaneous spending area, but if you use cash sources other than income it will be a wash to your monthly savings.
Example: this upcoming September I know I will have some medical bills that I will use my HSA savings for. I have a net worth asset column that has my HSA balance. Once I pay that bill, I'll update the row on the net worth sheet to reflect my new HSA balance.
In the September sheet I will add two line items to the miscellaneous spending area: 1) for the bill itself 2) for the incoming transfer of cash from my HSA. These two cancel each other out, and as I'm not using my monthly income to pay that bill it doesn't impact the September savings. Make sense?
-
Can I ask a quick question?
[...] How do I account for those expenses in my monthly expenses spreadsheet?
If you are referring to the net worth asset columns, whenever I have something that I have to use my emergency fund for, I simply deduct it from that asset in the net worth row for that month.
You can also make a note in the monthly spreadsheet in the miscellaneous spending area, but if you use cash sources other than income it will be a wash to your monthly savings.
Example: this upcoming September I know I will have some medical bills that I will use my HSA savings for. I have a net worth asset column that has my HSA balance. Once I pay that bill, I'll update the row on the net worth sheet to reflect my new HSA balance.
In the September sheet I will add two line items to the miscellaneous spending area: 1) for the bill itself 2) for the incoming transfer of cash from my HSA. These two cancel each other out, and as I'm not using my monthly income to pay that bill it doesn't impact the September savings. Make sense?
That makes sense, but how do you record incoming transfer of cash from your HSA? As income?
-
I would not record it as income, as the appropriate time to do that would be when I initially earned or received the cash that I socked away in my HSA. The recording of the transfer takes place in the net worth tab--that assets value is reduced by the amount you transfer out.
-
Ok I probably sound like an idiot here, but how do you 'transfer into' the monthly expense sheet? I always saw the monthly expense sheet as a balance sheet (income - expenses = savings/excess spending). Does that make sense?
-
Here's what I did.
I took money off Net Worth, and transferred that money into Unallocated Cash to balance off the negative spending.
That allowed me to keep the additional expenditure in check, while not affecting the gross & net saving.
Let me know if this made sense
-
That works just fine! I think you did right there.
-
I got another turn of Indy's spreadsheet if anyone is interested in taking it for a spin. Best if used in Google Sheets as that is all I have used. I gave his first spreadsheet a little twist and added the Mad Fientist's FI sheet in as I found that a good tool to look at what expenses cost in time and calculate when FI should arrive. Of course I put my little spin on some parts of each but most credit goes to Indy and Mad Fientist. (Not sure if he/she posts here?) http://www.madfientist.com/ (http://www.madfientist.com/)
I am open for feedback if anyone sees something wrong or something isn't clear. I removed Indy's dashboard sheet and the investment sheet as those need cleaned up yet. When I get time I will add them back in. I am also open to feedback if you got an idea to make it better. I tried to include plenty of different types of accounts and plenty of expenses so you can use what what you need. Just leave them blank or if you want shorten the sheet up by deleting the rows you don't need under the expenses that should work. (You will have to do it on each monthly sheet and the FI sheet so they all match. You will only use the delete row feature and the formulas should all move up. Be sure to keep the totals at the bottom of each expense bundle.)
A bunch of fields update automatically or don't appear until after the first of the month. Your expenses won't get averaged into the FI calculation until the first of the next month but the way it calculates it should take a running average for you over the year. At the end of the year you should be able to save it as a new sheet and update just the Jan 1, 2015 date to Jan 1, 2016 and the rest of the monthly sheets and the FI sheet will update for you.
At the first of the month if you update the accounts under the NW sheet then it should update your total net worth for you. On the FI sheet there are two True and False fields. In the Mad Fientist's FI sheet he/she included home equity in the calculation. To me that didn't seem right since it isn't liquid unless your plan is to move and to use that cash then. I put an easy way to go back and forth so not sure if that is useful to everyone? The equity gets pulled automatically from the mortgage page each month. If you set it to False it won't calculate the principal into the monthly savings rate. You can make true or false the field that uses the budget for the FI calculation. You then can adjust the budget numbers on the FI sheet and set it to true to see how the FI time differs from your real expenses or costs compared to a proposed budget.
Link to sheet: FI Tracker V2 (https://docs.google.com/spreadsheets/d/1v6ZoC_TssTs-YNFHM0u6s9pv466i2rFGVrK_2zcXAfA/edit?usp=sharing)
ToDos yet:
Get Indy's Dashboard back in
- Get Indy's investment sheet back in
- Make it easier for people to start out by replicating Jan to all the months. (Cut and paste isn't bad now? Any feedback on this?)
- Get a way to automatically update expenses each month from an export from programs like Mint
Edit: For updates to the sheets.
Most of the dash is back. Just need allocation pie charts once investment sheet is in.[/list]
Sweet spreadsheet! One issue I am running into... I entered the mortgage info and when it refreshed, month 2+ has now cleared. What did I do wrong? I am a bit new to google sheets... Thanks.
-
I haven't been on much so I just saw the mortgage question. The mortgage sheet is only designed to show the current month after the 1st of the month occurs. The mortgage calculator shows the total payment layout. So if you look at the example sheet you see the loan started on in May of 2012. You can scroll down and it only shows Oct 2015. If you check it again after Nov 1 it will then show November. I did it that way as it sheet takes the last value in the column and adds it into the NW and a few other calculations so it updates for you each month.
Does that help? If you can give me some more details on what you entered I can check it.
I have updated the investment page that Indy had so it now updates the value of the stock automatically. I will see if I can get some time this week to push that page to the posted version.
-
uh...duh! that makes sense. Thanks Wiener... if I may be so bold... ;)
I just signed on to personal capital, against my inner voices... and this spreadsheet is a good supplement to it.
-
Thanks for the tool Indy and WD!
-
The blank FI tracker has been updated for 2016!
I've updated the formula on the YMOYL tab to use the 2016 months in the Net Worth tab.
I also fixed one bug in cell C13 on the YMOYL sheet.
-
Following.
-
I updated the alternate version of the FI Tracker for 2016 and added a couple more items. Link to sheet: FI Tracker V2 (https://docs.google.com/spreadsheets/d/1v6ZoC_TssTs-YNFHM0u6s9pv466i2rFGVrK_2zcXAfA)
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.
-
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.
-
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:-)
-
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.
-
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!
-
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.
-
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
-
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.
-
thanks:)
-
thanks:)
You're the guinea pig. Let me know what I screwed up. LOL
-
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:
=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
-
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?
-
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!
-
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?
-
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.
-
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
-
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!
(http://i.imgur.com/BIMq9kx.png)
*Edit: Fixed broken in-line image. D'oh!
-
wow cool!
-
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 ....
-
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.
-
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
-
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
-
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.
-
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.
-
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.
-
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!
-
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!
-
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?
-
*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.
-
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
-
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.
-
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!
-
Hello there,
Thanks for sharing this body of work Indy. I am new to google docs. Can you post the link to v3?
-
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.
-
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
-
- 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.
-
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!
-
Should be updated. Thanks for the catches!
-
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.
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.
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.
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.
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.
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.
-
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)
-
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!
-
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.
-
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.
-
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.
-
...(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.
-
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?
-
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.
-
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).
-
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
-
This is the dumbest question ever but I know on the January tab, I should put in December's expenses. But I put in January's paychecks?
-
What's the difference between "discretionary" and "monthly excess" categories?
-
Whooo! Found this at the perfect time for the new year! Definitely going to supplement this to my other spreadsheet and hopefully eventually move to one. Currently it takes me around 15-30 mins to pay all my bills and record everything on my other one but this one looks like it's going to take much longer, I guess something to try with this coming new year!
Thanks OP!!
-
This is the dumbest question ever but I know on the January tab, I should put in December's expenses. But I put in January's paychecks?
Never a dumb question. You should put January expenses and paycheck in the January tab. The sheets don't technically use that data until the 1st of the next month. So Jan data gets calculated in on Feb 1. If you look at the FI tab and scroll down you can see it is always a month behind as you can't calculate the data until it is all in at the end of the month. That is why it updates on the 1st.
Also on the 1st you will update all your accounts on the NW sheet so it takes a "snapshot" at that point in time of each account's value. Notice on the NW tab your expenses do lag one month behind as you can't calculate those until the end of the month. So on the 1st of Feb you put in all account values on that date and add the expenses to the Jan row. The NW tab keeps track of all your account balances and expenses over time even when you start over for a new year. That data is much of the data that is used on the dashboard.
To start over in Jan just "Save as" "Make a copy..." a new sheet and call it FI Tracker 2017. Then you just need to update the Jan sheet to 1/1/2017 and the rest of the sheets will update and all the data in the FI sheet will go blank until the first of the month.
What's the difference between "discretionary" and "monthly excess" categories?
Nothing really they are just left over stuff that you try not to spend on. I think they were divided that way in the original sheet and it has just remained.
-
Whooo! Found this at the perfect time for the new year! Definitely going to supplement this to my other spreadsheet and hopefully eventually move to one. Currently it takes me around 15-30 mins to pay all my bills and record everything on my other one but this one looks like it's going to take much longer, I guess something to try with this coming new year!
Thanks OP!!
Keeping up with two might take a little longer. This one looks complicated but it isn't actually that bad. What I do is when I pay my electricity bill online I just pull up the sheet and enter the value for that month. Anytime I pay a bill I just enter it at that time. It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.
I don't use Mint to track my bank account only credit cards so when I use the bank account to pay bills I just manually enter it into the sheet. My day to day spending like groceries, gas etc all get put on a card and I use Mint to sort those and total them for me then I just cut and paste to the current months expense sheet at the end of the month.
For the new year as mentioned above just open the sheet and "Save as" "Make a copy..." and name it FI Tracker 2017. Update the date on the Jan sheet cell A1 to 2017 and you should be good to go. If you you have old monthly data you could put those in the NW sheet for the respective dates or just change the first row to 1/1/2017 and the second to 2/1/2017. Cut and paste those two on down that column and you should have all the new dates for the next months to come. Mine goes out to 2025 I think right now. I haven't done that for a while so I would be interested to see how that goes. I think the color formatting for the year will be off so that might need to be redone.
-
It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.
I'm the same way. I use a version of the "one sheet to rule them all" and definitely enjoy updating the sheet each month. I read your post to my wife and she laughed.
-
It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.
I'm the same way. I use a version of the "one sheet to rule them all" and definitely enjoy updating the sheet each month. I read your post to my wife and she laughed.
A couple of times I have updated the sheet after the end of business say on the 31st but the sheet won't update until midnight. If I happen to wake up at 3am normally you would just roll over and go back to bed but on the 1st you have to get up and see what the sheet did since you went to bed. lol Like a kid at Christmas every month!
-
Thanks wienerdog! Is there anyway to force an update to the sheet? I've just put in almost all of my 2016 data (minus December) and was eager to see what it looks like, but I guess I can wait a few days if I have to.
-
The NW tab drives most of the data. If you want to cheat change the date in column A of that sheet. If you have all your data in there for December 2016 (column A will be 1/1/2017) then change 1/1/2017 to today's date and it will update it for you or just wait until midnight tonight. lol
-
I feel dumb, can't get this to pull into my googlesheets as editable?
*nevermind just took me 10 minutes to figure out...lol
-
Thank you so much for this awesome sheet! As someone who doesn't spreadsheet often, I have a question that's probably super basic...
I have a lot of categories in Mint, so that I can keep really close tabs on where my money goes. All of my categories have many sub-categories. Most of the sub-categories have only one or two items throughout the year. So what steps should I take to either:
a) add more lines to the expense categories on the FI page, or
b) export the CSV from Mint so that it only includes top level categories (e.g. just "Education" rather than "Tuition", "Books and Supplies", "Tutorials")
Thanks!
-
There are a couple ways to do it. Under the mint tab for each category under the dollar value there is this formula. This example uses the pet category:
=sumif(F$45:F$98,"Pet Food & Supplies",D$45:D$98)
The formula will look through lines 45 - 98 and total the value from the D column if the F column matches the text "Pet Food & Supplies".
If you wanted to sum a couple different categories from mint under one category in the FI sheet you would add additional fields to the formula. For example for Pets:
=sumif(F$45:F$98,"Pet Food & Supplies",D$45:D$98) + sumif(F$45:F$98,"Pet Grooming",D$45:D$98) + sumif(F$45:F$98,"Veterinary",D$45:D$98)
This new formula would total all the categories under Pet in mint to the FI sheet category. Note it only scans 43 lines that you paste in the mint sheet at the bottom. If you have more than 43 entries for the month you might change the second number to 200 in every formula under the categories. That will make sure it picks up all the entries.
That route is probably easier but then you lose all the categories for fine tracking. If you wanted to insert more categories I think it isn't too bad as the formulas should adjust. I haven't tried this but did do a quick check to see if it seems to work.
If you go to line 28 on the FI sheet that is the Pet category. (you can pick anywhere I just using the pet as an example) If you click on the row 28 and highlight the whole row. Then up to Insert up in the menu bar and Row below. That will insert one blank row below the pet category. If you want more new categories you can hit Ctrl + y nine times and that will repeat what you just did 9 more times. So you get a total of 10 new rows. You will then need to add titles to them.
Once you have the FI sheet complete then you need to go to each months sheet and insert the same number of rows. You then need to copy the formula from right above where you inserted the row (in my example you would copy the Pet formula and then paste it to the new rows. Once you do that then the categories should match what you entered on the FI sheet.
Then you have to do the same thing in the mint sheet and update the formulas similar to what I did in the first example so the new categories match your mint categories.
I hope that helps.
-
Thank you, worked like a charm! Seriously, the dashboard needle is just awesome to see.
-
Thank you so much for this sheet, it is so amazing and exactly what I need. I had one question, more just looking for opinions:
I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis. So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck. Then every quarter I purchase and then immediately sell said stock. This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that? Or does it even matter on the monthly tabs? I originally added it as a third paycheck, but I can't decide if that is correct. Also, if I dont add it in it seems like it affects my savings rate numbers.
Just curious what people's thoughts are.
Thanks!
-
Thank you so much for this sheet, it is so amazing and exactly what I need. I had one question, more just looking for opinions:
I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis. So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck. Then every quarter I purchase and then immediately sell said stock. This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that? Or does it even matter on the monthly tabs? I originally added it as a third paycheck, but I can't decide if that is correct. Also, if I dont add it in it seems like it affects my savings rate numbers.
Just curious what people's thoughts are.
Thanks!
The way you are doing it now it gets calculate in the savings rate as it probably falls into unallocated cash every 3 months. This basically is the difference between the total income - taxes - investments - mortgage - charity. But technically you are saving that amount every month so it is better to do as below.
It is a deduction but it sounds like you get the money eventually but it is always yours that you saved. To me it should be included in the saving rate each month. That is really all the monthly sheet is calculating for you. It totals what you bring in, what goes to taxes and what goes to savings (401k, mortgage, cash left over etc). What I would do is create a formula under one of the investment categories and sum the totals of the deduction from the your paycheck each month. Putting that total up under one of the 6 investment categories would make sure it gets calculate in for your savings rate when it was actually saved.
If you need help ask or look at how the 401K is done as a deduction. It takes the deductions for all the paychecks then adds in the employer match and totals that at the very end of each check. You could insert a new line for your special deduction and then use one of the investment categories to set that box equal to your total from down bottom in the check area.
Since it is totaled in for the month it will be calculated in. Every quarter then I would assume you see a jump on your NW tab of that amount documented under one of the accounts. You are saving that amount every quarter somewhere right? :>)
-
So, quick high-level question:
Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.
Or am I doing something wrong?
-
Thank you so much for this sheet, it is so amazing and exactly what I need. I had one question, more just looking for opinions:
I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis. So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck. Then every quarter I purchase and then immediately sell said stock. This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that? Or does it even matter on the monthly tabs? I originally added it as a third paycheck, but I can't decide if that is correct. Also, if I dont add it in it seems like it affects my savings rate numbers.
Just curious what people's thoughts are.
Thanks!
The way you are doing it now it gets calculate in the savings rate as it probably falls into unallocated cash every 3 months. This basically is the difference between the total income - taxes - investments - mortgage - charity. But technically you are saving that amount every month so it is better to do as below.
It is a deduction but it sounds like you get the money eventually but it is always yours that you saved. To me it should be included in the saving rate each month. That is really all the monthly sheet is calculating for you. It totals what you bring in, what goes to taxes and what goes to savings (401k, mortgage, cash left over etc). What I would do is create a formula under one of the investment categories and sum the totals of the deduction from the your paycheck each month. Putting that total up under one of the 6 investment categories would make sure it gets calculate in for your savings rate when it was actually saved.
If you need help ask or look at how the 401K is done as a deduction. It takes the deductions for all the paychecks then adds in the employer match and totals that at the very end of each check. You could insert a new line for your special deduction and then use one of the investment categories to set that box equal to your total from down bottom in the check area.
Since it is totaled in for the month it will be calculated in. Every quarter then I would assume you see a jump on your NW tab of that amount documented under one of the accounts. You are saving that amount every quarter somewhere right? :>)
Perfect, thanks for the detailed explanation. Much better than what I had. And again thanks for your contributions to this sheet.
-
So, quick high-level question:
Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.
Or am I doing something wrong?
I think you're looking at it wrong. The NW tab and FI calculation use the last months data. How would you track your spending for the month if it is not complete yet? The Jan tab is a place to store how much money you took in that month and how much you spent that month. It also trys to show you where that money went. At the very end of the month (IE Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month. It also calculates your savings rate from the previous months data and your FI date. So in all of Jan you know your net worth and your savings rate from the month before. You can't calculate your new rate until you have more data at end of the month.
You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.
-
So, quick high-level question:
Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.
Or am I doing something wrong?
I think you're looking at it wrong. The NW tab and FI calculation use the last months data. How would you track your spending for the month if it is not complete yet? The Jan tab is a place to store how much money you took in that month and how much you spent that month. It also trys to show you where that money went. At the very end of the month (IE Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month. It also calculates your savings rate from the previous months data and your FI date. So in all of Jan you know your net worth and your savings rate from the month before. You can't calculate your new rate until you have more data at end of the month.
You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.
Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)
-
OH! I know I'm going to spend some major time playing with this ! Just found it, so I wonder if it's possible to input starting now in Jan and going forward and also input data backwards from December ? Will the calculations work since you recommend a new sheet each year - will they eventually sync ? Or should I just forget about the past and move forward ?
Thanks for making this - it's so cool to have everything in one place !
-
So, quick high-level question:
Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.
Or am I doing something wrong?
I think you're looking at it wrong. The NW tab and FI calculation use the last months data. How would you track your spending for the month if it is not complete yet? The Jan tab is a place to store how much money you took in that month and how much you spent that month. It also trys to show you where that money went. At the very end of the month (IE Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month. It also calculates your savings rate from the previous months data and your FI date. So in all of Jan you know your net worth and your savings rate from the month before. You can't calculate your new rate until you have more data at end of the month.
You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.
Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)
I thought the same thing!!
-
OH! I know I'm going to spend some major time playing with this ! Just found it, so I wonder if it's possible to input starting now in Jan and going forward and also input data backwards from December ? Will the calculations work since you recommend a new sheet each year - will they eventually sync ? Or should I just forget about the past and move forward ?
Thanks for making this - it's so cool to have everything in one place !
You can fill back data on the NW tab. That is really the only tab that keeps data from year to year besides the Mortgage tab. You can put in as much as you have. My data starts back in July of 2015.
-
So, quick high-level question:
Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.
Or am I doing something wrong?
I think you're looking at it wrong. The NW tab and FI calculation use the last months data. How would you track your spending for the month if it is not complete yet? The Jan tab is a place to store how much money you took in that month and how much you spent that month. It also trys to show you where that money went. At the very end of the month (IE Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month. It also calculates your savings rate from the previous months data and your FI date. So in all of Jan you know your net worth and your savings rate from the month before. You can't calculate your new rate until you have more data at end of the month.
You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.
Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)
I thought the same thing!!
Fixed it!
-
I would love to use this, I did try. But I am so crap at worksheets, I need either a youtube tutorial step by step or a reallllly easy simple worksheet.
I have still not calculated my FI date as a result.
If anyone would like to do a short youtube screencapture video tour of how to use this sheet I would be happy happy happy!
-
Dandy what did you have trouble with? I haven't made a screenshot video so that is beyond what I can do. If you want to try again now is a good time to start. I would start over with a fresh sheet. Open the V3 then up top select the File menu option and Make a copy so you have your own copy.
The first thing you need to do is update the expense categories on the FI sheet. Use categories that make sense to you and that you want to track. If you don't have a pet change it to something else. If you don't use all the categories just leave them blank.
Now go to the NW sheet and delete all the rows so you only have 1/1/2017 and on. Over on the left select row 3 1/1/2015 by left clicking and holding the mouse button down. Now drag straight down until you reach 26. That should highlight rows 3 - 26. Now up in the Edit menu select the Delete rows 3 - 26. That will get you NW sheet started this month. It will blank out some of the calculation but at the end of the month when you update your expenses it will calculate again.
Now in NW sheet update the different titles for accounts that make sense for you. These are columns G - L and N - S. Now update the value of those accounts with the value of them today. This will be the 1/1/2017 row. If you have debts do the same thing for columns T - W.
That should be enough to get you going. At the end of January update you categories with the amount you spent on each in the Jan sheet. Update the information about your check / income at the bottom.
See if you can get that far then we will work on your mortgage if you have one so it will auto update. That isn't needed if you want to ignore it. Should be able to enter zeros.
-
Thanks Weinerdog. It is a good time you are right. I am just hopeless with spreadsheets, dont even know the basics, hubs is even worse than me lol. I will give it another go tomorrow as hubs is good with written directions.
We dont have a mortgage anymore - just paid it off yay!
I really want to see what that needle says :) :)
-
Cool tool, thanks for sharing!
I have some similar spreadsheets of numbers, but you make it so pretty!
-
oh crud. Had my husband helping me and we thought we were doing pretty well following the directions, but once done I went back to dashboard and it is all blank, and the needle graphic is gone.
Not sure where we went wrong. Also, how do we set all the mortgage stuff- to basically delete it since we own our house outright now.
Thanks for any help, will try again in the morning with a new saved spreadsheet.
-
The needle graphic is gone or reads zero? If it is gone you accidentally deleted it. If it reads zero that is probably right. You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.
I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.
-
The needle graphic is gone or reads zero? If it is gone you accidentally deleted it. If it reads zero that is probably right. You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.
I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.
Thanks Weinerdog, it is completely gone.
Actually glad I am doing it again, as I didn't realize I should make my categories match exactly to my budget in Mint. Is that right? I should get them the same if I want to copy paste them in using the Mint tab?
-
Just popping in to tip my hat to Weinerdog for keeping up with all these questions and replies.
Nice work, the forum is better off with you!
Sent from my iPhone using Tapatalk
-
The needle graphic is gone or reads zero? If it is gone you accidentally deleted it. If it reads zero that is probably right. You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.
I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.
Thanks Weinerdog, it is completely gone.
Actually glad I am doing it again, as I didn't realize I should make my categories match exactly to my budget in Mint. Is that right? I should get them the same if I want to copy paste them in using the Mint tab?
That doesn't matter so much as the actual Mint categories are in the formula the Mint sheet uses. I explained to another user how that works a few posts up as they found they had too many categories under Mint. I showed them a way where they could put several Mint categories under on FI Sheet category. Did you make any ground when you reworked it?
-
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.
-
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.
I do the same thing. Every month it seems like I'm telling my husband about a new tracking system I'm testing out or something. He just shakes his head at me and changes the subject because he knows I'm doing it to make sure we're set for our futures. I use Mint and PC too.
-
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.
I do the same thing. Every month it seems like I'm telling my husband about a new tracking system I'm testing out or something. He just shakes his head at me and changes the subject because he knows I'm doing it to make sure we're set for our futures. I use Mint and PC too.
I dumped PC long ago. On the second phone call I deleted my account. Mint I only use it to track the credit card purchases which is my daily spending. I guess I entered home value and car value in the beginning but ignore all that. Never have put in investment accounts and bank accounts so I always show debt if it wasn't for the car and home. LOL The new bill thing Mint has going is getting annoying as I know what my bills are.
I have been using Future Advisor for a year or so just to see how I do against the new fangled AI Betterment type portfolios. So far no bothersome phone calls from them.
-
Hello! I am getting along fairly well we my redo.
A few things so far that have tripped me up:
On the Mint sheet I couldn't get the 2 numbers to match, I went through it multiple times making sure that all my rebates, credits, transfers were deleted and that all the categories were going in to the right place.
Figured it out- on lines 10,14, 17 the formula has a shortened amount than the others at 98 instead of 145. Matched perfectly once I noticed it. Thanks for this mint page, works fab.
A) Now I am having another trip up. In v2 instructions it says in step 2 in FI tab, to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.
Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.
B) Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.
C) On the Monthly Budget Sheet instructions it says 1) The Monthly/Actual. This is the left section where you see the Budgeted and Actual. You should only manually enter numbers in the white cells. Colored cells are automatically calculated, so do not change them unless you want to fiddle with calculations. Note that there is a register-like feature for capturing miscellaneous spending to the right (off the screen). You can add line items here and they will be totaled in the Miscellaneous row of the Monthly/Actual
I couldn't figure out where this was?
D) LAstly, on the v3 instructions for Net worth it says "Do keep in mind that the first 4 green columns are used in the formulas to calculate your passive income (this can be changed, if you like, in the YMOYL tab formulas)."
I am not sure what 4 green columns you mean?
Thanks for any help. Sorry for being so asky!
-
Hello! I am getting along fairly well we my redo.
A few things so far that have tripped me up:
On the Mint sheet I couldn't get the 2 numbers to match, I went through it multiple times making sure that all my rebates, credits, transfers were deleted and that all the categories were going in to the right place.
Figured it out- on lines 10,14, 17 the formula has a shortened amount than the others at 98 instead of 145. Matched perfectly once I noticed it. Thanks for this mint page, works fab.
I will check the master mint sheet. One thing I have found you have to be careful when you are removing extra credits, rebates and transfers. I use to delete the whole row. I didn't notice until later it was changing the formula up top to look at less and less lines. Now I just highlight the row and hit the back space to "blank" that row. That way it won't change the formulas.
Thanks for the heads up. I will fix it in the master sheet if it accidentally got into there.
A) Now I am having another trip up. In v2 instructions it says in step 2 in FI tab, to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.
Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.
In version 3 one of the forum members mentioned we could only edit the categories on the FI and use formulas to update the rest of them in all the other sheets so you don't have to manually do it. I thought I got all them in Version 3. Which ones didn't update in version 3?
B) Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.
How many rows do you have in the NW sheet? If you only have one row (IE 1/1/2017) then you should only have account balances and you haven't updated yet with the income and expenses column because January isn't done yet. Once you have two rows the month before will have your expenses and income. Then the sheet will be able to calculate your average at E1 and F1. From there it should update your FI sheet and the gauge. Why are you waiting until April? You should update the months row in the NW sheet at beginning of each month or whenever the markets close at the end of the month.
C) On the Monthly Budget Sheet instructions it says 1) The Monthly/Actual. This is the left section where you see the Budgeted and Actual. You should only manually enter numbers in the white cells. Colored cells are automatically calculated, so do not change them unless you want to fiddle with calculations. Note that there is a register-like feature for capturing miscellaneous spending to the right (off the screen). You can add line items here and they will be totaled in the Miscellaneous row of the Monthly/Actual
I couldn't figure out where this was?
Sorry that was left over from Ver 1 I think. In that version it was hard to enter new rows as there was stuff farther over to the right. If you inserted a row it would move everything down. That was eliminated in Ver 2. I deleted it in the instructions. Thanks for the catch!
D) LAstly, on the v3 instructions for Net worth it says "Do keep in mind that the first 4 green columns are used in the formulas to calculate your passive income (this can be changed, if you like, in the YMOYL tab formulas)."
I am not sure what 4 green columns you mean?
Thanks for any help. Sorry for being so asky!
That was a left over also! I have edited the master. Thanks for the input.
-
Hey Weinerdog,
Thanks again for this amazing spreadsheet!
I'm still a spreadsheet novice, so forgive me...
Quick question about cell C5 on the Month sheets (Jan-Dec):
Shouldn't the formula be =sum(B79:B82)
instead =sum(B79:B83)
because medical is a deduction from our pay.
Right? (I might be missing something)
-
Hey Weinerdog,
Thanks again for this amazing spreadsheet!
I'm still a spreadsheet novice, so forgive me...
Quick question about cell C5 on the Month sheets (Jan-Dec):
Shouldn't the formula be =sum(B79:B82)
instead =sum(B79:B83)
because medical is a deduction from our pay.
Right? (I might be missing something)
Thank IndyPendent not me. I just modified his work but all credit should go to him.
You are correct but there is a note on C20 that the value there should match your deduction. It is recorded as an expense so you can track what you spend on it over the years. It gets added in down below but then subtracted in the expenses. Good question though. I did it that way as it made sense at the time to track it.
-
Thanks, that solves it!
(I see what I did...customized all my monthly expenses and removed "Health Insurance" from C20.
So it was not being included in my expenses.
The FI sheet uses C11 (which uses C5) from the monthly sheets to calculate row 57 ("Monthy Income after Tax"), so my income went down a little, which lowered my savings rate for the year a tiny bit.
-
Is this the section of the forum where people ask excel spreadsheet questions? I thought I saw a spreadsheet section when I first joined, but I just looked around and don't see one.
-
Thank IndyPendent not me. I just modified his work but all credit should go to him.
That's being exceedingly modest, WD. I may have started the fire, but you've been adding logs ever since. :D
Sent from my iPhone using Tapatalk
-
A) Now I am having another trip up. In v2 instructions it says in step 2 in FI tab, to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.
Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.
In version 3 one of the forum members mentioned we could only edit the categories on the FI and use formulas to update the rest of them in all the other sheets so you don't have to manually do it. I thought I got all them in Version 3. Which ones didn't update in version 3?
The totals of the FI sheet- starting at A36, in previous versions it told us to copy paste down there
B) Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.
How many rows do you have in the NW sheet? If you only have one row (IE 1/1/2017) then you should only have account balances and you haven't updated yet with the income and expenses column because January isn't done yet. Once you have two rows the month before will have your expenses and income. Then the sheet will be able to calculate your average at E1 and F1. From there it should update your FI sheet and the gauge. Why are you waiting until April? You should update the months row in the NW sheet at beginning of each month or whenever the markets close at the end of the month.
OOPS my bad I meant end of Jan :) i left the 2015 line as instructions said to do otherwise the sheet would go wonky .. maybe that got fixed? Thanks for all your help :)
-
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N. I only know how to filter groceries and then manually sum up the individual cells that did not collapse. Is there an easier way to filter?
I have my figures in the attached excel file.
Thanks for any advice.
-
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?
-
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?
Yes that is fine. Note that it won't update until you put your income and expenses in at the end of the month.
-
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?
Yes that is fine. Note that it won't update until you put your income and expenses in at the end of the month.
Yep absolutely. Thanks. This is great so far. Kudos to all you guys for the hard work.
-
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N. I only know how to filter groceries and then manually sum up the individual cells that did not collapse. Is there an easier way to filter?
I have my figures in the attached excel file.
Thanks for any advice.
=SUMIF(N$9:N$21,D9,M$9:M$21)
-
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N. I only know how to filter groceries and then manually sum up the individual cells that did not collapse. Is there an easier way to filter?
I have my figures in the attached excel file.
Thanks for any advice.
=SUMIF(N$9:N$21,D9,M$9:M$21)
Thank you
This helped alot
-
Hey all,
Really appreciated browsing this thread and thought I'd throw my own "sheet to rule them all" into the mix, primarily to get feedback on it. What works well? What needs to be fixed/adjusted? What am I missing?
https://docs.google.com/spreadsheets/d/18EoyZ4g0dLcY21atwxK25e-XLTKaSVhKT_HWaCf5bak/edit?usp=sharing
Tell me what you think!
-
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?
-
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.
-
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.
-
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 ?
-
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 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.
-
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?
-
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!
-
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.
-
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?
-
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.
-
Thank you so much!!! You have been a great help and really appreciate it. Looks RAD!
-
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!
-
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.
-
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.
-
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!
-
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.
-
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.
-
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 :)
-
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?
-
The Use Budget = True uses the white Budget column on the FI tab for the values in the FI calculation. But if you have Use All-time set to True it really doesn't do anything besides update the calculations in the FI Sheet below the Spending Totals row.
If you want to see what a set amount of spending will do to your FI date put your theoretical values in the white Budget column for what your average spending will be each month for each category. Now set Use Budget = True and Use All-time = False. Your FI calculation will now be based on your monthly spending that is in the budget column. Put different values in the White budget column with the above settings and see how your FI date changes.
If you have Use All-time set to true the FI calculations are based on your average income and expenses from the NW sheet. Basically cell E1 and F1. E1 and F1 are calculated from the average of all the values below in the column on the NW sheet. For you it is probably better to set Use All-time set to True since you don't have a full year of data. Of course turn the budget to false.
If you are starting in April the numbers are going to be skewed a little if you blank out Jan - Mar if you have Use All-time = False (along with the budget turned off) because it is using the average spending of all the months that have passed this year. Zeros in those months will make your average spending low.
The Use All-time was put in because as you use the spreadsheet year after year you lose the monthly spending each year. So if you document your income and expenses each month on the NW sheet (note income and expenses always ride a month behind) then you get a bigger picture over time instead of just that year. After you get a year or two of data in the NW sheet once you get into April or so you can turn Use All-time to False to see if you are doing better or worse this year compared to the average of all your data.
-
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?
I just learned something. I took "blank out" as putting zeros in which would skew the average. If you actually put a blank in where the actual expenses would be for the past months it isn't calculated in the average. I guess I never realized the sheet did it like that but not sure why I thought it was any different as it doesn't make sense. I did notice you have to at least have data in one calculation for the sheet not to error. Just make note the expenses calculation is one month behind so if you enter your expenses for March it won't be calculated in the average until April 1 and you will probably get a division by zero error if you blank out Jan - Mar and it isn't April yet.
I hope that helps. Let me know if you got other questions and we will get you going in the right direction.
-
Whoa this is AWESOME. Thank you!
-
Hi
I just started reading this blog and found this sheet. Is there any way to switch out the dollar sign for my own currency or nothing at all? (I live in Norway and we don't use dollars)
Thanks
Magnus
-
I just found this sheet and thread. Looks great.
Question .. where do I enter existing retirement accounts?
I have a 401K, tIRA, and my wife has a 403B.
I also will have a pension and SS when I retire. Do I factor that in anywhere?
Thx,
Rob
-
Also, how do I input other debt such as car loans, student loans, credit cards, etc?
-
Also, how do I input other debt such as car loans, student loans, credit cards, etc?
I believe on the NW tab (the tab with all of the account totals that you update once a month). Far right has debts section.
-
OK, I see that now. Thanks.
The income and expenses in the NW tab ... where do these come from? Do I manually copy them from the monthly tabs?
-
OK, I see that now. Thanks.
The income and expenses in the NW tab ... where do these come from? Do I manually copy them from the monthly tabs?
Yup.
so my process is basically, track my budget in YNAB. at the end of the month, i copy my YNAB category totals to my monthly tab totals that are setup the same way. When i get my paystub i put my paycheck in that monthly tab as well.
then at the end of the month I take the numbers from the top of the monthly tab and put them in the NW tab columns. I also run down my personalcapital account list and update the other accounts in the NW tab.
-
How would you edit this spreadsheet if you have a second mortgage?
-
How would you edit this spreadsheet if you have a second mortgage?
If the first mortgage sheet works for you could copy that sheet for the second mortgage. Then you would need to insert a row on the NW sheet and copy the formula from the Home equity column and update it so it looks at the new mortgage sheet.
-
Great Spreadsheet!
I'm working on set up right now but am curious what others have done if starting mid year? Do you go back and fill out all months or just wipe out certain cells in months that have already happened?
Thanks
-
Would be really useful to have conditional formatting for the monthly spending breakdowns on the FI sheet to be pegged to your set budget amounts.. like green if you were under and red if over.
-
Hey all,
I just started using this sheets and have been messing around with it over the last few days. I feel like I understand almost everything but I am a little curious about the purpose of the Mint sheet. I use Mint to do my monthly budget and track my expenses. The Mint sheet just seems to be a little repetitive.
Love the sheet and thanks in advance for your responses.
UPDATE:
I never fully read the instructions provided so that explains my confusion. I think I figured it out and now it all makes sense.
-
Sounds like you got it. I only use mint to track my day to day credit card charges as it is an easy place to put categories to spending on phone or computer. Not bad for cash also but you have to remember to put it in. At the end of the month it just helps to pull the spending for the month and get it broke down to categories in the sheet. I know some people use it and others have deleted the sheet.
-
Does the spreadsheet work in excel,=update-
Love the spreadsheet, thanks for sharing
Sean
-
i like the new version but cannot figure out why the sum of G4:G10 is equal to unallocated cash. how is that true? it's allocated and subtracted from income. to clarify, if I have my expenses exactly equal my income, the amount is $0 in the budgeted column and equal to G11. If I have leftover (unallocated income), then that amount shows up in F14 and is added to G14. I am very confused on that box(orange colored)
-
i like the new version but cannot figure out why the sum of G4:G10 is equal to unallocated cash. how is that true? it's allocated and subtracted from income. to clarify, if I have my expenses exactly equal my income, the amount is $0 in the budgeted column and equal to G11. If I have leftover (unallocated income), then that amount shows up in F14 and is added to G14. I am very confused on that box(orange colored)
Unallocated cash is equal to the income total minus savings (investment) total minus charity minus Necessary Expenses minus Discretionary Expenses minus Monthly Excess Expenses. Unallocated cash can go negative if you spend more than you bring in that is left over after taxes etc.
-
Unallocated cash is equal to the income total minus savings (investment) total minus charity minus Necessary Expenses minus Discretionary Expenses minus Monthly Excess Expenses. Unallocated cash can go negative if you spend more than you bring in that is left over after taxes etc.
thanks!
-
Friendly reminder you don't need to request access to use the sheet. Just hit the file menu option and select make a copy. If you are logged in via your Google account you will save your own copy that you can edit as you like right in your Google drive. That keeps the original as the generic master copy. If there are updates it
should shouldn't be hard to copy the difference from the master to your copy.
*typo correction
-
Hi all,
First time poster. I downloaded this sheet not too long ago and have a question. How come in the FI tab, for Withdrawl Rate%, when I lower the % my years to FI goes up. Shouldn't it be opposite? Please excuse my dumbness as I'm just trying to learn about FI.
-
Hi all,
First time poster. I downloaded this sheet not too long ago and have a question. How come in the FI tab, for Withdrawl Rate%, when I lower the % my years to FI goes up. Shouldn't it be opposite? Please excuse my dumbness as I'm just trying to learn about FI.
The reason it goes up is because you are not changing the amount of money you need per year, therefore to access the same annual amount for living expenses at a lower SWR% you need a larger pie of money. As an example, assume you wanted to withdraw $100,000--at 4% you should have a nest egg of roughly $2.5MM, while at 3% you would need approximately $3.3MM. I get the impression what you were after was a scenario where you say, using this example, I don't really need $100k per year, I only need $75k per year--for that you would need to alter your expenses as the spreadsheet takes the actual or budgeted expenses in the spreadsheet, and with that being constant the SWR% going down will make the total needed go up.
Does that make sense?
-
Amazing spreadsheet, but I have three questions.
1) I paid off my car with some large payments. The FI sheet is using those payments to calculate my FI timeline. But since the car is paid off, those payments should not be included in the FI calculation. How do I change that?
2) How do I account for deposits in investments? I am thinking I would have to use the unallocated cash each month, and then update my NW tab with the investment totals. Or do I log it in cell G6 (or 7,8,9, etc) so that it affects the unallocated cash balance?
3) How do I account for statement credits with credit cards? Do I allocate that as extra income on the month's income entry?
Thanks in advance.
-
Amazing spreadsheet, but I have three questions.
1) I paid off my car with some large payments. The FI sheet is using those payments to calculate my FI timeline. But since the car is paid off, those payments should not be included in the FI calculation. How do I change that?
That is one problem with the sheet. It can only use history as an calculation for FI. If you wait for a new year and start the new sheet with your new spending it will only look at that year if you have. Note you will have to use "Use all time" set to false on the FI sheet. There are some other ways around it but it would need some formulas changed. I did this because for 2 months I spent more than double my normal spending so restore an older vehicle.
2) How do I account for deposits in investments? I am thinking I would have to use the unallocated cash each month, and then update my NW tab with the investment totals. Or do I log it in cell G6 (or 7,8,9, etc) so that it affects the unallocated cash balance?
The two numbers that really matter are your income after taxes (C11) and your expense (E1). G5 - 10 and unallocated cash G14 just shows where the money is placed for that month. If you didn't enter anything in G5- G11 and your expenses was smaller than your income then you will have allocated cash. You are right the spreadsheet assumes you will save the allocated cash so at the beginning of the month update you NW tab with your investement totals and it will account for the money. The money sheet just shows where it is at each month.
3) How do I account for statement credits with credit cards? Do I allocate that as extra income on the month's income entry?
Thanks in advance.
Not sure why you would have that many statement credits but yea you could put it under monthly income so it gets figured in your FI calculations if you think it is going to be steady.
-
Does anyone do Facebook that also belongs to the ChooseFI group? It seems the Sheet got cross-posted over there and people keep asking for access to the master copy. If you do belong could you post that all you have to do is File -> Make a Copy... to create your own editable copy then the master stays as is. It is only to be a template. There is no need to edit it unless someone finds a problem.
-
Does anyone do Facebook that also belongs to the ChooseFI group? It seems the Sheet got cross-posted over there and people keep asking for access to the master copy. If you do belong could you post that all you have to do is File -> Make a Copy... to create your own editable copy then the master stays as is. It is only to be a template. There is no need to edit it unless someone finds a problem.
Link to the FB group? I’m curious.
Sent from my iPhone using Tapatalk
-
I assume it is this: https://www.choosefi.com/ In the upper right hand corner they have a Facebook group. https://www.facebook.com/ChooseFi/
There has been a few people now requesting access to the Sheet which they don't need to use it. I wish they would read the directions. I guess they must have linked to it in their group. Seem to be getting 1 or 2 requests to access the sheet every other week or so.
-
I don't know if I have thanked whoever made this sheet, but have been using this sheet for a year now and just....... THANK YOU!
-
First off, thank you for sharing this. It is ridiculously awesome.
I have a questions about the income section of the monthly sheets. Can someone explain why C5 includes IRA/401k/HSA contributions as income? Why not just use net income.
Also I added to my spreadsheet sumif formula to each of the expense categories so I can keep a running tab each month (starts at row 115) of what my actual expenses are and have them auto fill.
https://docs.google.com/spreadsheets/d/1Fv5xEDJdzyHqmy_mA67PMJfEsuqzy1UB6mQOonIkyrM/edit?usp=sharing
-
First off, thank you for sharing this. It is ridiculously awesome.
I have a questions about the income section of the monthly sheets. Can someone explain why C5 includes IRA/401k/HSA contributions as income? Why not just use net income.
Also I added to my spreadsheet sumif formula to each of the expense categories so I can keep a running tab each month (starts at row 115) of what my actual expenses are and have them auto fill.
https://docs.google.com/spreadsheets/d/1Fv5xEDJdzyHqmy_mA67PMJfEsuqzy1UB6mQOonIkyrM/edit?usp=sharing
Its been a while but those are added because it is needed to calculate your saving rate. Anything beside taxes is calculated as income. After removing all you expenses for the month what is left is your saving % compared to what you brought in.
-
Thank you for this awesome spreadsheet! Starting in January should keep things nice and clean for me. I did make a couple of edits, like removing the Invest sheet (already tracked through Fidelity and Personal Capital), and automatically pulling the monthly Income and Expenses data into the NW tab. Finally, since my discretionary and excess expenses will change each month, I grouped the non-standard categories into a row called "Other" in the FI tab, and that is calculated with whatever's left after the standard categories.
-
Thanks for the sheet!!
Awesome calculations, but I am stuck. I still have 3 properties with mortgages. I cannot see how to calculate the other sheets into the main page. I duplicated the mortgage page and set them up, but just having calculation issues.
Any excel tips on this type of setup?
-
I'm a few years late to the party, but I started using the sheet a few months ago and I love it! Quick question though, where does tax refund come into the picture? Is there somewhere I can keep track of it?
-
I just put it in one of the 6 different income entries on the month it comes in. IE right below you paycheck. It will get added into income and then calculated into you saving rate as I am sure you are investing it all right????
-
Oh, I should have probably thought of that - thank you! That makes perfect sense, I just put those as positive State Tax/Federal Tax item in the paystub section. And yes, I invest all of it :)
Now I just can't wait until the end of the year to see what's my real savings rate now that the tax refund is counted!
-
Can anyone tell me why I am getting this error on FI tab (in the attachment)?
I've inserted values for each month and the mortgage tab, but that's it. Any ideas?
Thank you in advance.
-
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?
I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).
-
Can anyone tell me why I am getting this error on FI tab (in the attachment)?
I've inserted values for each month and the mortgage tab, but that's it. Any ideas?
Thank you in advance.
You need to fill out one row on the NW tab. This is your "snapshot" in time at the end of each month that will stay with the sheet from year to year. As you get more data in there the numbers will become more accurate.
-
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?
I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).
Interesting—whats your vision for the self-hosted version? Meaning, what would others get from it that they wouldn’t get now? Im curious what value you anticipate app-ifying it would create.
Sent from my iPhone using Tapatalk
-
I apologize if this has already been asked/answered elsewhere but: Has anyone turned this into a Ruby app or similar? And if so, a GitHub repo?
I know there are several software engineers here so I figured I'd ask before I went ahead and made this a side project (also, I'm not a great programmer, but good enough to create a self-hosted version of this).
Interesting—whats your vision for the self-hosted version? Meaning, what would others get from it that they wouldn’t get now? Im curious what value you anticipate app-ifying it would create.
Sent from my iPhone using Tapatalk
Connecting to third party APIs used for the basic input functions: Mint, Investment brokers, etc. (I'm unsure if they have public APIs)
In effect, like Mint, but for FIRE. In short, I'm lazy when it comes to trivial tasks and I like to automate them away.
-
Sorry if this has been asked before, but is it possible to accurately start this in the middle of the year. Or, does it require all data for Jan-Dec? I dont think I can get the wife's payroll info prior to last month.
Nm, I figured it out. RTFM. ;)
-
First - this sheet looks fantastic. In the process of molding it to my preferences now!
These may be two dumb questions but..
1) Where does the investment tab tie back in to the rest of the spreadsheet (i.e., income, net worth, etc.)?
2) On the investment tab, how / where would I input "normal" stocks that I invest in (i.e., GM, Tesla, etc.)? I do this through Robinhood with money that is not tied to an IRA or 401K retirement account.
Thanks so much for your help!! Very much appreciated.
-
First - this sheet looks fantastic. In the process of molding it to my preferences now!
These may be two dumb questions but..
1) Where does the investment tab tie back in to the rest of the spreadsheet (i.e., income, net worth, etc.)?
The investment tab doesn't really tie back into anything. It is just a way for you track your investments at different accounts if you have that. IE you might have a 401k at work, IRA from previous jobs and HSA. You need to manually copy the value at the end of each month and enter it into you NW sheet. You should have a column for each account there. The NW sheet stays with you from year to year so you can see how your accounts grow. Some people don't use the investment sheet and just go look at your account balance and enter that on the NW sheet at the end of the month. Just think of of the investment sheet as a tracker and then the NW sheet is a snapshot of that investment sheet at the end of the month. I find it convenient to use the investment sheet then once every several months log into Vanguard as your dividends and things like that will slightly change your holding amount of any particular stock / bond.
2) On the investment tab, how / where would I input "normal" stocks that I invest in (i.e., GM, Tesla, etc.)? I do this through Robinhood with money that is not tied to an IRA or 401K retirement account.
Just take one of the accounts on the investment sheet and make it Robinhood. Rename the taxable one if you like. You can then insert rows in between the middle of that account to how many rows you need for each stock. Then you will have to cut and paste the formula's down from the first row to get everything to update right. Then up top make sure the formula is correct for that account. It needs to sum from the top row of that account to the bottom. I believe if you insert a row in the middle it will adjust the formula up top for you.
I hope that helps. Sorry it took a while to answer been really busy with work.
Thanks so much for your help!! Very much appreciated.
-
Hi everyone! A little late to the party here but it's fantastic to see such a wonderful tool for FI upon my return to the MMM world from a slight hiatus.. first time posting on the forum, however!
I'm having a couple of problems in setting up the spreadsheet and hoping I can get some help:
1. My 'budgeted' costs don't seem to be flowing through to the monthly tabs (it is using 'Costs' in FI column B) - I have selected 'TRUE' against 'Use Budget?' in the FI tab
2. My adjusted income doesn't seem to be correct - as if it is adding my monthly taxes, rather than subtracting. I'm nervous to play with the formula, so there might be a trick here?
Obviously it's pretty key to get these two things correct, rather than having erroneous or incorrect figures... I can live with a few $ here or there, but adding taxes and having the default expenses seems less than useful!
-
The budget doesn't pull from the monthly tabs (I think it is left over from the original Sheet). If you want to look at what budget would do compared to your real spending just set use budget to TRUE on the FI page and set your monthly budget there in column C. You can switch back and forth by setting it to TRUE or FALSE to compare the two.
Did you enter you Federal, Social Security, Medicare etc. as as a negative? It should end up looking like the accounting nomenclature. If you enter -250 for Federal it will show it as ($250).
-
Nice work. I recently posted yet another "One Spreadsheet to Rule Them All" style thread here:
https://forum.mrmoneymustache.com/share-your-badassity/launch-of-the-2019-retireator!/ (https://forum.mrmoneymustache.com/share-your-badassity/launch-of-the-2019-retireator!/)
Your expense tab has more granularity, but the Retireator expense tab provides for a transition from the accumulation phase to the distribution phase. I don't support HELOC's and student loans yet, but those can be roughly approximated.
My net worth calculation takes into account the tax obligation on before-tax savings based on the average tax rate, it doesn't appear you are doing that.
You have a lot of granularity on the investments which can be desirable, I went with basic investment type modeling including target date fund support.
Those month tabs are a bit brutal.
Your dashboard tab is very nice, I like it better than my simple graph interface.
The killer app on my tool is that it can run a binary search algorithm to forecast the exact FIRE date based on the simulation parameters. You could plug your data into mine and have a go at that (Microsoft Excel for Windows required).
-
I only use a Chromebook or an older Chromebook running Linux so I can't give yours a spin. I do have an old XP machine I keep around to run Tax software but only use it once a year.
-
Just checked out this spreadsheet and noticed something odd: when I accessed the sheet via OP's link, the dashboard tab appears to display fine. However, when I open it using Office 2016 or upload it to my own Google Drive, I get the following display error:
(https://i.imgtc.com/ajxLR12.png)
Any tips?
-
Did you save it in Office first? The gauge that it isn't displaying wasn't supported on tablets and phones for a while.
Try going directly to the link in the original post and select File -> Make a copy. That should save an original in you G drive. You can then rename and edit from there. See if that works.
-
Did you save it in Office first? The gauge that it isn't displaying wasn't supported on tablets and phones for a while.
Try going directly to the link in the original post and select File -> Make a copy. That should save an original in you G drive. You can then rename and edit from there. See if that works.
I haven't saved it in Excel just yet. All I did was make a copy -> open locally using Excel on my own computer. Just repeated the process and saved a copy as .xlsx locally on my PC - opened it up and it's still having the same display problem.
-
I am going to guess the gauge just isn't supported in Excel. I don't use Excel so I have no idea if that is right or not but for a several years I know the gauge didn't work on mobile Sheets.
-
Yeah... I'll have to end up editing this in Google Sheets.
-
Amazing sheet, I am in awe, thanks for creating it. I'm also not having the budget pull to the months, there doesn't seem to be a formula in those boxes, I tried setting to True in appropriate box in FI sheet. Using google sheets so not sure if that is problem or more likely 'picnic' (problem in chair, not in computer!)
The budget doesn't pull from the monthly tabs (I think it is left over from the original Sheet). If you want to look at what budget would do compared to your real spending just set use budget to TRUE on the FI page and set your monthly budget there in column C. You can switch back and forth by setting it to TRUE or FALSE to compare the two.
-
The Use Budget feature only works on the FI sheet. On the month tabs it is just a "visual" number so you can see where your real spending is compared to a budget on the month tabs. On the FI sheet if you enter numbers in the budget column then set Use Budget to TRUE you should see the numbers change on your Years to Fire only in the FI sheet.
-
@IndyPendent and @wienerdog, and others who contributed to this sheet:
Thank you for offering this gift to the world! I've been in my own bubble for YEARS, after giving up on finding something more comprehensive than Mint and Personal Capital, I set out to build my own spreadsheet...I had no idea tools like this were being shared!
Last month, I just happened to hit the lottery find MMM, and after reading nearly every post, I moved on to reading forums, and came across this sheet. That was about 4 days ago. I've officially cannibalized my hodgey-podgey sheet, and moved pretty much all my stuff over to my version of your sheet.
Thank you just does not seem like enough.
Very grateful for your time, effort, and energy. <3
-
SimpleLifer, that’s quite a thank you! I’m really glad you’ve found it useful, and I’m grateful that weinerdog has spent so much time making it a better tool than it was when it started. (As well as responding to this thread much, much more frequently than I.)
-
I looked to see if this has been asked in the thread, and I couldn't find any discussion on this question.
Perhaps I'm just missing something really basic. I'm having a hard time understanding how the Monthly Savings Rate is calculated, whether it's Pre-Tax or Post-Tax.
Because I have pre-tax deductions like 401k, HSA, ESPP, etc., a lot of my savings are pre-tax but if I'm reading the formula correctly, pre-tax (tax-deferred) and post-tax (unallocated cash) are all in one bucket.
Questions are:
1. How is Monthly Savings Rate calculated: Pre-Tax or Post-Tax?
2. On FI page, similar question...if I mark "Use All-Time" it looks like the cell is pulling data from the averages vs. actual from the monthly pages...would these calculations always be Pre-Tax or Post-Tax?
As a side note, this sheet has changed how I plan for my future financially, in so many ways. I can't think you all enough for all the hard work that has gone into this. THANK YOU!!!
-
Hi! Thanks for this amazing spreadsheet, I am starting to get it set up for 2020. Question: In the FI tab, what does the "Costs" column mean, and how does it differ from "Budget"? I feel like they are the same thing but I also think I might be misunderstanding something.
-
Hi savingmybest, think of budget vs. costs as planned vs actual. The costs cells average up what actually happened throughout the year, whereas the budget cells are what you planned to have happened.
Glad you're finding it to be valuable!
-
That's the tip I needed, thank you!
-
Got another question. I don't have a mortgage right now, I am a renter. How should I represent this in the Mortgage tab? I'm not sure if zero-ing out everything is messing up the spreadsheet.
-
I figure it's worth coming back and showing what I've done with the sheet after some years of use. I was originally planning on just showing my changes and showing bug-fixes but they became too numerous to track. I've done a lot of automation and there's much less work to do each month.
https://docs.google.com/spreadsheets/d/1Xj50VYo4Ji_9c2K_4PCVfjuWThHXO1OSw89npUiPf8s/edit?usp=sharing
I hope some of you find this helpful.
-
Hello all,
The sheet looks amazing, and I'd like to set it up for 1/1/2020. How do I go about switching the start date on the NW page without screwing with the formulas?
-
For the version in the original post, you have to update the dates manually. You can do this by setting up the first do and then selecting all the dates you updated. The blue border will have a small blue box in the corner that you can drag down to auto-update the boxes relative to the ones you updated.
-
@Andere, thanks for the guidance there.
If I update them manually, will the numbers dump in from the month sheets, or will I need to add them in manually?
Also, I'm considering using your sheets instead, actually, because it seems a bit easier to navigate.
-
In both the original sheet and my sheet, there is an expectation that you will manually update the Net Worth sheet. While the dates in my version auto-update from the start, you still have to manually make the step of preserving a snapshot of your networth every month.
-
there is an expectation that you will manually update the Net Worth sheet.
I see that now on Instruction #6. Sorry about that. I saw the instruction not to change colored cells, saw that the date cells in the NW tab are colored, and immediately assumed I was missing something.
I don't plan on importing from Mint, but doing it manually instead, so it seems pretty straight forward from here! It's nice to know this thread is still active with knowledgable people to help, too. Thanks again for answering my very obvious question in a very nice way.
-
@Andere , I've been using your sheet and I really like it. Had a question, though. I'm trying to use it to track my expenses manually, but I can't seem to add new expense categories without breaking the ones you already set. I have been trying add new categories in the Values sheet in column B, "Associated Mint Fields," but that doesn't seem to work. Is that where I should be doing it? If not, how/where should I be making those instead? Thanks!
-
@savingmybest, please show me the line you have that's not working, both the A-column title and the B-column comma-separated list that's failing.
These lists should be comma-separated, with no spacing such as Column A Value (the title): Totally Legitimate Purchases and the Column B value should be the drop-down category: beanie babies,fancy hats. This should add "beanie babies" and "fancy hats" to the category drop downs.
-
@savingmybest, please show me the line you have that's not working, both the A-column title and the B-column comma-separated list that's failing.
These lists should be comma-separated, with no spacing such as Column A Value (the title): Totally Legitimate Purchases and the Column B value should be the drop-down category: beanie babies,fancy hats. This should add "beanie babies" and "fancy hats" to the category drop downs.
Just DMed you, that might make it easier for us to troubleshoot without clogging up this thread. Thank you for the response!
-
@Andere thanks for adding this revised spreadsheet. I'm finding it way more intuitive. My one issue is really on me, but wondering if there is a fix. I use Personal Capital, not Mint and find my CSV file gets imported differently, where the amounts end up in a different section--making the whole thing very inefficient. Is there a way to fix the headers so that when I import mine it follows the Personal Capital pattern, rather than the Mint pattern?
-
@Andere thanks for adding this revised spreadsheet. I'm finding it way more intuitive. My one issue is really on me, but wondering if there is a fix. I use Personal Capital, not Mint and find my CSV file gets imported differently, where the amounts end up in a different section--making the whole thing very inefficient. Is there a way to fix the headers so that when I import mine it follows the Personal Capital pattern, rather than the Mint pattern?
You will have to modify each individual category parser to use a different column. Currently the formula for any given category looks like this:
=SUMPRODUCT(SUMIF($G$88:$G, SPLIT(VLOOKUP($B17, SpendingCategories, 2, FALSE), ","), $K$88:$K))
And you will need to modify $K$88:$K to be about a different column besides K. And $G$88:$G to be another column. G is the category. K is the money. Unfortunately you will need to make this change for every category row on every month. If you were to modify this to look at row H for price and row A for category it would look like this.
=SUMPRODUCT(SUMIF($A$88:$A, SPLIT(VLOOKUP($B17, SpendingCategories, 2, FALSE), ","), $H$88:$H))
-
@Andere thank you. Would I only have to do that on the Mint tab or in other tabs?
-
@Andere thank you. Would I only have to do that on the Mint tab or in other tabs?
Any tab you're using to derive the spending data. If you're pasting into January's table, that's you will have to modify all months. If you're using the mint tab and then pasting over the formulas in each month, then you only need to modify the mint tab.
-
I love this sheet. It replaces at least five separate spreadsheets we had going for various aspects of financial tracking. Thank you!
-
Update: Realized that I didn't credit the good folks at YNAB for the code here as it's not mine. Details and better directions on their site: https://support.youneedabudget.com/t/k9rxc9/using-google-apps-script-with-the-api (https://support.youneedabudget.com/t/k9rxc9/using-google-apps-script-with-the-api)
Replying with, I think, my first post in MMM forums to share how I use this sheet with YNAB's API to automatically import category spend. I didn't see anything about this in search, but can delete this is if's a dupe.
Code block at the bottom goes in to the script editor(Tools -> Script Editor). I then have =get_ynab_categories_for_month("API-key","budgetURL",$DATE) spit out the month, category name, and activity in 3 columns and =SUMIF(K$16:K,A15,L$16:L)*-1 look up the amount for each category. You can generate an API token for YNAB at https://app.youneedabudget.com/settings/developer (https://app.youneedabudget.com/settings/developer) and your budgetURL should be the string in between "https://app.youneedabudget.com/" and "budget" in the address bar of your browser.
This has made it so that when I go to close out each months entry, all the spend is already populated. Not sure how many YNAB users are around here, but it's saved me time and thought others might get a kick out of it. Enjoy!
function fetch_ynab_data(accessToken, path){
const url = "https://api.youneedabudget.com/v1/" + path;
const options = {
"headers": {
"Authorization": "Bearer " + accessToken
}
};
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText()).data;
return data;
}
function get_ynab_categories_for_month(accessToken, budgetId, month) {
const budget_month = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/months/" + month).month;
const columns = ["Month", "Name", "Activity"];
const rows = [];
// Add the categories
for (var category_idx = 0; category_idx < budget_month.categories.length; category_idx++) {
var category = budget_month.categories[category_idx];
var name = category.name;
var id = category.id;
// Calculate currency amounts from mulliunits
var budgeted = category.budgeted / 1000.0;
var activity = category.activity / 1000.0;
var balance = category.balance / 1000.0;
rows.push([month, name, activity]);
}
return [columns].concat(rows);
}
-
Replying with, I think, my first post in MMM forums to share how I use this sheet with YNAB's API to automatically import category spend. I didn't see anything about this in search, but can delete this is if's a dupe.
Nice is there a way to turn it on or off? If there was we might include it in the original spreadsheet. I still use Mint but this looks more simple. I suppose if I had time I could probably do the same thing with Mint. I looked at Sheets scripting early on but it never went anywhere.
-
Nice is there a way to turn it on or off? If there was we might include it in the original spreadsheet. I still use Mint but this looks more simple. I suppose if I had time I could probably do the same thing with Mint. I looked at Sheets scripting early on but it never went anywhere.
The code is effectively inert until provided with a valid API key and budget url, so the code in the script editor should be reasonably safe to keep there(though it may need updates if/when some API calls change).
The biggest question for merging back in to the master template would probably be around implementation in the actual sheet. Once placed, inserting the date, API token, and url are pretty straightforward.
For placement, I've modified the month template quite a bit and have my "actual" column populated with SUMIF commands against budget categories for activity amounts. That would obviously be disruptive for anyone who doesn't use YNAB(example of my implementation here: https://imgur.com/neQNGSY (https://imgur.com/neQNGSY)
-
I figure it's worth coming back and showing what I've done with the sheet after some years of use. I was originally planning on just showing my changes and showing bug-fixes but they became too numerous to track. I've done a lot of automation and there's much less work to do each month.
https://docs.google.com/spreadsheets/d/1Xj50VYo4Ji_9c2K_4PCVfjuWThHXO1OSw89npUiPf8s/edit?usp=sharing
I hope some of you find this helpful.
Thanks so much for posting this. I believe I found an error you may want to correct. On the "FI" tab, the formula on rows 85-94, from column C through M, are incorrect. They weren't dragged down from row 84.
-
Really curious to hear if/how people are actually using this sheet as a monthly budgeting too.
I do not. I use it to track the results of monthly budgeting, but I use Mint for the transaction tracking.