Use the PMT function to find your monthly payment
Excel's PMT function calculates a monthly loan payment in one formula. 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: car loans, personal loans, mortgages, or student loans. It assumes the payment stays the same every month and that you make a payment every month without skipping.
Excel returns a negative number by default (because money is leaving your account). Most people put a minus sign in front of the formula to flip it to positive, so the payment shows as a number you can read without squinting.
Key Takeaways
- The PMT formula 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.
- You must convert the annual interest rate to a monthly rate by dividing by 12, or the formula will calculate wrong.
- The loan amount goes in as a negative number (like -250000 for a $250,000 mortgage) because Excel treats it as money you received.
- The result is your principal and interest payment only—it does not include property taxes, insurance, or other costs added to a mortgage payment.
Set up your loan information in separate cells
Before you write the formula, create a simple table with the loan details. Put each piece of information in its own cell so you can see what you are calculating and change numbers easily later.
In one column, write labels: "Loan Amount", "Annual Interest Rate", "Loan Term (Years)". In the next column, enter the actual numbers. For example: $250,000 in one cell, 6.5% in another, and 30 in a third. This layout makes the formula easier to read and lets you test different scenarios by changing one number at a time.
If your interest rate is written as 6.5, Excel will treat it as 650% unless you format the cell as a percentage or divide by 100 in the formula. The safest approach is to type the rate as a decimal: 0.065 for 6.5%, or 0.04 for 4%.
Write the PMT formula with the correct inputs
The PMT function needs three numbers in this order: monthly interest rate, total number of payments, and loan amount as a negative.
If your loan amount is in cell B2, your annual rate is in B3, and your term in years is in B4, the formula is:
=PMT(B3/12, B4*12, -B2)
Break this down: B3/12 converts your annual interest rate to a monthly rate. B4*12 converts years to the total number of monthly payments. -B2 is your loan amount as a negative number. The minus sign is required—if you forget it, Excel returns a negative payment, which is backwards.
Type this formula into an empty cell and press Enter. Excel calculates and shows your monthly payment.
Add a minus sign to make the payment positive
By default, PMT returns a negative number. This is correct mathematically—it represents money flowing out of your account—but it looks odd when you are reading a payment amount.
To flip it to positive, put a minus sign in front of the entire formula:
=-PMT(B3/12, B4*12, -B2)
Now the result shows as a positive number. This is purely for readability; the payment amount is the same either way.
Test your formula with a known loan
Before you trust the formula on your own loan, test it with a loan you already know the answer to. A $200,000 mortgage at 6% interest over 30 years should give you a monthly payment of roughly $1,199. If your formula returns something close to that, it is working.
Small differences (within a dollar or two) are normal because of rounding. If your result is way off—like $12,000 or $120—check that you divided the annual rate by 12 and multiplied the years by 12. Those are the two most common mistakes.
You can also verify your answer using an online loan calculator. Plug in the same numbers and see if the results match. If they do, your Excel formula is correct.
Adjust the formula for different loan types
The PMT formula works the same way for any loan, but the inputs change based on how often you make payments. Most personal loans and mortgages use monthly payments, so you divide the annual rate by 12 and multiply years by 12. If your loan uses quarterly payments instead, divide the annual rate by 4 and multiply years by 4.
Some loans charge interest differently—for example, some car loans use simple interest instead of compound interest. The PMT function assumes compound interest, which is what banks use for mortgages, personal loans, and most auto loans. If your loan uses a different method, PMT will not be accurate, and you should check your loan documents or contact your lender.
For loans with variable interest rates (rates that change over time), PMT only calculates based on the rate you enter. It cannot predict future rate changes, so use it to estimate your current payment or your payment if the rate stays the same.
Create a payment schedule to see principal and interest breakdown
Once you have your monthly payment, you can build a table that shows how much of each payment goes to principal and how much goes to interest. This is useful if you want to see how your loan balance shrinks over time.
Create columns for: Payment Number, Beginning Balance, Payment Amount, Interest Paid, Principal Paid, Ending Balance. In the first row, your beginning balance is the full loan amount. Each month, calculate the interest (beginning balance times monthly rate), subtract it from the payment to get principal paid, and subtract principal from the balance to get the ending balance for next month.
The ending balance of one month becomes the beginning balance of the next. Copy the formulas down for all 360 months (or however many payments you have), and you will see exactly when your loan is paid off and how much interest you pay in total.
Frequently Asked Questions
What if I want to see what my payment would be with a different interest rate?
Change the number in the interest rate cell, and Excel recalculates the payment instantly. This is why setting up your loan details in separate cells is useful—you can test different rates without rewriting the formula. Try 5%, then 6%, then 7% to see how sensitive your payment is to rate changes.
Does PMT include property taxes and insurance on a mortgage?
No. PMT calculates principal and interest only. A real mortgage payment (called PITI) also includes property taxes, homeowners insurance, and sometimes mortgage insurance. Add those costs separately to get your true monthly payment.
Can I use PMT for a loan with extra payments or skipped payments?
PMT assumes you make the same payment every month without missing or adding to it. If you plan to pay extra some months, PMT still tells you the minimum required payment. Use a payment schedule table to model what happens when you pay extra—your loan will be paid off faster and you will pay less interest.
What if my loan has a balloon payment at the end?
PMT does not account for balloon payments. It calculates as if you pay off the entire loan in equal monthly payments. If your loan has a lump sum due at the end, you need a different approach—either use a loan calculator designed for balloons, or build a custom spreadsheet that includes the final payment separately.
Why does my PMT result not match my loan statement?
The most common reason is that your interest rate or loan term is wrong. Check your loan documents for the exact annual percentage rate (APR) and the number of months remaining. Also check that you are using the original loan amount, not the current balance. If the numbers match and the result still differs, your lender may be using a different calculation method or may have added fees to your payment.