Guide · 6 min read
Loan amortisation schedules explained, with an example
An amortisation schedule lists every payment on a loan and splits each one into interest and capital. It shows the balance after every payment, the total interest over the life of the loan, and what to post to the books each month.
- Each month, interest is charged on the balance still owed.
- The rest of the instalment pays off capital.
- So the interest part shrinks and the capital part grows, month after month.
The instalment formula
For a loan repaid in equal monthly instalments, the instalment is:
- P is the amount borrowed.
- r is the monthly rate: the yearly rate divided by 12, as a decimal.
- n is the number of monthly instalments.
Example: R250,000 over 12 months at 12% a year. The monthly rate is 1%, or 0.01, so the instalment is R250,000 × 0.01 ÷ (1 − 1.01−12) = R22,212.20. In Excel, =PMT(12%/12, 12, -250000) gives the same figure.
The schedule, month by month
Each row starts from the previous row's closing balance. Interest is 1% of the opening balance, and whatever is left of the instalment reduces the capital.
| Month | Opening balance | Instalment | Interest | Capital | Closing balance |
|---|---|---|---|---|---|
| 1 | R250,000.00 | R22,212.20 | R2,500.00 | R19,712.20 | R230,287.80 |
| 2 | R230,287.80 | R22,212.20 | R2,302.88 | R19,909.32 | R210,378.48 |
| 3 | R210,378.48 | R22,212.20 | R2,103.78 | R20,108.42 | R190,270.06 |
| 4 | R190,270.06 | R22,212.20 | R1,902.70 | R20,309.50 | R169,960.56 |
| 5 | R169,960.56 | R22,212.20 | R1,699.61 | R20,512.59 | R149,447.97 |
| 6 | R149,447.97 | R22,212.20 | R1,494.48 | R20,717.72 | R128,730.25 |
| 7 | R128,730.25 | R22,212.20 | R1,287.30 | R20,924.90 | R107,805.35 |
| 8 | R107,805.35 | R22,212.20 | R1,078.05 | R21,134.15 | R86,671.20 |
| 9 | R86,671.20 | R22,212.20 | R866.71 | R21,345.49 | R65,325.71 |
| 10 | R65,325.71 | R22,212.20 | R653.26 | R21,558.94 | R43,766.77 |
| 11 | R43,766.77 | R22,212.20 | R437.67 | R21,774.53 | R21,992.24 |
| 12 | R21,992.24 | R22,212.16 | R219.92 | R21,992.24 | R0.00 |
| Total | R266,546.36 | R16,546.36 | R250,000.00 |
The last instalment is four cents less than the others. Rounding each month to the cent means the balance left for the final month is a few cents short of a full instalment, so the last payment clears exactly what is left.
Reading the schedule
- Interest falls every month. In month 1, R2,500.00 of the R22,212.20 is interest. By month 12 it is only R219.92.
- The balance falls slowly, then faster, because more of each instalment goes to capital as the interest shrinks.
- The total cost of the loan is the interest column: R16,546.36 on R250,000 over a year.
- To settle early, the borrower pays the closing balance after the last instalment, plus interest from that payment date to the settlement date.
Posting it to Xero
For the lender, each month has two entries. The interest is a journal: debit the loan, credit interest income (see How to post loan interest in Xero). The instalment received is a bank transaction coded to the loan account, which reduces the balance by the full instalment. After both, the loan balance in Xero should match the closing balance in the schedule. The borrower does the mirror image, with interest expense and a payment.
When real life differs from the schedule
This example charges a twelfth of the yearly rate each month, which keeps the arithmetic tidy. Real loans move away from a fixed schedule for three common reasons:
- Daily interest. If the agreement charges interest daily, each month's interest depends on its number of days, so the split differs slightly month to month. How to calculate interest on a loan, day by day shows the difference.
- Payments that are late, early or extra. The balance changes on the day money moves, so every row after it changes.
- Rate changes. A loan linked to prime gets a new rate whenever prime moves, and the rest of the schedule has to be worked out again from that date.
That is why a schedule worked out once at the start of a loan drifts from the books over time. It has to be rebuilt from the real balance and rate each time something changes.