Use the PMT function to find your monthly payment
Excel's PMT function calculates what you owe each month on a loan or installment plan. You give it three pieces of information — the interest rate, the number of payments, and the total amount borrowed — and it returns the monthly payment amount.
The formula looks like this: =PMT(rate, nper, pv). The function works for any loan: a car loan, a personal loan, a mortgage, or a buy-now-pay-later plan. 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 a PPMT function (principal payment) and IPMT function (interest payment) if you want to see how much of each monthly payment goes toward interest versus the amount you borrowed. Most people start with PMT and add those later if they need the breakdown.
Key Takeaways
- The PMT function requires three inputs: monthly interest rate (annual rate divided by 12), total number of payments, and the loan amount as a negative number.
- Excel returns a negative number by default; add a minus sign before the formula or multiply by -1 to show the payment as a positive amount.
- If you know the monthly payment and want to find the loan amount, use the PV function instead; if you want to find the interest rate, use the RATE function.
- The PMT calculation assumes a fixed interest rate and equal payments every month — it does not account for variable rates, late fees, or prepayment.
Set up your spreadsheet with the loan details
Start by entering the information you have into separate cells. You need the loan amount (principal), the annual interest rate, and the loan term in months. Put labels in one column and the numbers in the next so you can see what each value represents.
For example:
| Loan Amount | 25000 |
| Annual Interest Rate | 0.065 |
| Loan Term (months) | 60 |
Enter the interest rate as a decimal (6.5% becomes 0.065). If your rate is already shown as a percentage in the cell, Excel will handle it, but decimals are clearer. The loan term must be in months, not years — multiply years by 12 if you need to convert.
Enter the PMT formula with the correct syntax
Click on an empty cell where you want the monthly payment to appear. Type the formula, using cell references instead of typing the numbers directly. This way, if you change the loan amount or interest rate later, the payment recalculates automatically.
The formula is: =PMT(B2/12, B3, -B1) (assuming your interest rate is in B2, term in B3, and loan amount in B1). Notice three things:
- Divide the annual interest rate by 12 to get the monthly rate.
- The loan amount must be negative (use the minus sign) because Excel treats money you borrow as a negative cash flow.
- The term must be the total number of payments, already in months.
Press Enter. Excel returns the monthly payment. If the number is negative, add a minus sign before PMT in the formula (=-PMT(...)) or multiply the result by -1 to show it as a positive payment amount.
Understand what the result means and its limits
The number Excel returns is the fixed monthly payment you would owe if you borrowed that amount at that interest rate for that term. It includes both principal (the money you borrowed) and interest (the cost of borrowing). The payment stays the same every month until the loan is paid off.
This calculation assumes several things that may not be true in real life. It assumes the interest rate never changes, that you make every payment on time, that there are no extra fees or penalties, and that you do not pay the loan off early. If your loan has a variable rate, a balloon payment at the end, or a grace period before payments start, the PMT result will not match your actual payment.
For installment plans through retailers or buy-now-pay-later services, the PMT function gives you a close estimate, but the actual payment may differ slightly because of how the company rounds or applies fees. Use it to compare options, not as a may provide of what you will owe.
Use PV to find the loan amount if you know the payment
Sometimes you know what monthly payment you can afford and want to know how much you can borrow. Use the PV function (present value) instead. The syntax is similar: =PV(rate, nper, pmt).
For example, if you can pay $400 a month for 60 months at 6.5% annual interest, the formula is: =PV(0.065/12, 60, -400). Excel returns the maximum loan amount you could take on. Again, the payment must be negative, and the result will likely be negative — multiply by -1 to see it as a positive number.
Use RATE to find the interest rate if you know the payment and loan amount
If you have been offered a monthly payment and a loan amount but not told the interest rate, use the RATE function to reverse-engineer it. The syntax is: =RATE(nper, pmt, pv).
For example: =RATE(60, -400, 25000) returns the monthly interest rate. Multiply the result by 12 to get the annual rate. This is useful when comparing loan offers — you can see the true interest rate even if the lender only quoted the monthly payment.
Build a payment schedule to see principal and interest breakdown
Once you have the monthly payment, you can create a table showing how much of each payment goes toward interest and how much reduces the loan balance. This is called an amortization schedule.
Set up columns for payment number, payment amount, interest paid, principal paid, and remaining balance. In the first row, the remaining balance is your original loan amount. For each row after that:
- Interest paid = remaining balance × monthly interest rate
- Principal paid = monthly payment − interest paid
- New remaining balance = previous balance − principal paid
Use formulas so the schedule updates if you change the loan details. The IPMT and PPMT functions can do this automatically: =IPMT(rate, period, nper, pv) and =PPMT(rate, period, nper, pv). This shows you exactly how much interest you pay over the life of the loan and when the balance reaches zero.
Frequently Asked Questions
Why does Excel show the payment as a negative number?
Excel treats borrowed money as a negative cash flow (money going out) and payments as money coming in. The PMT function follows this convention. To display the payment as positive, put a minus sign before the PMT function or multiply the result by -1.
Can I use PMT for a loan with a variable interest rate?
No. PMT assumes the interest rate stays the same for the entire loan term. If your rate changes, you would need to recalculate the payment for each period when the rate changes, or use a more complex formula that accounts for rate adjustments.
What if I want to include a down payment or balloon payment?
Subtract the down payment from the loan amount before entering it into PMT. For a balloon payment (a lump sum due at the end), use the FV parameter in the PMT function: =PMT(rate, nper, pv, fv). The FV is the balloon amount, entered as a negative number.
Does PMT account for fees, insurance, or taxes?
No. PMT calculates only the payment on the principal and interest. If your loan includes origination fees, insurance, or taxes, add those separately or include them in the loan amount before calculating.
How do I compare two loan offers using Excel?
Set up two PMT calculations side by side with the terms of each offer. Calculate the total amount paid over the life of each loan (monthly payment × number of payments) to see which costs more. You can also build amortization schedules for each to see how much total interest you pay.