Use the PMT function to find your monthly payment
Excel's PMT function calculates what you owe each month on a loan. You give it three pieces of information—the interest rate, the number of payments, and the loan amount—and it returns the monthly payment amount.
The formula looks like this: =PMT(rate, nper, pv). The function works for any loan: car loans, personal loans, mortgages, or lines of credit. It assumes you make equal payments every month and that the interest rate stays the same for the life of the loan.
Excel also has related functions if you need to find other loan details: RATE finds the interest rate if you know the payment, NPER finds how many payments you'll make, and PV finds the original loan amount. But PMT is the one you use when you want to know what your monthly bill will be.
Key Takeaways
- The PMT function requires three inputs: the monthly interest rate (annual rate divided by 12), the total number of payments, and the loan amount as a negative number.
- If your annual interest rate is 6%, divide it by 12 to get 0.005 for the monthly rate, or divide 6 by 100 first to get 0.06, then divide by 12.
- The loan amount must be entered as a negative number (like -50000 for a $50,000 loan) so the result shows as a positive payment amount.
- Excel's PMT function assumes payments happen at the end of each month; if your loan requires payments at the start of the month, add a fourth input of 1 to the formula.
Set up your loan details in separate cells
Before you write the PMT formula, create cells for each piece of information the function needs. This makes the formula easier to read and easier to change if you want to test different loan amounts or interest rates.
In one column, label what each number represents. In the next column, enter the actual values. For example:
- Cell A1: "Loan Amount" | Cell B1: 50000
- Cell A2: "Annual Interest Rate" | Cell B2: 0.06 (or 6%)
- Cell A3: "Loan Term (Years)" | Cell B3: 5
- Cell A4: "Monthly Payment" | Cell B4: (this is where your formula goes)
Keep the annual interest rate and loan term in their original form—Excel will handle the conversion. The loan amount should be the total you borrowed, not what you still owe.
Convert annual rate and years into monthly values
The PMT function works with monthly numbers, so you must convert the annual interest rate and the loan term before you plug them into the formula.
For the interest rate: divide your annual rate by 12. If your loan charges 6% per year, that is 0.06 ÷ 12 = 0.005 per month. If you entered the rate as a percentage (like 6 in the cell), divide by 100 first, then by 12: (6 ÷ 100) ÷ 12 = 0.005.
For the number of payments: multiply the loan term in years by 12. A 5-year loan means 5 × 12 = 60 monthly payments. A 30-year mortgage means 30 × 12 = 360 payments.
You can do these conversions inside the PMT formula itself, or in separate cells. Doing them in separate cells makes it easier to see what you calculated and to spot errors.
Write the PMT formula with the correct syntax
Once your numbers are in place, the PMT formula goes into the cell where you want the payment amount to appear. The syntax is:
=PMT(rate, nper, pv)
Replace each part with either a cell reference or a calculation:
- rate: the monthly interest rate. If it is in cell B2 and is an annual rate of 6%, write B2/12 or (B2/100)/12 depending on how you entered it.
- nper: the total number of monthly payments. If the term in years is in cell B3, write B3*12.
- pv: the loan amount as a negative number. If the amount is in cell B1, write -B1 or just reference it as -50000 if you type the number directly.
A complete formula might look like: =PMT(B2/12, B3*12, -B1). When you press Enter, Excel calculates and displays your monthly payment.
Understand why the loan amount must be negative
Excel treats the loan amount as money flowing out of your account (a negative cash flow from your perspective), and the monthly payment as money flowing back in (positive). This is why you enter the loan as a negative number: it tells Excel you borrowed that money.
If you forget the negative sign and write =PMT(B2/12, B3*12, B1), Excel will return a negative payment amount. That is technically correct from Excel's perspective—it is showing the opposite direction of cash flow—but it is confusing to read. Always use the negative sign so your result shows as a positive monthly payment.
Some people prefer to enter the loan amount as positive and put the negative sign in the formula itself, like =PMT(B2/12, B3*12, -B1). Either way works; choose whichever feels clearer to you.
Test your formula with a known loan
Before you rely on your formula for a real decision, test it with a loan you can verify. Use an online loan calculator or a loan statement you already have, and compare the result.
For example, a $200,000 mortgage at 4% annual interest over 30 years should produce a monthly payment of approximately $954.83 (not including taxes, insurance, or other fees). If your formula gives a very different number, check that you converted the interest rate and term correctly, and that the loan amount is negative.
Once you confirm the formula works, you can change the numbers in your cells to match any loan you want to calculate. The formula itself stays the same.
Add optional inputs for payments at the start of the month
Most loans require you to pay at the end of each month. Excel's PMT function assumes this by default. But some loans or leases require payment at the beginning of the month instead.
If your loan requires a payment at the start of each month, add a fourth input to the PMT formula: =PMT(rate, nper, pv, 1). The 1 tells Excel to calculate for beginning-of-month payments. Use 0 (or leave it blank) for end-of-month payments, which is the standard.
This change will make your monthly payment slightly lower, because you are paying earlier and the lender is earning interest on that payment sooner. The difference is usually small but worth noting if you are comparing loan offers.
Frequently Asked Questions
What if my interest rate changes during the loan?
The PMT function assumes a fixed rate for the entire loan term. If your rate is variable or adjusts at certain points, PMT will not account for that. You would need to calculate separate payments for each rate period and add them together, or use a more complex spreadsheet model that recalculates the balance and payment at each rate change.
Can I use PMT for a loan that compounds interest daily instead of monthly?
PMT is designed for monthly compounding, which is standard for most consumer loans. If your loan compounds daily, the difference is usually small enough that PMT will give you a close estimate. For exact accuracy, you would need to adjust the rate and payment frequency to match the loan's actual terms, which may require a custom formula.
How do I find out how much interest I will pay over the life of the loan?
Multiply your monthly payment by the total number of payments, then subtract the original loan amount. For example, if your payment is $954.83 per month for 360 payments, that is $343,738.80 total paid minus $200,000 borrowed, which equals $143,738.80 in interest.
What if I want to know how much of each payment goes toward interest versus principal?
Excel has two functions for this: IPMT shows how much of a specific payment is interest, and PPMT shows how much is principal. Both require the same inputs as PMT plus a payment number. For example, =IPMT(rate, 1, nper, pv) tells you how much interest is in your first payment.
Can I use PMT for a loan with irregular payment amounts?
No. PMT assumes equal payments every month. If your loan has a balloon payment at the end, or if payments change over time, you need a different approach—usually a loan amortization table that you build row by row in Excel, calculating the remaining balance and interest charge for each period.