The amortisation table: what it is and how to build one
By Equipo Prestafolio
What an amortisation table is, how the French method splits each instalment between principal and interest, and a full 12-instalment example worked out to the cent.
An amortisation table is the list of all the instalments on a loan, and for each one: how much is interest, how much is principal, and how much is still owed afterwards. It is the document that turns "I lent you 10,000 at 2%" into a payment plan the client understands and you can collect. If your loan has no table, you do not have a loan: you have a verbal agreement with numbers in it.
The five columns
Column | What it holds |
|---|---|
Instalment | The payment number: 1, 2, 3… |
Date | When that instalment falls due |
Interest | Outstanding balance × the period's rate |
Principal | Total instalment − interest |
Balance | Previous balance − principal |
And the rule that governs everything: interest is always calculated on the remaining balance, never on the original amount. That is why the first instalment is almost all interest and the last one is almost all principal, even though the client always pays the same.
The French method (fixed instalment)
It is the world's standard credit method: the instalment never changes, what changes is how it is split inside.
The instalment formula:
Element | Value |
|---|---|
Instalment | amount × i ÷ (1 − (1 + i) to the power of −n) |
i | rate per period (2% monthly = 0.02) |
n | number of instalments |
In a spreadsheet you do not need to write it out: =PMT(0.02; 12; -10000) returns 945.60.
A full example: 10,000 over 12 instalments at 2% per month
Fixed instalment: 945.60.
Instalment | Interest | Principal | Balance |
|---|---|---|---|
10,000.00 | |||
1 | 200.00 | 745.60 | 9,254.40 |
2 | 185.09 | 760.51 | 8,493.89 |
3 | 169.88 | 775.72 | 7,718.17 |
4 | 154.36 | 791.24 | 6,926.93 |
5 | 138.54 | 807.06 | 6,119.87 |
6 | 122.40 | 823.20 | 5,296.67 |
7 | 105.93 | 839.67 | 4,457.00 |
8 | 89.14 | 856.46 | 3,600.54 |
9 | 72.01 | 873.59 | 2,726.95 |
10 | 54.54 | 891.06 | 1,835.89 |
11 | 36.72 | 908.88 | 927.01 |
12 | 18.54 | 927.06 | −0.05 |
Total | 1,347.15 | 10,000.05 |
|
Read the table top to bottom and you get the whole story:
- On instalment 1, of the 945.60 the client pays, 200 is interest (21%) and only 745.60 reduces the debt.
- On instalment 12, the interest is 18.54 (2%) and practically the whole payment is principal.
- Total interest on the loan is 1,347.15, not 2,400. And that difference is exactly the gap between charging on the balance and charging flat.
The last instalment almost never closes at exactly zero
Look at instalment 12: the final balance is not 0.00, it is −0.05. And that is not a mistake in the table, it is the unavoidable residue of rounding.
The exact instalment on this loan is 945.5959…. Since it is collected in money, it gets rounded to 945.60: four thousandths over, twelve times. By the end of the schedule those thousandths have turned into five cents the client overpays. Round the instalment down instead and the opposite happens: the balance closes positive and the loan stays alive over a few cents.
It is a residue of cents, not a calculation error, but you have to decide what to do with it before it shows up, because a settled loan's balance has to be zero:
- Adjust the last instalment so it closes exactly: in the example, collect instalment 12 at 945.55 instead of 945.60. It is what banks do, and it is what we recommend if you are building your own spreadsheet.
- Or close the residue on settlement, treating the loan as cancelled once the balance falls below one cent.
What you cannot do is ignore it: a five-cent balance nobody closes is a loan that stays open in your reports forever.
French is not the only method
Method | Instalment | Principal | When it is used |
|---|---|---|---|
French | Fixed | Rising | The standard. The client knows what they pay each time |
German | Falling | Fixed | Less total interest, but the first instalments bite |
American | Interest only | All at the end | Bridge loans. High risk: the whole principal falls due at once |
Flat | Fixed | Interest on the original amount. Not real amortisation |
About flat: it is the most used method in informal lending because it is the easiest to do by hand, but it does not produce a real amortisation table: the interest does not depend on the balance, so there is nothing to amortise. If you use it, at least work out what effective rate you are charging: we explain it in How much interest to charge for lending money.
If the client wants to pay early
It is the question that arrives sooner or later: "I want to settle today, how much is it?"
With the French method the answer is clean: the principal balance on the row you are on, plus the interest accrued since the last instalment paid. If the client paid instalment 8, they owe 3,600.54 plus the accrued days. They do not owe the interest on instalments 9 to 12: that interest has not been generated yet, because interest is charged for the time the money was on loan, and that time is not going to exist.
Charging the full future instalments (or front-loading the interest with the old "rule of 78") turns early repayment into a punishment. In several jurisdictions that is expressly prohibited, and the client has a right to the interest reduction. Check your country's rules; the right to prepay with an interest reduction is one of the most widespread financial consumer protections in the region.
Mistakes you see every day
Mistake | What happens |
|---|---|
Calculating interest on the original amount | You are charging flat and calling it amortisation |
Not adjusting the last instalment | The loan never closes at zero |
Adding 30 days instead of using the real month | The dates drift from month 3 onwards |
Storing only the instalment, not the split | You cannot tell the client how much principal they have paid |
Rebuilding the table when the client falls behind | The table is the contract: leave it alone. Arrears are separate |
The last one deserves emphasis: the amortisation table does not change because the client is late. Arrears are a charge calculated separately, on the overdue instalment (see How to calculate arrears on a loan). If you rewrite the table every time there is a delay, you lose the very reference you are measuring the delay against.
Summary
- The table splits each instalment into interest (on the balance) and principal (the rest).
- French method: fixed instalment, falling interest, rising principal.
- The rounding residue on the last instalment is only cents, but it has to be closed.
- To settle early: principal balance + accrued interest, not the whole future instalments.
- The table is not rewritten because of a delay. Arrears are a different thing.
Prestafolio generates the full table before you hand the money over, with the real date of every instalment according to the frequency (daily, weekly, biweekly or monthly). The instalment it emits is the constant one from the French method (945.60 across all twelve rows in the example), so the rounding residue you have just seen shows up in the last row of the schedule exactly as it is: see Amortisation schedule and Frequencies and calendar.