Compound interest

Review absolute and relative cell references by watching this video¹.

Enter values for the principal and interest rate (decimal) to begin.

1. 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.

2. 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.

3. 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.

1. What formulae would you type into cell G9 to test this rule?

2. 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.