FI V3
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

View only
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Questions? Post in this thread: http://forum.mrmoneymustache.com/share-your-badassity/one-sheet-to-rule-them-all/
2
To use this for yourself, click File -> Make a Copy. This will create your own copy to edit. No need to request access.
3
Note: All numbers are faked because I stubbornly hold onto some illusion of online privacy.
4
Assumptions about this sheet: 1) You're familiar with Excel. 2) You calculate your own cash withdrawals (nothing but love for the Ramsey folks) and allocate that to spending categories or use the Mint integration to help track all of your spending. You will need to do some cut and pasting to bring the Mint data over to the past month. 3) Update your account balances and the previous months expenses at the begining of the month. 4) Create a new copy of the spreadsheet each year (networth, income and expenses will move along in its sheet so you can see your progress).
5
Across the entire document, do NOT edit any of the numbers in colored cells as they are auto-calculated and used in formulas. Only edit them if you're familiar with Excel formulas. Cells that are for you to update are white. On initial setup you might delete some rows depending on how many categories you have and your desire to streamline a little. There are many cells that won't update until the current date reaches that point in the spreadsheet.
6
7
Initial Setup (This will take you the most time but once its setup it is close to auto pilot)
8
1) First you need to concentrate on getting your expense labels correct. Work on the FI sheet and the rest of the needed places will update. There are 3 main categories: Necessary Expenses, Discretionary Expense and Monthly Excess Expenses. Concentrate on Cells A13 to A29 and change them to labels that make sense to you. Do the same with A33 to A43 and A47 to A52. Hopefully there are enough rows to get all your expense labels. If there are too many you can leave them blank towards the end like A41 to A43. It is probably best to streamline later but if you want to streamline you can select the blank row (click on the 41 on the far left so it is selected and highlighted then Edit -> Delete row 41). Thanks to user input the month sheets are now all setup to automatically update labels from the FI sheet which eliminated a huge amount of cut and paste! Move on to step 2.
9
2) Now go to the Jan sheet and scroll down Row 59. Study the paycheck section and fill in your values. Any deduction should be entered as a negative number and it will show up in financing form as (23.00). Once you get the paycheck section to your liking you will need to cut and paste those sections just like you did for the expenses to each month. IE select row 59 then selct row 111 while holding shift to select all the rows dealing with the paycheck section. Now paste those over each months sheet.
10
3) Now you have completed the worst part of the setup!
11
4) Move to the NW sheet. This sheet is where everything is logged for each month and the sheet updates your networth automatically at the beginning of the month. You will have to update each one of the account's balance each month except the Home Equity which comes from the Mortgage sheet. To setup the NW sheet you need to change the labels in Row 2 starting at Column F with the 401k. Make these labels match the different accounts that you might have. Typically investment accounts will use Columns F - K. Bank accounts or other assets that you might track are M - Q. Debt or credit cards are S - V. Once you have those matching your accounts you need to decide where your monthly data will start. The NW sheet is setup to go back to January 2015. If you don't have data going back that far you can select the rows you don't want and delete those rows. IE if I only have data for Dec 2015 on I would select Row 3 and then Shift and select Row 13 to highlight row 3-13. Then Edit -> Delete rows 3 - 13. (Note: If you delete all of 2015 for some reason it goofs the Cash Flow chart up on the Dash sheet. Keep at least 12/1/2015 for the chart to still work. Looking into the problem but it is something goofy with Google Sheets).
12
5) The Mint sheet and Mortgage sheet need some setup also to your particular situation. There are some directions included on the Mint sheet. (More detailed added here later) The Mortgage sheet can be filled out to your values. Basically concentrate on appraised value and the upper right box's white cells. (More detail on Mortgage sheet needs added)
13
What to update each month
14
1) At the begining of the month you need to update your expenses for the previous month and the income if it changes from month to month (IE on 26 pay periods per year 2 months will have 3 paychecks instead of 2). If you have any extra income for the month like a side hustle you can enter it in Cells C7 - C10. You can also update the investment cells if you contributed money to them but it isn't necesarry as anything left over from your income after expense will be accounted for in the unallocated cash. HSA and IRA is pulled from the Paycheck section. (Might need to redo the investment section. Would like feedback).
15
2) Update the Net Worth sheet with the value of you different accounts and debt. You can pull your income and expenses for the month from the month tab from cells C11 and E1. Note that your expenses and income is always 1 month behind your net worth calculation.
16
3) The Dash and the FI sheet will update with your new values at the beginning of the month. (Note most of the auto calculated values aren't calculate until the time on the computer is greater than the start of the month)
17
18
What to update on January 1 of the new year
19
1) Create a new copy of the spreadsheet you finished instead of overwriting your old one. Seriously. If you're anything like me you'll want to look back to make sure you didn't screw something up.
20
2) In the new spreadsheet just update the year in the Jan sheet in cell A1. The rest of the sheets will update automatically.
21
3) You can update you budget columns for each month with whatever the new year rings in if you use these.
22
23
Individual Tab descriptions
24
Dashboard
25
The needle: You can set the needle to be between whatever numbers are meaningful to you (e.g. 0 to 10 years, 0 to 15 years, etc). To do this, just hover over the chart and click the little down arrow, then click Edit.
26
Your Money or Your Life: Not read the book? It's great. The gist of this graph shows that as you save, you get closer to being Financially Independent. When the blue and yellow lines cross, you're there! I've made some assumptions about the blue line; namely, that it's a 4% withdrawal rate of your passive-income investments (retirement accounts, brokerage accounts, etc). The data source for this graph (and the savings rates graphs) are the sheet "NW". The withdrawal rate can be changed on the "FI" sheet at B2.
27
Average Savings rates and the Savings. These are taken directly from the "FI" sheet--they aren't even charts. I've colored the cells using conditional formatting, so if the savings rate average dips below a set value it shows yellow/red/etc. You can change these yourself by clicking the cell, then choosing Format->Conditional Formatting.
28
Net Worth: A visual line graph of the data you enter into the Net Worth spreadsheet tab
29
Mortgage Loan Balance: A line graph of your decreasing mortgage balance according to the data you enter into the Home Payoff spreadsheet tab
30
Current and Target Asset Allocations: Pie charts based on the data you enter into the Investments spreadsheet tab.
31
32
33
Monthly Budget Sheet Instructions (January, February, etc)
34
35
There are three main sections to the monthly budget sheets:
36
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.
37
2) Summary: This upper-left section tallies the category spends and calculates your savings rates for the month. Principle is pulled from the Mortgage sheet and counted in your savings rate if you have it set to TRUE on the "FI" sheet. You'll see that I have a net savings rate. These are my own interpretations of how these should be calculated (charity being a difference of opinion amongst people) so feel free to edit to suit yourselves.
38
3) Paycheck deductions: Similar to your paystub, these should be used to ensure that your taxes and other deductions are accounted for. I've included 4 pay periods, but you don't have to use them all (there will be no impact to the calculations if you don't).
39
40
Net Worth
41
This spreadsheet tab keeps a running record of your net worth on a monthly basis. The accounts will need updated at the beginning of the month and the new Networth total will calculate as soon as it is the first of the month. The green title columns are assets, and the orange title columns are debts. I've added a few asset titles just as an example, but they aren't fixed. Feel free to replace them with your own. Do keep in mind that blue columns A - F are formulas that are used to calculate the FI number. Don't edit those!
42
43
FI
44
45
46
Mortgage and Mort Calculator
47
Enter the proper values for you mortgage and the start date on the top right in the white cells. Enter the appraised value in the upper left in the white cell. The sheet should update up to the current date of the loan. The numbers are then used in monthly sheets for the proper date. The calculator can be used to see what different values would do to your mortgage. It can be used for exploring without fear of the date being used anywhere else in the sheets.
48
49
Investments
50
These create the basic Asset Allocations on the dashboard. I update once a couple times a month. You can track multiple accounts and you just need to enter the proper ticker and keep the shares number up to date. The spreadsheet will update your accounts automatically. Once a month the account balances up top can be used to update the value at the beginning of the month on the "NW" sheet.
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...