Use the PMT function to find your monthly payment
Excel's PMT function calculates the monthly payment on a loan or installment plan in seconds. You give it three pieces of information — the interest rate per month, the number of months you'll pay, and the total amount borrowed — and it returns your payment amount.
The formula looks like this: =PMT(rate, nper, pv). The function returns a negative number (because it represents money leaving your account), so most people put a minus sign in front to flip it positive and easier to read.
You do not need to understand the math behind it. You only need to know what numbers go where and how to format them so Excel understands them correctly.
Key Takeaways
- The PMT function requires three inputs: monthly interest rate (annual rate divided by 12), total number of months, and the loan amount.
- If your annual interest rate is 6%, divide by 12 to get 0.06/12, or enter it as 0.005 (which is 0.5% per month).
- The loan amount must be entered as a negative number (like -50000 for a $50,000 loan) so the payment comes out positive.
- A $200,000 mortgage at 7% annual interest over 30 years (360 months) produces a different payment than the same loan over 15 years, and PMT shows you both instantly.
Set up your spreadsheet with the loan details
Start by creating cells for the information you'll need. In column A, write labels. In column B, write the numbers. For example:
| Label (Column A) | Value (Column B) |
| Loan Amount | 200000 |
| Annual Interest Rate | 0.07 |
| Loan Term (Years) | 30 |
| Monthly Payment | (formula goes here) |
The annual interest rate should be entered as a decimal: 7% becomes 0.07, 5% becomes 0.05. If your rate is given as a percentage, divide it by 100 first.
The loan amount can be any number — $5,000, $50,000, $500,000. Write it as a plain number without a dollar sign or comma (Excel will ignore those anyway).
Convert annual interest rate to a monthly rate
The PMT function works with monthly payments, so it needs the interest rate per month, not per year. To convert, divide the annual rate by 12.
If your annual rate is in cell B2 (for example, 0.07 for 7%), the monthly rate is B2/12. You can either calculate this in a separate cell or put the division directly into the PMT formula. Most people do it in a separate cell so they can see what the monthly rate actually is.
Example: 7% annual becomes 0.07 ÷ 12 = 0.00583 (roughly 0.583% per month). A 5% annual rate becomes 0.05 ÷ 12 = 0.00417 (roughly 0.417% per month).
Convert the loan term from years to months
The PMT function also needs the total number of months you'll be paying, not the number of years. Multiply the years by 12.
If your loan term is in cell B3 (for example, 30 years), the number of months is B3*12, which equals 360 months. A 15-year loan becomes 180 months. A 5-year car loan becomes 60 months.
Again, you can calculate this in a separate cell or put it directly into the PMT formula. Seeing it as a separate number helps you double-check that you entered the term correctly.
Enter the PMT formula with the correct cell references
Once your loan details are set up, click on the cell where you want the monthly payment to appear. Type the formula, using cell references instead of typing the numbers again:
=PMT(B2/12, B3*12, -B1)
Breaking this down:
- B2/12 is the monthly interest rate (annual rate divided by 12).
- B3*12 is the total number of months (years multiplied by 12).
- -B1 is the loan amount as a negative number (the minus sign in front flips it).
Press Enter. Excel calculates the monthly payment and displays it as a positive number. If it shows as negative, add a minus sign in front of the entire formula: =-PMT(B2/12, B3*12, B1).
Adjust the formula if your rate or term changes
The beauty of using cell references instead of typing numbers directly is that you can change any value and the payment recalculates instantly. If you want to see what the payment would be at 6% instead of 7%, just change the number in B2. If you want to compare a 30-year loan to a 15-year loan, change B3 and watch the payment update.
This is how you compare options without rebuilding the formula each time. Create one set of cells with your formula, then experiment by changing the inputs.
If you want to keep multiple scenarios side by side (for example, a 30-year mortgage and a 15-year mortgage), create separate columns with the same structure. Column B for the 30-year scenario, Column D for the 15-year scenario. Each has its own PMT formula using its own cells.
Common mistakes to avoid
The most common error is forgetting to make the loan amount negative. If you enter =PMT(B2/12, B3*12, B1) without the minus sign, Excel returns a negative payment, which looks wrong. Add the minus sign: =PMT(B2/12, B3*12, -B1).
The second mistake is entering the annual interest rate without dividing by 12. If you use 0.07 instead of 0.07/12, the payment will be 12 times too high. Always divide the annual rate by 12 inside the formula or in a separate cell first.
The third mistake is entering the loan term in years instead of months. If you use 30 instead of 360, the payment will be 12 times too high. Always multiply years by 12.
If your payment looks unreasonably high or low, check these three things first: the minus sign on the loan amount, the division of the interest rate by 12, and the multiplication of years by 12.
Frequently Asked Questions
What if I have an extra payment or lump sum I want to apply?
The PMT function calculates the regular monthly payment only. If you want to see how an extra payment affects the total interest or payoff date, you'll need a separate calculation or an amortization table. For now, PMT tells you what the standard monthly payment is.
Can I use PMT for a credit card or line of credit?
PMT works for any loan with a fixed interest rate and a set payoff date. Credit cards typically don't have a fixed payoff date — you choose how much to pay each month. If you want to know what monthly payment would pay off a credit card balance in a specific number of months, PMT will show you that number, but your actual credit card payment is up to you.
Why does PMT show a negative number?
Excel treats the loan amount as money you receive (positive) and the payment as money you give back (negative). To flip the payment to a positive number so it's easier to read, put a minus sign in front of the loan amount in the formula: =PMT(rate, nper, -pv).
How do I know if my interest rate is annual or monthly?
Interest rates on mortgages, car loans, and personal loans are almost always stated as annual rates. Credit card rates are also annual (called APR, or annual percentage rate). If the rate is given as a percentage without the word "monthly", assume it's annual and divide by 12 in your formula.
Can PMT handle variable interest rates?
No. PMT assumes the interest rate stays the same for the entire loan. If your rate changes (like an adjustable-rate mortgage), you would need to recalculate the payment for each period when the rate changes. PMT works only for fixed-rate loans.