The formula
The fixed monthly payment on a loan of P dollars, at a monthly rate r (the annual rate ÷ 12) for n monthly payments, is:
M = P · r(1 + r)n ÷ [(1 + r)n − 1]
Each month: interest = balance × r; principal = M − interest; new balance = balance − principal. In a spreadsheet:
| What you want | Excel / Google Sheets |
|---|---|
| Monthly payment | =PMT(rate/12, years*12, -loan) |
| Interest in month k | =IPMT(rate/12, k, years*12, -loan) |
| Principal in month k | =PPMT(rate/12, k, years*12, -loan) |
Worked example
A $200,000 loan at 6% for 30 years has a monthly payment of $1,199.10. The first six months:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $1,199.10 | $1,000.00 | $199.10 | $199,800.90 |
| 2 | $1,199.10 | $999.00 | $200.10 | $199,600.80 |
| 3 | $1,199.10 | $998.00 | $201.10 | $199,399.70 |
| 4 | $1,199.10 | $997.00 | $202.10 | $199,197.60 |
| 5 | $1,199.10 | $995.99 | $203.11 | $198,994.49 |
| 6 | $1,199.10 | $994.97 | $204.13 | $198,790.36 |
Interest takes about 83% of the first payment. Over the whole first year the borrower pays about $11,933 in interest, and over 30 years about $231,677 — more than the amount borrowed.
What a schedule shows — and what it leaves out
Why early payments are mostly interest
Interest is charged on the balance, which is largest at the start. In the worked example, interest is about 83% of the first payment and about 3% of the payments in the final year.
How amortization worksA schedule is not your whole payment
It covers principal and interest only. Property tax, homeowners insurance, PMI and HOA dues are added on top — often collected through escrow — so the bill you pay is higher.
Payment with taxes & insuranceExtra money must go to principal
Ask your servicer to apply extra payments to principal; otherwise they may be held or treated as an early next payment. Most loans allow it without a fee, but check your note for a prepayment penalty.
Mortgage payoff calculatorWhen a fixed schedule does not apply
An adjustable-rate loan re-amortizes when its rate resets, an interest-only loan repays no principal at first, and a balloon loan leaves a lump sum due — each needs its own schedule.
ARM calculatorPrintable worksheet
Name: ______________________ Date: ____________
| Month | Starting balance | Interest (balance × r) | Principal (M − interest) | Ending balance |
|---|---|---|---|---|
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | ||||
| 11 | ||||
| 12 |
After 12 months, what share of your total payments went to interest? ____________
From mortgageloanscalculator.com/amortization-worksheet — free to copy for classroom use.
Practice problems
-
A family borrows $250,000 for 30 years at 6.5% interest. What is the monthly payment (principal and interest)?
Show answer
$1,580.17. Use M = P·r(1+r)ⁿ ÷ [(1+r)ⁿ − 1] with P = 250,000, r = 0.065 ÷ 12, n = 360.
-
How much of the first payment is interest, and how much reduces the loan?
Show answer
Interest = $250,000 × (0.065 ÷ 12) = $1,354.17. Principal = $1,580.17 − $1,354.17 = $226.00.
-
What is the loan balance after the first payment?
Show answer
$250,000 − $226.00 = $249,774.00.
-
How much total interest is paid over 30 years? Over 15 years at the same rate?
Show answer
30 years: about $318,862 (payment $1,580.17). 15 years: about $141,998 (payment $2,177.77). The shorter term costs $598 more a month but saves about $176,863 in interest.
-
If the family pays an extra $100 every month on the 30-year loan, how much sooner is it paid off, and how much interest is saved?
Show answer
It is paid off in 304 months instead of 360 — about 4 years and 8 months sooner — saving about $58,860 in interest.
For teachers
- Free to use and copy in class, with no sign-up and no ads on the calculators.
- One class period: derive the payment formula (10 min), fill in three months by hand (15 min), then open the spreadsheet template and compare the hand results to the formulas (20 min).
- Extension: have students change the rate or term in the template, or add an extra payment, and explain why total interest moves so much.
- Put a live calculator on your class page with our free embed code; it runs entirely in the student's browser and stores nothing they type.
University housing, HR and financial-wellness offices: see free tools for universities. Student veterans: see the VA home loan guide for student veterans.
Frequently asked
What is an amortization sheet?
An amortization sheet (or schedule) is a table that lists every payment on a loan and splits each one into interest and principal, with the balance left after each payment.
It shows why early payments are mostly interest and how the balance falls faster near the end of the loan.
Where can I download a free amortization schedule template?
Download the spreadsheet template on this page. It is a standard .xlsx file that opens in Excel, LibreOffice, Google Sheets or Numbers in any language, and its formulas rebuild the full schedule — up to 40 years, with payment dates and extra payments — when you change the yellow input cells. For your own numbers right away, build your schedule above and print it or download it as a CSV file.
Is there an Excel formula for amortization?
Yes. =PMT(rate/12, years*12, -loan) gives the monthly payment. =IPMT(rate/12, month, years*12, -loan) gives the interest part of a given month, and =PPMT(...) with the same arguments gives the principal part. Google Sheets uses the same functions.
How do I calculate amortization by hand?
Each month, multiply the balance by the monthly rate (annual rate ÷ 12) to get the interest. Subtract the interest from the fixed payment to get the principal.
Subtract the principal from the balance, then repeat with the new balance for the next month.
Can I make my own amortization schedule?
Yes. Enter your loan amount, rate, term and first payment month in the schedule builder on this page to get every payment with its date, then print it or download it as a CSV file.
You can also fill in the printable worksheet by hand or use the free spreadsheet template.
How do I create a loan amortization schedule in Excel?
Put the loan amount, annual rate and term in three cells. Compute the payment with =PMT(rate/12, years*12, -loan).
In each row, interest = previous balance × rate/12, principal = payment − interest, and the new balance = previous balance − principal; copy the row down for every month.
The template on this page already does this for up to 480 months, with dates and extra payments.
How do extra payments change an amortization schedule?
Every extra dollar goes straight to principal, so the balance falls faster and each later interest charge is smaller.
On a $250,000, 30-year loan at 6.5%, paying $100 extra a month ends the loan 4 years and 8 months early and saves about $58,860 in interest.