Use the PMT function to calculate what you owe each month

Excel's PMT function calculates your monthly loan payment in seconds. You give it three pieces of information — the interest rate, the number of payments, and the loan amount — and it returns the payment you owe each month. The formula looks like this: =PMT(rate, nper, pv).

The function works for any loan: a car loan, a mortgage, a personal loan, or a business line of credit. It assumes you make equal payments every month and that the interest rate stays the same for the life of the loan. If your loan has a variable rate or a balloon payment at the end, you will need a different approach.

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 as a negative number.
  • Excel returns the monthly payment as a negative number by default; multiply the result by -1 or format the cell to show it as positive.
  • You can build a full amortization table in Excel to see how much of each payment goes to interest versus principal.
  • The PMT function assumes a fixed interest rate and equal monthly payments; variable-rate loans require manual recalculation when the rate changes.

The three inputs the PMT function needs

Rate is your monthly interest rate. If your loan agreement states an annual rate of 6%, divide by 12 to get 0.06/12, or 0.005 per month. Excel accepts this as a decimal (0.005) or as a percentage (0.5%). If you enter the annual rate directly without dividing, your payment will be wildly wrong.

Nper is the total number of payments you will make. If you have a 5-year loan, that is 5 times 12, or 60 payments. If the loan is 30 years, it is 360 payments. Count every single payment, including the last one.

Pv (present value) is the loan amount. Enter it as a negative number. If you borrowed $200,000, enter -200000. This negative sign tells Excel that money left your account; the payment it calculates will be positive, representing money flowing back out each month.

A worked example with real numbers

Say you borrowed $25,000 for a car at 5.5% annual interest over 5 years. In an empty cell, type:

=PMT(0.055/12, 5*12, -25000)

Excel returns -472.55. The negative sign is a convention; your actual payment is $472.55 per month. If you want the result to display as positive, wrap the formula in a negative sign: =PMT(0.055/12, 5*12, -25000)*-1, which returns 472.55.

Over 60 months, you will pay $472.55 × 60 = $28,353. The difference between $28,353 and your original $25,000 loan is $3,353 in interest.

Building an amortization table to see interest versus principal

The PMT function tells you the payment, but it does not show you how much of each payment goes to interest and how much reduces the loan balance. An amortization table breaks this down month by month.

Set up four columns: Payment Number, Beginning Balance, Payment, Interest, Principal, and Ending Balance. In the first row, the beginning balance is your original loan amount. For each row, calculate interest as Beginning Balance × Monthly Rate, then Principal as Payment − Interest, then Ending Balance as Beginning Balance − Principal. Copy the ending balance down to become the beginning balance of the next row.

In the first month of the $25,000 car loan, interest is $25,000 × 0.055/12 = $114.58. Principal is $472.55 − $114.58 = $357.97. Your balance drops to $24,642.03. In month 2, interest is calculated on $24,642.03, which is slightly less, so slightly more of your payment goes to principal. By month 60, almost the entire payment is principal because the balance is nearly zero.

What to do if the interest rate changes mid-loan

If your loan has a variable rate and the rate changes, the PMT function does not automatically recalculate. You have to do it manually. When the rate changes, calculate how many payments remain, what your current balance is, and run PMT again with the new rate and new balance.

For example, if you are 24 months into a 60-month loan and the rate jumps from 5.5% to 6.5%, you have 36 payments left. Look up your current balance from your amortization table or your loan statement. Then calculate the new payment: =PMT(0.065/12, 36, -[current balance]). Your new monthly payment will be higher because the rate is higher and you still have to pay off the remaining balance.

Common mistakes and how to fix them

The most common error is forgetting to divide the annual interest rate by 12. If you enter 0.055 instead of 0.055/12, your payment will be 12 times too high. Always divide the annual rate by 12 to get the monthly rate.

The second mistake is entering the loan amount as a positive number. PMT expects it as negative. If you enter 25000 instead of -25000, Excel returns a negative payment, which is confusing. Either enter the loan amount as negative, or multiply your result by -1.

The third mistake is using the wrong number of periods. If you have a 5-year loan, use 60, not 5. If you have a 30-year mortgage, use 360, not 30. The function counts individual payments, not years.

When PMT does not work for your loan

PMT assumes equal payments and a fixed interest rate. If your loan has a balloon payment — a large lump sum due at the end — you need the PPMT and IPMT functions instead, or you need to adjust your approach. If your loan has a grace period where you do not pay for the first 6 months, you have to account for the interest that accrues during that time before calculating the payment on the remaining term.

If your loan compounds interest daily instead of monthly, or if payments are made quarterly or annually, you need to adjust the rate and period inputs to match your actual payment schedule. The function is flexible, but it requires you to think through what the inputs actually represent in your situation.

Frequently Asked Questions

Why does PMT return a negative number?

Excel treats money leaving your account as negative and money entering as positive. When you borrow, the loan amount is negative (money leaves the lender's account). The payment is calculated as negative for consistency. Multiply by -1 or format the cell as currency to display it as positive.

Can I use PMT for a mortgage?

Yes. Use the annual interest rate divided by 12, the number of years times 12 for the period, and the loan amount as negative. For a $300,000 mortgage at 7% over 30 years: =PMT(0.07/12, 360, -300000) returns approximately $1,996 per month. This does not include property taxes, insurance, or HOA fees.

What if I want to know the total interest paid?

Multiply the monthly payment by the number of payments, then subtract the original loan amount. For the $25,000 car loan at $472.55 per month over 60 months: ($472.55 × 60) − $25,000 = $3,353 in total interest.

Can PMT handle extra payments toward principal?

PMT calculates the standard payment only. To model extra payments, build an amortization table and manually reduce the balance each month by the extra amount. This shortens the loan term and reduces total interest, but PMT itself does not calculate this scenario.

What if my payments are quarterly instead of monthly?

Divide the annual rate by 4 instead of 12, and multiply the years by 4 instead of 12. For a $50,000 loan at 6% annual interest over 5 years with quarterly payments: =PMT(0.06/4, 5*4, -50000).