Money and tax
How to Calculate EMI Manually and in Excel, with Worked Examples
By the AllToolsDesk team · Updated
An EMI (equated monthly instalment) is the fixed amount you pay every month until a loan is repaid. Banks work it out with one formula, and you can too, with a phone calculator or a spreadsheet. Checking it yourself is the best way to compare offers and to catch a quote that does not add up.
This guide walks through the formula with three typical Indian loans, shows how each EMI is split between interest and principal, and gives the Excel and Google Sheets formulas that do it in one cell.
The EMI formula
EMI = P × r × (1 + r)^n ÷ ((1 + r)^n − 1)
- P is the loan amount (the principal).
- r is the monthly interest rate: the yearly rate ÷ 12 ÷ 100. For 9% a year, r = 0.0075.
- n is the number of monthly instalments: years × 12.
The only awkward part is (1 + r)^n, a number raised to the power of the number of months. On a phone calculator, turn it sideways for the scientific keys and use the x^y button.
Worked examples
| Step | Car loan | Personal loan | Home loan |
|---|---|---|---|
| Loan amount P | ₹8,00,000 | ₹5,00,000 | ₹30,00,000 |
| Yearly rate | 9% | 12% | 8.5% |
| Tenure | 5 years | 3 years | 20 years |
| Monthly rate r | 0.0075 | 0.01 | 0.007083 |
| Instalments n | 60 | 36 | 240 |
| (1 + r)^n | 1.565681 | 1.430769 | 5.441243 |
| EMI | ₹16,606.68 | ₹16,607.15 | ₹26,034.70 |
For the car loan: 8,00,000 × 0.0075 = 6,000. Multiply by 1.565681 to get 9,394.09, then divide by 0.565681 (that is, 1.565681 − 1): the EMI is ₹16,606.68. Keep at least six decimal places in (1 + r)^n, or the result can be off by a few rupees.
Over the full term, the car loan costs ₹1,96,401.05 in interest on top of the ₹8 lakh borrowed, and the home loan ₹32,48,327.28 in interest on ₹30 lakh: more than the loan itself, because the term is long.
How each EMI splits into interest and principal
Every month the bank first charges interest on the balance still owed: balance × r. The rest of the EMI repays principal. In month 1 of the car loan, interest is 8,00,000 × 0.0075 = ₹6,000, so ₹10,606.68 goes to principal and the balance falls to ₹7,89,393.32. In month 2, interest is charged on that lower balance, so a little more goes to principal, and so on.
That is why early EMIs are mostly interest. In the first year of the car loan, ₹66,616.19 of the ₹1,99,280.16 paid is interest; by the last year almost all of each EMI is principal. It is also why prepaying early saves the most: every rupee prepaid stops attracting interest for all the remaining months.
EMI in Excel or Google Sheets
- EMI: =PMT(9%/12, 60, -800000) gives 16,606.68. The minus sign makes the answer positive.
- Interest in month k: =IPMT(9%/12, k, 60, -800000). For k = 1 it gives 6,000.
- Principal in month k: =PPMT(9%/12, k, 60, -800000).
- Total interest: =PMT(9%/12, 60, -800000) * 60 - 800000.
Bank figures can differ by a few rupees from the formula, because some lenders count interest daily or round each EMI up to the next rupee. The formula is still the right way to compare two offers.