EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1), where P is the loan, r the monthly rate and n the number of months. For ₹1,00,000 at 12% a year for 12 months it gives ₹8,884.88, which the bank rounds to ₹8,885. Below, the same sum by hand, in Excel and on a phone.
On this page11
What is the EMI formula?
EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1)
- P is the loan amount (the principal), in rupees.
- r is the monthly interest rate as a decimal: yearly rate ÷ 12 ÷ 100. At 12% a year, r = 12 ÷ 12 ÷ 100 = 0.01.
- n is the number of monthly instalments. A 2-year loan has n = 24.
The yearly rate here is the bank's reducing-balance rate, the one on your loan offer. If someone quotes a flat rate, convert it first: see flat vs reducing interest rate.
EMI calculation example: ₹1,00,000 at 12% for 12 months
- Monthly rate. r = 12 ÷ 12 ÷ 100 = 0.01.
- Power. (1 + r)^n = 1.01^12. By hand, square again and again: 1.01² = 1.0201, 1.01⁴ = 1.040604, 1.01⁸ = 1.082857. Then 1.01¹² = 1.01⁸ × 1.01⁴ = 1.126825.
- Top of the formula. P × r × (1 + r)^n = ₹1,00,000 × 0.01 × 1.126825 = 1,126.825.
- Bottom of the formula. (1 + r)^n − 1 = 1.126825 − 1 = 0.126825.
- Divide. EMI = 1,126.825 ÷ 0.126825 = ₹8,884.88.
- Round. The bank charges a whole-rupee EMI: ₹8,885.
Over the 12 months you repay ₹1,06,618.46 in all, of which ₹6,618.46 is interest. Why that is not exactly 12 × ₹8,885 is explained further down.
Try it in the calculatorOpen this loan in our EMI calculator, then change the amount, rate or months.How to calculate EMI on a mobile calculator
Any phone calculator with an xʸ (or ^) key will do. If you can't see it, turn the phone sideways or open the scientific view. Here is ₹1,00,000 at 12% for 12 months, key by key:
- Work out 1 + r in your head: 12 ÷ 12 ÷ 100 = 0.01, so 1 + r = 1.01.
- Type 1.01, press xʸ, type 12, press =. You see 1.126825. Note it down.
- Press × 0.01 × 100000 =. You see 1,126.825: the top of the formula. Note it down.
- Type 1.126825 − 1 =. You see 0.126825: the bottom.
- Type 1126.825 ÷ 0.126825 =. You see 8,884.88: the EMI, ₹8,884.88.
No xʸ key? Multiply 1.01 by itself: 1.01 × 1.01 × 1.01 and so on, with 1.01 typed 12 times, then carry on from step 3.
A second example: ₹5,00,000 at 10.5% for 5 years
- r = 10.5 ÷ 12 ÷ 100 = 0.00875, and n = 5 × 12 = 60.
- (1 + r)^n = 1.00875^60 = 1.686603. By hand this takes many steps; the xʸ key on a phone calculator does it at once.
- Top: ₹5,00,000 × 0.00875 × 1.686603 = 7,378.888.
- Bottom: 1.686603 − 1 = 0.686603.
- EMI = 7,378.888 ÷ 0.686603 = ₹10,746.95, charged as ₹10,747.
Over 60 months the interest adds up to ₹1,44,816.17. What that means as simple interest is worked out in EMI vs simple interest.
How each EMI splits into interest and principal
Every EMI does two jobs. First it pays this month's interest on what you owe; the rest reduces the loan.
- Interest = balance at the start of the month × r
- Principal repaid = EMI − interest
- New balance = old balance − principal repaid
| Month | Balance at start | EMI | Interest | Principal repaid | Balance at end |
|---|---|---|---|---|---|
| 1 | ₹1,00,000 | ₹8,885 | ₹1,000 | ₹7,885 | ₹92,115 |
| 2 | ₹92,115 | ₹8,885 | ₹921.15 | ₹7,963.85 | ₹84,151.15 |
| 3 | ₹84,151.15 | ₹8,885 | ₹841.51 | ₹8,043.49 | ₹76,107.66 |
Month 1: ₹1,00,000 × 0.01 = ₹1,000 interest, so ₹7,885 of the ₹8,885 goes to the loan, leaving ₹92,115. Month 2: ₹92,115 × 0.01 = ₹921.15, and ₹7,963.85 reduces the loan. Month 3: ₹84,151.15 × 0.01 = ₹841.51. Each month the interest falls, and the principal part rises by exactly the same amount, because the EMI stays fixed.
Why interest is high at the start and then falls
Interest is charged on what you still owe, and you owe the most at the start. As each EMI chips away at the balance, the interest part shrinks and more of the same EMI goes to the loan. In the last month of this loan the interest is only ₹87.96.
The longer the loan, the more the early EMIs lean towards interest. Here is the same ₹1,00,000 at 12% over longer terms:
| Term | EMI | Interest in the first EMI | Share of first EMI |
|---|---|---|---|
| 12 months | ₹8,885 | ₹1,000 | 11% |
| 36 months | ₹3,321 | ₹1,000 | 30% |
| 60 months | ₹2,224 | ₹1,000 | 45% |
This is also why paying off part of a loan early saves more interest than paying the same amount near the end: the money you return early stops earning interest for every month that is left.
How banks round the EMI, and why the last instalment is different
The formula gives ₹8,884.88, but banks usually round the EMI to a whole rupee: here ₹8,885, which is ₹0.12 more than the exact figure. Those few paise a month clear the loan slightly early, so the 12th instalment only needs to be ₹8,883.46: the ₹8,795.50 still owed plus ₹87.96 interest.
That is why the total interest is ₹6,618.46 and not 12 × ₹8,885 − ₹1,00,000 = ₹6,620. If an EMI is rounded down instead, the last instalment comes out a little larger.
Some lenders also count interest by the actual days in each month, or charge interest for the days between the loan being paid out and the first EMI. So your bank's schedule can differ from this one by a few rupees. The method is the same.
Common mistakes when you work out EMI by hand
- Using the yearly rate for r. r is the monthly rate as a decimal: 12% a year is 0.01, not 12 or 0.12. Put in 0.12 by mistake and you get an EMI of ₹16,143.68.
- Counting n in years. n is the number of monthly instalments, so a 5-year loan has n = 60, not 5.
- Putting a flat rate into the formula. The formula needs the reducing rate. Convert a flat rate first.
- Rounding (1 + r)^n too early. Keep at least six decimals. Round 1.126825 to 1.13 and the EMI comes out as ₹8,692.31 instead of ₹8,884.88.
EMI in Excel and Google Sheets, cell by cell
Five functions do all of it: PMT for the EMI, IPMT and PPMT for the interest and principal in any month, RATE for the rate behind an EMI, and NPER for the number of EMIs. Type the loan into column B of a blank sheet:
| Cell | What it is | Type this | You see |
|---|---|---|---|
| B1 | Loan | 100000 | 1,00,000 |
| B2 | Yearly rate | 12% | 12% |
| B3 | Months | 12 | 12 |
| B4 | EMI | =PMT(B2/12, B3, -B1) | 8,884.88 |
| B5 | EMI the bank charges | =ROUND(B4, 0) | 8,885 |
| B6 | Rate from the EMI | =RATE(B3, -B5, B1)*12 | 12% |
| B7 | EMIs to repay | =NPER(B2/12, -B5, B1) | 12 |
The rate is divided by 12 because the EMI is monthly, and the loan has a minus sign so the EMI comes out positive. RATE gives a monthly rate, so it is multiplied by 12. Now the month-by-month table: put headings in row 10 (Month, Interest, Principal, Balance) and type row 11.
| Cell | Type this | You see |
|---|---|---|
| A11 | 1 | 1 |
| B11 | =IPMT($B$2/12, A11, $B$3, -$B$1) | 1,000.00 |
| C11 | =PPMT($B$2/12, A11, $B$3, -$B$1) | 7,884.88 |
| D11 | =$B$1-SUM($C$11:C11) | 92,115.12 |
In A12 type =A11+1, copy B11:D11 down to row 12, then copy row 12 down to row 22 for all 12 months. Month 2's interest comes out at ₹921.15. These use the exact EMI of ₹8,884.88, so they can differ from a bank's table, built on the rounded ₹8,885, by a few paise.
Find the interest rate from an EMI in Excel (RATE)
The formula turns a rate into an EMI. Going backwards, from an EMI to the rate, has no neat formula, because the rate sits inside (1 + r)^n on both the top and the bottom. You find it by trial: guess a rate, work out the EMI, and adjust. That is what RATE does.
In a spreadsheet, =RATE(12, -8885, 100000)*12 gives about 12%. RATE finds the monthly rate by trial, so multiply it by 12. Without a spreadsheet, use our EMI to interest rate calculator: it narrows the rate down the same way and also counts the processing fee. To see what the rate means in simple interest and sekda, read EMI vs simple interest.
Try it in the calculatorKnow only your EMI? Enter it to get the bank's rate, the flat rate and the sekda.How many EMIs are left? (NPER)
NPER answers "how many EMIs will it take?": give it the monthly rate, the EMI and what you still owe. It helps after a rate change, when a lender keeps the EMI and stretches the loan instead.
=NPER(yearly rate/12, -EMI, amount still owed)
8 short answersEMI formula and Excel: common questions
Open ▾Close ▴
What is the formula to calculate EMI?
EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1). P is the loan amount, r is the monthly rate (yearly rate ÷ 12 ÷ 100) and n is the number of monthly instalments. For ₹1,00,000 at 12% for 12 months it gives ₹8,884.88.
How do I calculate EMI in Excel or Google Sheets?
Use PMT: =PMT(yearly rate/12, months, -loan). For ₹5,00,000 at 10.5% for 5 years, =PMT(10.5%/12, 60, -500000) gives ₹10,746.95. The minus sign before the loan makes the answer positive. For the whole-rupee EMI a bank charges, use =ROUND(PMT(10.5%/12, 60, -500000), 0), which gives ₹10,747.
How do I find the interest and principal in each EMI in Excel?
Use IPMT for the interest and PPMT for the principal; the second argument is the month. For ₹1,00,000 at 12% over 12 months, =IPMT(12%/12, 1, 12, -100000) gives ₹1,000 and =PPMT(12%/12, 1, 12, -100000) gives ₹7,884.88. Change the month to 2 and the interest falls to ₹921.15, while the principal part rises by the same amount.
How do I find the interest rate from an EMI in Excel?
Use RATE and multiply by 12, because RATE returns the monthly rate: =RATE(months, -EMI, loan)*12. For ₹5,00,000 repaid at ₹10,747 a month for 60 months, =RATE(60, -10747, 500000)*12 gives about 10.5%. RATE finds the answer by trial, just as our EMI to interest rate calculator does.
How do I find the loan tenure from the EMI?
Use NPER: =NPER(yearly rate/12, -EMI, loan). It gives the number of monthly EMIs. ₹1,00,000 at 12% with an EMI of ₹8,885: =NPER(12%/12, -8885, 100000) gives 12, so 12 EMIs. On a loan already running, put in what you still owe to see how many EMIs are left.
How do I calculate EMI on a mobile calculator?
Use the xʸ key. For ₹1,00,000 at 12% for 12 months: 1.01 xʸ 12 = 1.126825. Multiply 1,00,000 × 0.01 × 1.126825 = 1,126.825. Divide that by 1.126825 − 1, which is 0.126825, to get ₹8,884.88. With no xʸ key, multiply 1.01 × 1.01 × 1.01 and so on, with 1.01 written 12 times.
What are r and n in the EMI formula?
r is the monthly interest rate as a decimal: the yearly rate ÷ 12 ÷ 100, so 12% a year is 0.01 and 10.5% is 0.00875. n is the number of monthly instalments, so a 5-year loan has n = 60. Putting in the yearly rate, or years for n, is the most common slip.
Why is my last EMI different from the others?
Because the EMI is rounded to a whole rupee. Rounded up, the loan clears a little early and the last instalment is smaller (₹8,883.46 in our example); rounded down, it is a little larger.