Debt

Finding how many EMIs are left on a loan

How many EMIs are left on a loan? Three ways to find out, from the lender statement, your own debit count and an Excel formula, on a 60-month example.

Editorial illustration for Finding how many EMIs are left on a loan

There are three reliable ways to find how many EMIs are left: read it from your lender's statement or app, count the debits in your own records, or calculate it with a formula from the outstanding balance. The lender's figure is the one that counts; the other two are checks. A part-prepayment is the usual reason the numbers disagree.

Way 1: what does the lender's statement say?

Ask for, or open, the loan account statement or the repayment schedule in the lender's app. Look for the outstanding principal, the number of instalments remaining and the date of the last EMI. If you cannot find these, ask the lender to confirm them. Rely on this figure when planning to close the loan.

Way 2: how do I count from my own records?

Filter your bank statement for the lender's name and count the EMI debits since the loan began. If you track in a sheet or a tracker, a monthly tick against each EMI is the same thing. Then subtract from the original tenure.

Worked example with invented numbers: a loan of ₹6,00,000 for 60 months, at 12% a year, has an EMI of about ₹13,347. You count 17 EMI debits. Without any prepayment, months left = 60 - 17 = 43.

Way 3: what formula gives remaining months?

If you know the outstanding balance, the interest rate and the current EMI, the number of months left comes from the NPER function, which works in Excel and Google Sheets. Put the annual rate in B1, the EMI in B2 and the outstanding principal from the lender in B3:

  • Months left: =NPER(B1/12, -B2, B3)
  • Rounded up to whole instalments: =ROUNDUP(NPER(B1/12, -B2, B3), 0)
  • Last EMI month: =EDATE(TODAY(), ROUNDUP(NPER(B1/12, -B2, B3), 0))

The EMI goes in with a minus sign because NPER treats money you pay as negative and the balance you owe as positive. The rate in B1 is a percentage, so write 12% as 0.12 or 12%.

What happens to the count after a part-prepayment?

Say that after the 17th EMI the outstanding balance is about ₹4,64,598 and you prepay ₹1,00,000, leaving ₹3,64,598. What changes depends on what the lender does with the prepayment, so ask which they apply.

Lender keepsEMI after prepaymentMonths leftWhy
EMI the sameAbout 13,347About 33 (32.07, so 33 with a smaller last EMI)Balance clears sooner
Tenure the sameAbout 10,47443Each EMI is smaller

Check against the formula: =NPER(0.01, -13347, 364598) gives about 32.07, which rounds up to 33. Without the prepayment the same formula on 4,64,598 gives about 43, matching the simple count of 60 - 17.

How do I keep the number current?

Update it every month on the same day you check your auto-debit, and again after any prepayment or rate change. If you use SpendixAI to categorise your EMI debits, the count of debits in your records is easy to read off a filtered list, though the lender's schedule remains the source for outstanding principal and exact tenure.

A related question is the date, not just the count. If your next EMI is due on the 5th of next month and 43 remain including it, the last one falls 42 months after that. In a sheet, =EDATE(DATE(2026,4,5), 42) returns the 5th of October 2029 for an invented first date of 5 April 2026. Counting inclusively, the first EMI is month one, so the last is the date plus months left minus one.

A short checklist

  1. Get the outstanding principal and remaining tenure from the lender.
  2. Count the EMI debits in your own records.
  3. Run the NPER formula as a third check.
  4. Note which of the three disagree, and ask the lender about it.
  5. After any prepayment, write down whether the EMI or the tenure changed.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports