Explanation
A while back, I got from a reader about investing for his grandkids.
And I want to graph this table of annual returns to demonstrate the effect of compounding.
I have started an investment program and have invested $100 per quarter for each grandchild for several years.
Everyone knows Excel can solve this problem, but most people have no experience setting things up.
Let’s do it!
This is the return earned as the underlying investment grows in value over time.
Growth is calculated as a percentage of the current balance (excluding new deposits or distributions).
Growth compounds over time as the base balance grows with deposits and reinvestments.
The distribution rate is calculated as a percentage of the current balance reinvested each quarter.
Distributions contribute to future compounding because they are reinvested.
Note: This model is designed for educational purposes only and should not be used as financial advice.
Actual investment results will vary!
Note: there are many tricks we could use to make this model more flexible and powerful.
That way, if you change an assumption, the model should respond accordingly.
It can also help you avoid basic mistakes by using Excel like a calculator.
Here are the formulas used in the worksheet:
Quarter- a running count of quarters.
Note this is the only formula in the worksheet that requires Excel 2021 or later.
Another option is to enter 1 in E5, and the formula =E5+1 in E6.
Then copy the formula down until it returns 72.
Take care when you copy the formulas to change the formula in F6beforecopying down the rest of the table.
Again, we lock the reference to C8 to prevent it from changing.
Note the first cell is anabsolute referencebut the second cell is arelative reference.
The formula in cell C12 is:
Ending Balance- The final ending balance in quarter 72 after 18 years.
The formula in D16 is:
This is just a simple way to indicate that the results match.
It uses an example where $100 is invested each quarter for 18 years.
The model assumes the investment grows by 3% annually and earns 2% in reinvested income.
The last step is to calculate the final results.
This is done in two different ways.
The table is better for learning because it shows how the money grows step by step.
However, the worksheet does use the FV function to check that the calculations are correct.
After 18 years is that a total of $7,200 is deposited.
This highlights how even small, regular investments can grow significantly over time.
Step-by-step instructions are provided for writing all formulas, and tips are included to verify everything works correctly.
you might download the final worksheet at the top of this page.
