EMI Formula in Excel: PMT Function + Free Calculator

To calculate EMI in Excel, use the PMT function: =PMT(annual rate/12, number of months, -loan amount). For a ₹25,00,000 loan at 9% a year for 15 years, type =PMT(9%/12, 180, -2500000) and Excel returns 25,356.66, the monthly EMI. The same formula works in Google Sheets and LibreOffice. To check your answer, enter the same loan in the free EMI calculator; it should match to the paisa.

The PMT formula, piece by piece

=PMT(rate, nper, pv)

• rate is the interest rate per month: the yearly rate divided by 12. For 9% a year, use 9%/12 or 0.09/12.
• nper is the number of monthly instalments: years × 12. For 15 years, use 180.
• pv is the loan amount (present value). Put a minus sign in front, -2500000, so the EMI comes out as a positive number. Without it, Excel shows the EMI as a negative, because it treats it as money going out.

PMT also takes two optional arguments, fv (a balance left at the end, normally 0) and type (0 if you pay at the end of each month, which is standard for loans). Leave both out for a normal EMI.

This is exactly the EMI formula, EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1), in one function. For the maths behind it, see how to calculate EMI.

Set up the EMI calculation formula in Excel

Put the inputs in cells so you can change them later:

Cell	Label	Value
A1 / B1	Loan amount	2500000
A2 / B2	Interest rate (yearly)	9%
A3 / B3	Tenure (years)	15
A4 / B4	EMI	=PMT(B2/12, B3*12, -B1)
A5 / B5	Total paid	=B4*B3*12
A6 / B6	Total interest	=B5-B1

With these numbers:

• EMI: ₹25,356.66
• Total paid over 180 months: about ₹45,64,200
• Total interest: about ₹20,64,200

Format B2 as a percentage, or type 0.09. If you type 9 without the % sign, Excel treats it as 900% and the EMI comes out absurdly large.

Interest and principal for one month

Two related functions split any single EMI:

• =IPMT(9%/12, 1, 180, -2500000) gives the interest in month 1: ₹18,750.00.
• =PPMT(9%/12, 1, 180, -2500000) gives the principal in month 1: ₹6,606.66.

They add up to the EMI. Change the second argument to see any other month. In month 2 the interest falls to ₹18,700.45 and the principal rises to ₹6,656.21, because the balance is a little lower.

For a whole year's interest, use CUMIPMT. Note that it takes a positive loan amount and returns a negative result:

=CUMIPMT(9%/12, 180, 2500000, 1, 12, 0) returns −2,21,646.55: the interest you pay in the first 12 months.

Build a month-by-month schedule

If you want to see every row, make five columns:

Column	Heading	Formula for row 2 (month 1)
A	Month	1
B	Opening balance	=$B$1 from the inputs (later rows: previous row's closing balance)
C	Interest	=B2*$B$2/12
D	Principal	=$B$4-C2
E	Closing balance	=B2-D2

Fill down to month 180. The closing balance should reach zero (or a few paisa, from rounding) in the last row. At the end of year 1, the balance is about ₹24,17,366.57: you've paid ₹3,04,280 in EMIs, but only ₹82,633 of that reduced the loan.

Check against the calculator

Open the free EMI calculator and enter:

1. Loan amount: 25,00,000
2. Interest rate (yearly): 9
3. Loan term: 15, with Years selected

It shows Monthly EMI ₹25,356.66 and the total interest, and the Repayment schedule lists each year's principal, interest and balance. Open year 1 to see month by month: month 1 shows ₹6,606.66 principal and ₹18,750.00 interest, the same as PPMT and IPMT. If your sheet doesn't match, one of the mistakes below is the usual cause.

Common mistakes

Mistake	What you typed	What you get
Yearly rate not divided by 12	=PMT(9%, 180, -2500000)	₹2,25,000.04 (wildly wrong)
Tenure in years, not months	=PMT(9%/12, 15, -2500000)	₹1,76,840.98 (wrong)
Rate typed as 9, not 9%	=PMT(9/12, 180, -2500000)	Nonsense
No minus sign on the loan	=PMT(9%/12, 180, 2500000)	−25,356.66 (right amount, negative)

Other questions Excel can answer

• How long to repay with a bigger EMI? =NPER(9%/12, -30000, 2500000) returns about 131.3 months. Paying ₹30,000 a month instead of ₹25,357 clears the loan in about 11 years instead of 15 (the last payment is smaller). The home loan prepayment guide shows how prepaying changes the tenure.
• What rate am I really paying? If you know the EMI, months and the amount you actually received after fees, =RATE(months, -EMI, amount received)*12 gives the yearly rate. The personal loan EMI guide uses this to show how processing fees raise the true cost.
• Different currencies: PMT doesn't care about currency. The EMI calculator works in rupees; for dollars, euros or pounds, the free loan calculator uses the same maths.

Why your bank's EMI may differ slightly

Banks sometimes round the EMI up to the next rupee, charge interest daily rather than monthly, or start with a broken-period interest charge if the loan is disbursed mid-month. The difference is usually small. Your sanction letter or loan agreement has the exact EMI. Both the spreadsheet and the calculator give estimates, not financial advice.

Check your numbers

Whatever you build in Excel, a quick cross-check in the free EMI calculator catches a missing /12 or a tenure in years. It's free and your numbers stay on your device.