# How to calculate a monthly mortgage payment in Excel

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

**Excel:** `=PMT(B4/12,B5*12,-B2*(1-B3))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Mortgage | Value |
| Home price | 420000 |
| Down payment | 0.2 |
| Annual interest rate | 0.0625 |
| Term (years) | 30 |
| Monthly payment |  |

Formula in B6: `=PMT(B4/12,B5*12,-B2*(1-B3))`

Result: $2,068.81

## 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.

## Other ways

- `=PMT(B4/12,B5*12,-(B2-B2*B3))`: The same loan amount, written as price minus down payment.
- `=PMT(B4/12,15*12,-B2*(1-B3))`: A 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-a-monthly-mortgage-payment