Personal Finance

Loan EMI Formula Explained: Calculate Monthly Instalments by Hand and in Excel

How a loan EMI is calculated with the reducing-balance formula, a worked example in rupees, the Excel PMT function and what the total interest really costs.

An EMI (equated monthly instalment) is the fixed amount you pay every month to repay a loan. Each payment covers some interest and some of the original amount. Early on, most of the payment is interest. Later, most of it reduces the loan.

The formula

EMI = P × r × (1 + r)n ÷ ((1 + r)n − 1)

  • P = the loan amount (principal).
  • r = the monthly interest rate. Divide the yearly rate by 12 and by 100. For 15% a year, r = 0.0125.
  • n = the number of monthly payments.

A worked example

You borrow Rs 1,000,000 at 15% a year for 36 months. Here r = 0.0125 and n = 36.

StepResult
(1 + r)n = 1.012536about 1.5639
EMI = 1,000,000 × 0.0125 × 1.5639 ÷ 0.5639about Rs 34,665
Total paid = 34,665 × 36about Rs 1,247,952
Total interestabout Rs 247,952

In month one, interest is 1,000,000 × 0.0125 = Rs 12,500, so about Rs 22,165 of the first payment reduces the loan. Interest falls every month as the balance falls.

Calculate it in Excel or Google Sheets

Use the PMT function:

=PMT(15%/12, 36, 1000000)

It returns a negative number (about −34,665) because it is a payment going out. Put a minus sign in front to show it as positive: =-PMT(15%/12,36,1000000). Total interest is =-PMT(15%/12,36,1000000)*36-1000000.

Why a flat rate looks cheaper but is not

Some lenders quote a flat rate, where interest is charged on the full original amount for the whole term. At a flat 15% on the same Rs 1,000,000 for three years, interest is 1,000,000 × 15% × 3 = Rs 450,000, and the instalment is about Rs 40,278. That is far more than the Rs 247,952 on a 15% reducing-balance loan. Always ask which method a quote uses. Our guide on flat rate and reducing balance shows how to compare offers.

What lowers the cost of a loan

  • A shorter term, because you pay interest for fewer months.
  • A larger down payment, so you borrow less.
  • A lower rate, so compare offers from more than one lender.
  • Extra payments, if your lender allows early repayment without a penalty. Ask about fees first.

Check the full cost

Add processing fees, insurance and any other charges to the interest when you compare. Put your own numbers into the free Loan EMI Calculator to see the instalment and total interest instantly.

Lenders may round figures or calculate days slightly differently, so your bank’s schedule can differ by a small amount.

Keep reading