Excel PMT Function: Calculate a Loan or Mortgage Payment
Works out the fixed periodic payment on a loan, given the amount, rate and number of periods.
PMT calculates the repayment on a loan where every payment is the same size. Give it the interest rate per period, how many periods there are, and how much was borrowed, and it returns what you pay each period. It is the formula behind every mortgage calculator on the internet.
Two things about it catch everyone, and both are conventions rather than bugs. The first is the sign: PMT returns a negative number, because Excel's finance functions treat money leaving you as negative. It is not wrong; wrap it in ABS or negate the loan amount if you want a positive figure to display.
The second is that all three arguments must agree on the period. A 5.9% annual rate over five years, paid monthly, is a rate of 0.059/12 and a term of 5*12 — not 0.059 and 5. Getting this wrong produces an answer that looks plausible and is wildly out, which is much more dangerous than an error message.
Syntax
=PMT(rate, nper, pv, [fv], [type])Arguments
rateRequired- The interest rate per period. Divide an annual rate by 12 for monthly payments — this is the argument people get wrong.
nperRequired- The total number of payments. Multiply years by 12 for monthly, to match the rate.
pvRequired- The present value: how much was borrowed. Enter it positive and the payment comes back negative.
fvOptional- The balance you want left at the end. Defaults to 0, which is a loan paid off in full. Use it for a balloon payment.
typeOptional- 0 if payments are at the end of each period (the default), 1 if at the start. Rent is usually 1; loans are usually 0.
The example data
Headers in row 1, data in A2:D4. Rates are annual; terms are in years.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan | Amount | Annual rate | Years |
| 2 | Van finance | 24000 | 0.059 | 5 |
| 3 | Fit-out loan | 85000 | 0.072 | 10 |
| 4 | Equipment | 12500 | 0.045 | 3 |
Worked examples
=PMT(C2/12, D2*12, B2)Result: -462.66
The monthly payment on the van. Rate divided by 12 and years multiplied by 12, so both are in months.
=-PMT(C2/12, D2*12, B2)Result: 462.66
The same figure as a positive number, for a sheet someone else will read. The leading minus is the usual way of doing it.
=PMT(C2, D2, B2)Result: -5688.71
The mistake to watch for: annual rate with a term in years gives the *annual* payment, which is a very different answer from a monthly one.
=ROUND(-PMT(C3/12, D3*12, B3), 2) * D3 * 12 - B3Result: 34428.00
Total interest over the life of the fit-out loan: everything paid, minus what was borrowed.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
The result is negative
Why it happens: By design. Excel's finance functions treat outgoing money as negative and incoming as positive.
How to fix it: Put a minus in front of the function, or enter pv as a negative number. Do not do both.
Payment is about twelve times too big
Why it happens: The annual rate was used with a monthly term, or vice versa.
How to fix it: rate and nper must describe the same period. Monthly means rate/12 and years*12.
#NUM!
Why it happens: The arguments describe something impossible, such as nper of zero.
How to fix it: Check the term is a positive number of periods.
#VALUE!
Why it happens: One of the arguments is text — often a rate typed as "5.9%" into a text-formatted cell.
How to fix it: Rates must be numbers. 5.9% can be entered as 0.059 or as 5.9% in a percentage-formatted cell, but not as text.
Tips worth knowing
- Keep rate, term and amount in their own cells and reference them. A PMT with the numbers typed inline cannot be checked by anyone.
- IPMT and PPMT split a single payment into its interest and principal parts; together they always equal PMT.
- For a full amortisation schedule, use PMT for the constant payment and IPMT/PPMT per row.
- CUMIPMT totals the interest across a range of periods without building the schedule row by row.
Frequently asked questions
Why does PMT return a negative number?
Excel's financial functions use a cash-flow convention: money you pay out is negative and money you receive is positive. Since you borrowed a positive amount, the repayments are negative. Put a minus sign in front of the whole function to display it positively.
How do I calculate a monthly payment from an annual rate?
Divide the rate and multiply the term: =PMT(annual_rate/12, years*12, amount). Both arguments have to be in the same unit, and mixing them is the most common error with this function because the answer still looks like a number.
What is the difference between PMT, IPMT and PPMT?
PMT is the whole payment, which stays constant. IPMT is the interest portion of a particular payment and PPMT is the principal portion. Early on most of the payment is interest; later most is principal. IPMT plus PPMT always equals PMT for the same period.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.