SAVINGS AND COMPOUND INTEREST
By Leighton McIntyre
Goal : To use spreadsheet application to explore savings and compound interest calculations and derive formula for compound interest.
Compound Interest Problem
Jasmine has $5000 in her piggy bank. Her dream is to pay the deposit for her dream house for $6,100. If the bank pays interest on savings of 5% per annum compounded monthly. How long will it take Jasmine to save enough money to pay the deposit for her dream home? How much would Jasmine be able to save in this same time period if the interest was compounded:
annually?
quarterly?
semiannually?
Solution
Microsoft Excel is a spread application that can be used to calculate the compound interest on the sums involved and this will be shown in a table of items following the amounts for each of the given program times involved.
We must first input the values of the initial amount of $5000 in the cell. The annual interest rate is 5% percent so to compound this on a monthly basis, we must divide 5% by 12. This gives us a monthly interest rate of 0.00416666666666667. We now use this interest rate to calculate the interest on Jasmine's savings. The Excel calculation shows that Jasmine will achieve the deposit for her dream home in 48 months. See diagram below.
In the diagram above, the first column represents the months that the money remains in the bank, the second column represents the compounded interest earned each month, and the third column represents the compounded total each month. The figure shows that the money that Jasmine puts into the bank initially will earn her enough money, compounded total of $6104.48, to make the deposit in her dream home in 48 months.
The interest rate of 0.00416666666666667 was input into another cell (not shown) and this was used to multiply the initial $5000 to get the interest for the first month, which was added to the initial amount of $5000 and then multiplied by the interest rate to get the interest the interest for the second month. The use of the excel spread sheet simplifies this computation because the formula van simply be copied into the cells and the calculations are automatically done by the software.
Thus for the second set of calculations for the interest compounded annually, the interest amount of 5% is used for this four year period. The figure below gives the results of this Excel calculation.
Notice here that there are only 4 compoundings. this is because there are 48 months in four years and so compounded annually it is only 4 times. the annually compounding interest is 5% (not shown on diagram). Notice also that the final total is $6077.53, which is less that the amount received when the interest was compounded monthly.
For the third set of calculations, the interest is compounded on a semiannual basis and the interest rate is 5% divided by two, which is 2.5% on a each half year period. Thus the interest will be compounded over 8 periods because there are 8 half years in 48 months. The figure below shows the Excel calculations for the money that Jasmine puts into the bank, compounded semiannually.
Notice here that the final total is $6092.15, which is less that the amount received when the interest was compounded monthly but more than when the interest was compounded annually.
For the fourth set of calculations, the interest is compounded on a quarterly basis and this lead to 16 compoundings of the money that Jasmine puts into the bank. The interest rate is now 5% divided by 4 which is 1.25% per quarter. The sixteen compoundings arise because there are 16 quarters in 48 months. THe diagram below shows the the Excel calculations for the money that Jasmine puts into the bank, compounded quarterly.
Notice here that the final total is $6099.45, which is less that the amount received when the interest was compounded monthly but more than when the interest was compounded annually or semiannually.
The Compound Interest Formula
Notice that in each case Jasmine puts $5000 in the bank. This amount is called the principal, denoted by the letter P.
Notice also that there is the annual percentage interest or APR (or simply r for short) of 5% in each case.
Let us begin with the simplest case of the interest is paid annually (case 2).
So for the first year we have P + P*r in the bank. Let us call the total accumulated amount A, with numbers 1, 2, 3...,y representing the years the money stays in the bank. Thus A1 = P (1+r)
Now in the second year we have the accumulated for the first year times the interest again so it is A2 = P (1+r) (1+r) or A2 = 
where 2 represents the number of years.
Continuing to the third year, we have accumulated the total for the second year plus interest on that accumulated total for the third year and thus we have
A3 = P (1+r) (1+r) or A = 
Because we can see a pattern emerging we cant assign a letter to the number of years and thus write for Y years Ay = 
Now notice that when we calculated the interest for the monthly compounding we divided the APR by 12 and when we calculated the semiannual compounding se divided the APR by 2, and when we calculated the rate for the quarterly compounding we divided the APR by 4. Now if we let the divisions be denoted by the letter n, then the r in the parenthesis will become r/n. Notice also that instead of once per year, the number of confounding will be 12 per year for the monthly, twice per year for the semiannually and four times per year for the quarterly. Thus the power raised in the number of compoundings will be by the number of times that the interest is compounded per year.
Any = 
Where
A = Accumulated amount
P= Principal
r = annual percentage rate
n= number of compounding periods
y= number of years.
Additional Investigation:
It would be very interesting for you to try to find out how long it would take Jasmine to reach her goal if she placed an additional $100 in the bank each month.