Mortgage Amortization Calculator Excel Template

Option 1: Download the Ready-Made Schedule

Enter your loan amount, rate, and term into the calculator above — add extra payments, PMI, taxes, insurance, or HOA if they apply — then click "Download schedule (CSV for Excel / Sheets)". The file opens directly in Excel, Google Sheets, or Numbers with a row for every month: payment, principal, interest, extra payment, PMI, escrow, and remaining balance, computed from the same engine used throughout this site.

Set the First Paymentmonth before you download and the Date column will hold the real month of each payment (for example 2026-11, 2026-12, 2027-01), which makes the file easy to match against your servicer's statements. Once it is open, three steps turn it into a working template. Select the data and press Ctrl+T (Cmd+T on a Mac) to make it an Excel table, so filters and totals follow new rows. Add a total row and set the Interest column to Sum: that cell is your lifetime interest, and it should match the calculator's “Total Interest Paid” figure, give or take a few cents of rounding. Then insert a line chart of the Balance column to see the curve bend as principal payments grow. If you would rather have paper, the calculator's Print schedule button prints the page with the year-by-year table; open any year first to include its months.

Option 2: Build Your Own With Excel Formulas

If you want to build the schedule yourself — to customize it further or understand the mechanics — three built-in Excel functions do all the work:

Set up five columns — Month, Payment, Principal, Interest, Balance — and fill down 360 rows for a 30-year loan (180 for 15-year). The Balance column for each row is simply the previous row's balance minus that row's Principal value, starting from your original loan amount in row one. For the full formula derivation without Excel-specific syntax, see our calculation methodology page.

Worked Example

For a $320,000 loan at 6.5% over 30 years: =PMT(6.5%/12, 30*12, -320000) returns $2,022.62. For month 1's interest: =IPMT(6.5%/12, 1, 360, -320000) returns $1,733.33. For month 1's principal: =PPMT(6.5%/12, 1, 360, -320000) returns $289.29 — the same numbers you'd get from our main amortization calculator or from the hand-calculated example on our methodology page, since both use the identical underlying formula.

Common Excel Amortization Mistakes

• Forgetting to divide the annual rate by 12. PMT, IPMT, and PPMT all expect a periodic (monthly) rate, not an annual one.
• Sign errors. Enter the loan amount as negative (or wrap it in a negative sign as shown above) — otherwise every formula returns a negative payment, which is easy to misread as a formula error.
• Rounding drift in the final row. See the FAQ below for the fix — cap the last payment so the balance lands at exactly zero.

Adding Extra Payments to Your Formula-Based Schedule

The built-in PPMT/IPMT functions assume a fixed schedule with no extra payments, so to model prepayment you need to switch from formula-per-row to a running-balance approach: track the balance in its own column, calculate each month's interest as =balance*(rate/12), principal as =payment - interest + extra_payment, and the new balance as =old_balance - principal. Add an IF statement so the schedule stops once the balance reaches zero, since extra payments mean the loan finishes before your original term. This is exactly the row-by-row method our extra payments calculator uses internally — the CSV download from that page already includes this column if you don't want to build it yourself.

Adding Taxes, Insurance, and PMI Columns

Property taxes and homeowners insurance don't use PMT/IPMT/PPMT at all — they're simply your annual cost divided by 12, added as a flat column every month. For PMI, add a column that checks your running balance against 80% of the original home value with an IF statement, charging your PMI rate ÷ 12 only while the balance is above that threshold. Our PITI calculator runs this exact logic if you'd rather download it pre-built than replicate the formulas by hand.

Comparing Two Loan Scenarios Side by Side

The main advantage of a real spreadsheet over a web calculator is running two scenarios at once. Duplicate your schedule onto a second tab, change one variable — a different rate, a shorter term, or an added extra-payment column — and reference both tabs' final totals (total interest, payoff month) on a summary tab with a simple subtraction formula. This is exactly what our refinance calculator does automatically on the web, but a spreadsheet lets you save and revisit as many scenario pairs as you want, side by side, rather than one comparison at a time.

Locking the Schedule So It Survives Editing

The single most common way a working spreadsheet breaks is a relative reference that should have been absolute. Your rate, term, and starting balance live in one cell each, but every row of a 360-row schedule points back at them. Write =PMT(B2/12, B3*12, -B1) and then fill it down, and Excel helpfully shiftsB2 to B3, B3 to B4, and quietly produces a column of numbers that look plausible and are entirely wrong.

Anchor the input cells with dollar signs — $B$1, $B$2, $B$3 — so they stay pinned no matter how far you fill. Better still, select each input cell and give it a defined name (Formulas → Define Name), then write =PMT(Rate/12, Years*12, -Loan). Named ranges are absolute by default and make the formula readable a year later when you have forgotten which cell held what.

One more guard worth adding: put =ROUND(...,2) around your interest and principal columns. Excel carries roughly fifteen significant digits internally, so an unrounded 360-row schedule accumulates fractions of a cent that leave a final balance of −0.0000000004 instead of zero. Rounding each row to cents matches what a servicer actually posts to your account and makes the closing balance land clean.

Reconciling Your Spreadsheet Against a Real Statement

When a spreadsheet disagrees with a lender statement, the spreadsheet is usually right about the math and wrong about the calendar. Three differences account for nearly every mismatch, and none of them mean your formulas are broken.

The first is interim interest. Loans rarely close on the first of the month, so the first payment often covers a partial period — close on the 18th and you may owe interest for the remaining days of that month before the regular schedule begins. Your row 1 will not match the statement, but row 2 onward will. The second is escrow. The payment your servicer collects bundles property tax and homeowners insurance alongside principal and interest, and that escrow portion changes every year when the tax bill or premium is reassessed — model it in a separate column rather than trying to force it into PMT. The third is payment timing: interest accrues to the date a payment posts, so a payment that clears on the 5th rather than the 1st carries a few extra days of interest that a clean monthly model does not anticipate.

The Consumer Financial Protection Bureau's explainer on escrow accounts covers why the escrowed portion moves from year to year, and its loan options guide is a good sanity check on the terms you are modelling before you commit them to a spreadsheet.

Methodology

Excel's PMT/IPMT/PPMT functions and our own calculation engine both implement the identical standard amortization formula — we verified this page's worked example against both independently before publishing. Microsoft documents the argument order and sign convention for the PMT function, which is worth reading once if your payment comes back negative — that is the sign convention working as designed, not an error. See our full methodology page for the underlying math without any spreadsheet-specific syntax.