Creating your own spreadsheet
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.
- Formulas always start with an "=" sign.
- Cell references such as B2 are used in the formulas to make the spreadsheet flexible and able to be changed easily.
- If there are "$" signs with the cell name (eg: $B$2) it means that the formula will not change as it is copied down the page. This is called an absolute reference.
Let's work through setting up a spreadsheet for a simple and compound interest loan using an example.
Simple interest loan
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:
- Information in cells B2 to B5 have been entered according to the information in the scenario but these can easily be adjusted to investigate a different scenario.
- Look carefully at what the formulas are referring to and calculating:
- The cell B8, the first cell under Balance at start, refers to the principal loan amount in cell B2.
- The second cell under Balance at start refers to the previous row's Balance at end cell.
- The Interest in cell C8 is calculated by multiplying the principal loan amount by the annual rate divided by the payments per year. As this is simple interest all references are absolute as this will remain constant for the spreadsheet.
- Under Payment there is an absolute reference to the cell containing the repayment amount, so this will stay constant throughout the loan.
- The Balance at the end is calculated by adding the cells containing the balance at the start and the interest and then subtracting the payment.
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.
Questions
- Calculate the amount of the final payment for the loan. The remaining balance plus the interest for the period.
- Calculate the total amount paid for the car. The total amount is the number of full payments, plus the final payment.
- How long does it take to pay the loan if the interest rate is decreased to 3.6\%? Change the interest rate cell B3 in your spreadsheet to 3.6\% and scroll down to where the balance becomes negative.
- How long does it take to pay the loan if the interest rate remains 4.5\% and the payment is changed to $500 per month? Change the payment cell B4 in your spreadsheet to $500 and scroll down to where the balance becomes negative.
Compound interest loan
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:
- Information in cells B2 to B5 have been entered according to the information in the scenario but these can easily be adjusted to investigate a different scenario.
- Look carefully at what the formulas are referring to and calculating:
- The cell B8, the first cell under Balance at start, refers to the principal loan amount in cell B2.
- The second cell under Balance at start refers to the previous row's Balance at end cell.
- The Interest in cell C8 is calculated by multiplying the balance at the start of the period by the annual rate divided by the compounds per year. Notice the rate and compounds per year are absolute references but the balance at the start is not.
- Under Payment there is an absolute reference to the cell containing the repayment amount, so this will stay constant throughout the loan.
- The Balance at the end is calculated by adding the cells containing the balance at the start and the interest and then subtracting the payment.
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.
Questions
- Calculate the amount of the final payment for the loan.
- Calculate the total amount paid for the car. The total amount is the number of full payments, plus the final payment.
- How long does it take to pay the loan if the interest rate is decreased to 3.6\%? Change the interest rate cell B3 in your spreadsheet to 3.6\% and scroll down to where the balance becomes negative.
- How long does it take to pay the loan if the interest rate remains 4.5\% and the payment is changed to $500 per month? Change the payment cell B4 in your spreadsheet to $500 and scroll down to where the balance becomes negative.
- How do the answers given here compare with the simple interest case scenario? Why does a simple interest rate loan at the same rate cost more?
Setting the term of the loan
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.
Questions
- How much did the simple interest loan cost in total over the 24 months?
- How much did the compound interest loan cost in total over the 24 months?
- How much would you save by using a compound interest loan?
- Explain why the compound interest loan less expensive?
- Would halving the payment double the time taken to pay off the loan?
- What impact does doubling the rate have on the payment required and interest paid?
Comparing loans graphically
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:
- What is the starting value? (y-intercept)
- Does the balance increase or decrease over time?
- Does the balance reach zero?
- Does the balance increase/decrease at a constant rate?
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:
- Highlight the two columns
- Select chart from the insert menu
- Select a line chart
- Adjust formatting such as headings and you can also display the balance as individual dots.
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:
- Small initial loan amount
- Low interest rate
- Short term/high payments
Adjust the loan amount, rate and payment to see how the values accentuate the curve in the following applet:
|
|