How to use PMT in Excel
The payment for a loan with a fixed rate.
Syntax
rateInterest rate per period, like 6%/12nperNumber of paymentspvLoan amount (negative to get a positive payment)[fv]Balance left at the end optional[type]0 = pay at the end of each period optionalExample
Here’s PMT on a small table of orders. The highlighted cells are the ones the formula reads.
=ROUND(PMT(6%/12,60,-20000),2)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with PMT
Match the rate to the period
For monthly payments, divide the annual rate by 12 and multiply years by 12: PMT(6%/12, 5*12, …).
The sign is negative
PMT returns a negative number because it’s money going out. Put a minus before the loan amount (or the function) to show a positive payment.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=ROUND(PMT(6%/12,60,-20000),2
Parentheses don’t match. You have 2 opening and 1 closing parentheses. Every ( needs a ).
Questions
What does the PMT function do in Excel?
The payment for a loan with a fixed rate. The syntax is PMT(rate, nper, pv, [fv], [type]).
Why is my PMT formula giving the wrong answer?
Match the rate to the period: For monthly payments, divide the annual rate by 12 and multiply years by 12: PMT(6%/12, 5*12, …). The sign is negative: PMT returns a negative number because it’s money going out. Put a minus before the loan amount (or the function) to show a positive payment.
Does PMT work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.