The quickest way to calculate a loan payment in Excel is the PMT function. In its simplest form, =PMT(rate, nper, pv) returns the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount (present value). For example, =PMT(5%/12, 60, 20000) returns about -$377.42, which is the monthly payment for a $20,000, 5-year loan at 5% annual interest.
What the PMT Function Actually Does
PMT stands for “payment.” It calculates the payment required to fully amortize a loan—meaning the payment stays the same each period, and by the final period the principal balance reaches zero. The function assumes payments are made at regular intervals and the interest rate is constant over the life of the loan.
The full syntax is:
=PMT(rate, nper, pv, [fv], [type])
- rate – interest rate per period. If you have an annual rate and make monthly payments, divide by 12.
- nper – total number of payment periods. For a 5-year monthly loan, that’s 60.
- pv – present value, or the total loan amount. Enter as a positive number.
- fv – future value, or cash balance after the last payment. Default is 0, which is what you want for a fully paid-off loan.
- type – when payments are due: 0 (end of period, default) or 1 (beginning of period).
Note that Excel returns the payment as a negative number because it represents an outgoing cash flow. To show it as a positive amount, wrap the formula in ABS() or put a minus sign in front: =ABS(PMT(...)).
Setting Up Your Spreadsheet for a Loan Calculation
Instead of typing numbers directly into the formula, it’s better to put them in cells so you can change them later. Here’s a clear layout:
- A1: Annual Interest Rate (e.g., 0.05 for 5%)
- A2: Loan Term in Years (e.g., 5)
- A3: Loan Amount (e.g., 20000)
- A4: Payments per Year (e.g., 12)
Then in cell A5, enter:
=PMT(A1/A4, A2*A4, A3)
This converts the annual rate to a monthly rate (A1/A4) and the term in years to total periods (A2*A4). With the example numbers, the result is -$377.42. If you prefer a positive number, use =ABS(PMT(A1/A4, A2*A4, A3)).
Building a Full Amortization Schedule
To see how much of each payment goes to interest versus principal, you can build an amortization schedule. Start with these column headers in row 1:
- A: Period
- B: Payment
- C: Interest Paid
- D: Principal Paid
- E: Remaining Balance
Assume the loan data is in cells B1 (annual rate), B2 (term in years), B3 (loan amount). In A2, put 1 for the first period. In B2, enter the payment formula (make it positive):
=ABS(PMT($B$1/12, $B$2*12, $B$3))
In C2, calculate interest for the period. The balance at the start is the original loan amount, so:
=B3*($B$1/12)
In D2, principal paid is payment minus interest:
=B2-C2
In E2, the new balance is the old balance minus principal paid:
=B3-D2
For the next row (row 3), change the formulas to reference the previous balance. In C3: =E2*($B$1/12), in D3: =B3-C3, and in E3: =E2-D3. Copy these down for as many periods as you need (e.g., 60 rows for a 5-year loan). The final balance should be 0 (or very close, due to rounding).
Handling Different Payment Frequencies
The PMT function works for any period as long as you adjust the rate and number of periods consistently. For example:
- Monthly: rate = annual/12, nper = years*12
- Quarterly: rate = annual/4, nper = years*4
- Semi-annual: rate = annual/2, nper = years*2
- Weekly: rate = annual/52, nper = years*52
If you have a nominal annual rate compounded monthly but you want quarterly payments, you’d need to convert the rate using the EFFECT function or a formula. But for most personal loans, the rate quoted is the nominal annual rate, and payments are monthly, so the simple division works.
Common Errors and How to Fix Them
Several mistakes trip up users:
- Negative payment – Excel returns a negative number because it’s a cash outflow. Use
ABS()or a minus sign to display positive. - Wrong rate per period – Forgetting to divide the annual rate by 12 results in a payment that’s far too high. Always match the rate period to the payment period.
- Wrong number of periods – Using the number of years instead of months (e.g., 5 instead of 60) gives a much larger payment. Multiply years by payments per year.
- Entering the loan amount as negative – If you enter pv as a negative number, the payment becomes positive, which can be confusing. Keep pv positive and interpret the result.
- #NUM! error – Occurs if the rate is negative or if nper is 0. Check your inputs.
If you get a very large payment, double-check that you divided the rate and multiplied the years correctly.
Comparing Loan Scenarios with PMT
One of the best uses of PMT is comparing different loan offers. You can create a small table with columns for interest rate, term, and loan amount, then use PMT for each row. For example, with a $25,000 loan:
- Row 1: 4.5% annual, 60 months →
=PMT(4.5%/12, 60, 25000)= -$466.03 - Row 2: 5.0% annual, 60 months →
=PMT(5%/12, 60, 25000)= -$471.78 - Row 3: 4.5% annual, 72 months →
=PMT(4.5%/12, 72, 25000)= -$396.62
This shows that a longer term lowers the payment but increases total interest paid. To see total interest, multiply the monthly payment by the number of periods and subtract the loan amount.
Frequently Asked Questions
Why does Excel give a negative number for PMT? Excel treats money you pay out as a negative cash flow. To get a positive number, wrap the formula in ABS() or put a minus sign before PMT.
Can I use PMT for a loan with a balloon payment? Yes. Use the fv argument to specify the balloon amount. For example, if you owe $5,000 at the end, use =PMT(rate, nper, pv, -5000). The payment will be lower because you’re not paying off the full principal.
What’s the difference between PMT and IPMT/PPMT? PMT gives the total periodic payment. IPMT gives the interest portion of a specific payment, and PPMT gives the principal portion. Together, IPMT + PPMT = PMT for the same period.
Does PMT include taxes or insurance? No. PMT only covers principal and interest. Escrow items like property tax and homeowners insurance are not included—you’d add those separately.
How do I calculate the total cost of the loan? Multiply the monthly payment by the number of payments. For example, $377.42 × 60 = $22,645.20. Subtract the original $20,000 to get $2,645.20 in total interest.
Can I use PMT for a credit card or line of credit? PMT assumes a fixed payment and fixed rate. Credit cards typically have variable rates and minimum payments, so PMT isn’t directly applicable. Use it for installment loans like auto loans or mortgages.
Remember: This article is for educational purposes. Always verify actual loan terms with your lender or a qualified financial professional.
Related
- Oracle String to Value: Converting Text in SQL
- Snowflake String Contains: SQL Methods and Examples
- Print String and Int on Arduino
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Function Keys: What F1–F12 DoIn Excel for Microsoft 365 on Windows, the function keys F1–F12 open Help, edit cells, repeat actions, move…
- Excel Counting Unique Values in a List: Formulas and TipsTo count distinct, nonblank values in an Excel list, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your range. In Microsoft…
- Excel Formula for Grade Average: A Complete GuideThe simplest Excel formula for a grade average is =AVERAGE(range), for example =AVERAGE(B2:B10) when grades are in cells…
- Filter Excel Using a PivotTable: Practical Steps and TipsTo filter Excel data with a PivotTable, click inside the PivotTable, open the PivotTable Fields pane, and drag…
- Excel Formula Enum: How to Create Enum-Like Lists in ExcelExcel has no built-in ENUM worksheet function or enum data type, so there is no single “Excel formula…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.