Modelling Investments with a Spreadsheet
Use a spreadsheet to follow an investment for NSW Year 12 Mathematics Standard 1 (NESA outcome MST-12-S1-02). Each row of the spreadsheet is one year: the interest is added to the opening balance, and the closing balance carries down to become the next year's opening balance.
You will read, complete and check spreadsheets for simple interest and for compound interest, see why simple interest adds the same amount each year while compound interest grows, and find the total interest earned — the same skills you need to plan your own savings.
Theory
Modelling investments with a spreadsheet is part of the Investment topic in Year 12 Standard 1 (NSW). Each row of the spreadsheet is one year: closing balance = opening balance + interest, and the closing balance carries down to the next row. This guide shows how to complete simple and compound interest spreadsheets and find the total interest earned.
A spreadsheet can model an investment one year per row. Each row has an opening balance, the interest earned that year and a closing balance, where closing balance \(=\) opening balance \(+\) interest.
The closing balance carries down to become next year's opening balance. With simple interest the interest is always worked out on the original amount, so the same interest is added every year and the balance grows in a straight line. With compound interest the interest is worked out on that year's opening balance, so the interest grows every year and the balance grows in an upward-bending curve.
The total interest is the final closing balance minus the amount invested (or the sum of the Interest column). In this Year 12 Standard 1 (NSW) topic you read, complete and check investment spreadsheets and compare simple with compound interest.
Every row of an investment spreadsheet uses the same two rules:
For simple interest the interest is the same every year, worked out on the original amount \(P\):
For compound interest the interest is the rate times that year's opening balance. The final balance can be checked with:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Opening balance ($) | Interest ($) | Closing balance ($) |
| 2 | 1 | 2000.00 | 120.00 | 2120.00 |
| 3 | 2 | 2120.00 | 127.20 | 2247.20 |
| 4 | 3 | 2247.20 | 134.83 | 2382.03 |
Spreadsheet formulas for the table above: C2 = 0.06*B2, D2 = B2+C2, B3 = D2, then fill down.
How to model an investment with a spreadsheet
- Set up the columns: Year, Opening balance, Interest and Closing balance, with one row per year.
- Work out the interest: simple interest uses rate \(\times\) original amount (the same every year); compound interest uses rate \(\times\) that year's opening balance. Round to the nearest cent.
- Add and carry down: closing \(=\) opening \(+\) interest, then copy the closing balance down as the next year's opening balance.
- Answer the question: read the final closing balance, or find the total interest \(=\) final balance \(-\) amount invested. Check compound answers with \(FV=PV(1+r)^n\).
| Year | Opening ($) | Interest ($) | Closing ($) |
|---|---|---|---|
| 1 | 4000 | 240 | 4240 |
| 2 | 4240 | 240 | 4480 |
| 3 | 4480 | 240 | ? |
Simple interest adds the same amount every year, \(6\%\) of the original \(\$4000\).
| \(\text{Interest}\) | \(=\) | \(0.06 \times 4000 = 240\) |
| \(\text{Closing}\) | \(=\) | \(4480 + 240\) |
| \(\) | \(=\) | \(4720\) |
Check: \(4000 + 3 \times 240 = 4720\). The closing balance is \(\$4720\).
Year \(2\) opens with Year \(1\)'s closing balance, and the interest is \(8\%\) of that.
| \(\text{Opening}\) | \(=\) | \(3240\) |
| \(\text{Interest}\) | \(=\) | \(0.08 \times 3240 = 259.20\) |
| \(\text{Closing}\) | \(=\) | \(3240 + 259.20\) |
| \(\) | \(=\) | \(3499.20\) |
The Year \(2\) interest is \(\$259.20\) and the closing balance is \(\$3499.20\).
Interest \(=0.06\times\) opening; closing \(=\) opening \(+\) interest, carried down.
| Year | Opening ($) | Interest ($) | Closing ($) |
|---|---|---|---|
| 1 | 1500.00 | 90.00 | 1590.00 |
| 2 | 1590.00 | 95.40 | 1685.40 |
| 3 | 1685.40 | 101.12 | 1786.52 |
| \(\text{Total interest}\) | \(=\) | \(1786.52 - 1500\) |
| \(\) | \(=\) | \(286.52\) |
Check: \(90 + 95.40 + 101.12 = 286.52\). The total interest earned is \(\$286.52\).
Simple adds \(0.10\times2000=200\) every year; compound adds \(10\%\) of each opening balance.
| \(\text{Simple}\) | \(:\) | \(2200,\ 2400,\ 2600\) |
| \(\text{Compound}\) | \(:\) | \(2200,\ 2420,\ 2662\) |
| \(\text{Difference}\) | \(=\) | \(2662 - 2600 = 62\) |
Compound interest gives \(\$62\) more after \(3\) years. Its interest grows (\(200, 220, 242\)), so over a longer time its graph bends upwards away from the straight line.
Common pitfalls
Frequently asked questions
How do you model an investment in a spreadsheet?
Use one row per year with columns for the opening balance, the interest and the closing balance. The closing balance is the opening balance plus the interest, and it is copied down to become the next year's opening balance.
What spreadsheet formula calculates compound interest?
If the opening balance is in cell B2 and the rate is 6 percent, the interest is =0.06*B2, the closing balance is =B2+C2 and the next opening balance is =D2. Filling these formulas down builds the whole table.
What is the difference between simple and compound interest in a spreadsheet?
With simple interest the Interest column is the same every year because it is always worked out on the original amount. With compound interest the Interest column grows because it is worked out on each year's opening balance.
How do you find the total interest earned?
Subtract the amount invested from the final closing balance, or add up the Interest column. For $1500 at 6 percent compound interest for 3 years the final balance is $1786.52, so the total interest is $286.52.
Why does a compound interest graph curve upwards?
Each year the interest is added to the balance, so the next year's interest is worked out on a bigger amount. The balance grows by more each year, so the graph bends upwards, while simple interest gives a straight line.