=PMT(rate, nper, pv) returns the fixed payment on a loan with constant payments and a constant interest rate. For monthly payments, divide the annual rate by 12 and multiply the years by 12, and expect a negative answer, because Excel shows cash you pay out as a minus.
PMT is one of Excel's financial functions. Microsoft's support page describes it as calculating the payment for a loan based on constant payments and a constant interest rate. CFI adds that the payment covers both principal and interest, and that the same function works for an investment you want to build up over a set period.
The PMT syntax
The full syntax is PMT(rate, nper, pv, fv, type). The first three arguments are required and the last two are optional.
| Argument | What it means (Microsoft) | Required? |
|---|---|---|
| rate | The interest rate for the loan, per period | Yes |
| nper | The total number of payments | Yes |
| pv | The present value: the amount borrowed, or principal | Yes |
| fv | A balance you want left after the last payment; 0 if you leave it out | No |
| type | 0 or blank for payments at the end of each period, 1 for the start | No |
A worked example, step by step
This is Microsoft's own example, in US dollars as of October 2026: a $10,000 loan at 8% a year, repaid in 10 monthly payments.
- Type the annual rate, 8%, into A2.
- Type the number of monthly payments, 10, into A3.
- Type the loan amount, 10000, into A4.
- In another cell, enter =PMT(A2/12,A3,A4).
- Excel returns ($1,037.03). The brackets mean a negative number: cash going out of your account.
- If payments are due at the start of each month instead, enter =PMT(A2/12,A3,A4,,1). Excel returns ($1,030.16).
Notice the A2/12. The rate in A2 is annual, but the payments are monthly, so the rate has to be monthly too. The two commas in the second formula skip the optional fv argument so Excel knows the 1 is the type.
Microsoft's tip for the total cost of the loan is to multiply the PMT result by nper. On this example, that's 1,037.03 x 10, or 10,370.30 in total (my arithmetic, using Microsoft's method), so about 370 of the total is interest.
The catch: rate and nper must use the same units
This is where most PMT answers go wrong. Microsoft's rule is to be consistent. For monthly payments on a four-year loan at 12% annual interest, use 12%/12 for rate and 4*12 for nper. For annual payments on the same loan, use 12% and 4. Mix them up, with an annual rate and a monthly count, and Excel will happily give you a confident, wrong number.
Two more things to know. Microsoft says the payment PMT returns includes principal and interest but no taxes, reserve payments or fees, so your real monthly outgoing may be higher. And CFI lists the errors: #NUM! when the rate is -1 or less or nper is 0, and #VALUE! when any argument isn't a number.
Using PMT to plan savings
PMT also works backwards from a target. Set pv to 0 and put your goal in fv. Microsoft's example asks how much to save each month to reach $50,000 at the end of 18 years at 6% a year: =PMT(A9/12,A10*12,0,A11) returns ($129.08). (Microsoft's data table labels the 18 as months in one place, but the formula treats it as years, so go by the formula.)
CFI's example goes the other way: to receive $75,000 after two years at 3.5% a year, paying at the start of each month, PMT returns $3,240.20 each month. Type is set to 1, the rate is converted to monthly, and the years become months, 2*12 = 24.
Splitting each payment into interest and principal
Every loan payment has two parts, and CFI points out that only the principal part reduces what you owe. Excel has a function for each part and two for running totals. These are Microsoft's example results:
| Function | What it returns | Microsoft example result |
|---|---|---|
| PMT | The fixed payment per period | ($1,037.03) for $10,000 at 8% over 10 months |
| IPMT | The interest part of one payment | ($66.67) in month 1 of $8,000 at 10% over 3 years |
| PPMT | The principal part of one payment | ($75.62) in month 1 of $2,000 at 10% over 2 years |
| CUMIPMT | Total interest over a range of periods | -11,135.23 for year 2 of $125,000 at 9% over 30 years |
| CUMPRINC | Total principal over a range of periods | -934.11 for year 2 of the same loan |
IPMT and PPMT take an extra argument, per, the period you want, which must be between 1 and nper. So =IPMT(A2/12, 1, A4*12, A5) gives the interest in the first month. CUMIPMT and CUMPRINC take a start and end period instead, and Microsoft says they return #NUM! if the rate, nper or pv is zero or negative, or if the start period is after the end period.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Building a simple amortization schedule
CFI describes an amortization schedule as a table of loan and payment details: the original amount, the balance after each payment, the rate, the term, the total payment and the split between interest and principal. It says you can build one with a few basic Excel functions. Here's the layout I'd use, which is my own way of putting IPMT and PPMT to work rather than a Microsoft template:
- Period: 1 in the first row, counting up to nper.
- Opening balance: the loan amount in row 1, then the previous row's closing balance.
- Payment: the PMT result, the same in every row.
- Interest: IPMT with per set to the row's period.
- Principal: PPMT with the same period.
- Closing balance: opening balance minus the principal paid.
You'll see the pattern CFI describes for an equal payment (blended) loan, such as a residential mortgage. The payment never changes, but the interest share starts high and shrinks, because interest is charged on a balance that keeps falling. CFI contrasts this with an equal amortizing loan, where the principal portion is fixed and interest goes on top, so payments fall over time. A loan repaid in one go at the end is a bullet repayment, and doesn't need a schedule.
Check your first row by hand: opening balance x monthly rate should equal the first month's interest. On Microsoft's $125,000 loan at 9%, that's 125,000 x 0.09 / 12 = 937.5, which is the same figure CUMIPMT returns for month 1.
Related functions: NPER and RATE
If you know the payment and need a different answer, Excel has you covered. NPER(rate, pmt, pv) returns the number of periods. RATE returns the interest rate per period; Microsoft says it works by iteration and returns #NUM! if it can't converge after 20 tries. If you'd rather find the right input by trial, Goal Seek in Excel can solve for the loan amount that gives you a payment you can afford.
My verdict
Get the units right and the rest is easy. Divide the rate, multiply the years, and read the minus sign as cash going out rather than a mistake. Then build the schedule, because seeing how much of each early payment is interest is the quickest way to understand what a loan costs you. If you're fitting a repayment into your plans, drop the PMT result straight into a monthly budget in Excel.
Frequently asked questions
Why is my PMT result negative?
Excel shows cash you pay out as a negative number. Microsoft's example result of ($1,037.03) is a payment going out, not an error.
How do I use PMT for monthly payments?
Divide the annual rate by 12 and multiply the number of years by 12. Microsoft's example for a four-year loan at 12% uses 12%/12 and 4*12.
Does PMT include fees or taxes?
No. Microsoft says the payment PMT returns includes principal and interest but no taxes, reserve payments or fees.
How do I see the interest part of a payment?
Use IPMT for the interest and PPMT for the principal, giving the period number you want. CUMIPMT and CUMPRINC add them up over a range of periods.
How do I work out the total cost of a loan?
Microsoft's tip is to multiply the PMT result by nper, the number of payments. Subtract the amount borrowed to see the total interest.
