How to Build a Mortgage Amortization Schedule in Excel
An amortization schedule shows exactly where every shekel or dollar of your mortgage payment goes — how much covers interest, how much pays down the principal, and what you still owe after each month. Building one yourself in Excel is the best way to understand your mortgage, compare offers from different banks, and see what refinancing would really save you. This guide walks through the whole process, formula by formula.
What is an amortization schedule?
An amortization schedule is a month-by-month table of your loan. Each row shows the payment number, the payment amount, the interest portion, the principal portion, and the remaining balance. In the early years most of your payment is interest; over time the balance shifts toward principal. Seeing that curve laid out is what makes the true cost of a mortgage — and the value of early repayments — obvious.
Spitzer vs. equal principal: know your repayment method
Most mortgages use the Spitzer (annuity) method: a fixed monthly payment, with the interest/principal mix changing every month. The alternative is equal principal: you repay the same amount of principal each month plus interest on the remaining balance, so payments start higher and fall over time. Equal principal costs less in total interest but demands a bigger payment up front. Your Excel formulas differ between the two, so decide which one you are modeling before you start.
What you'll need before you start
Three numbers define the basic schedule: the loan principal, the annual interest rate, and the term in months (years × 12). If your mortgage is split into several tracks — for example a fixed-rate track, a prime-linked track, and a CPI-linked track — treat each track as its own loan with its own schedule, then add them up. Get the numbers from your bank's offer letter; each track is listed separately.
Step 1 — Set up an inputs area
Put the loan parameters in dedicated cells rather than typing them into formulas: B1: principal (e.g. 900,000) B2: annual interest rate (e.g. 3.5%) B3: term in months (e.g. 240) Every formula in the schedule should reference these cells. That way, changing one input instantly recalculates the whole schedule — which is exactly what makes the model useful for comparing offers.
Step 2 — Calculate the monthly payment with PMT
For a Spitzer loan, the fixed monthly payment is: =PMT(B2/12, B3, -B1) PMT takes the monthly rate (annual rate divided by 12), the number of payments, and the principal (negative, because it is money you received). For 900,000 at 3.5% over 20 years this returns about 5,219.64 per month. This one number stays constant for the life of a fixed-rate Spitzer loan.
Step 3 — Split each payment with IPMT and PPMT
Build the table with one row per month. If column A holds the payment number (1, 2, 3…): Interest portion: =IPMT($B$2/12, A7, $B$3, -$B$1) Principal portion: =PPMT($B$2/12, A7, $B$3, -$B$1) The two always add up to the PMT amount. Note the $ signs: the inputs are absolute references so you can fill the formulas down the whole table, while the period number stays relative.
Step 4 — Track the remaining balance
Add a balance column. The first row starts from the principal minus the first principal payment; every following row subtracts that month's principal portion from the previous balance. The final row should land at exactly zero — if it doesn't, a reference in one of your formulas slipped. Summing the interest column gives you the total interest cost of the loan, usually an eye-opening number.
Multiple tracks and CPI-linked loans
Real mortgages are usually a mix: part fixed, part prime-linked, part CPI-linked. Model each track on its own sheet and sum the monthly payments in a combined view. CPI-linked tracks add a twist — the remaining balance itself is indexed each month, so multiply the balance by expected monthly inflation before computing interest. Variable-rate tracks (for example, resetting every 5 years) need the rate recalculated at each reset point. This is where a hand-built sheet starts getting genuinely hard to keep correct.
Comparing refinancing options
To evaluate refinancing, build a second schedule for the new loan (remaining balance, new rate, new term) and compare two things: the change in monthly payment and the change in total remaining interest. Don't forget early-repayment fees. A lower monthly payment that stretches the term can still cost far more in total interest — the schedule makes that trade-off visible instead of hiding it.
Common pitfalls
The classic mistakes: using the annual rate where the monthly rate belongs (always divide by 12); mixing up payment counts when the term is in years; forgetting the minus sign on the principal in PMT/IPMT/PPMT; hardcoding numbers into formulas instead of referencing the inputs; and ignoring indexation on CPI-linked tracks, which understates the real cost. Always sanity-check that the final balance is zero and that interest + principal equals the payment in every row.
Prefer it done for you?
The Mortgage Excel Builder generates this entire workbook for you — multiple interest tracks, CPI indexation, full amortization schedules with live formulas, and a refinancing comparison — ready to download and edit.
Build my mortgage Excel — $20Modeling a real-estate purchase or investment?
Combine this amortization schedule with our feasibility study guide to see NPV, IRR and payback alongside your loan costs.
Feasibility study guideFAQ
How do I calculate a mortgage amortization schedule in Excel?
Use PMT to get the fixed monthly payment, then IPMT and PPMT to split each payment into interest and principal, and add a running-balance column that subtracts the principal portion each month. The step-by-step build above walks through every formula.
What Excel formula calculates the monthly mortgage payment?
=PMT(rate/12, term_months, -principal). The first argument is the annual rate divided by 12 (the monthly rate), the second is the number of monthly payments (years × 12), and the third is the loan amount entered as a negative so the result comes out positive.
Is there a free mortgage calculator Excel template to download?
You can build your own in a few minutes using the steps in this guide — the formulas are all standard Excel. If you would rather skip the setup, our Mortgage Excel Builder generates a ready, fully formula-linked workbook for your exact numbers. Try the Mortgage Excel Builder
How do I compare fixed, CPI-linked and קל"צ (fixed-unlinked) mortgage tracks in Excel?
Build one amortization schedule per track on its own sheet, then add a summary sheet that sums the payments, total interest and final cost of each track side by side. Our builder produces the multi-track comparison automatically.
What's the difference between the Spitzer method and equal-principal repayment?
Spitzer keeps the total monthly payment equal, so the interest share shrinks and the principal share grows over time. Equal-principal repays a fixed principal amount every month, so the payment starts higher and declines steadily — and total interest paid is lower.
Can I model an interest-only or variable-rate mortgage in Excel?
Yes. For interest-only periods, set the payment to balance × monthly rate and leave the balance unchanged. For a variable rate, put the rate for each period in its own column and recalculate PMT on the remaining balance and remaining term whenever the rate changes.
How accurate is a mortgage schedule built by AI compared to doing it manually?
It uses exactly the same standard PMT, IPMT and PPMT formulas you would write yourself, linked live to the input cells. Nothing is hard-coded, so you can audit every cell, change an input and watch the whole schedule recalculate.