Spreadsheets are powerful tools for "what-if analysis" as you can change the principal, interest rate, payment or compounding period and see the effect this has on the loan. In the investigation Exploring interest with spreadsheets we looked at examples involving investments and how to set up a spreadsheet to view and analyse the progressing of the investment over time. This investigation will focus on loans, firstly let's review some key concepts.
Let's work through setting up a spreadsheet for a simple and compound interest loan using an example.
You wish to borrow \$15000 to buy a car. The interest rate is 4.50% p.a. simple interest and you think you can pay \$400 per month off the loan.
Step 1 - Enter the data below into the spreadsheet program.
Notice:
Step 2 - To create the numbered list in column A, first highlight the two cells containing 1 and 2, then point your mouse at the right hand bottom corner of the selection and drag the box down the page.
Step 3 - Now drag the formula in the other cells down the spreadsheet as shown in the screenshot below. This copies the formula down the page.
Step 4 - The spreadsheet should now look like this:
Step 5 - Scroll down and look for the row where the balance changes from a positive number to a negative number. This means the loan is paid off. In this case it is in row 51, or after 44 months.
Use the spreadsheet to answer the following questions.
Let's consider the same scenario as above but this time with a compound interest rate. Again we wish to borrow $15000 to buy a car. The interest rate is 4.50\% p.a. compounded monthly and you think you can pay $400 per month off the loan.
Step 1 - Enter the data below into the spreadsheet program.
Notice:
Step 2 - To create the numbered list in column A, first highlight the two cells containing 1 and 2, then point your mouse at the right hand bottom corner of the selection and drag the box down the page.
Step 3 - Now drag the formula in the other cells down the spreadsheet as shown in the screenshot below. This copies the formula down the page.
Step 4 - The spreadsheet should now look like this:
Step 5 - Scroll down and look for the row where the balance changes from a positive number to a negative number. This means the loan is paid off. In this case it is in row 48, or after 41 months.
Use the spreadsheet to answer the following questions.
In the examples above we investigated the time taken to pay off a loan given a payment amount. It is also common to choose a term of the loan and find the minimum payment required to pay off a loan in this set time frame.
For simple interest we can find the total loan and interest to be paid over the term of the loan and divide this into equal payments. Let's take our previous example of a \$15000 loan at 4.5\% simple interest but this time we want to pay off the loan in two years.
\text{Interest} | = | P\times R\times T |
= | \$15000\times 0.045\times 2 | |
= | \$1350 | |
\text{Total cost of loan} | = | \text{Principal}+\text{Interest} |
= | \$15000+\$1350 | |
= | \$16350 |
Over 2 years we will have 24 monthly payments, hence each payment will be:
\text{Payment} | = | \frac{13650}{24} |
= | \$681.25 |
We can simply update the spreadsheet with this payment or we could include a cell to calculate the payment given the term if we were investigating several similar cases.
Using this payment the spreadsheet should reach a zero balance at the end of 24 months and look like:
For compound interest it is also common to set the term of the loan and find the minimum payment required. To calculate the minimum payment we could use a financial application in a calculator or online, or we can also use the following formula in the spreadsheet:
=PMT( Rate per period, Total number of periods, Loan amount)
For example, =PMT\left(0.045/12,24,\$15000\right) would calculate the repayment required for our \$15000 loan at 4.5% p.a. compounded monthly for 2 years.
Using this formula in the payment cell, we can produce the following spreadsheet.
In the lesson Comparing simple and compound interest we examined the differences in graphs of simple and compound interest investments. We saw investments with the same rate and principal would start at the same principal value (y-intercept) and the simple interest graph would increase at a linear rate, while the compound interest graph increased at an increasing rate leading to a curve. Generally the graphs look something like:
How do you expect the graph of the balance of simple and compound interest loans over time to look? Take a moment to think and sketch the general shape of the balance of a loan, think about:
We can use a spreadsheet to graph the balance over time to investigate.
For example we can use the spreadsheet for our previous example with the term of 24 months.
Using a column for the time in months and balance as follows:
Next:
You should obtain a graph which looks like:
For a simple interest loan we have the balance starting at the principal loan amount and decreasing at a constant rate.
What do you expect to see if you graph the balance of the compound interest account over time?
We should expect to the balance starting at the principal loan amount and then decreasing at a faster and faster rate causing the graph to curve. Since the balance does not decrease linearly, looking at the table for compound interest you can see as time progresses the interest paid decreases and hence, more of each payment goes towards paying off the loan and the balance reduces by a greater amount each month.
What do we actually see if we graph the balance? Try graphing the balance over time as we did with in the simple interest case. From the graph it is very hard to discern a curve. For any combination of the following features it will be difficult to see a clear curve in the graph of a compound interest loan:
Adjust the loan amount, rate and payment to see how the values accentuate the curve in the following applet:
|