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 full schedule (CSV)". 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.

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.

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. See our full methodology page for the underlying math without any spreadsheet-specific syntax.