Free Mortgage Amortization Template: How to Build One in Excel

Last updated: September 10, 2026

Key Takeaways

• Use actual values such as 250000, 0.065, 30, 12, and a date like 1/1/2026.
• A plain 6.5 would imply 650% annual interest and the schedule will look obviously wrong.
• Verify that a 30-year mortgage at 12 payments per year gives 360 periods.
• Mortgage formulas are unforgiving because a one-cell mistake can ripple through every row of the schedule.

A mortgage amortization template in Excel is a table. Month by month, it shows how each payment breaks into interest and principal, then how the balance shrinks. No mystery there. If you want the math behind a loan without buying software, this is the cleanest route — and it fits a mortgage amortization template nicely.

This piece is for anyone who has a mortgage amount, an annual interest rate, a loan term, and basic Excel skills. I’m assuming you already know the difference between a payment and a balance, and that you can type formulas into cells without help. I’m also assuming you want a working template, not a mortgage recommendation. This is information rather than financial advice; mortgage terms, tax treatment, and lending rules vary by country and change often, so consult a qualified adviser or lender for your own situation and check guidance from sources such as the Consumer Financial Protection Bureau or your local regulator.

What a mortgage amortization template does, and what it does not do

A mortgage amortization template lays out the payment schedule for a fixed-rate loan and shows how each installment gets allocated. Amortization is the basic idea here: a repayment schedule that slowly reduces the principal, which is the amount borrowed, over time. In Excel, that usually means one row per month, with columns for payment number, payment date, beginning balance, interest, principal, and ending balance.

Sounds simple. Mostly, it is. But a generic mortgage amortization template often misses one crucial thing: it treats every mortgage like a plain monthly fixed-rate loan. That works for a standard repayment mortgage with a fixed interest rate and level payments. It does not fit adjustable-rate mortgages, interest-only periods, balloon loans, offset mortgages, or loans with irregular extra payments. A generic sheet also will not answer questions about affordability, taxes, escrow, insurance, or prepayment penalties.

I’d build the template for a very specific use case: a fixed-rate amortizing loan with monthly payments, a start date, and no extra fees baked into the payment itself. Honest and tidy. That keeps the sheet easy to audit. If your loan has a teaser rate, a rate reset date, or an interest-only period, the basic template needs extra logic. Don’t guess. If you do not know the exact product terms in your note or offer letter, stop and read those first, or confirm them with your lender.

A good mortgage amortization template should let you change four inputs and instantly recalculate the schedule: loan amount, annual interest rate, term in years, and first payment date. In many markets, payment timing is monthly, but some loans use biweekly or weekly schedules. Excel can handle all of them; the formula structure just shifts. Use the wrong frequency, and the monthly interest is off from row 1. That math stops working fast.

How do I build a mortgage amortization template in Excel?

You build it by setting inputs, using the PMT function for the payment, then calculating interest and principal row by row. The order matters.

Start with a clean sheet and place your inputs at the top. I would use A1:B8 for the control area so the schedule stays below it.

1. Enter the loan inputs in separate cells: put loan amount in B2, annual interest rate in B3, loan term in years in B4, payments per year in B5, and first payment date in B6. Use actual values such as 250000, 0.065, 30, 12, and a date like 1/1/2026. Verify the rate is entered as a percentage decimal in Excel, not as 6.5 as a plain number. A plain 6.5 would imply 650% annual interest and the schedule will look obviously wrong.
2. Calculate the periodic interest rate: in B7, divide the annual rate by payments per year with a formula like =B3/B5. Verify that a 6.5% annual rate with monthly payments becomes roughly 0.5417% per month, not 6.5%. If the cell shows 6.5% again, your formula is missing the division.
3. Calculate the total number of payments: in B8, multiply term years by payments per year with =B4*B5. Verify that a 30-year mortgage at 12 payments per year gives 360 periods. If the number is not a whole number, one of your inputs is wrong or the loan is not a standard monthly mortgage.
4. Compute the payment with PMT: in B9, use =-PMT(B7,B8,B2). The PMT function returns the level periodic payment for a loan. Verify that the result is a positive payment amount. If Excel shows a negative number, you likely omitted the leading minus sign or reversed the cash-flow sign convention.
5. Build the schedule headers: starting in A12, enter Period, Payment Date, Beginning Balance, Payment, Interest, Principal, Ending Balance. Verify the columns line up with a single row per payment. If you add extra detail later, keep the base seven columns unchanged so the formulas stay easy to audit.
6. Enter the first row formulas: in A13 put 1; in B13 put =EDATE($B$6,0) or your first payment date; in C13 link to the loan amount; in D13 link to the payment cell; in E13 calculate =C13*$B$7; in F13 calculate =D13-E13; in G13 calculate =C13-F13. Verify that interest plus principal equals payment. If principal goes negative, the payment is too small for the rate and term.
7. Copy the formulas down for all periods: drag the first row down through 360 rows for a 30-year, monthly loan, or through the exact number from B8. Use =EDATE(B13,1) in the next payment date row and then fill down. Verify that each row’s beginning balance matches the prior row’s ending balance. If the balance jumps or repeats, one of the links is broken.
8. Add a final-balance check: in a cell near the bottom, calculate =G372 for a 360-period schedule, or use =INDEX(G:G,B8+12) if you want a flexible reference. Verify the ending balance is very close to zero. A tiny rounding residue is normal; a large remaining balance means a formula error, wrong rate frequency, or an incomplete term.

For the first row and the rows below it, the logic stays the same: interest equals the current balance times the periodic rate, principal equals payment minus interest, and ending balance equals beginning balance minus principal. That is the engine of the sheet. If you want the template to be portable, freeze the input cells with absolute references, like $B$2 and $B$7, so formulas copy correctly.

A small but important detail: Excel’s PMT, IPMT, and PPMT functions use cash-flow sign logic. If you prefer positive numbers throughout, you can wrap formulas with a minus sign. I do. It keeps the schedule readable. You can also use IPMT for interest and PPMT for principal, but I prefer showing the arithmetic directly in the table because it makes mistakes easier to spot. For official Excel function behavior, see Microsoft’s PMT documentation, and if you are verifying loan math against lender calculations, consult the loan note or a qualified professional.

What columns should be in the sheet?

Start with the seven basic columns, then add only the extras your loan actually needs. Fewer moving parts. Less room for trouble.

The base columns are enough for most fixed-rate mortgages:
– Period number
– Payment date
– Beginning balance
– Payment
– Interest
– Principal
– Ending balance

That gives you a transparent mortgage amortization template. Scan any row and you can see where the payment went. If you want to make the sheet easier to review, add a year label or an annual total section off to the right, but do not change the core math.

Two optional columns are worth adding for many readers. One is cumulative interest, which tracks total interest paid to date. That helps compare the early years with the later years, when more of each payment goes to principal. The other is extra payment, if your loan allows overpayments without a penalty. In that case, the formula for principal changes because payment plus extra payment reduces the balance faster. The loan may be paid off early, and the schedule should stop when the balance hits zero, not at row 360 just because the term says 30 years.

I would also add a summary box above the schedule:
– Original loan amount
– Total of scheduled payments
– Total interest scheduled
– First payment date
– Final payment date

Those summary figures help you catch formula drift. If the final payment date is wrong by 1 month, or the total scheduled payments look unreasonable, the schedule probably has a date or period error. For a 360-payment mortgage, the final date should be 360 months after the first payment date, adjusted for how your lender counts the first installment.

Do not add escrow, insurance, and taxes into the amortization math unless you are intentionally building a full housing-cost worksheet. Those items are not part of principal amortization. Mixing them into the loan schedule makes the sheet harder to verify.

What mistakes do people actually make in Excel?

They usually make the same five errors, and each one produces a schedule that looks plausible until you inspect a few rows. Mortgage formulas are unforgiving because a one-cell mistake can ripple through every row of the schedule.

1. Using the annual rate as if it were monthly. The consequence is huge interest charges and a payment that looks far too high. The fix is to divide the annual nominal rate by 12 for monthly payments, or by the correct payments-per-year figure for your loan.

2. Entering the rate as 6.5 instead of 6.5%. The consequence is a spreadsheet that behaves like the loan is charging 650% a year. The fix is to format the cell as Percentage or enter 0.065.

3. Forgetting absolute references. The consequence is that copied formulas drift when you fill down, so row 50 may point to row 51’s input by accident. The fix is to lock control cells with dollar signs, like $B$2 and $B$7.

4. Treating dates as text. The consequence is that monthly payment dates do not increment properly, or Excel sorts them alphabetically instead of chronologically. The fix is to enter real Excel dates and format the column as Date. If you use EDATE, confirm that the date advances by exactly 1 month per row.

5. Ignoring rounding. The consequence is a tiny residual balance that may grow into a few cents or a few dollars by the last row. The fix is to round displayed values sensibly, usually to 2 decimal places, while keeping internal formulas unrounded until the final display.

6. Assuming every mortgage is fixed-rate and fully amortizing. The consequence is a template that silently misstates loans with rate resets, payment caps, or interest-only periods. The fix is to modify the sheet for the actual contract terms or use a separate model.

A clean template should fail loudly, not quietly. If the ending balance is not near zero, or if a principal payment exceeds the outstanding balance before the final row, something is wrong and needs checking.

When should I stop using the standard template?

Stop when the loan structure no longer matches a plain monthly fixed-rate mortgage. These are the cases where the standard template is the wrong tool.

Adjustable-rate mortgage with scheduled resets: the interest rate changes on a known date, sometimes more than once — you need separate rate blocks and a new payment calculation after each reset.

Interest-only period: the borrower pays interest without reducing principal for a set time, often the first 12 months or longer — you need a different formula for those rows because principal stays flat.

Extra payments are contractually limited or penalized: the loan may charge a prepayment fee or cap overpayments — you need to model the fee and the lender’s rules before assuming faster payoff is allowed.

Balloon payment at maturity: the balance does not amortize to zero by the final scheduled payment — you need a balloon column and a final lump sum, not a standard amortization run.

Payment frequency is not monthly: the lender bills weekly, biweekly, or semi-monthly — you need to recalculate the periodic rate and the period count, because 26 biweekly payments is not the same as 12 monthly payments.

Your loan includes taxes, insurance, or escrow: the monthly amount on the statement is not the same as principal-and-interest — keep the amortization schedule separate from the housing-cost budget.

If any of those apply, the template still helps, but only after you modify it to fit the contract. A standard monthly mortgage amortization template that ignores the loan terms can mislead you more than it helps.

How can I make the template more useful without breaking it?

Add checks, not every shiny feature. The best upgrades are the ones that catch errors.

I’d add conditional formatting to flag the final balance if it is above a small threshold, such as a value that is visibly not near zero after the last payment. I would also add a simple payoff-date cell that returns the first row where ending balance reaches zero or below. That makes extra-payment scenarios easier to read. If you are using Excel 365, dynamic array formulas can help, but they are not required.

If you want a chart, keep it simple: a line chart with period on the x-axis and ending balance on the y-axis. That makes the decline visible and helps spot odd jumps. A second line for cumulative interest can show how heavily the early payments tilt toward interest, and it gives the mortgage amortization template an at-a-glance check.

Need a printout? Keep one page for inputs and one page for the table headers plus the first dozen rows. Long mortgage schedules can run 360 lines or more, so printing the whole thing is usually a bad use of paper. A PDF export is often better for sharing, but that does not change the math.

The most useful improvement, in my view, is a separate “assumptions” box. Put everything that can change in one place: amount, rate, term, payment frequency, start date, and any extra payment. That turns the worksheet into a reusable mortgage amortization template and makes it easier to audit against the loan documents.

For more guidance on Excel date and payment functions, see Microsoft’s documentation on EDATE and PMT. For mortgage terminology and consumer loan basics, the Consumer Financial Protection Bureau and your local housing authority are useful starting points, especially if your mortgage terms are unusual or state-specific.