The PMT function does the math for you

Excel has a built-in function called PMT that calculates your monthly loan payment in seconds. You give it three pieces of information — the interest rate, the number of payments, and the loan amount — and it returns what you owe each month. You do not need to know the formula behind it or do any math by hand.

The PMT function works for any loan: a car loan, a personal loan, a mortgage, or a student loan. As long as you know the interest rate, the loan term, and the amount borrowed, you can find the monthly payment.

Key Takeaways

  • The PMT function in Excel calculates monthly loan payments using the interest rate, number of payments, and loan amount.
  • You must convert the annual interest rate to a monthly rate by dividing by 12, and convert the loan term to total months.
  • The formula structure is =PMT(monthly_rate, total_months, -loan_amount), with the loan amount entered as a negative number.
  • Excel shows the payment as a negative number by default, but the actual amount you pay is positive.

Setting up your spreadsheet

Start by opening a blank Excel spreadsheet and entering your loan information in separate cells. You need three numbers: the loan amount, the annual interest rate, and the loan term in years.

For example, put the loan amount in cell A1, the annual interest rate in cell A2, and the loan term in years in cell A3. Label each one in column B so you remember what each number means. This makes it easier to spot mistakes and to change numbers later if you want to see how different rates or terms affect the payment.

Converting the interest rate and term to the right units

The PMT function needs the monthly interest rate, not the annual rate. If your loan has a 6% annual interest rate, you divide 6 by 12 to get 0.5% per month. In Excel terms, that is 0.06 divided by 12, which equals 0.005.

You also need to convert the loan term from years to months. A 5-year loan is 60 months. So if your term is in cell A3, you multiply it by 12 to get the total number of payments.

You can do these conversions inside the PMT formula itself, so you do not need separate cells for them. This keeps your spreadsheet clean and makes the formula easier to read.

Writing the PMT formula

In the cell where you want the monthly payment to appear, type this formula:

=PMT(A2/12, A3*12, -A1)

Here is what each part means: A2/12 is the monthly interest rate (annual rate divided by 12). A3*12 is the total number of months (years multiplied by 12). -A1 is the loan amount as a negative number. The negative sign is required by Excel's PMT function — it represents money you borrowed.

Press Enter, and Excel calculates your monthly payment. The result will appear as a negative number, like -$966.71. The negative sign is just how Excel displays it; the actual payment you make is positive.

Understanding the result

The number Excel shows you is your monthly payment before taxes, insurance, or any other fees. For a mortgage, your actual monthly bill will be higher because it includes property taxes and homeowners insurance. For a car loan, it does not include insurance or registration. For a personal loan, the payment shown is usually what you actually pay each month.

If the payment seems too high or too low, double-check that your interest rate is entered as a decimal. A 6% rate should be entered as 0.06, not as 6. If you entered 6 instead of 0.06, the payment will be wildly wrong.

Changing numbers to see different scenarios

One advantage of using Excel is that you can change any number and see the result when ready. If you want to know how much the payment drops if you borrow less, change the amount in A1 and the payment recalculates. If you want to see what happens with a longer loan term, change A3.

This is useful when you are deciding between options. You might compare a 5-year car loan against a 7-year car loan, or a mortgage at 6% against one at 6.5%, without having to call a lender or use multiple websites.

What the payment includes and does not include

The PMT function shows only the principal and interest portion of your payment. It does not include property taxes, homeowners insurance, mortgage insurance, car insurance, registration fees, or any other costs tied to the loan.

For a mortgage, lenders often quote a total monthly payment that includes taxes and insurance. That number is higher than what PMT shows. For a car loan, the payment PMT shows is usually the full amount you owe the lender each month, though insurance is separate.

If you want to add taxes and insurance to your mortgage calculation, you can do that in a separate cell by adding those amounts to the PMT result. But PMT itself only calculates the loan payment itself.

Frequently Asked Questions

What if my interest rate changes during the loan?

PMT assumes a fixed interest rate that does not change. If you have an adjustable-rate loan where the rate changes over time, PMT will not give you an accurate picture of your full payment history. You would need to recalculate the payment each time the rate changes, or use a more complex spreadsheet that models each rate change separately.

Why does Excel show the payment as a negative number?

Excel treats borrowed money as positive and payments as negative, because they are opposite directions of cash flow. The negative sign is just formatting. The actual amount you pay each month is the absolute value — so -$966.71 means you pay $966.71.

Can I use PMT for a loan with monthly payments that are not the same every month?

No. PMT calculates only fixed payments — the same amount every month. If your loan has variable payments or a balloon payment at the end, PMT will not work. You would need to calculate those payments separately or use a different method.

What if I want to know the total amount I will pay over the life of the loan?

Multiply the monthly payment by the total number of months. If PMT shows -$966.71 and you have 60 months, the total paid is $966.71 times 60, which is $58,002.60. Subtract the original loan amount to see how much interest you paid.

Does PMT work for loans with different payment schedules, like biweekly?

Yes, but you have to adjust the inputs. For a biweekly payment, divide the annual rate by 26 instead of 12, and multiply the years by 26 instead of 12. The same logic applies to weekly or any other schedule — divide and multiply by the number of periods in a year.