Use the PMT Function to Find Your Monthly Payment
Excel's PMT function calculates your monthly mortgage payment in seconds. You give it three pieces of information—your 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 returns a negative number (because it represents money leaving your account), so most people add a minus sign in front to make it positive and easier to read.
You do not need to be an Excel expert to use this. The function works the same way whether you are calculating a $150,000 mortgage or a $500,000 one, and it handles the math that would take you hours to do by hand.
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.
- A typical formula looks like =PMT(0.06/12, 360, -300000) for a $300,000 loan at 6% over 30 years.
- Excel returns a negative number by default, so add a minus sign before the formula to show the payment as a positive amount.
- You can build a spreadsheet that shows how payment changes when you adjust the interest rate, loan amount, or loan term.
Set Up Your Spreadsheet with the Right Labels
Start by creating labels for the information you will input. In column A, type the labels you need: "Loan Amount", "Annual Interest Rate", "Loan Term (Years)", and "Monthly Payment". This makes your spreadsheet easy to read and lets you (or anyone else looking at it) understand what each number represents.
In column B, enter the actual numbers. For example, if you are calculating a payment on a $300,000 loan at 6% interest over 30 years, your spreadsheet might look like this:
| Label (Column A) | Value (Column B) |
| Loan Amount | 300000 |
| Annual Interest Rate | 0.06 |
| Loan Term (Years) | 30 |
| Monthly Payment | (formula goes here) |
Keep your numbers clean: enter the interest rate as a decimal (0.06 for 6%, not 6), and the loan amount as a whole number without commas or dollar signs. Excel reads numbers more reliably that way.
Build the PMT Formula Step by Step
The PMT function needs the interest rate as a monthly figure, not an annual one. If your annual rate is 6%, divide it by 12 to get the monthly rate: 0.06 ÷ 12 = 0.005. This is the first piece of the formula.
Next, calculate the total number of payments. A 30-year mortgage has 360 monthly payments (30 years × 12 months). This is the second piece.
The third piece is the loan amount, entered as a negative number. This tells Excel you are borrowing money (a negative cash flow at the start). So a $300,000 loan becomes -300000.
Put it together: =PMT(0.06/12, 360, -300000). If you are using cell references instead of typing numbers directly, it would look like =PMT(B2/12, B3*12, -B1), where B1 holds the loan amount, B2 holds the annual rate, and B3 holds the years.
Enter the Formula and Read the Result
Click on the cell where you want the monthly payment to appear (usually next to "Monthly Payment" in your label column). Type the formula, then press Enter. Excel calculates the result instantly.
The number will appear as negative (like -1799.37) because the PMT function returns money going out as a negative value. To make it positive and easier to read, wrap the formula in a minus sign: =-PMT(0.06/12, 360, -300000). Now it displays as 1799.37, which is your actual monthly payment.
This payment includes principal and interest only—it does not include property taxes, homeowners insurance, or mortgage insurance (PMI), which are often added to your actual monthly bill.
Test Different Scenarios by Changing Your Inputs
The real power of Excel is that you can change one number and see how it affects your payment instantly. Try raising the interest rate from 6% to 7% and watch the payment increase. Lower the loan term from 30 years to 15 years and see how much higher the payment becomes.
You can also create multiple scenarios side by side. Build one column for a 30-year mortgage at 6%, another for a 15-year mortgage at 5.5%, and a third for a different loan amount entirely. This helps you compare options without doing the math six times over.
Some people build a small table showing how payment changes as interest rate moves in 0.25% increments (5.5%, 5.75%, 6%, 6.25%, and so on). This gives you a quick sense of how sensitive your payment is to rate changes, which is useful when you are shopping for a mortgage and rates are moving.
Common Mistakes to Avoid
The most common error is forgetting to divide the annual interest rate by 12. If you enter 0.06 instead of 0.06/12, your payment will be wildly too high because Excel treats it as a monthly rate of 6%, not 0.5%.
Another mistake is entering the loan amount as a positive number. The PMT function is designed for loans, and it expects the borrowed amount to be negative. If you forget the minus sign, Excel returns a negative payment, which is confusing.
A third error is mixing up the loan term. Make sure you multiply years by 12 to get the number of monthly payments. A 30-year loan is 360 payments, not 30.
If your result looks wildly off—like a payment of $50,000 a month on a $300,000 loan—go back and check these three things first. Nine times out of ten, one of them is the culprit.
Add Extra Rows to Show Principal, Interest, and Taxes
Once you have your basic payment calculated, you can expand the spreadsheet to show what portion of each payment goes to interest versus principal. This requires a bit more work, but it gives you a clearer picture of how your mortgage actually works.
You can also add rows for property taxes, homeowners insurance, and PMI (if applicable) to calculate your true monthly housing cost. Many lenders bundle these into a single payment called PITI (Principal, Interest, Taxes, Insurance). Building this into your spreadsheet shows you the full number you will actually owe each month, not just the loan payment itself.
If you want to go further, you can create an amortization schedule—a month-by-month breakdown showing how much of each payment goes to interest, how much goes to principal, and what your remaining balance is. Excel can handle this too, though it requires more rows and a few additional formulas.
Frequently Asked Questions
What if I want to calculate a payment for a loan that is not 30 years?
Change the loan term in your spreadsheet. For a 15-year mortgage, use 180 payments (15 × 12). For a 20-year mortgage, use 240 payments (20 × 12). The PMT formula works the same way; only the number of payments changes.
Do I need to include property taxes and insurance in the PMT formula?
No. The PMT function calculates principal and interest only. Property taxes, insurance, and PMI are separate costs that you add afterward. Your lender will provide estimates for these, and you can add them as separate line items in your spreadsheet.
What if my interest rate changes during the loan?
The PMT function assumes a fixed rate for the entire loan term. If you have an adjustable-rate mortgage (ARM), your payment will change when the rate adjusts. You would need to recalculate the payment for the remaining balance and remaining term when the rate changes.
Can I use PMT to calculate payments for other types of loans?
Yes. The PMT function works for car loans, personal loans, and any other loan where you make regular monthly payments. Just plug in the monthly interest rate, the number of payments, and the loan amount as a negative number.
Why does Excel show my payment as a negative number?
The PMT function returns negative numbers because it treats the loan as money leaving your account. Adding a minus sign before the formula (or wrapping the entire formula in parentheses and negating it) converts it to a positive number that is easier to read.