The PMT function does the math for you

Excel has a built-in function called PMT that calculates your monthly mortgage payment in seconds. You give it three pieces of information — your interest rate, how many months you'll be paying, and the loan amount — and it returns your payment. You don't need to understand the formula behind it; you just need to know where to put your numbers.

The PMT function works the same way in Excel on Windows, Mac, Google Sheets, and most other spreadsheet programs. Once you set it up once, you can change any number and see the new payment when ready. This is much faster than using a calculator or a mortgage website, especially if you want to test different loan amounts or interest rates.

Key Takeaways

  • The PMT function in Excel calculates monthly payment using the formula =PMT(rate, nper, pv), where rate is your monthly interest rate, nper is the total number of months, and pv is the loan amount as a negative number.
  • Your annual interest rate must be divided by 12 to get the monthly rate, and your loan term in years must be multiplied by 12 to get the total number of months.
  • The loan amount must be entered as a negative number (like -300000) for the payment to show as a positive number you actually pay.
  • You can build a straightforward spreadsheet with cells for loan amount, interest rate, and loan term, then use one PMT formula to see your payment update whenever you change any number.

Setting up your spreadsheet with labels and numbers

Start by opening a blank Excel sheet or Google Sheet. In the first column, you'll create labels for the information you need. Type these labels in cells A1 through A4:

  • A1: Loan Amount
  • A2: Annual Interest Rate
  • A3: Loan Term (Years)
  • A4: Monthly Payment

Now move to column B and enter your actual numbers. In B1, enter your loan amount as a plain number — for example, 300000 (no dollar sign or commas). In B2, enter your annual interest rate as a decimal — for example, 0.065 for a 6.5% rate. In B3, enter the number of years — for example, 30.

Leave B4 empty for now. That's where your PMT formula will go and calculate the payment automatically. Your spreadsheet should now look like a straightforward table with your loan details on the left and your numbers on the right.

Writing the PMT formula step by step

Click on cell B4 (the empty cell next to "Monthly Payment"). This is where you'll type the formula. The PMT function needs three pieces of information, and you have to prepare each one slightly differently.

Type this formula exactly:

=PMT(B2/12, B3*12, -B1)

Here's what each part means. B2/12 takes your annual interest rate and divides it by 12 to get your monthly rate — because you pay interest once a month, not once a year. B3*12 takes your loan term in years and multiplies it by 12 to get the total number of months you'll be paying. -B1 is your loan amount with a minus sign in front — Excel requires this so the payment comes out as a positive number.

Press Enter. Excel will calculate your monthly payment and show it in cell B4. The number will be negative at first (like -1687.71) because of how the function works. If you want it to show as positive, change the formula to =PMT(B2/12, B3*12, -B1)*-1 instead.

Testing different loan amounts and rates

Once your formula is in place, you can change any number in column B and watch your payment update when ready. Try changing B1 to 250000 and see how the payment drops. Change B2 to 0.075 (a 7.5% rate) and see how the payment rises. Change B3 to 20 years and see how the payment increases because you're paying off the loan faster.

This is the real power of building your own spreadsheet instead of using a website calculator. You can sit with a lender on the phone, hear them quote you a different rate, type it in, and see the payment change in real time. You can ask "what if I put down more money" and change the loan amount when ready.

Keep your spreadsheet saved so you can come back to it. If you're shopping for mortgages, you can create multiple sheets — one for each lender's offer — and compare them side by side.

Understanding what your payment includes and doesn't include

The number your PMT formula shows is principal and interest only. It does not include property taxes, homeowners insurance, or mortgage insurance (PMI). Your actual monthly payment to your lender will be higher because these costs get added on top.

Some lenders quote you a "total monthly payment" that includes everything. Others quote you just the principal and interest. When you're comparing offers, make sure you know which number you're looking at. Your Excel formula gives you only the principal and interest part, so you'll need to add the other costs separately if you want to see your true total payment.

If you want to include an estimate of taxes and insurance in your spreadsheet, you can add more rows below your payment. For example, add a row for "Estimated Monthly Taxes and Insurance" and type in a number, then create a final row that adds your PMT payment plus that estimate.

Fixing common mistakes in the formula

If your payment looks way too high or way too low, check these things first. Make sure your interest rate is entered as a decimal (0.065, not 6.5 or 6.5%). Make sure your loan amount doesn't have a dollar sign or comma in it — Excel needs a plain number. Make sure you divided the rate by 12 and multiplied the years by 12 in your formula.

If you see an error message like #NUM! or #DIV/0!, it usually means one of your numbers is in the wrong format or the formula has a typo. Go back and check that you typed =PMT(B2/12, B3*12, -B1) exactly, with commas between each part and parentheses around the whole thing.

If your payment shows as a very large negative number, you probably forgot the minus sign before B1. Add it in and the payment will flip to positive. If you want the payment to show as positive without the minus sign in the formula, use =PMT(B2/12, B3*12, -B1)*-1 instead.

Moving beyond the basic formula

Once you're comfortable with the basic PMT formula, you can build a more detailed spreadsheet. Add a row for down payment, then subtract it from your home price to get your loan amount automatically. Add rows for property tax rate and insurance estimate, and calculate your total monthly cost in one cell.

You can also use PMT to answer "what if" questions. For example, create a second payment calculation below the first one with a different loan amount or rate, and compare them. Some people create a small table showing what the payment would be at different interest rates (6%, 6.5%, 7%, 7.5%) so they can see the impact of each quarter-point change.

Google Sheets works exactly the same way as Excel for this formula. If you're using a different spreadsheet program like LibreOffice or Numbers, PMT should work the same, though the exact steps to insert a function might be slightly different.

Frequently Asked Questions

What if I want to see what my payment would be with a different down payment?

Change the loan amount in B1. If you know your home price and down payment, subtract the down payment from the price to get the loan amount, then enter that number. For example, if the home costs $400,000 and you put down $80,000, your loan amount is $320,000.

Can I use this formula to calculate a car loan or personal loan payment?

Yes. The PMT formula works for any loan where you make equal monthly payments. Just enter the loan amount, annual interest rate, and number of years, and it calculates the same way. The only difference is the numbers are smaller.

Why does my payment show as negative?

Excel shows it as negative because of how the function is designed — it treats money you borrow as negative and money you pay back as positive. If you want it to show as positive, either put a minus sign in front of the whole formula (=-PMT...) or multiply the result by -1 at the end (...)*-1).

Does this payment include property taxes and insurance?

No. PMT calculates principal and interest only. Your actual monthly payment will be higher because your lender adds property taxes, homeowners insurance, and possibly mortgage insurance on top. You'll need to add those costs separately if you want to see your total payment.

What if my interest rate changes during the loan?

This formula assumes a fixed interest rate that stays the same for the entire loan. If you have an adjustable-rate mortgage (ARM) where the rate changes after a certain period, this formula only shows your payment for the fixed-rate period. You would need a different calculation for the adjusted-rate period.