Excel Monthly Loan Payment Formula: PMT Explained

Formulas & functions · Updated November 22, 2026

The Excel formula for a monthly loan payment is =PMT(rate/12, nper*12, pv), where rate is the annual interest rate, nper is the number of years, and pv is the loan amount (entered as a negative number). For example, =PMT(5%/12, 10*12, 25000) returns -$265.16, meaning a $25,000 loan at 5% annual interest over 10 years costs $265.16 per month.

What the PMT Function Does

PMT stands for “payment” and is Excel’s built-in function for calculating the periodic payment on a loan or investment based on constant payments and a constant interest rate. It is part of the financial functions category, alongside IPMT (interest portion) and PPMT (principal portion). The function assumes that payments are made at the end of each period by default, which matches most consumer loans like car loans and mortgages.

The syntax is =PMT(rate, nper, pv, [fv], [type]):

  • rate – the interest rate per period. For monthly payments, divide the annual rate by 12.
  • nper – the total number of payment periods. For monthly payments over years, multiply years by 12.
  • pv – the present value, or the total loan amount. In Excel, this is entered as a negative number because it represents money you owe.
  • fv – optional future value, usually 0 for loans. Defaults to 0.
  • type – optional: 0 for end-of-period payments (default), 1 for beginning-of-period (annuity due).

How to Build the Formula Step by Step

Assume you have a loan of $20,000 at 4.5% annual interest for 5 years. To calculate the monthly payment:

  1. In a cell, type =PMT(.
  2. Enter the monthly rate: 4.5%/12 (or 0.045/12).
  3. Enter the number of periods: 5*12 (60 months).
  4. Enter the loan amount as a negative value: -20000.
  5. Close the parenthesis and press Enter: =PMT(4.5%/12, 5*12, -20000).

This returns $372.86. Excel displays the payment as a negative number because it’s a cash outflow. To show it as a positive number, you can either enter the loan amount as positive and the result will be negative, or wrap the formula in =-PMT(...).

Understanding the Result: Negative or Positive?

Excel’s financial functions follow the sign convention: money you receive is positive, money you pay out is negative. So a loan amount (money received) is positive, and the payment (money paid) is negative. If you enter the loan amount as a negative number, the payment becomes positive. Many people prefer to see a positive payment, so they use =-PMT(rate/12, nper*12, loan_amount) where loan_amount is positive.

Common Variations: Adjusting for Different Payment Frequencies

The PMT formula adapts to any payment frequency by adjusting the rate and nper:

  • Monthly payments: rate/12, nper*12 (as shown).
  • Quarterly payments: rate/4, nper*4.
  • Semi-annual payments: rate/2, nper*2.
  • Weekly payments: rate/52, nper*52 (though this assumes 52 weeks per year, which is slightly off for actual calendar weeks).

For a mortgage, you might also want to know the total interest paid. That is =CUMIPMT(rate/12, nper*12, pv, start_period, end_period, 0) or simply multiply the monthly payment by the number of periods and subtract the principal.

Example: Comparing Loan Terms

Suppose you’re choosing between a 3-year and a 5-year loan for $10,000 at 6% annual interest. Using =PMT(6%/12, 3*12, -10000) gives $304.22 per month. For 5 years, =PMT(6%/12, 5*12, -10000) gives $193.33 per month. The shorter loan has a higher payment but much lower total interest: $10,951.92 total paid for 3 years versus $11,599.80 for 5 years. This illustrates the trade-off between monthly affordability and total cost.

Common Errors and How to Fix Them

  • Using the annual rate directly: If you enter =PMT(5%, 10, 25000), Excel treats 5% as the monthly rate, giving a wildly high payment. Always divide by 12 for monthly payments.
  • Forgetting to multiply years by 12: =PMT(5%/12, 10, -25000) would treat 10 as the total months, not years. Use 10*12.
  • Entering the loan amount as positive: This yields a negative payment, which is correct but may confuse. Use a negative sign or wrap with =-PMT(...).
  • Using commas as decimal separators: In some regional settings, Excel uses commas for decimals. Use the correct separator for your locale, or use the formula bar.

Frequently Asked Questions

Does the PMT formula include taxes and insurance?

No. PMT only calculates principal and interest. For a mortgage, property taxes, homeowner’s insurance, and PMI (private mortgage insurance) are separate costs. You would need to add those manually to get the full monthly payment.

Can I use PMT for a credit card minimum payment?

Credit card minimum payments are calculated differently (often a percentage of the balance plus interest). PMT assumes a fixed interest rate and a fixed number of payments, which doesn’t match revolving credit. Use PMT only for installment loans with a set term.

What if my interest rate changes over time?

PMT assumes a constant rate. For adjustable-rate loans, you would need to break the loan into segments or use more advanced functions. Excel’s RATE or NPER functions can help, but a full amortization schedule with varying rates requires a custom table.

How do I get the payment as a positive number?

Enter the loan amount as a negative value in the pv argument, or use =-PMT(...). For example, =-PMT(5%/12, 10*12, 25000) returns 265.16.

Why does my formula return #NUM! or #VALUE!?

#NUM! usually means the rate or nper is negative or nonsensical (e.g., a negative interest rate). #VALUE! often indicates text in the cell references. Check that all arguments are numbers, not text, and that the rate is positive.

Alternatives to PMT

If you need to see the breakdown of principal and interest each month, use IPMT and PPMT. For example, =IPMT(5%/12, 1, 10*12, -25000) returns the interest portion of the first payment ($104.17), and =PPMT(5%/12, 1, 10*12, -25000) returns the principal portion ($160.99). You can also build a full amortization schedule using a data table. For more complex scenarios, such as a balloon payment, use the optional fv argument to specify a future balance.

Remember: PMT gives you the payment amount, but it does not account for fees, compounding frequency differences, or prepayment penalties. Always check the actual loan terms with your lender or a financial advisor before making decisions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.