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:
- In a cell, type
=PMT(. - Enter the monthly rate:
4.5%/12(or0.045/12). - Enter the number of periods:
5*12(60 months). - Enter the loan amount as a negative value:
-20000. - 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. Use10*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
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel Eye Center Spanish Fork: Services, Doctors & More
- Excel Energy Group: What It Is and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Criteria Examples for ExcelIn Excel for Microsoft 365, COUNTIF counts cells in one range that meet one condition: enter the range…
- Find Lowercase Letters in Excel with Formulas and FiltersTo find cells containing at least one lowercase letter, put the text in A2 and enter =IF(A2="",FALSE,LET(chars,MID(A2,SEQUENCE(LEN(A2)),1),SUMPRODUCT(--EXACT(chars,LOWER(chars)),--NOT(EXACT(chars,UPPER(chars))))>0)) in…
- COUNTIF for Text in Excel: Examples and WildcardsTo count cells containing a particular text value in Excel, use COUNTIF: with the values to check in…
- How to Filter Words in Excel: Text Filters Made ClearTo filter words in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel OFFSET Height: How the Argument Works in FormulasIn Excel, OFFSET’s height argument sets how many rows the returned reference includes; it does not control how…
- Excel Formula Line Break: How to Add One in a CellTo put a line break in an Excel formula, join the text with CHAR(10), then turn on Wrap…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.