Excel Formula for Mortgage Amortization?

Formulas & functions · Updated November 22, 2026

The core Excel formula for mortgage amortization is =PMT(rate, nper, pv), which calculates the fixed monthly payment. To build a full amortization schedule, you pair it with =IPMT and =PPMT to break each payment into interest and principal portions.

What Is Mortgage Amortization?

Amortization is the process of paying off a loan through regular, equal payments over a set term. Each payment covers the interest that has accrued since the last payment, and the remainder reduces the principal balance. Early in the schedule, interest dominates; later, principal dominates. Excel’s financial functions automate these calculations, but you must understand the inputs to avoid errors.

The Key Excel Functions: PMT, IPMT, PPMT

Three built-in functions form the backbone of any amortization schedule:

  • =PMT(rate, nper, pv) – returns the total periodic payment (interest + principal).
  • =IPMT(rate, per, nper, pv) – returns the interest portion for a specific period.
  • =PPMT(rate, per, nper, pv) – returns the principal portion for a specific period.

The arguments are:

  • rate – the interest rate per period. For monthly payments, divide the annual rate by 12.
  • nper – total number of payment periods (loan term in years × 12).
  • pv – present value, or the loan amount (entered as a negative number to get a positive payment).
  • per – the period number for which you want the interest or principal (1 to nper).

For example, a $250,000 loan at 6% annual interest over 30 years (360 months) uses =PMT(6%/12, 360, -250000), which returns $1,498.88. The interest for month 1 is =IPMT(6%/12, 1, 360, -250000) = $1,250.00, and the principal is =PPMT(6%/12, 1, 360, -250000) = $248.88.

Building a Full Amortization Schedule Step by Step

To create a complete schedule in Excel, follow these steps:

  1. In A1, type “Loan Amount”; in B1, enter 250000.
  2. In A2, type “Annual Rate”; in B2, enter 6%.
  3. In A3, type “Years”; in B3, enter 30.
  4. In A4, type “Payment”; in B4, enter =PMT(B2/12, B3*12, -B1) – result: $1,498.88.
  5. Start a table with headers in row 7: Period, Payment, Interest, Principal, Balance.
  6. In A8, enter 1. In B8, enter =$B$4.
  7. In C8, enter =IPMT($B$2/12, A8, $B$3*12, -$B$1).
  8. In D8, enter =PPMT($B$2/12, A8, $B$3*12, -$B$1).
  9. In E8, enter =B1-D8 (starting balance minus principal).
  10. For row 9, set A9 to =A8+1, and set E9 to =E8-D9. Then drag formulas down to row 367 (period 360).

Check that the final balance is $0.00 (or close, due to rounding). The sum of the interest column equals total interest paid.

Common Mistakes and How to Avoid Them

Several frequent errors trip up users:

  • Wrong rate per period – Using the annual rate directly in PMT for monthly payments gives a wildly incorrect result. Always divide by 12 (or the number of payments per year).
  • Negative vs. positive PV – If you enter the loan amount as positive, PMT returns a negative number. That’s not wrong, but it confuses schedules. Use a negative PV to get a positive payment.
  • Forgetting the final balance – The schedule should end at zero. If it doesn’t, check the number of periods or the formula references.
  • Using IPMT/PPMT with wrong ‘per’ – The ‘per’ argument must be between 1 and nper. If you copy formulas down without adjusting the period reference, every row will show the same interest.
  • Rounding too early – Excel stores more decimal places than displayed. If you manually round the payment and then calculate interest, small errors accumulate. Use the full precision or set the payment cell to show only two decimals but keep the underlying value.

Adding Extra Payments or Changing Terms

Most borrowers want to see the impact of extra payments. To model this, add a column for “Extra Payment.” In the balance formula, subtract both the scheduled principal and the extra amount. For example, if E8 is the balance after period 1, then for period 2: =E8-D9-F9, where F9 contains the extra payment. Adjust the schedule so that the balance never goes below zero; the final period will be smaller than the regular payment.

You can also use the =NPER(rate, pmt, pv) function to find how many periods remain if you change the payment. For instance, with the same loan, if you pay $1,800 monthly, =NPER(6%/12, -1800, 250000) returns about 236.8 months, meaning you’d pay off the loan in roughly 19.7 years.

Comparing Loan Options with Excel

Excel is ideal for comparing different loan scenarios. Create a small table with columns for rate, term, and loan amount, then use PMT to compute the payment. For example:

  • Loan A: 6%, 30 years, $250,000 → payment $1,498.88
  • Loan B: 5.5%, 30 years, $250,000 → payment $1,419.47
  • Loan C: 6%, 15 years, $250,000 → payment $2,109.64

To compare total interest, multiply the payment by the number of periods and subtract the principal. For Loan A, total interest is $1,498.88 × 360 − $250,000 = $289,596.80. For Loan C, it’s $2,109.64 × 180 − $250,000 = $129,735.20. This shows how a shorter term saves interest but raises the monthly payment.

Using Data Tables for What-If Analysis

Excel’s Data Table feature (Data > What-If Analysis > Data Table) lets you see how payments change with different rates or terms without rewriting formulas. Set up a single-cell formula referencing your PMT, then create a table with possible rates in one column and possible terms in another. The data table fills in the resulting payments. This is a quick way to visualize the trade-off between rate and term.

Frequently Asked Questions

Why does my PMT formula return a negative number? Because the loan amount is entered as positive. PMT returns a negative cash flow (money paid out). To get a positive number, enter the loan amount as negative (e.g., =PMT(6%/12, 360, -250000)).

Can I use Excel for a biweekly payment schedule? Yes, but adjust the rate and number of periods. For biweekly payments (26 per year), use =PMT(annual_rate/26, years*26, -loan). The payment will be roughly half of a monthly payment, but because you make 26 half-payments per year (equivalent to 13 full monthly payments), you pay extra principal and shorten the term.

How do I calculate the total interest paid over the life of the loan? Multiply the monthly payment by the total number of payments and subtract the loan amount. Or sum the IPMT column in your schedule. Both methods give the same result.

What if I want to see the schedule with a balloon payment? Use the =FV(rate, nper, pmt, pv) function to find the remaining balance after a certain number of periods. For example, after 120 payments on the 30-year loan, =FV(6%/12, 120, 1498.88, -250000) returns about $209,000, which would be the balloon amount due.

Does Excel have a built-in amortization template? Yes, Excel offers a loan amortization template in the template gallery (File > New, search “amortization”). It includes a full schedule and summary, but it may not match your exact needs. Building your own gives you full control.

Why is my schedule not balancing to zero? Usually due to rounding or incorrect period count. Check that your last period number equals nper, and that the balance formula references the correct previous balance. Also, ensure your payment formula uses the same rate and nper as your schedule.

Remember, these calculations assume a fixed interest rate and equal payments. For adjustable-rate mortgages or loans with fees, the math is more complex. Always verify your numbers with your lender or a qualified financial professional before making decisions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.