Say we invest \$5000 at 6% per annum where interest is compounded monthly. At the end of each month, after the interest has been added, we'll make an additional deposit of \$100.
Firstly we need to change our interest rate from annually to monthly by dividing by 12, so we get 0.5%.
The balance at the end of the first few months can be calculated as follows.
| End of month 1 | 1.005\times 5000+100 | = | \$5125 |
|---|---|---|---|
| End of month 2 | 1.005\times 5125+100 | = | \$5250.63 |
| End of month 3 | 1.005\times 5250.63+100 | = | \$5376.88 |
Notice for our purposes here we round each balance to 2 decimal places and then use the rounded amount in our summary.
To be able to examine the balance in a few years time, we don't really want to manually calculate the values in the table. Instead, we'll write a recurrence relation and then analyse it using a spreadsheet.
B_{n+1}, the balance at the end of month n, can be defined as:
B_{n+1}=1.005B_n+100, where B_0=5000
Notice I've used our initial investment amount and provided B_0=5000 rather than using B_1=5125. We can use either of these.
To determine the value of our investment after 2 years, we'll use the spreadsheet applet below:
You can change the original investment amount, the annual interest rate, how often the interest is compounded and the regular deposit amount. Note that here our extra deposits always match the number of compounding periods each year.
For the example above, the amount invested is \$5000, the annual interest rate is 6\%, the number of compounding periods is 12 per year and the deposit amount is \$100. So the values in cells B1 to B4 are correct for our example. However, the data only goes to the 12th month, and we need to go up to the 24th month.
Activity 1
- Double click on the cells in the above spreadsheet to see how the values are being calculated. (We can use the esc button to stop viewing the formula for a cell)
- Extend column A so that the time period goes up to 24.
- Extend column D so that the deposit is 100 for all 24 time periods.
- Row by row, drag the formulas in columns B, C, and E to generate the values for each time period. (To drag down the cell, click on the cell then drag down the bottom right corner)
In cell E30 you should have a total of \$8178.99 after 24 months.
Activity 2
- Use the applet to double the interest rate. Describe the effect this has on the value of the annuity after 24 months.
- Now halve the monthly contribution, making it \$50 per month. Does this cancel out the effect of doubling the interest rate? Explain your answer.
- With this new interest rate and contribution, use trial and error to find a new original investment amount that generates a similar final amount to \$8178.99.
- \$6000 is invested at an interest rate of 8\% compounded quarterly, where contributions of \$250 are made every quarter. Use the applet to find the duration of the annuity if we want a final value to be at least \$12000.