Modelling a Loan with a Spreadsheet
Learn to model a loan with a spreadsheet for NSW Year 12 Mathematics Standard 1 (NESA outcome MST-12-S1-03). A spreadsheet works through a reducing balance loan one row per period: it adds the interest on the opening balance, takes off the repayment and carries the closing balance down to the next row.
You will use a spreadsheet to work through loan questions, complete and check its formulas, find when the loan is paid off, and add up the total interest and total repaid — handy when planning a holiday, car or personal loan.
Theory
A spreadsheet models a reducing-balance loan one row at a time, a key skill in Year 12 Mathematics Standard 1 (NSW). Each row works out the interest on the opening balance, adds it, takes off the repayment and carries the closing balance down to the next row, so you can see when the loan is repaid and how much interest it costs.
A reducing-balance loan charges interest only on the amount still owing. A spreadsheet models it one row per period, with the columns Period, Opening, Interest, Repayment and Closing.
In each row the interest is the rate per period times that row's opening balance, e.g. \(1.5\%\) of \(\$6000=0.015\times6000=\$90\). The closing balance is opening \(+\) interest \(-\) repayment, and it becomes the next opening balance.
Add the Interest column for the total interest and the Repayment column for the total repaid. The loan is repaid in the period whose closing balance is \(\$0\); that last repayment is only what is still owing (opening \(+\) interest). Common uses: planning car or personal loan repayments and checking a lender's repayment table.
Every row of the spreadsheet follows the same three rules.
If the rate is given per annum, divide by \(12\) for monthly periods, e.g. \(6\%\) p.a. \(=0.5\%\) per month.
In a spreadsheet with the opening balance in column B, these become cell formulas that are filled down:
| Cell | Formula | Meaning |
|---|---|---|
| C2 | =B2*0.015 | interest at \(1.5\%\) of the opening balance |
| E2 | =B2+C2-D2 | opening \(+\) interest \(-\) repayment |
| B3 | =E2 | next opening \(=\) this closing |
How to model a loan with a spreadsheet
- Find the interest on the period's opening balance: rate per period \(\times\) opening balance, rounded to the nearest cent.
- Find the closing balance: opening \(+\) interest \(-\) repayment.
- Carry it down: the closing balance is the next period's opening balance. Repeat until the balance reaches \(\$0\).
- Total up: add the Interest column for total interest; when opening \(+\) interest is less than the usual repayment, that amount is the last repayment.
Find the interest on the opening balance, then add it and subtract the repayment.
| \(\text{Interest}\) | \(=\) | \(0.015\times4000=\$60\) |
| \(\text{Closing}\) | \(=\) | \(4000+60-900\) |
| \(\) | \(=\) | \(\$3160\) |
| \(\text{P2 open}\) | \(=\) | \(\$3160\) |
Interest \(\$60\), closing balance \(\$3160\), and period \(2\) opens at \(\$3160\).
| Period | Opening | Interest | Repayment | Closing |
|---|---|---|---|---|
| 1 | 7000 | 140 | 1500 | 5640 |
| 2 | 5640 | ? | 1500 | 4252.80 |
| 3 | 4252.80 | 85.06 | 1500 | ? |
Take \(2\%\) of each opening balance; closing \(=\) opening \(+\) interest \(-\) repayment.
| \(\text{Int. (2)}\) | \(=\) | \(0.02\times5640=\$112.80\) |
| \(\text{Close (3)}\) | \(=\) | \(4252.80+85.06-1500\) |
| \(\) | \(=\) | \(\$2837.86\) |
| \(\text{Total int.}\) | \(=\) | \(140+112.80+85.06\) |
| \(\) | \(=\) | \(\$337.86\) |
Missing cells \(\$112.80\) and \(\$2837.86\); total interest \(\$337.86\).
Work down the sheet, one period at a time.
| \(\text{P1}\) | \(=\) | \(2500+25-900=\$1625\) |
| \(\text{P2}\) | \(=\) | \(1625+16.25-900=\$741.25\) |
| \(\text{P3 int.}\) | \(=\) | \(0.01\times741.25=\$7.41\) |
| \(\text{P3 owes}\) | \(=\) | \(741.25+7.41=\$748.66\) |
\(\$748.66\lt\$900\), so the period-\(3\) payment clears the loan: repaid in period \(3\) with a last repayment of \(\$748.66\).
The part of a repayment that reduces the debt is repayment \(-\) interest.
| \(\text{P1}\) | \(=\) | \(1500-140=\$1360\) |
| \(\text{P2}\) | \(=\) | \(1500-112.80=\$1387.20\) |
\(\$1360\) then \(\$1387.20\). The interest falls each period because it is worked out on a smaller opening balance, so more of each repayment comes off the debt.
Common pitfalls
Frequently asked questions
How do you model a loan in a spreadsheet?
Use one row per period with columns for period, opening balance, interest, repayment and closing balance. Interest is the rate per period times the opening balance, the closing balance is opening plus interest minus repayment, and the closing balance is copied down as the next opening balance.
What is the spreadsheet formula for the closing balance of a loan?
If the opening balance is in B2, the interest in C2 and the repayment in D2, the closing balance is =B2+C2-D2. The next opening balance is then =E2, and the formulas are filled down.
Why does the interest go down each month on a reducing-balance loan?
Because interest is charged only on the balance still owing. Each repayment lowers the balance, so the next interest charge is on a smaller amount and more of each repayment comes off the debt.
How do you find the total interest paid on a loan?
Add up every value in the Interest column of the spreadsheet. For example, interest of 140, 112.80 and 85.06 dollars over three months gives total interest of 337.86 dollars.
Why is the last loan repayment smaller?
In the last period the amount still owing (opening balance plus that period's interest) is less than the usual repayment, so only that amount is paid. For example, 741.25 dollars plus 7.41 dollars interest gives a last repayment of 748.66 dollars.