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.
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
D1
=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.
B1=PMT(0.04,10,-100000)
| A | B | |
|---|---|---|
| 1 | 12329.09443 |
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 get the monthly payment on an annual rate?
Divide the annual rate by 12 and multiply the number of years by 12, so rate and number of payments are both in months.