Excel mortgage payment formula
The PMT formula for a mortgage payment in Excel and Google Sheets, what each argument means, and how to add tax, insurance and PMI.
Updated November 3, 2026
See the Mortgage Calculator Spreadsheet
The formula
Excel and Google Sheets both have a built-in function for a fixed-rate loan payment, PMT. For a mortgage it looks like this:
=PMT(annual_rate/12, years*12, -loan_amount)
With the rate in B2, the term in years in B3 and the loan amount in B4, the cell reads =PMT(B2/12, B3*12, -B4). The result is the monthly principal and interest: the part of the payment that goes to the lender.
What each part means
- rate is the interest rate per payment. Mortgages are quoted as a yearly rate and paid monthly, so divide by 12. Type 6.75% as
6.75%or0.0675, not 6.75. - nper is the number of payments: 30 years is 360, 15 years is 180.
- pv is the amount you borrow, the price minus the down payment. The minus sign makes the answer positive; without it Excel shows the payment as a negative number, because it is money going out.
- Two optional arguments, fv and type, can stay empty for a normal mortgage: the balance ends at zero and payments are made at the end of each month.
The same formula works the same way in both programs, so a sheet built in Excel gives the same payment when you open it in Google Sheets.
See the Mortgage Calculator Spreadsheet
From principal and interest to the real monthly payment
PMT only covers the loan. The amount that leaves your account each month is usually larger, because most lenders also collect money for an escrow account. People call the full payment PITI:
- Principal and interest: the PMT result.
- Property tax: the yearly tax divided by 12. Without a tax bill, a rough estimate is the price times your area's tax rate.
- Homeowners insurance: the yearly premium divided by 12.
- PMI, private mortgage insurance, on a conventional loan with less than 20% down. A common estimate is a yearly percentage of the loan, divided by 12. Your lender quotes the real figure.
- HOA dues, if the home has them. They are often paid to the association directly, but they belong in the budget.
In a sheet, each line is a short formula: =B5*B6/12 for tax at a rate, =B7/12 for insurance, =B4*B8/12 for PMI. Add them to the PMT cell and you have the payment to plan around.
When PMI stops
PMI does not last for the whole loan. Under the federal Homeowners Protection Act, on most conventional loans you can ask to cancel it once the balance reaches 80% of the home's original value, and it ends automatically at 78% if you are current on payments. To find that month in a spreadsheet, build a payment schedule and look for the first row where the balance is at or below 78% of the price. Rules differ for FHA and some other loans, so check with your lender.
Worked example
A $425,000 home with 10% down, a 30-year fixed loan at 6.75%, property tax of 1.1% a year, insurance of $1,800 a year and PMI at 0.5% of the loan. These are the sample numbers in the Reamwise Mortgage Calculator Spreadsheet, so you can check them against the file.
| Line | Formula | Per month |
|---|---|---|
| Loan amount | 425,000 − 42,500 | 382,500.00 |
| Principal and interest | =PMT(6.75%/12, 360, -382500) | 2,480.89 |
| Property tax | 425,000 × 1.1% ÷ 12 | 389.58 |
| Insurance | 1,800 ÷ 12 | 150.00 |
| PMI | 382,500 × 0.5% ÷ 12 | 159.38 |
| Total monthly payment | 3,179.85 |
Over 30 years the loan costs about $510,620 in interest on top of the $382,500 borrowed. The same rate on a 15-year loan with 20% down has a much bigger payment and much less interest; putting the options side by side in one sheet is the quickest way to see the trade.
See the Mortgage Calculator Spreadsheet
Common mistakes
- Using the yearly rate as the monthly rate.
=PMT(6.75%, 360, ...)gives a wildly wrong answer. Divide by 12. - Typing the rate as 6.75. That is 675%. Use 6.75% or 0.0675.
- Comparing PMT with the lender's quote. The quote usually includes escrow. Compare like with like.
- Leaving out closing costs. Cash to close is the down payment plus closing costs, often 2% to 5% of the price.
- Rounding each month by hand. Lenders round to the cent each month, so a schedule built from an unrounded PMT can differ from theirs by a few cents over time. That is normal.
Questions
What is the Excel formula for a mortgage payment?
=PMT(rate/12, years*12, -loan). It returns the monthly principal and interest on a fixed-rate loan.
Why does PMT show a negative number?
Excel treats money you pay as negative. Put a minus sign in front of the loan amount, or in front of PMT, to show it as a positive payment.
Does the PMT formula work in Google Sheets?
Yes. Google Sheets has the same PMT function with the same arguments, so the same formula gives the same result.
How do I add property tax and insurance?
Divide the yearly tax and the yearly insurance premium by 12 and add them to the PMT result. Add PMI and HOA dues the same way if you have them.
How do I work out the total interest on a mortgage?
Multiply the monthly principal and interest by the number of payments and subtract the loan amount: =PMT(...)*360-loan. Extra payments lower it, which a full payment schedule shows.
Is the spreadsheet result what my lender will charge?
It is an estimate. Your loan estimate from the lender has the real rate, tax, insurance, PMI and closing costs, and those can differ from the numbers you assume.
Summary
The mortgage payment formula in Excel and Google Sheets is =PMT(rate/12, years*12, -loan). It gives principal and interest only; add tax, insurance, PMI and HOA dues for the payment you will really make. In the example, a $382,500 loan at 6.75% costs $2,480.89 a month before escrow and $3,179.85 with it. The Mortgage Calculator Spreadsheet does all of this for three loan options at once, shows the cash you need to close and the month PMI drops off; for a payment-by-payment schedule with extra payments, use the Loan Amortization Schedule.
Disclaimer. For general information only. Not tax, legal or financial advice. Check the rules for your country or ask a qualified adviser. Read the full disclaimer.