PMT calculates the regular payment on a loan with a fixed rate and a fixed term. It is the function behind an EMI calculator.
Syntax
=PMT(rate, nper, pv, [fv], [type])
- rate: interest rate per period.
- nper: total number of payments.
- pv: the loan amount (present value).
Worked example
A loan of 500,000 at 9% a year for 5 years, paid monthly:
=PMT(9%/12, 5*12, 500000)
The rate is divided by 12 because payments are monthly, and the term is multiplied by 12 for the number of months.
Why is the answer negative?
Excel treats money paid out as negative. To show a positive EMI, put a minus sign in front of the loan: =PMT(9%/12, 60, -500000).
Total interest paid
=PMT(9%/12, 60, -500000)*60 - 500000
Related functions
IPMT: the interest part of a payment.PPMT: the principal part of a payment.NPER: how many payments you need.
Worked example
Loan of 500000 at 9% per year for 5 years, paid monthly. =PMT(9%/12,5*12,-500000) returns about 10379.18 per month. The minus on the loan makes the payment positive.
Common mistakes
- Using the yearly rate with monthly periods. Divide by 12.
- Forgetting to multiply years by 12.
- Sign confusion. Loan negative gives a positive payment.
Frequently asked questions
How do I get total interest?
=PMT(...)*nper-loan, with signs handled.
What are the arguments?
rate, nper, pv, then optional fv and type.
Can I use it for a savings goal?
Yes, with fv as the target and pv as 0.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: PMT, NPV, RATE.
Start Excel practice → Case studies
