I really enjoy creating financial spreadsheets. For me, there is something… cathartic about the whole process. Anyway, all of my spreadsheets are available for free. To get your copy, subscribe to my mailing list on the right (or at the end of this page if you are on mobile).
You won’t be spammed with emails, and you can always unsubscribe after you’ve received the spreadsheets if you want to, I won’t judge 🙂
This page shows what to expect from the spreadsheet, and how to use each one. If you ever find any errors or want additional functionality, send me an email (david[at]zerodayfinance[dot]com) and let’s talk!
For both of these spreadsheets, I have screenshots showing their content. If you click on the screenshot, it will enlarge the photo to full size so you can see it better.
Zero Day Challenge Spreadsheet
The Zero Day Challenge is the system that I use to help control my spending. If you’ve never heard of it, I would give this post a quick read, then head back here. You only need to fill out a little bit of data for this spreadsheet to work. Specifically, fill out data in the second column of the January – December sheets, labeled on the bottom of your Excel document.
This is the first page in the Zero Day Challenge financial tracking spreadsheet. In this sheet, you enter your expenses into the second vertical column (the text is grayed out). That’s it! The sheet then calculates your spending curve and cumulative spending. In addition, you can fill out the “Goals” section at the top of the sheet. Then the spreadsheet will compare your actual spending with your target spending to hit your spending goal, as well as determine if you got as many zero days as planned.
There is a sheet for each month of the year. Just fill out the day’s spending, and it will do the rest.
This next sheet is called “log.” It will log spending for every day of the year in a running format. This information is used to populate the graph at the top of this page. The blue graph shows your day-to-day spending (scale on the left). The orange graph shows your cumulative spending (scale on the right).
This sheet is calculated for you automatically, you don’t need to do anything!
This final sheet presents your spending summary, broken down by moth. The most interesting parts of the sheet are “Average Spending” and “Average Real Spending.” “Average Spending” takes the total amount of money you spent during the year, and divides it by 365. That way, you can see how much you spend on average.
“Average Real Spending” takes the total amount of money you spent during the year, and divides it by the total number of days when you actually spent money. This shows you how much money you spend on days when you actually spend money. If you are considering trying out the Zero Day Challenge, this number is gold. In the photo above, you see that I spend an average of $107 on days when I spend money. Well, if I can eliminate 10 spending days, 10 days * $107 = $1,070 in savings.
Financial Master Spreadsheet
This next spreadsheet is what I use to track all of my financial information. Right now, I’m not directly investing in real estate. So this spreadsheet does not do a good job of tracking any of that. However, it is great at tracking your net worth, income, spending, savings, and your path towards retirement. We’ll go through every single sheet in this spreadsheet, and feel free to contact me if you have any questions.
As a note, if you are entering in data that is a debt, e.g. you owe money, enter that number as a negative number. For example, when entering in the account value of a credit card. If you owe $500 on the credit card, enter -$500.
All of the spreadsheets have a similar structure. Accounts, spending categories, income categories, etc are on the left hand side. There are columns for each month of the year across the top. Horizontal blue header lines denote different logical areas (for example, traditional vs. roth retirement accounts).
The first sheet is “Accounts.” Here, you enter in all of your account balances. I left in example accounts, feel free to replace them with your own. If you need to enter new accounts, enter it between two already existing rows. For example, if you want to add another asset, create a new row between Asset 1 and Asset 2, or Asset 2 and Asset 3. This ensures that the formulas remain consistent throughout. This holds true for all sheets. If you want to add a new row do it between two rows that already have values.
This sheet gives you a good summary of where your account balances have been over time. You can clearly compare how quickly you are increasing or decreasing your account balances. It is also great to see the value of your liabilities shrink. If you own any real estate, I would put the appraised value of the property under “Assets,” and the mortgage under “Liabilities.”
The second sheet covers savings. For each of your accounts that you regularly put money into, track your monthly contributions here. Make sure you separate taxable, pre-tax, and post-tax savings, these directly relate to savings rate, amongst other functions.
The third sheet covers spending. Here, feel free to track your spending however you wish. Your spending categories will obviously be different than mine. Remember, when adding rows to this sheet, make sure you do it between two rows that are already populated. For example, you can add “dentist payment” between “Car Insurance” and “Utilities.” However, if you try and add it after “Utilities,” Excel will not pick up on the change.
I wish I could call the Excel developers, and ask them why they designed insertion like this, it doesn’t make any sense.
The fourth sheet covers income. Add in all sources of income. The distinction between W2 and 1099 does not matter for calculations. However, notice that “PRE-TAX INVESTMENTS” is replicated here. Make sure that you enter in all savings that you deposit into these accounts on this sheet. This is important, and makes the savings rate calculation much easier to compute.
Phew! Those 4 sheets are where you need to input data, everything else will be calculated automatically. This sheet is the “summary” of your financial situation. Starting from the top, your monthly and cumulative income are calculated. Then your monthly and cumulative spending is calculated. Those two data points go into the saving section. Savings are calculated on a monthly and cumulative basis.
However, it also calculates a monthly and cumulative savings rate. If you have a total savings or savings rate goal, this is a great spot for you to see how well you are doing.
The next major section is net worth. It will calculate your net worth based on your investments, assets (such as home or car value), and your liabilities. Then your net worth is calculated by summing all of the values. Then your monthly / cumulative change is calculated in both dollars and percentage. This lets you see if you are on track to hit your savings and net worth goals.
The first column for this section is grayed out, because there is no previous data to compare it to. Just leave them blank.
This sheet shows a summary of your summary. See what I did there?
Anyway, it will show you your min / average / max for savings, spending, and income. Then, there are a set of FI parameters. Remember, FI means Financial Independence. The spreadsheet uses the information that you provided to calculate your average spending. Your average spending becomes your “Target Passive Income.” Your “Target Passive Income” is divided by the “Safe Withdrawal Rate” to calculate your “Target Portfolio Value.” If you want more information on this, read the Trinity Study.
Next, the sheet will use 3 similar models to calculate different FI countdown dates. The “min” data uses an optimistic 7% yearly growth rate, combined with the assumption that you will continue to invest monthly, using your maximum monthly savings amount.
The “expected” data uses a realistic 6% yearly growth rate, combined with the assumption that you will continue to invest monthly, using your average monthly savings amount.
The “max” data uses a conservative 5% yearly growth rate, combined with the assumption that you will continue to invest monthly, using your minimum monthly savings amount.
These calculations are purely theoretical. Your results will vary. They are designed to show you what could happen if you continue investing, not what actually will happen.
Finally, “Current Status” shows your current investment, asset, and liability values. In addition, it calculates an estimated passive income based on your investments and a 4% safe withdrawal rate. If you own significant real estate, this calculation will not work nicely, and you’ll need to tweak the spreadsheet. Finally, “% FI” takes the calculated passive income, and divides it by your “Target Passive Income” from above, to determine how far along on your way to financial independence you are.
This final sheet is uses primarily for research. The top section contains a chart. The vertical axis is a target portfolio value. The horizontal axis is your investment time horizon in years. Values in the chart represent how much money you need to invest every month to reach your “target portfolio value” in a given “investment time horizon.” It uses information from the chart to the right. Namely, your current portfolio and growth rate. In addition, it will highlight cells that fall between your minimum and maximum monthly investment, as recorded by the spreadsheet. This shows you where you currently stand. Any one of those highlighted scenarios is possible, but your results will vary.
The second chart on the bottom of the page estimates what your investment portfolio will loo like over time. The data points in the chart and the graph are correlated (e.g. they have the same exact value, I just display them differently). This uses information from the spreadsheet to estimate your future portfolio value. It uses your current portfolio value, a 7% growth rate, and your average monthly investment for this calculation.