The PMT function calculates what you owe each month on a loan or investment

Excel's PMT function does the math for monthly payments automatically. You give it three pieces of information—the interest rate, the number of payments, and the loan amount—and it returns the monthly payment you need to make. This works for mortgages, car loans, personal loans, and any other debt with a fixed rate and fixed term.

The formula looks like this: =PMT(rate, nper, pv). The function returns a negative number by default (Excel treats it as money leaving your account), so most people add a minus sign in front to flip it positive and easier to read.

Key Takeaways

  • The PMT formula requires three inputs: monthly interest rate, total number of payments, and the loan amount (present value).
  • You must divide the annual interest rate by 12 to get the monthly rate, and multiply the loan term in years by 12 to get total payments.
  • The result appears as a negative number by default; add a minus sign before the formula to display it as a positive payment amount.
  • You can add a fourth input (future value) if the loan has a balloon payment or if you want to calculate savings that reach a target amount.

Setting up the three required inputs

Before you write the formula, gather the loan details and set them up in separate cells so you can change them easily later. Put the annual interest rate in one cell, the loan amount in another, and the loan term in years in a third.

For example, if you have a $250,000 mortgage at 6.5% interest over 30 years, you might put 6.5% in cell B2, 250000 in cell B3, and 30 in cell B4. Keeping these separate makes it straightforward to test different scenarios—change the rate to 7% and see the new payment when ready.

Converting annual figures to monthly figures

The PMT function works in months, not years. Your interest rate and loan term need to be converted before they go into the formula. Divide the annual interest rate by 12 to get the monthly rate. Multiply the years by 12 to get the total number of monthly payments.

If your annual rate is in cell B2 and your years are in cell B4, the formula becomes: =PMT(B2/12, B4*12, -B3). Notice the loan amount (B3) has a minus sign in front—Excel requires this. The negative sign tells the function that money is going out (you are borrowing), so the payment comes back as a positive number.

Reading the result and flipping the sign if needed

When you enter the formula, Excel returns a number. If it appears negative (with a minus sign), add a minus sign at the start of your entire formula to flip it: =-PMT(B2/12, B4*12, -B3). This makes the payment display as a positive amount, which is easier to read on a budget or loan document.

The number that appears is your monthly payment before taxes, insurance, or other fees. For a mortgage, you would add property tax and homeowners insurance separately. For a car loan, you would add insurance and registration separately.

Testing different loan scenarios

Once your formula is set up, change any of the three inputs to see how the payment shifts. Raise the interest rate by half a percent and watch the monthly payment climb. Lower the loan term from 30 years to 20 and see the payment jump. Lower the loan amount and the payment drops.

This is useful when you are deciding between loan offers. A lender offers you 6.5% for 30 years or 7% for 25 years—plug both into your spreadsheet and compare the monthly cost against how much interest you pay over the life of the loan. The lower monthly payment is not always the better deal if you end up paying thousands more in interest.

Adding a balloon payment or savings target

The PMT function has a fourth input called future value (fv). Use this if your loan has a balloon payment due at the end—a large lump sum you pay when the loan matures. The formula becomes: =-PMT(B2/12, B4*12, -B3, B5), where B5 holds the balloon amount.

You can also use future value for savings calculations. If you want to know how much to save each month to reach $50,000 in five years at 4% annual interest, set the loan amount to 0, the future value to 50000, and the rate to 4%. The PMT function tells you the monthly deposit needed.

Common mistakes and how to fix them

The most frequent error is forgetting to convert the annual rate to a monthly rate or the years to months. If your payment looks wildly too high or too low, check that you divided the rate by 12 and multiplied the years by 12. A second common mistake is putting the loan amount in as a positive number when it should be negative—Excel needs the minus sign to calculate correctly.

If you see a #DIV/0! error, the interest rate is probably zero or formatted incorrectly. If you see #NUM!, one of your inputs is negative when it should be positive, or vice versa. Check the sign of each input and flip it if needed.

Frequently Asked Questions

What if I have a loan with no interest?

Enter 0 for the interest rate. The PMT function will divide the loan amount evenly across all payments. A $12,000 loan with 0% interest over 60 months returns $200 per month.

Can I use PMT for a credit card or line of credit?

Yes, but only if you know the exact interest rate and the exact payoff date you want. Credit cards charge variable rates and minimum payments change based on your balance. PMT works best for fixed-rate loans with set terms.

Why does my payment look different from what the lender quoted?

Lenders often round payments to the nearest dollar, and some loans have fees or insurance built into the rate. Your PMT calculation shows the pure interest and principal payment. Check your loan documents for the exact annual percentage rate (APR) and confirm you are using the right number of payments.

Can I calculate how much total interest I will pay?

Yes. Multiply the monthly payment by the total number of payments, then subtract the original loan amount. If your payment is $1,200, you have 360 payments, and the loan was $250,000, the total interest is (1200 × 360) − 250000 = $180,000.

What if the interest rate changes during the loan?

PMT assumes a fixed rate for the entire term. If your rate adjusts (as with an ARM mortgage), you would need to recalculate the payment each time the rate changes, using the remaining balance as the new loan amount and the remaining months as the new term.