Investigation: Exploring interest with spreadsheets

Spreadsheet programs can be very useful for computing and presenting numerical information. In this investigation we will learn how to use spreadsheets to compute schedules of interest for both simple and compound interest. 

Note: this investigation was written using a particular spreadsheet software, there are many available but the basic functions covered here are included in most programs. The notation and behaviour of your spreadsheet may differ slightly from what is presented if you are using a different program. If in doubt, ask your teacher!

Investigation 1: simple interest

Open up a spreadsheet and enter the following values and headings:

Highlight both of the cells A8 and A9 and then click and drag bottom the right corner of the highlighted region to continue the pattern down the column to get 8 periods. We will use this fill down feature frequently.

With your cursor in cell C8, type =B4 and press enter. You should find that the value of the principal in cell B4 now appears in cell C8. Try changing the value in B4 - you should see the new value also appear in C8 after pressing enter.

Next we want to calculate the simple interest accrued by the end of the first period. This is equal to the principal times the rate, i.e. B4*B5. We can enter this formula into cell B9. Be sure to use an asterisk for multiplication.

However, if we do this as above we will find errors when we fill down the formula. This is because when we fill down, B4*B5 gets shifted down to B5*B6 and so on.

We can prevent this from happening by inserting a $ sign into the formulas as follows:

To get the updated balance at the end of the first period, we add the interest in B9 to the previous balance C8. This time we do want these values to change as we fill down, so we don't use the $ sign.

Press enter and then fill down both columns

 

Questions

1. How much interest has been accumulated over the 8 years?
2. Check that the above amount agrees with what you get by using the simple interest formula.
3. What will the balance be at the end of 10 years?

Investigation 2: compound interest - annual compounding

Open up another spreadsheet and enter the following:

As before, the interest at the end of period 1 should be equal to C8*B5 and the new balance will then be C8+B9. But we are now using compound interest, so interest is calculated using the previous balance and not the principal. This means the interest at the end of period 2 will be C9*B5. So when we fill down the interest column we want the values C8*B5, C9*B5, C10*B5 ... and we can make this happen by entering the formula =C8*B$5 as in the picture below.

Next we add the interest to the previous balance to get the new balance:

 

Questions

1. Fill down the period, interest and balance columns to determine the balance after 10 years. Compare this value with the balance you found in investigation 1 for 10 years of simple interest.

2. Describe how the interest changes over time, and compare this with the behaviour you observed for simple interest in Investigation 1.

   • the first year

   • the second year

   • the tenth year

   • total over 10 years

3. If half the principal is invested, is the interest earned over 10 years also halved?

4. If the interest rate is halved, is the amount of interest earned over 10 years also halved?

5. If $10000 is loaned instead of invested would you need to change the spreadsheet to represent the situation? What would be the amount owing after 10 years if no repayments were made? 

6. Fill down the columns until the balance is double the principal.

   • How many years does it take?

   • If you change the principal does the doubling time change?

   • If you change the interest rate does the doubling time change?

 

Investigation 3: compound interest - other compounding periods

Modify your compound interest spreadsheet to have an input for the number of compounding periods per year.

The interest at the end of the first compounding period is equal to the previous balance multiplied by the interest rate per compounding period. The interest rate per compounding period will be the rate per annum divided by the number of compounding periods per year.

 

Questions

1. With the compounds per year set to 12, fill down the interest and balance all the way to 24 periods. How many years does this correspond to? What is the balance at the end of 24 periods?

2. Change the compounds per year so that it corresponds to quarterly compounding.

   • How many years are there in 24 periods now?

   • What is the balance after 2 years? How does this compare to the balance after 2 years of monthly compounding?

3. Change to weekly compounding and fill the columns down all the way to two years. What is the balance after two years now? How does it compare to the balance after 2 years of monthly compounding?

 

Extension

When saving up for an item it would be common to add a regular amount to a savings account rather than rely on interest to reach a goal. How could you alter the spreadsheet created above to include a regular payment?

Amelia wants to go on a holiday that costs \$3000, she has found a bank account that pays 5% p.a. compounded monthly and she has \$1800 to saved already. 

1. Using a spreadsheet, find how long will it take her to reach her goal if:

• She does not make any further payments.
• She makes payments of \$50 per month.
• She makes payments of \$75 per month.

2. If she wants to go on holidays in 8 months time, what is the minimum monthly payment she needs to make?

3. Amelia decides instead to borrow the additional money and go on holiday now. She plans to pay the loan off at \$50 per month at 5\% p.a. compounded monthly. How could we alter the spreadsheet to represent this situation? How long would it take to pay off the loan?