To find how many EMIs are left, get one number from the lender's loan statement or repayment schedule, one from counting the debits in your own bank statement, and a third from the NPER formula in a spreadsheet. If the three roughly agree, you are fine. If they do not, the gap is telling you something: a prepayment, a rate change or a missed instalment.
Which source is the most reliable?
The lender's current repayment schedule or loan statement is the authority, since it knows the outstanding principal and the revised tenure. Ask for it in the lender's app or on the loan page, or in writing. Your own count and the formula are checks, not replacements.
How do you count from your own statement?
- Note the sanctioned tenure in months from the loan document, say 60.
- Filter your bank statement for the loan debits and count the ones that cleared.
- Subtract the cleared count from the tenure.
- Adjust for any prepayment or restructuring that changed the tenure; the lender's letter says by how much.
Counting only works if each debit is one instalment. If you paid extra or a debit bounced and was re-presented, read the narration of each line.
How does the NPER formula work?
NPER returns the number of payments needed to clear a balance at a given rate. In Excel and Google Sheets the syntax is NPER(rate, payment, present value). With a monthly EMI, the rate is the annual rate divided by twelve and the payment is entered as a negative number.
Say the outstanding principal is ₹3,00,000, the annual rate is 12 per cent and the EMI is ₹10,000. In a cell, type =NPER(12%/12, -10000, 300000). The answer is about 35.8. That means 35 full instalments and a smaller 36th, or 36 months to go. You can check it by hand: monthly interest on ₹3,00,000 is ₹3,000, so the first ₹10,000 pays ₹7,000 of principal; the balance falls faster each month.
| Input | Value in the example | Cell formula part |
|---|---|---|
| Annual rate | 12% | 12%/12 |
| EMI | ₹10,000 | -10000 |
| Outstanding principal | ₹3,00,000 | 300000 |
| Result | About 35.8 months | =NPER(12%/12,-10000,300000) |
Why might the numbers disagree?
- Floating rate: the lender may have changed the rate, and either the EMI or the tenure moved.
- Prepayment: a lump sum reduced the principal, so the remaining tenure is shorter than the count suggests.
- Moratorium or pre-EMI period: some loans do not follow a simple month-by-month count.
- Outstanding includes charges: the figure on the loan statement may not be pure principal.
- A missed or late instalment: the count of cleared debits will be lower than the months elapsed.
Can a tracker help count them?
If your loan debits are in your statements, a tracker can show every instalment as a row. In SpendixAI, importing the statement of the account that pays the EMI and filtering to that merchant gives you the list to count. It does not know your loan terms, so the lender's schedule still decides the number left.
What if the lender will not give a clear number?
Write to the lender and ask for three things on one page: the outstanding principal today, the number of instalments remaining, and the date of the last instalment. Ask for the repayment schedule as an attachment. Keep the date you asked and the date they answered. A written reply is far easier to rely on than a phone call, and if your own count differs from theirs, the schedule shows which instalment each of you is counting differently.
What to write down
- Date of your check and the source of the outstanding figure.
- EMIs left according to the lender, and your two cross-checks.
- Expected last instalment date.
- Any difference and the explanation you got.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

