Forecasting is very broad topic and analysis to get into. However, there is method in accounting using very popular method called, Financial Planning & Forecasting - Spreadsheet Modeling [Ref: 4]. Using this model and some basic reference to account 101, we can calculate Balance Sheet. All personal finance advice seems to boil down to three core pieces of advice:
Make more money
Spend less money
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.
We will take a simple look at this method step by step using Excel.
Step 1:
My income: DKK 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:
Step 2:
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:
Step 3:
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):
Step 4:
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)
Step 5:
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.
Step 6:
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)
Step 7:
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.
Step 8:
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:
Step 9:
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:
Step 10:
We highlight the whole table (Cells B51:H57), then go to the ribbon –> DATA –> What-if Analysis –> Data table
Step 11:
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:
Step 12:
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.
TanvirAlam
commented
7 years ago
Forecasting with mfbf.dk
Forecasting is very broad topic and analysis to get into. However, there is method in accounting using very popular method called, Financial Planning & Forecasting - Spreadsheet Modeling [Ref: 4]. Using this model and some basic reference to account 101, we can calculate Balance Sheet. All personal finance advice seems to boil down to three core pieces of advice: Make more money Spend less money 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. We will take a simple look at this method step by step using Excel.
Step 1: My income: DKK 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:
Step 2: 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:
Step 3: 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):
Step 4: 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)
Step 5: 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.
Step 6: 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)
Step 7: 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.
Step 8: 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:
Step 9: 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:
Step 10: We highlight the whole table (Cells B51:H57), then go to the ribbon –> DATA –> What-if Analysis –> Data table
Step 11: 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:
Step 12: 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.