How to calculate an amortization schedule
An amortization schedule shows, for every payment, how much goes to interest and how much to principal, and what balance remains. For a $250,000 mortgage at 6.5% over 30 years, the payment is $1,580.17 a month. In the first month, $1,354.17 of that is interest and only $226 pays down the loan. Generate the full schedule with the amortization calculator.
Step 1: the fixed payment
For a standard (annuity) loan, the monthly payment is:
M = P × r ÷ (1 − (1 + r)^(−n))
- P = loan amount ($250,000)
- r = monthly interest rate (6.5% ÷ 12 = 0.0054167)
- n = number of payments (30 × 12 = 360)
That gives M = $1,580.17.
Step 2: build the schedule row by row
For each month:
- Interest = balance × r
- Principal = M − interest
- New balance = balance − principal
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
Each month the balance drops a little, so the interest drops a little and the principal part grows. In a spreadsheet, you only need these three formulas copied down 360 rows.
Why so little is paid off early on
At the start, the balance is at its highest, so most of the payment is interest. After 10 years, the balance on this loan is still $211,940: only $38,060, or 15%, has been repaid, while you have paid about $151,600 in interest. The split reaches 50/50 between interest and principal only after about 19 years. Over the full 30 years you pay $318,861 in interest, more than the original loan.
| After | Balance | Paid off |
|---|---|---|
| 1 year | $247,206 | 1% |
| 10 years | $211,940 | 15% |
| 20 years | $139,163 | 44% |
| 30 years | $0 | 100% |
How rate and term change the schedule
- A lower rate shifts more of each payment to principal from day one.
- A shorter term, such as 15 years, raises the payment but cuts total interest dramatically.
- Extra payments go straight to principal, so every later interest charge is smaller. Even one extra payment a year can shorten a 30-year mortgage by several years.
Annual vs monthly schedules
A monthly schedule has 360 rows for a 30-year mortgage. An annual summary adds up the 12 payments, interest and principal for each year, which is easier to read. The calculator shows the yearly summary and lets you see how the balance falls over time.
Other loan types
With linear amortization, common for Dutch mortgages, you repay the same amount of principal every month, so payments start higher and fall over time. With an interest-only loan, the balance doesn't fall at all until the end. The formulas above apply to the standard fixed-payment loan used for most mortgages and car loans.
Frequently asked questions
How do I calculate the interest part of a payment?
Multiply the outstanding balance by the monthly rate (annual rate ÷ 12). The rest of the payment is principal.
How do I make an amortization schedule in Excel?
Use =PMT(rate/12, years*12, -loan) for the payment, then for each row: interest = balance × rate/12, principal = payment − interest, new balance = balance − principal. IPMT and PPMT give the interest and principal parts directly.
Why does my balance go down so slowly?
Because interest is charged on the full balance, which is highest at the start. The principal part grows every month.
Do extra payments reduce the term or the payment?
That depends on the lender. Usually the payment stays the same and the loan is paid off earlier.