Finance · Topic 16 of 19
Loans & Loan Schedules
Theory
When an individual or business borrows money, they must repay the original amount (the capital) plus an additional fee for borrowing the money (the interest). There are several ways to structure this repayment, such as repaying nothing until the end of the term, repaying only the interest during the term and the capital at the very end, or making regular level repayments of both capital and interest.
In the Higher Applications exam, you will primarily focus on level repayments, which are tracked using a Loan Schedule.
1. The Structure of a Loan Schedule
A loan schedule is a table that tracks the outstanding debt period by period. It contains the following core elements:
- Repayment: The fixed, level amount of money the borrower pays each month.
- Interest Content: The portion of the repayment that pays off the interest generated that month. This is calculated by multiplying the previous month's outstanding balance by the monthly interest rate.
- Capital Content: The portion of the repayment that actually reduces the debt itself. This is calculated by subtracting the interest content from the total repayment.
- Loan Outstanding: The new, reduced balance of the loan, found by subtracting the capital content from the previous loan outstanding.
2. The Changing Balance (Exam Concept)
- Because the outstanding loan balance gets smaller with every payment, the interest content of the monthly payment naturally decreases over time.
- Because the total monthly repayment remains level, the capital content naturally increases over time.
3. Spreadsheets and the Goal Seek Function
To find the exact level repayment required to clear a loan in a spreadsheet, you must use a specific sequence of steps:
- Enter a "guess" for the repayment amount and complete the schedule down to the final month.
- Use the Goal Seek function to set the final Loan Outstanding cell to exactly £0 by changing your guessed repayment cell.
The Crucial Final Step:
Goal Seek will produce an unrounded decimal. You must manually round this repayment figure to 2 decimal places (to represent real pennies).
Because of this rounding, your final balance will no longer be exactly zero. You must manually adjust the final monthly repayment (by adding or subtracting the small remaining balance) to ensure the loan clears to exactly £0.
Worked examples
Example 1
Example 1: Manual Calculation of a Loan Schedule
Finn takes out a personal loan of £8,000. He makes level monthly repayments of £260 at the end of each month. The effective rate of interest on the loan is 0.6% per month.
Complete the loan schedule for Month 1.
| Time (months) | Repayment (£) | Interest content (£) | Capital content (£) | Loan outstanding (£) |
|---|---|---|---|---|
| 0 | 8000.00 | |||
| 1 | 260.00 |
- Calculate the Interest Content: Multiply the outstanding balance by the decimal interest rate.
- Calculate the Capital Content: Subtract the interest from the total repayment.
- Calculate the Loan Outstanding: Subtract the capital content from the previous balance.
(The completed row for Month 1 should read: 260.00 | 48.00 | 212.00 | 7788.00).
Example 2
Example 2: Constructing Spreadsheet Formulae
A spreadsheet is set up to model a loan repayment schedule.
- The monthly effective rate of interest is locked in cell B3.
- The fixed monthly repayment is locked in cell B11.
- For Month 1 (Row 10), the initial Loan Outstanding is in cell E10.
Write down the exact Excel formula required in cell C11 to calculate the Interest Content for Month 1.
You must multiply the outstanding balance (E10) by the locked interest rate (B3), and remember to use the ROUND function to 2 decimal places.
Example 3
Example 3: Adjusting the Final Repayment (Goal Seek)
Maya takes out a 36-month loan. She uses the Goal Seek function on her spreadsheet to find her monthly repayment, which gives an unrounded value of £145.2874...
She manually rounds this to £145.29 and updates her spreadsheet to use this rounded figure for all 36 months. Because she rounded the payment up, she slightly overpays each month.
Her spreadsheet now shows a final outstanding balance at month 36 of -£0.09 (negative 9 pence).
Calculate the adjusted final repayment Maya must make in Month 36 to bring the balance to exactly £0.
Because Maya has overpaid by 9 pence over the course of the loan, she can subtract this amount from her very last payment.
- .
Adjusted Final Repayment = £145.20