The PMT function does the math for you

Excel's PMT function calculates what you owe each month on a loan or mortgage. You give it three pieces of information—the interest rate per period, the total number of payments, and the loan amount—and it returns the monthly payment. The formula looks like this: =PMT(rate, nper, pv).

The function works backward from what you might expect: it returns a negative number by default, because Excel treats the payment as money leaving your account. Most people add a minus sign in front to flip it positive and easier to read: =-PMT(rate, nper, pv).

Key Takeaways

  • The PMT function requires three inputs: monthly interest rate (annual rate divided by 12), total number of payments (years times 12), and the loan amount.
  • Excel returns a negative number by default, so use a minus sign in front of the formula to show the payment as a positive amount.
  • If you have a down payment, subtract it from the purchase price before entering the loan amount into the formula.
  • You can build a payment table that shows how much principal and interest you pay each month by combining PMT with the PPMT and IPMT functions.

Setting up the three required inputs

Before you write the formula, gather your loan details and set them up in separate cells so the formula can reference them. Put the annual interest rate in one cell, the loan amount in another, and the loan term (in years) in a third. This makes the formula easier to read and lets you change numbers without rewriting the whole thing.

For a $300,000 mortgage at 6.5% annual interest over 30 years, you might put 6.5% in cell B1, 300000 in cell B2, and 30 in cell B3. Then the formula will pull from those cells instead of having the numbers buried inside it.

Converting annual interest to a monthly rate

The PMT function works with the interest rate for each payment period. Since you are making monthly payments, you need the monthly interest rate, not the annual one. Divide the annual rate by 12.

If your annual rate is in cell B1, the monthly rate becomes B1/12. If the annual rate is 6.5%, the monthly rate is 0.065/12, or about 0.00542. Excel handles this division inside the formula, so you do not have to calculate it separately.

Converting years to the total number of payments

The PMT function also needs the total count of payments, not the number of years. Multiply the loan term in years by 12 to get the number of months.

If your loan term is in cell B3, the total payments becomes B3*12. A 30-year loan means 360 monthly payments. Again, Excel does this math inside the formula.

Writing and using the complete PMT formula

Once you have the three inputs ready, combine them into the PMT formula. If your annual interest rate is in B1, your loan amount is in B2, and your loan term in years is in B3, the formula is:

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

Enter this in a cell where you want the monthly payment to appear. Excel will calculate and display the result. For the $300,000 mortgage at 6.5% over 30 years, the monthly payment (principal and interest only, not taxes or insurance) comes to about $1,896.

The minus sign in front flips the negative result to positive. Without it, Excel shows -1896, which is technically correct but harder to read in a payment context.

Building a payment breakdown table

If you want to see how much of each payment goes toward principal versus interest, you can build an amortization table using two additional functions: PPMT (principal payment) and IPMT (interest payment).

Set up columns for payment number, principal paid, interest paid, and remaining balance. In the first row, use =PPMT(B1/12, 1, B3*12, B2) to show how much principal you pay in month 1, and =IPMT(B1/12, 1, B3*12, B2) to show the interest. The "1" tells Excel you are looking at the first payment; change it to 2, 3, 4 and so on for later months. The remaining balance after each payment is the previous balance minus the principal paid that month.

This table shows you exactly how your payment splits between interest and principal over time. Early payments are mostly interest; later ones are mostly principal.

Adjusting for down payments and extra costs

If you are buying something with a down payment, subtract the down payment from the purchase price before you enter the loan amount. For a $400,000 house with a $100,000 down payment, the loan amount is $300,000.

The PMT function covers only principal and interest. It does not include property taxes, homeowners insurance, HOA fees, or mortgage insurance. If you need a total monthly housing cost, add those amounts in separate cells below your PMT result.

Common mistakes and how to fix them

The most common error is forgetting to divide the annual interest rate by 12. If you enter 6.5 instead of 0.065, or 0.065 instead of 0.065/12, the payment will be wildly wrong. Always check that your interest rate is in decimal form (0.065, not 6.5) and divided by 12.

Another mistake is entering the loan term as months instead of years. If you put 360 in the nper field thinking it is the number of months, but then multiply it by 12 in the formula, you are asking Excel to calculate a 360-year loan. Stick to years as your input, then multiply by 12 inside the formula.

If your result is negative, you forgot the minus sign in front. Add it: =-PMT(...) instead of =PMT(...).

Frequently Asked Questions

What if I want to see what the payment would be at different interest rates?

Keep your formula the same but change the number in the interest rate cell. Excel recalculates when ready. You can also set up a small table with different rates in one column and use the PMT formula in the next column to see how each rate affects the payment. This is called a sensitivity table.

Can I calculate a payment for a loan that is not monthly?

Yes. Divide the annual rate by the number of periods per year (4 for quarterly, 2 for semi-annual, 1 for annual), and multiply the loan term by the same number. For a quarterly payment, use =-PMT(B1/4, B3*4, B2).

Does PMT include taxes and insurance?

No. PMT calculates principal and interest only. Add property taxes, homeowners insurance, and any other costs in separate rows below to get your true monthly housing expense.

What does the negative number mean if I forget the minus sign?

Excel treats the loan amount as money you receive and the payment as money you pay out, so it shows the payment as negative. The minus sign in front of PMT flips the sign so the result reads as a positive number. Both are mathematically correct; the positive version is just easier to understand.

Can I use PMT for something other than a mortgage?

Yes. PMT works for any loan with a fixed interest rate and regular payments: car loans, personal loans, student loans, or equipment financing. The inputs are the same: monthly interest rate, total number of payments, and loan amount.