Amortization Schedule
Free Amortization Schedule: compute the payment with =PMT(rate/12, years*12, -amount), then split each month into interest (=balance*rate/12) and...
Total interest €137,642.42 · Total paid €387,642.42
| Year | Principal | Interest | End balance |
|---|---|---|---|
| 1 | €6,111.41 | €9,394.29 | €243,888.59 |
| 2 | €6,347.73 | €9,157.97 | €237,540.86 |
| 3 | €6,593.19 | €8,912.51 | €230,947.67 |
| 4 | €6,848.14 | €8,657.56 | €224,099.53 |
| 5 | €7,112.95 | €8,392.75 | €216,986.58 |
| 6 | €7,388.00 | €8,117.70 | €209,598.58 |
| 7 | €7,673.69 | €7,832.01 | €201,924.90 |
| 8 | €7,970.42 | €7,535.28 | €193,954.48 |
| 9 | €8,278.62 | €7,227.07 | €185,675.86 |
| 10 | €8,598.75 | €6,906.95 | €177,077.11 |
| 11 | €8,931.25 | €6,574.44 | €168,145.85 |
| 12 | €9,276.62 | €6,229.08 | €158,869.24 |
| 13 | €9,635.33 | €5,870.37 | €149,233.91 |
| 14 | €10,007.92 | €5,497.78 | €139,225.99 |
| 15 | €10,394.91 | €5,110.78 | €128,831.07 |
| 16 | €10,796.87 | €4,708.82 | €118,034.20 |
| 17 | €11,214.38 | €4,291.32 | €106,819.82 |
| 18 | €11,648.02 | €3,857.67 | €95,171.80 |
| 19 | €12,098.44 | €3,407.26 | €83,073.36 |
| 20 | €12,566.27 | €2,939.42 | €70,507.09 |
| 21 | €13,052.20 | €2,453.50 | €57,454.89 |
| 22 | €13,556.91 | €1,948.79 | €43,897.98 |
| 23 | €14,081.14 | €1,424.56 | €29,816.84 |
| 24 | €14,625.64 | €880.05 | €15,191.20 |
| 25 | €15,191.20 | €314.50 | €0.00 |
Need it as an auditable file?
The full month-by-month schedule ships inside the Corporate Finance Suite — formula-driven, unlocked, audit-ready.
How it works
In Excel or Google Sheets, compute the payment with =PMT(rate/12, years*12, -amount), then split each month into interest (=balance*rate/12) and principal (=payment-interest).
Calculate the level payment with =PMT(rate/12, years*12, -amount).
In the first schedule row, interest = opening balance × rate/12.
Principal = payment − interest; new balance = opening balance − principal.
Copy the row down for every month; the balance should reach zero on the final payment.
What this does
An amortization schedule shows where every payment goes: early on, most of it is interest and little touches the balance; over time the split flips and principal dominates. Seeing that crossover is what makes the schedule useful — it explains why overpaying early saves so much more interest than overpaying late.
A worked example
On a €250,000 loan at 3.8% over 25 years, the payment is about €1,292. In month one, interest is 250000×3.8%/12 ≈ €792 and only ≈€500 reduces the balance. By the final year almost the entire payment is principal. The yearly table above aggregates all 300 months so you can read the principal/interest split at a glance. Use an amortization schedule to compare the true cost of loan offers, to see the impact of overpayments, or to plan an early payoff. It is the difference between knowing your monthly payment and understanding your loan.
Common mistakes
- Letting rounding leave a tiny non-zero balance at the end — adjust the last payment to clear it.
- Applying the annual rate per month instead of rate/12.
- Forgetting that extra principal payments change every subsequent interest row.
- Building 360 rows by hand instead of copying one correct formula row down.
FAQ
Why is so much early payment interest?
Interest is charged on the outstanding balance, which is largest at the start. As the balance falls, the interest portion shrinks and principal grows.
How much does overpaying save?
A lot, and most when done early, because every euro of extra principal stops accruing interest for the entire remaining term. The schedule lets you test it directly.
Can I build this in Google Sheets?
Yes — PMT and the row formulas are identical in Google Sheets, so the same schedule works without any changes.