How the result is calculated
An amortization schedule is the loan written out one payment at a time. It answers the questions a single monthly figure cannot: how much of this year's payments went to the lender as interest, what you will still owe in year ten, and what a lump sum in year five is worth.
The calculator first finds the level payment, the one amount that, paid every period, brings the balance to exactly zero at the end of the term. It needs three things: the loan, the number of payments, and the rate charged each payment.
PaymentP × r ÷ (1 − (1 + r)−n)
each row:
interestbalance × r, rounded to the cent
principalpayment − interest
new balancebalance − principal − extra − lump sum
- P
- the amount borrowed
- r
- the rate charged each payment period
- n
- the number of payments over the term
The rate per payment is where loans in different countries part company. An American or British lender quoting 6% charges 6% ÷ 12 = 0.5% a month: that is the nominal convention, and the one Excel's PMT function assumes. A Canadian mortgage is quoted compounded half-yearly, so 6% means 3% every six months and the monthly rate is (1.03)^(1/6) − 1, about 0.4939%. An effective annual quote, common for European consumer credit figures, means the rate already includes compounding, so the monthly rate is (1.06)^(1/12) − 1. The calculator converts the quote you choose into the rate per payment and uses that one number everywhere.
Worked example
| Item | Amount | How it is obtained |
|---|---|---|
| Loan, P | 300,000.00 | Loan amount |
| Rate per month, r | 0.54167% | 6.5% ÷ 12, nominal quotation |
| Payments, n | 360 | 30 years × 12 |
| Monthly payment | 1,896.20 | 300,000 × 0.0054167 ÷ (1 − 1.0054167−360) |
| Interest in payment 1 | 1,625.00 | 300,000 × 0.54167% |
| Principal in payment 1 | 271.20 | 1,896.20 − 1,625.00 |
| Balance after payment 1 | 299,728.80 | 300,000 − 271.20 |
| Balance after year 5 | 280,833.26 | 60 payments: 19,166.74 of principal repaid |
| Total interest over 360 payments | 382,636.71 | final payment 1,900.91 settles the rounding |
Five years in, you have paid 113,772.00 and the balance has fallen by only 19,166.74. That is not a trick of the lender: interest is charged on what you owe, and for the first years you owe almost all of it. The principal share of each payment grows by about 0.54% a month, the monthly rate itself, and overtakes the interest share in month 233, during the twentieth year.
| Item | Amount | How it is obtained |
|---|---|---|
| Balance after payment 60, before the lump sum | 280,833.26 | as above |
| Lump sum | 20,000.00 | One-off payment with payment no. 60 |
| Balance carried into payment 61 | 260,833.26 | the payment itself stays 1,896.20 |
| Payments needed | 314 | instead of 360 |
| Interest saved | 69,076.48 | 382,636.71 − 313,560.23 |
The 20,000 saves more than three times itself because it removes 20,000 of balance that would otherwise have carried interest for the remaining 25 years. The same sum paid with payment 240 saves far less, since there are fewer years left for it to stop interest on.
Reading the result
The headline is the regular payment for the plan you chose, principal and interest only. Beneath it, the payoff tells you when the balance reaches zero: as a month and year when you give a first payment date, otherwise as a duration.
Total interest is the sum of every row's rounded interest, which is what the lender will actually collect. The note under the table also gives the formula total, payment × n − P without per-period rounding, which is the figure most online calculators show. On the opening loan the two are 382,636.71 and 382,633.47: a little over three units of rounding across thirty years.
Interest saved compares your schedule with the same loan paid the plain way: no extra, no lump sums, and for an accelerated plan, the ordinary monthly schedule. The balance chart draws both lines, so you can see where they separate.
The year view sums twelve monthly payments (or 26 biweekly, or 52 weekly) into each loan year, counted from the first payment. Switch to every payment to see each row, the date it falls on and any extra it carried. The CSV follows whichever view is on screen.
What changes the result
The quotation. At 5% over 25 years on 500,000, the Canadian half-yearly quote gives a payment of 2,908.02 and the nominal quote 2,922.95. Matching the quotation to your contract matters more than the second decimal of the rate.
The payment plan. A plain biweekly plan recalculates the payment for 26 payments a year over the same term and costs almost exactly what monthly payments do. An accelerated plan does something different.
| Item | Amount | How it is obtained |
|---|---|---|
| Monthly payment on the same loan | 1,199.10 | PMT(0.5%, 360, 200,000) |
| Accelerated biweekly payment | 599.55 | 1,199.10 ÷ 2, paid 26 times a year |
| Paid in a year | 15,588.30 | 26 × 599.55, one monthly payment more than 12 × 1,199.10 |
| Interest in payment 1 | 461.54 | 200,000 × 6% ÷ 26 |
| Payments needed | 638 | about 24 years 6 months instead of 30 years |
| Interest saved against paying monthly | 49,624.10 | 231,677.04 − 182,052.94 |
Extra with every payment. A fixed extra amount compounds in your favour: each extra unit stops interest on itself for the rest of the term. On the 200,000 loan, 200 a month extra ends it after 252 payments and saves 79,800.86.
The rate and the term. A longer term lowers the payment and raises the total interest, often by more than people expect: the same 300,000 at 6.5% over 25 years costs 2,025.62 a month and about 307,686 in interest by the formula, roughly 75,000 less.
What is not included
The schedule is for a fixed rate held for the whole term. A tracker, a variable rate or a fixed period that reverts to a standard rate will change every row after the change. The payment is principal and interest only, so property tax, insurance or fees collected by the lender with the payment are not in it. Interest is charged per payment period, as on most mortgages; a loan that accrues interest daily on the actual days in each month will differ by small amounts from row to row.
Lenders may limit overpayments or charge for them, especially during a fixed period. The calculator applies every extra and lump sum in full and charges nothing for it, so check your contract's overpayment allowance before relying on a saving.
Using the result
Match three things to your loan documents before reading anything else: the amount, the rate with its quotation, and the number of payments. If the first row's interest agrees with your statement, every later row will.
Then use the schedule for the questions it is good at: how much you will owe at the end of a fixed period, which sets what you have to refinance; how much interest a year you pay, which some tax systems let you deduct; and what a bonus is worth set against the loan rather than in savings. If an overpayment has to choose between a shorter term and a lower payment, the mortgage overpayment calculator compares the two.