How to calculate a loan payment in Excel
PMT works out the regular payment on a loan from the interest rate, the number of payments and the amount borrowed.
For monthly payments, divide the annual rate by 12 and multiply the years by 12. Put a minus before the loan amount so the payment shows as a positive number.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Loan | Annual rate | Years |
| 2 | 20,000 | 6% | 5 |
=PMT(B2/12,C2*12,-A2)Result: 386.66
Borrowing 20,000 at 6% over 5 years costs about 386.66 a month.
Step by step
- Type =PMT(B2/12, for the monthly rate.
- Add C2*12, for the number of monthly payments.
- Add -A2 for the loan amount, close the bracket and press Enter.
Other ways to write it
=-PMT(B2/12,C2*12,A2)The same result, flipping the sign outside the function instead.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Using the annual rate
- PMT(B2,C2*12,-A2) charges a year’s interest every month, which gives a payment far too high.
- Using years as payments
- With C2 instead of C2*12, PMT thinks there are only 5 payments.
Reading isn’t the same as doing
Loan payments gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.