Assignment #12, Spreadsheets in Mathematics Exploration

Use Excel to Demonstrate the Power of Compound Interest

By: Keith Schulte

Spreadsheets are capable of demonstrating and presenting many mathematics explorations. Today, we will use an Excel spreadsheet to demonstrate the power of compound interest over simple interest. Hopefully the demonstration will help us understand the impact of compound interest and simple interest. What if your bank offered to give you the choice of which one to use to add interest to your savings account! Would it make a difference? How big a difference would it make?

Before we look at a spreadsheet, let's review the concepts of compound interest and simple interest. Let's consider simple interest. The name implies that it is, somehow, less complicated. Simple interest calculates interest on the principal amount or the initial amount invested. It does not calculate interest on any interest that you have previously earned. A quick example, if you invested one dollar at six percent, at the end of year 1, your bank would give you your dollar plus six cents. If you left the money if the bank for another year, your bank would give you back your dollar plus the six cents for year one and another six cents for year two, for a total of $1.12 at the end of two years.

Compound interest returns interest on the initial investment but it also calculates interest on any interest previously earned. So, using our simple interest example numbers, we would have the following. We invest one dollar at six percent. At the end of year one, our bank would give us back our dollar plus six cents. There is no difference between the simple interest and compound interest calculations at the end of the first 'compounding' period. The reason is that there is no previous interest to calculate interest on. Let's consider a slightly larger amount so that the numbers will make more sense. Let's say that we invest $100. At the end of year one, we would be able to receive back our $100 plus $6 in interest (at 6%). At the end of year two, we would receive our $100 plus the first year's interest of $6 plus interest for one year on the investment of $100+$6 interest, at six percent which is $6.36. So, we would receive back $100+$6+$6.36, which totals $112.36. Using simple interest, on $100, we would receive $112.00.

We should also understand that the rate involved will make a difference in the compounding. To understand this concept, let me ask some questions. If you invested $1000 at 6%, how long would it take for your investment to double, that is, to grow to $2000? How long would it take for that same investment to double at 12%?

We've mentioned investments, but remember that the same idea of compounding applies to loans. Is the loan at simple interest or compound interest? Credit cards usually charge compound interest. Most car loans charge simple interest. What kind of interest is charged on a mortgage on a home?

The spreadsheet that I have attached, will allow you to change the principal amount and the interest rate. When you change either or both of these numbers, you will be able to see the new compounded and simple amounts.

Let me explain how I have used the spreadsheet to calculate the amounts. Cell A1,B1 and C1 are input areas. These are the only cells that should be changed. The compounded amounts in column E, use the column A and column B amounts. The simple interest amounts use columns A, B and C. So if you want to change the compound interest amounts, change the amounts in either A1 or B1 (or both). To change the simple interest calculations, change A1 and both B1 and C1 (if you want the rate to be 8%, then B1 should be 1.08 and C1 should be .08). ˆ

Excel also allows you to see the information in the form of a graph. I have put a line graph at the bottom of the spreadsheet comparing the compound amounts to the simple interest amounts. The graph shows the dramatic difference over longer periods of time.

So, click HERE to look at the spreadsheet. Enjoy your exploration!

Some ideas for further exploration are:

Ø Set up a worksheet that would allow you to compare different rates, say a compound rate of 6% and simple interest of 8%.

Ø Set up a spreadsheet that would allow you to compare a one-time initial investment of $1000 using compound interest to another investment that doesn't start for 20 years and invests $1000 a year, every year at the same rate. Which investment would be worth more after another 20 years?