Use a spreadsheet to help see the effects of three key personal finance tips: Earn more, spend less, invest wisely
A quick search for “personal finance” on Amazon.com will reveal a vast amount (100+ pages of results) of literature on this important topic. We imagine that a fresh 22 year old college graduate could easily get overwhelmed by the chorus of advice and opinions. For them, we offer this post on how to use a spreadsheet to forecast your financial future, and see how your decisions will affect your finances in future years.
All personal finance advice seems to boil down to three core pieces of advice:
1. Make more money
2. Spend less money
3. Invest your saved money wisely
These three things should lead to the ultimate goal of saving and investing enough money so that someday your investments can generate enough income to substitute for working income, allowing for retirement. Also, hopefully allowing for some fun along the way!
1. Defining spreadsheet Inputs:
We’ll base our spreadsheet on the numbers in this website. The beauty of spreadsheets is that the inputs can then be set to any specific situation.
Today’s work income: $63,784
Expenses: We’ll enter these as a % of income – Taxes (20%), Housing (30%), Food (10%), Transportation (15%), Entertainment/Other (10%), Healthcare (5%). The leftover goes into saving (10%)
Here’s how our sheet looks so far, note the formulas in B7:B12 uses a fixed cell reference on income and multiplies it by the assumed % to calculate the expense:
2. Constructing the Projection Model
Now we set up our projection model. We start with the above assumptions for the year 2015 in column B, then each column to the right of B will be one year forward:
Note that we summed total expenses and took (Income – Total Expenses) to get Net Income.
Now that we have some savings, we need to add in a row that tracks our total savings, and we have to add in an assumption that we will invest our savings at a reasonable rate of return (here we assume 4%, though we know that you can’t get that in a savings account these days…that may be a future post), and then add in an income line for investment income with the formula (last year’s savings x investment return rate):
Next we forecast forward all the rows for 2016 using formulas that we will later copy over through 2070. We start by adding an assumption for income growth (remember rule #1. Make more money)
We add in formulas for the expenses assuming they stay at the same percentage of income. Then we add last year’s cash balance to the savings we generated this year.
Finally, assuming we did our cell referencing correctly, we can copy all these formulas right to the year 2070, and our model is complete, giving us a projection of our income, expenses, and cash position for each year through 2070:
(Here we hide the years between the decades for display purposes)
3. Now that we have a spreadsheet model of our income, expenses, and savings over time, we can ask and calculate the answers to interesting questions, like:
What year will I become a millionaire?
– We use an IF function to see whether our cash/investment balance is above $1 million, returning “Y” if yes and “N” if no. We paste this formula rightwards through 2070.
Then we use the MATCH() function to determine how far down the first “Y” occurs, and add it to 2015 to get the year we become a millionaire.
Millionaire in 2057!
What year will our investment income alone cover our expenses, allowing us to kick back and not work?
We take a similar approach as the millionaire problem, this time the IF function checks our investment income against our expenses:
Unfortunately in this example, financial freedom doesn’t happen by 2070, so we’ll have to rely on social security, asset spend down, or one of our kids making it big in professional sports!
Advanced Topic – Use an Excel data table to test the effects of the three central pieces of personal finance advice: Earn more, spend less, invest wisely
Data Tables allow us to input a variety of variables into our model in order to see what their effect on a key output variable is, without having to manually enter in each combination of potential inputs individually.
First, we set up the table. We put the key output variable (the year we achieve financial freedom) in cell B51, and the expense ratios we want to test in cells B52:B57, and the income growth rates in C51:H51:
We highlight the whole table (Cells B51:H57), then go to the ribbon –> DATA –> What-if Analysis –> Data table
Excel asks us what cell we want to use as the row input (Annual Income growth) – we give it B14. Then the column input is F4.
Note here we changed cells C7:C12 to be a function of our expenses/income ratio, which is located in cell F4, allowing us to change our expenses with one input cell:
The output: In the data table, for each cell C52:H57, excel plugs the same column annual income growth number in row 51 and the same row Expenses/Income ratio into our model and spits out the year that our investment income is higher than our expenses.
Here we see that the more we can grow our annual income, or the less of our income we spend, the sooner we’ll be able to cover our expenses with investment income and not need to work. If we really push it, saving half of our income, and growing income 6% every year, we can retire as soon as the year 2030. The data table helps us see the effect of changing each variable – the retirement year moves up to 2035 if we increase our spending to 60% of income while keeping income growth at 6%.
The spreadsheet can be accessed here: Personal Financial Model
Next time…we add in Rule #4 to our rules and our model: Don’t take out high interest rate debt!!