Compound interest
Save this compound interest (XLSX 24kB) spreadsheet to your laptop (right click > Save Target As).
On the COMPARISON worksheet complete tasks A and B, and complete task C using the PAY-OFF worksheet (record your answers within the spreadsheet):
Review absolute and relative cell references by watching this video.
Enter values for the principal and interest rate (decimal) to begin.
-
Explain why the formula =C6+$C$6*$E$3 is used in C7 to give the simple interest total for period one. Copy down to fill the rest of that column in the table.
-
Explain why the formula =C6*(1+$E$3) is used in cell E7 to explain the compound interest total in period one. Copy down to fill the rest of that column in the table.
-
Why is compound interest often called ‘interest on interest’?
Record your answers within the spreadsheet.
The ‘Rule of 72’ is a quick method used to estimate how long it will take an investment to double in value. Mathematically, the number of years N = 72÷r. For example, with 8% p.a. interest, it would take N = 72÷8 or 9 years for your money to double.
-
What formulae would you type into cell G9 to test this rule?
-
Does the rule seem to work? Try your own one in cell G12.
Record your answers within the spreadsheet.
In the compound interest spreadsheet click on the PAY-OFF worksheet tab at the bottom of the spreadsheet and complete the following task:
The graph shows repayments for loans up to a period of 10 years.
Enter various borrowing amounts and interest rates (entered as a decimal amount) and investigate the required number of monthly repayments.
Unhide column H (select columns G and I > right click > Unhide) and select cell H4. How would you change this formula to reflect a situation that involved fortnightly payments?
Design a series of five questions to accompany this spreadsheet. For example, what is the effect of doubling a repayment? Include the questions within the spreadsheet.