The PMT function in Excel
PMT works out the regular payment on a loan from the interest rate, the number of payments and the amount borrowed.
What PMT does
The rate and the number of payments must use the same period. For monthly payments on a loan with an annual rate, divide the rate by 12 and multiply the years by 12. Enter the loan as a negative number if you want the payment to come out positive. An optional fourth argument holds the value left at the end, which is useful for leases.
Syntax
=PMT(rate, nper, pv, [fv], [type])
- rate
- The interest rate for each payment period, such as the annual rate divided by 12.
- nper
- The total number of payments.
- pv
- The amount borrowed today. Use a minus sign to get a positive payment.
- fv
- Optional. The value still left at the end, such as a lease's final value.
- type
- Optional. 0 for payments at the end of each period, 1 for the start.
Worked example
| A | B | |
|---|---|---|
| 1 | Loan amount | 12000 |
| 2 | Annual rate | 0.06 |
| 3 | Years | 3 |
| 4 | Monthly payment | 365.06 |
The 6% annual rate becomes 0.5% a month and three years become 36 payments. ROUND tidies the answer to pence.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Work out loan, mortgage or lease payments before you commit.
Sales & Ops
Show customers what a finance deal costs each month.
Common mistakes
- Using the annual rate with monthly payments. Divide the rate by 12 so it matches the number of payments.
- Forgetting the minus sign on the loan, which makes the payment negative.
- Mixing years and months. If you divide the rate by 12, nper must be in months.
The PMT course
- 1Monthly loan paymentFree practice belowPractise
- 2Weekly or quarterly paymentsMembers lesson
- 3Total interest paidMembers lesson
- 4Lease with a final valueMembers lesson
- 5Payment for every loanMembers lesson
Common questions
How do I work out the total interest?
Multiply the payment by the number of payments and subtract the amount borrowed.
Does PMT include fees?
No. It only covers the interest rate and the amounts you give it.
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.