PMT Function in Excel: Loan EMI in One Formula

📎 This article includes 1 downloadable practice file ↓

FinancialLevel: IntermediateAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Power combo
5. Common errors and fixes
6. PMT in practice
7. Where you will use it
8. Worked example
9. Mistakes people make
10. Related functions
11. Continue learning

PMT calculates the fixed periodic payment for a loan — the EMI on home, car and personal loans.

Syntax

=PMT(rate, nper, pv, [fv], [type])

Argument	What it means
rate	Interest per period — annual rate / 12 for monthly EMIs.
nper	Number of payments — years × 12.
pv	Loan amount (use a minus sign to get a positive EMI).

Examples

Example 1

=PMT(9%/12, 20*12, -5000000)

EMI on a ₹50 lakh loan at 9% for 20 years (about ₹44,986).

Power combo

=PMT(B1/12, B2*12, -B3)*B2*12-B3

Total interest paid over the loan.

Common errors and fixes

You see	Why, and the fix
Huge EMI	Annual rate used without /12 — the rate and the number of periods must use the same unit.

PMT in practice

Where you will use it

• Monthly EMI of a home or car loan
• Compare loan offers
• Budget for repayments

Worked example

Monthly EMI (illustration).

Loan	Rate p.a.	Years	Formula	Result
2500000	0.085	20	=TEXT(-PMT(B2/12,C2*12,A2),"#,##0")	21,696
800000	0.095	5	=TEXT(-PMT(B3/12,C3*12,A3),"#,##0")	16,801
100000	0.12	1	=TEXT(-PMT(B4/12,C4*12,A4),"#,##0")	8,885

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Annual rate in a monthly formula	Use rate/12 and years*12
Negative EMI	PMT returns cash out as negative; put a minus in front

Related functions

IPMT · PPMT · RATE · NPER

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Financial guides →

RECEIVED Function in Excel: Amount Received at MaturityFinancialLevel: ExpertAvailable in: Excel 2007+RECEIVED calculates what you get back at maturity for a…PDURATION Function in Excel: Years Needed to Reach a TargetPDURATION returns how many periods an investment needs to grow to a target at…INTRATE Function in Excel: Interest Rate of a Fully Invested SecurityINTRATE returns the annualised return when you invest an amount and receive a larger…NPER Function in Excel: How Many Payments Until Paid OffNPER returns the number of periods needed to repay a loan or reach a…

📎 Practice files for this article

• 📗
  Practice workbook: Financial: EMI, SIP, NPV, IRR, depreciation23 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.