Coaching Centre
Comparing investments with graphs and spreadsheets
Simple and compound interest side by side in tables, graphs and spreadsheets, spreadsheet formulas with absolute cell references, and comparing savings accounts, term deposits, shares and property.
A formula gives one answer. A table or a graph shows the whole story: when the money passes a target, when one option overtakes another, and how fast the gap grows. Banks, super funds and financial planners all show their numbers this way, so the HSC does too.
Simple and compound, year by year
Simple interest is calculated on the original amount only, so the same dollar amount is added every year. Compound interest is calculated on the current balance, so each year's interest is bigger than the last.
$\$10\,000$ is invested for $10$ years at $5\%$ p.a. Build a table of values for simple interest and for interest compounded yearly.
| Year | Simple ($\$$) | Compound ($\$$) | Difference ($\$$) |
|---|---|---|---|
| $0$ | $10\,000$ | $10\,000$ | $0$ |
| $1$ | $10\,500$ | $10\,500$ | $0$ |
| $2$ | $11\,000$ | $11\,025.00$ | $25.00$ |
| $3$ | $11\,500$ | $11\,576.25$ | $76.25$ |
| $4$ | $12\,000$ | $12\,155.06$ | $155.06$ |
| $5$ | $12\,500$ | $12\,762.82$ | $262.82$ |
| $6$ | $13\,000$ | $13\,400.96$ | $400.96$ |
| $7$ | $13\,500$ | $14\,071.00$ | $571.00$ |
| $8$ | $14\,000$ | $14\,774.55$ | $774.55$ |
| $9$ | $14\,500$ | $15\,513.28$ | $1013.28$ |
| $10$ | $15\,000$ | $16\,288.95$ | $1288.95$ |
After one year they are equal, because in the first year both earn interest on the same $\$10\,000$. After that, compound interest pulls away, and the gap itself grows every year.
What the graphs look like
Simple interest grows by the same amount each year, so its graph is a straight line (linear growth, gradient $Pr$). Compound interest grows by the same percentage each year, so its graph is a curve that gets steeper (exponential growth). Both start at the same point on the vertical axis: the principal.
Start with $\$10\,000$ at $5\%$ and check the table above. Which year does compound interest first beat simple interest by more than $\$1000$?
Push the rate up to $10\%$. Does the gap appear sooner or later? Tap Show formulas to see the spreadsheet behind each column.
Spreadsheet formulas
A spreadsheet calculates each row from the row above, exactly like the table. Put the rate in its own cell so you can change it once and watch every row update.
| A | B | C | |
|---|---|---|---|
| 1 | Rate | 0.05 | |
| 2 | Year | Simple | Compound |
| 3 | 0 | 10000 | 10000 |
| 4 | =A3+1 | =B3+10000*$B$1 | =C3*(1+$B$1) |
Row 4 is then filled down. The dollar signs in $B$1 make an absolute reference: when the formula is copied down, it keeps pointing at the rate cell. Without them, the copied formula would drift to B2, B3, and so on, and the column would fill with nonsense.
Comparing real investments
Interest rate is only one part of a good decision. Each type of investment trades off return, risk and access to your money.
| Investment | Return | Risk | Access |
|---|---|---|---|
| Savings account | Variable rate, interest usually monthly | Very low (government guarantee up to a limit) | Any time |
| Term deposit | Fixed rate, often a bit higher | Very low | Locked in, penalty to withdraw early |
| Government bonds | Fixed interest payments | Low | Can be sold, price varies |
| Shares | Dividends plus price changes | High: prices rise and fall | Sell on the share market |
| Property | Rent plus price growth | Medium, large amounts | Slow and expensive to sell |
Ava has $\$20\,000$ to set aside for $2$ years. A savings account pays $4.5\%$ p.a. compounded monthly. A term deposit pays $4.7\%$ p.a. compounded yearly (both rates are examples). Which earns more, and by how much?
Your turn
$\$5000$ is invested for $10$ years. Option A pays $6\%$ p.a. simple interest. Option B pays $5.5\%$ p.a. compounded monthly. Which is worth more after $10$ years, and by how much?
Show the working
A: $5000 + 5000 \times 0.06 \times 10 = \$8000$. B: $5000\left(1 + \dfrac{0.055}{12}\right)^{120} \approx \$8655.38$. Option B is worth $\$655.38$ more, even though its rate is lower, because compounding over ten years beats a slightly higher simple rate.
$B$1, keep the formula pointing at cell B1 when it is copied or filled down.The graph of the value of an investment earning simple interest against time is:
Simple interest adds the same amount, $Pr$, every year, so the value increases linearly.
Find the simple interest earned on $\$6000$ at $4\%$ p.a. for $5$ years.
$I = 6000 \times 0.04 \times 5 = \$1200$.
Find the value of $\$6000$ invested for $5$ years at $4\%$ p.a. compounded yearly, to the nearest cent.
$FV = 6000(1.04)^5 \approx \$7299.92$.
In a spreadsheet, the rate is in cell B1 and the year-0 balance is in C3. Which formula in C4 can be filled down to give compound interest?
Each row multiplies the row above by $1 + \text{rate}$. The rate cell must be absolute so it does not move when the formula is filled down, while C3 must stay relative so it becomes C4, C5, and so on.
Use the table of $\$10\,000$ at $5\%$ in this section. In which year does the compound value first exceed the simple value by more than $\$500$?
Year 6 difference: $13\,400.96 - 13\,000 = \$400.96$. Year 7: $14\,071.00 - 13\,500 = \$571.00$. So year $7$.
A term deposit of $\$15\,000$ pays $4.4\%$ p.a. compounded yearly for $3$ years. Find the interest earned, to the nearest cent.
$FV = 15\,000(1.044)^3 \approx \$17\,068.40$. Interest $= 17\,068.40 - 15\,000 = \$2068.40$.
Which investment usually has the highest risk of losing money in the short term?
Share prices can fall sharply over weeks or months. The other three pay a set or guaranteed interest rate.
$\$10\,000$ is invested at $5\%$ p.a. compounded yearly. After how many whole years is it first worth more than $\$15\,000$?
$10\,000(1.05)^8 \approx \$14\,774.55$ and $10\,000(1.05)^9 \approx \$15\,513.28$. So after $9$ years.
Exam style: $\$20\,000$ is invested for $2$ years. A savings account pays $4.5\%$ p.a. compounded monthly, and a term deposit pays $4.7\%$ p.a. compounded yearly. How much more does the better option earn, to the nearest cent?
Savings: $20\,000(1.00375)^{24} \approx \$21\,879.80$. Term deposit: $20\,000(1.047)^2 = \$21\,924.18$. Difference: $21\,924.18 - 21\,879.80 = \$44.38$, so the term deposit is better.
Exam style: $\$5000$ is invested for $10$ years, either at $6\%$ p.a. simple interest or at $5.5\%$ p.a. compounded monthly. By how much does the better option beat the other, to the nearest cent?
Simple: $5000 + 5000 \times 0.06 \times 10 = \$8000$. Compound: $5000\left(1 + \dfrac{0.055}{12}\right)^{120} \approx \$8655.38$. Difference $= \$655.38$.