Spreadsheet Practice

How to calculate a monthly mortgage payment in Excel

Formula (Excel and Google Sheets)

=PMT(B4/12,B5*12,-B2*(1-B3))

Take the down payment off the price and use PMT: =PMT(B4/12,B5*12,-B2*(1-B3)).

Result on the example
$2,068.81 in B6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B6. Change any input and the result updates, or edit the formula to experiment.

How it works

The amount borrowed is the price minus the down payment: B2*(1-B3), $336,000 here. PMT spreads that over 360 monthly payments at a twelfth of the annual rate. The minus sign makes the payment come out positive.

=PMT(B4/12,B5*12,-B2*(1-B3))

Result2068.809793

  1. B46.25%

    The value in B4.

  2. B4/120.005208333333

    The / operator divides.

  3. B530

    The value in B5.

  4. B5*12360

    The * operator multiplies.

  5. B2$420,000

    The value in B2.

  6. -B2-420000

    Makes the value negative.

  7. B320%

    The value in B3.

  8. 1-B30.8

    The - operator subtracts.

  9. -B2*(1-B3-336000

    The * operator multiplies.

  10. PMT(B4/12,B5*12,-B2*(1-B3))2068.809793

    PMT: Payment per period for a loan or annuity.

    • rateB4/120.005208333333
    • nperB5*12360
    • pv-B2*(1-B3-336000

In Google Sheets

The same formula works in Google Sheets: =PMT(B4/12,B5*12,-B2*(1-B3)). We ran it in Google Sheets mode on the same data and it returns $2,068.81.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=PMT(B4/12,B5*12,-(B2-B2*B3))$2,068.81The same loan amount, written as price minus down payment.
=PMT(B4/12,15*12,-B2*(1-B3))$2,880.94Different resultA 15-year term, for comparison: a higher payment but far less interest. Higher payment over 15 years: $2,880.94.

Common mistakes

  • This is principal and interest only. Lenders quote PITI: add a twelfth of the yearly property tax and insurance, such as +B7/12+B8/12.
  • If the down payment is an amount rather than a percentage, subtract it directly: -(B2-B3).
  • Rates are often quoted to three decimals (6.125%). Format the rate cell to show them, or a typo can hide.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate a monthly mortgage payment

In B6, calculate the monthly principal and interest payment on the home in B2, with the down payment percentage in B3, the rate in B4 and the term in B5.

Questions

How do I include private mortgage insurance?

Add the monthly PMI to the payment: =PMT(B4/12,B5*12,-B2*(1-B3))+B2*(1-B3)*0.006/12 for a 0.6% yearly PMI rate.

How much interest will I pay over the loan?

Multiply the payment by the months and subtract the loan: =B6*B5*12-B2*(1-B3).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.