The Excel formula for loan payment calculation is =PMT(rate, nper, pv), where rate is the periodic interest rate, nper is the total number of payments, and pv is the loan amount (present value). For a $20,000 loan at 6% annual interest over 5 years with monthly payments, the formula =PMT(6%/12, 5*12, 20000) returns -$386.66 (negative because it’s a cash outflow).
How the PMT Function Works
PMT calculates the constant payment for a loan or investment with fixed interest and fixed payment periods. It uses the standard annuity formula: payment = pv * rate / (1 - (1 + rate)^-nper). Excel handles the math internally, but you must match the rate and nper to the same period. If you pay monthly, divide the annual rate by 12 and multiply the number of years by 12. If you pay quarterly, divide by 4 and multiply by 4.
The result is negative by default because it represents money you pay out. To show a positive number, wrap it with =ABS(PMT(...)) or multiply by -1. The function includes principal and interest only—it does not include taxes, insurance, or fees unless you add them separately.
Setting Up Your Spreadsheet for a Loan
Instead of typing numbers directly into PMT, reference cells so you can change assumptions easily. Assume this layout:
- A1: Annual interest rate (e.g., 0.06 for 6%)
- A2: Loan term in years (e.g., 5)
- A3: Loan amount (e.g., 20000)
- A4: Payments per year (e.g., 12 for monthly)
Then use =PMT(A1/A4, A2*A4, A3). In this example, the result is -$386.66. The payment is the same each period, assuming the rate stays fixed and you make every payment on time. If you want to see the total cost, multiply the payment by the total number of payments: =ABS(PMT(A1/A4, A2*A4, A3)) * (A2*A4) gives $23,199.60, meaning you pay $3,199.60 in interest over the life of the loan.
Understanding Rate, Nper, and PV Arguments
The three required arguments are straightforward, but each has a nuance:
- Rate: The periodic interest rate. Never enter the annual rate directly for monthly payments—divide by 12. If your rate is in a cell as a percentage (like 6%), Excel treats it as 0.06 when you reference it, so
A1/12works. - Nper: Total number of payments, not years. For a 30-year mortgage paid monthly, nper is 360. For a 3-year car loan paid monthly, nper is 36.
- PV: The present value, or the total loan amount you receive today. It should be entered as a positive number if you think of it as money received, but PMT still returns a negative payment. Some people enter it as negative to get a positive payment, but that’s confusing—use ABS instead.
There is also an optional fourth argument, [fv], for the future value (usually 0 for loans), and a fifth, [type], where 0 means payments at the end of each period (standard) and 1 means at the beginning. For most loans, you can omit both.
Comparing PMT with IPMT and PPMT
PMT gives the total payment, but you often want to break it into interest and principal. Use =IPMT(rate, per, nper, pv) for the interest portion of a specific period and =PPMT(rate, per, nper, pv) for the principal portion. The per argument is the period number (1 for first payment, 2 for second, etc.).
For the same $20,000 loan at 6% monthly over 60 months, the first payment’s interest is =IPMT(6%/12, 1, 60, 20000) which returns -$100.00, and the principal is =PPMT(6%/12, 1, 60, 20000) which returns -$286.66. Summing both gives the total payment of -$386.66. Over time, the interest portion decreases and the principal portion increases—this is called amortization.
Common Errors and How to Fix Them
Several mistakes trip up new users:
- Wrong rate period: Using
=PMT(6%, 60, 20000)instead of=PMT(6%/12, 60, 20000)returns-$1,238.34because it assumes 6% monthly interest, which is 72% annually. Always divide the annual rate by the number of payments per year. - Negative result confusion: The negative sign is normal. If you want a positive number, use
=ABS(PMT(...))or format the cell to show positive numbers, but do not change the formula to=-PMT(...)unless you understand it’s the same thing. - Entering years instead of periods: For a 5-year loan, nper must be 60 (5*12), not 5. The formula
=PMT(6%/12, 5, 20000)returns-$4,047.31, which is a 5-month loan, not 5 years. - #NUM! error: This happens if rate is -1 or less, or if nper is 0 or negative. Check your inputs.
- #VALUE! error: Usually means you have text in a numeric cell. Ensure all referenced cells contain numbers, not labels like “6%” as text.
Using PMT for Mortgages, Car Loans, and Personal Loans
The same formula works for any fixed-rate loan. For a $300,000 mortgage at 7% annual interest over 30 years with monthly payments, use =PMT(7%/12, 30*12, 300000) which returns -$1,995.91. That is the monthly principal and interest, not including property taxes or insurance—those are often escrowed separately.
For a $25,000 car loan at 4.5% over 5 years, =PMT(4.5%/12, 5*12, 25000) returns -$466.02. For a $5,000 personal loan at 10% over 3 years, =PMT(10%/12, 3*12, 5000) returns -$161.34. In all cases, the payment stays fixed, but the interest you pay over time changes based on the rate and term—longer terms mean lower payments but more total interest.
Alternatives: Calculating Payments Without PMT
If you don’t have PMT (older versions of Excel all have it, but you might be using a different tool), you can use the underlying formula directly. In a cell, enter =A3 * (A1/A4) / (1 - (1 + A1/A4)^(-A2*A4)) using the same cell references as above. This returns $386.66 as a positive number because you’re not using PMT’s sign convention.
For Google Sheets, the same PMT function works identically. In Excel for the web, PMT also works the same. If you need to calculate the payment for a variable-rate loan, PMT won’t work—you’d need to build a schedule with each period’s rate, but that’s beyond a single formula. For loans with fees or balloon payments, you’d add the fee to the PV or use the FV argument for a balloon.
Frequently Asked Questions
Why does PMT return a negative number? Excel treats money you pay out as a negative cash flow. It’s a standard convention in financial functions. To see a positive payment, wrap the formula in =ABS(PMT(...)). This does not change the actual payment amount.
Can I use PMT for a loan with monthly payments but annual interest? Yes, but you must divide the annual rate by 12. The rate argument must match the payment frequency. If you have a 6% annual rate and make monthly payments, use 6%/12 or 0.005 as the rate.
Does PMT include taxes and insurance? No. PMT only calculates principal and interest. For a mortgage, your actual monthly payment may include property tax, homeowners insurance, and PMI if required. Add those separately to the PMT result to get your total monthly obligation.
What if I want to know how much interest I pay over the whole loan? Multiply the monthly payment by the total number of payments, then subtract the loan amount. For the $20,000 loan example: =ABS(PMT(6%/12, 60, 20000)) * 60 - 20000 returns $3,199.60. Alternatively, use =CUMIPMT(6%/12, 60, 20000, 1, 60, 0) which returns -$3,199.60.
Can I use PMT for a loan with a balloon payment? Yes, use the optional fv argument. For example, a $20,000 loan with a $5,000 balloon at the end: =PMT(6%/12, 60, 20000, -5000) returns -$311.32, because you pay less each month and owe the balloon at the end. The fv must be negative because it’s a future cash outflow.
Is PMT available in older Excel versions? Yes, PMT has been in Excel since version 2.0 in 1987. It works in Excel 2010, 2013, 2016, 2019, and Microsoft 365. The same function exists in Google Sheets and LibreOffice Calc, so your formula will transfer across tools.
Related
- Excel Data Validation Must Be a Date? No—Here’s How
- Excel Data Validation Start Date: How to Set It Up
- Excel Daily Checklist Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
- SUMIF Excel Multiple Columns: Formulas and ExamplesTo use SUMIF with multiple columns, add separate SUMIF results for each value column, then combine them with…
- How to Filter in Excel and Delete the Rows You Don’t NeedTo keep certain records and delete the rest in Excel, filter for the records you want to remove,…
- Excel INDEX MATCH Duplicates: How to Handle ThemYes, Excel’s INDEX MATCH can return duplicate values, but the way it handles them depends on your formula…
- Excel RANK Function with Ties: How to Handle ThemThe Excel RANK function assigns a rank to a number within a list, and when two values are…
- Excel SEQUENCE: Create Automatic Number SequencesYes. In Microsoft Excel for Microsoft 365, enter a SEQUENCE formula to generate a series automatically: with an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.