Finance function
Excel PMT function
PMT works out the regular payment for a loan with a fixed interest rate and a fixed number of payments. Use it to compare car loans, mortgages, or equipment financing before you sign.
=PMT(rate, nper, pv, [fv], [type])Arguments
rate- The interest rate per period. Divide an annual rate by 12 for monthly payments.
nper- The total number of payments, like years times 12.
pv- The loan amount, or present value.
fv- Optional. A balance left after the last payment. Usually 0.
type- Optional. 0 for payments at the end of each period (default), 1 for the start.
PMT examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan | Amount | Annual rate | Years |
| 2 | Car | 25000 | 0.06 | 5 |
| 3 | Mortgage | 300000 | 0.065 | 30 |
| 4 | Equipment | 18000 | 0.08 | 3 |
Result: -483.32
The car loan costs 483.32 a month. It is negative because the money leaves you.
Result: 1896.2
Make the loan amount negative to get a positive payment: 1,896.20 a month.
Result: 2305.96
Total interest on the equipment loan: all payments minus the amount borrowed.
How to use PMT
- Click the cell for the payment.
- Type =PMT( and the annual rate divided by 12.
- Add the number of payments, years times 12.
- Add the loan amount. Put a minus in front if you want a positive result.
- Press Enter.
Common PMT mistakes
Using the annual rate for monthly payments
Divide the rate by 12 and multiply the years by 12 so both are in months.
Being confused by the negative result
PMT shows money paid out as negative. Use -PMT(...) or a negative loan amount to flip the sign.
Typing 6 for 6%
Excel reads 6 as 600%. Type 6% or 0.06.
PMT FAQ
How do I calculate a monthly loan payment in Excel?
Use =PMT(annual_rate/12, years*12, -loan_amount). For example, =PMT(6%/12,60,-25000) gives about 483.32.
Why is my PMT result negative?
Excel treats money you pay out as negative. Put a minus before PMT or before the loan amount to show it as positive.
Does PMT include taxes and insurance?
No. PMT covers principal and interest only. Add property tax, insurance, and fees separately.
Practice PMT in these lessons
Guides that use PMT
Related functions
Updated