Simple investing worksheet

A while back, I got from a reader about investing for his grandkids. The email asks the following question:

I hope you can assist me with an Excel calculation. I am keen to show my grandchildren the power of compound interest and the value of investing (patiently) for the long term. 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. I want to give each of them the benefits of investing $100 per quarter over 18 years, assuming an investment return of 3% per annum (or 0.75% per quarter) capital growth plus the reinvestment of distributions paid quarterly. I will assume distributions are 2% per annum (or 0.5% per quarter)

This is a great question! Everyone knows Excel can solve this problem, but most people have no experience setting things up. Let's do it!

The Goal

This project aims to create an Excel model that shows how money can grow over time through compound interest and regular investing. From a practical standpoint, the model needs to calculate the growth of an investment over time, factoring in ongoing contributions, compounding, and reinvested distributions. The key assumptions are as follows:

• Quarterly Deposits: $100
• Quarterly Growth Rate: 0.75%
• Quarterly Distribution Rate: 0.50%
• Total Quarters: 72 (18 years * 4)

The Quarterly Growth Rate refers to the increase in the value of the capital invested due to capital appreciation. 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 Quarterly Distribution Rate refers to the income generated by the investment (e.g., dividends or interest) that is reinvested rather than withdrawn. 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. The calculations are based on simplified assumptions and do not account for factors like taxes, fees, inflation, or changes in interest rates or market conditions. Actual investment results will vary!

Note: there are many tricks we could use to make this model more flexible and powerful. We could, for example, make the period a variable (i.e., month, quarter, year) and adjust the formulas to use a variable period. However, for this project, I want to keep the worksheet very simple in order to keep the focus on the goal, which is to model the problem as stated above.

Entering Assumptions

The first step in building this model is to set up an area to hold assumptions. In all models like this, the goal should be to keep all assumptions in one place and enter values in a way so they can be used by the formulas that perform the actual calculations. That way, if you change an assumption, the model should respond accordingly. In this worksheet, the assumptions appear in the upper left:

The inputs themselves are entered in column C5:C9. Some inputs are hardcoded, and some are entered as formulas:

• Starting balance - we need a starting point for our initial balance. Since the investment program starts from scratch in the first quarter, we enter zero in cell C5.
• Quarterly Deposit - the amount of money that will be invested each quarter. This is a fixed amount of 100, so we enter 100 in cell C6.
• Quarterly Growth Rate - This is the percentage to use for quarterly growth (0.75%). We can either hardcode the value as 0.75% or enter the value using the formula =3%/4 as a reminder that the quarterly rate comes from an estimated annual growth rate of 3%.
• Quarterly Distribution Rate - the percentage used to calculate quarterly distributions. Again, this can be entered as 0.50% or with the formula =2.00%/4.
• Term in Quarters - entered with the formula =18*4 to calculate 72 quarters over 18 years.

Tip: using simple formulas to enter some assumptions is an easy way to provide more information about how inputs are derived. It can also help you avoid basic mistakes by using Excel like a calculator. For example, entering the Quarterly Growth Rate with the formula =3%/4 is a reminder that this value is based on an estimated annual growth rate of 3%.

Single Formula Option

Before we go through the steps to set up the calculation table, I want to be clear that you can calculate the ending balance after 18 years with a single formula based on the FV function like this:

=FV(C7+C8,C9,-C6,-C5,0)

The FV function calculates the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. For this problem, I have configured the FV function like this:

• rate - The periodic interest rate. Provided as C7+C8 (0.0075+0.005 = 0.0125). Note that we need to match the rate to the period, which is quarterly.
• nper - Total number of payments. Provided as C9 (72) because there are 72 quarters in 18 years.
• pmt - The fixed payment made each period. Provided as -C6 (-100) because the quarterly deposit is an outgoing cash flow and supplied as a negative number by convention.
• pv - The present value. Provided as -C5 (0) since the starting balance is zero. 

This formula works fine and calculates the correct result. In fact, this is the formula used in cell C16 to check the results of the calculation table:

However, a formula like this lacks transparency because the FV function is a "black box" that doesn't show the step-by-step process of how compounding and contributions work. The calculation table in the workbook is better for teaching concepts like compound interest because you can see how contributions, contributions, growth, and reinvested distributions work in each quarter. 

Building the calculation table

The next task is to build the calculation table seen in columns E through K. The actual range is E5:K76, excluding the table headers in row 4. In total, there are 7 columns calculated using formulas:

Except for the formula in F5 (see below), all formulas are meant to be copied down the table without adjustment. Here are the formulas used in the worksheet:

Quarter - a running count of quarters. The quarter numbers are generated with the SEQUENCE function with this formula in cell E5:

=SEQUENCE(C10) // generate numbers 1-72

SEQUENCE gets the number in cell C10 (72) and generates a sequence of numbers from 1-72. SEQUENCE returns all numbers at once in an array that lands in cell E5 and spills into the range E5:E76.

Note this is the only formula in the worksheet that requires Excel 2021 or later. In an older version of Excel, a simple approach is to enter 1 in E5 and 2 in E6, then select both cells and drag the fill handle down to enter all 72 numbers. Another option is to enter 1 in E5, and the formula =E5+1 in E6. Then copy the formula down until it returns 72.

Start Balance - the starting balance in F5 is determined by a simple formula that gets a value from the assumptions table:

=C5 // get start balance from assumptions

After quarter 1 in row 5, we get each quarter's starting balance from the previous quarter's ending balance. In row 6, the formula for Start Balance in F6 is different and looks like this:

=J5 // get start balance from previous quarter

This is the only formula in the calculation table that is different from the formula in row 5. Take care when you copy the formulas to change the formula in F6 before copying down the rest of the table.

Deposit - The deposit amount in G5 comes directly from the cell C6 in the assumptions area:

=$C$6 // get deposit amount

Note that we use an absolute reference to lock the address ($C$6) so it doesn't change when the formula is copied down the table.

Growth - In the growth column, we multiply the Start Balance in column F by the quarterly growth rate in cell C7 in the Assumptions.

=F5*$C$7

The quarterly growth rate in C7 is also entered as an absolute reference to prevent changes when the formula is copied down the column.

Distribution - In the Distribution column, we multiply the Start Balance in column F by the Quarterly Distribution Rate in cell C8 in the Assumptions.

=F5*$C$8

Again, we lock the reference to C8 to prevent it from changing.

End Balance - the end balance in column J is calculated with the SUM function like this:

=SUM(F5:I5)

This formula adds up the Start Balance, the Deposit, the Growth, and the distribution in the current quarter to arrive at an ending balance for each quarter.

Total Deposits - The Total Deposits in column K are calculated with the SUM function and an expanding reference that looks like this:

=SUM($G$5:G5)

The reference inside SUM, $G$5:G5, is an expanding reference. This is a simple trick you can use to calculate running counts and running totals in a table. Note the first cell is an absolute reference but the second cell is a relative reference. As the formula is copied down the table, the reference inside of SUM will expand to include each new row.

Here is a summary of the basic steps to create the calculation table:

1. Enter all formulas in row 5, as shown above.
2. Copy the formulas in F5:K5 into row 6.
3. Adjust F6 to be =J5 as explained above
4. Copy all formulas F6:K6 down the rest of the table.

Tip: In step 4, select all formulas in F6:K6, then double-click the fill handle as a shortcut to "send" the formulas down in one step. Excel will use the quarter numbers in column E as a guide and will stop copying when it reaches the last quarter in row 76.

The Final Results

The final results from the model appear below the assumptions:

The formulas used in this section are as follows:

Total Deposits - The value of all quarterly deposits made over 18 years. The formula in cell C12 is:

=SUM(G5:G76)

Ending Balance - The final ending balance in quarter 72 after 18 years. The formula in C13 is:

=J76

Total Gain - The total gain achieved based on the assumed growth rate and distribution rate. The formula simply subtracts Total deposits from the Ending balance:

=C13-C12

Calculation check - This is a safety check to ensure the calculation table generates correct results. The formula in cell C16 is based on the FV function, as explained above:

=FV(C7+C8,C9,-C6,-C5,0)

Checkmark - in cell D16, we compare the calculated Ending balance in C13 to the result from FV and display "✓" for a matching result and "X" if the results don't match. The formula in D16 is:

=IF(C16=C13,"✓","X")

This is just a simple way to indicate that the results match.

Summary

This article shows how to use Excel to model how money can grow with compound interest and regular investing. 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 article explains how to set up the worksheet to capture all necessary assumptions and how to build a table to calculate the starting and ending balance for each quarter, including deposits, growth, and reinvested earnings.

The last step is to calculate the final results. This is done in two different ways. The article also compares using the FV function and a single formula to calculate the final balance with building a calculation table. 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. Thanks to the power of compound interest, this investment grows to $11,567.36, resulting in a gain of $4,367.36. 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 make sure everything works correctly. You can download the final worksheet at the top of this page.