About this lesson
Loan amortisation schedule: opening balance, interest, repayment and closing balance in Google Sheets
In Google Sheets, an amortisation schedule has one row per period: opening balance, interest (=B8*$B$2/4 for quarterly), repayment (=$B$4, from PMT), and closing balance (=B8+C8-D8). Each period's opening balance is the last one's closing balance (=E8), and filling the rows down ends with a closing balance of zero.
Using Excel? Do this lesson in Excel.
=PMT(B2/4,B3,-B1)What you'll practise
Build the repayment schedule for a five-year term loan, quarter by quarter: the repayment, the first quarter's interest and closing balance, link the second quarter, then fill the schedule down until the balance reaches zero.
5 steps · 50 points · the cells outlined in gold below
- amortisation schedules
- PMT
- term loans
- absolute references
- fill down
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Term loan ($k) | 3,500.0 | |||
| 2 | Annual rate | 6.80% | |||
| 3 | Quarters | 20 | |||
| 4 | Quarterly repayment ($k) | ||||
| 5 | |||||
| 6 | |||||
| 7 | Quarter | Opening ($k) | Interest ($k) | Repayment ($k) | Closing ($k) |
| 8 | 1 | ||||
| 9 | 2 | ||||
| 10 | 3 | ||||
| 11 | 4 | ||||
| 12 | 5 | ||||
| 13 | 6 | ||||
| 14 | 7 | ||||
| 15 | 8 | ||||
| 16 | 9 | ||||
| 17 | 10 | ||||
| 18 | 11 | ||||
| 19 | 12 | ||||
| 20 | 13 | ||||
| 21 | 14 | ||||
| 22 | 15 | ||||
| 23 | 16 | ||||
| 24 | 17 | ||||
| 25 | 18 | ||||
| 26 | 19 | ||||
| 27 | 20 |