The PMT function
Returns the payment per period on a loan with a fixed interest rate, paid off in equal payments. The result is negative because it is money going out.
How to use PMT
PMT works out what you pay each time on a loan where every payment is the same size. Picture a car loan or a mortgage: you borrow a lump sum, and the bank wants it back in equal installments with interest on top. PMT gives you the size of one installment.
It needs three things, and they must all speak the same language of time: the interest rate per period, the number of payments, and the amount borrowed. A loan of 500,000 at 6% a year over 5 years, paid monthly, becomes =PMT(B2/12,B3*12,-B1): the yearly rate divided by 12 and the years multiplied by 12.
Money going out counts as negative. Writing the loan amount as -B1 therefore gives a positive payment, while without the minus the payment comes out negative. The last two arguments are optional: fv is a balance still left after the final payment, and type 1 means you pay at the start of each period instead of the end. To find out how long a loan runs, use NPER. For what the loan is worth today, use PV.
Syntax
PMT(rate, nper, pv, [fv], [type])- rate
- The interest rate per period. For monthly payments on an annual rate, divide it by 12.
- nper
- The number of payments. For monthly payments over 5 years, that is 5*12.
- pv
- The amount borrowed today.
- fv (optional)
- What is left when the last payment is made. Leave it out for 0, a loan paid off in full.
- type (optional)
- 0 or left out if payments are due at the end of each period, 1 if at the start.
Examples
=PMT(1B2/12,2B3*12,3-B1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Amount borrowed | 500000 | 9666.400765 | |
| 2 | Annual interest rate | 0.06 | ||
| 3 | Years | 5 |
In plain words: The monthly payment on 3the amount in B1 over 2the years in B3, counted in months at 1the annual rate in B2, split into months.
| A | B | |
|---|---|---|
| 1 | 12329.09443 |
Common PMT mistakes
The payment is far too high or too low
The rate and the number of payments are in different units. For monthly payments, divide the yearly rate by 12 and multiply the years by 12:
=PMT(B2/12,B3*12,-B1).I typed the rate as 6 and got a huge payment
A rate of 6 means 600%. Type 6% or 0.06.
My bank's payment is higher than PMT
PMT only covers interest and principal at a fixed rate. Fees, insurance and similar costs come on top, and the bank may use a different payment schedule, such as quarterly. Make sure the rate and the number of payments match the bank's schedule.
PMT returns #VALUE!
One of the arguments is text. This happens when you type a unit into the cell, like 500,000 USD. Enter only the number and use a currency format to show the unit.
Practice PMT
Questions
Why does PMT give a negative number?
Excel treats money you receive as positive and money you pay as negative. Enter the loan amount as a negative number, like -B1, or put a minus in front of PMT to get a positive payment.
How do I calculate the total interest on a loan?
Multiply the payment by the number of payments and subtract the amount borrowed, as in =PMT(B2/12,B3*12,-B1)*B3*12-B1.
Can I use PMT to plan savings?
Yes. Set pv to 0 and fv to your goal, as in =PMT(0.04/12,10*12,0,50000). The result is negative, the amount you set aside each month to reach 50,000 in 10 years at 4%.