Spreadsheets

Check your statement for missed or double EMI debits with a sheet

Check a bank statement for missed or double EMI debits: list expected dates and amounts, match them with COUNTIFS in a date window, and flag odd months.

Editorial illustration for Check your statement for missed or double EMI debits with a sheet

To check EMI debits in a bank statement, build a two-column schedule of the dates and amounts your loan should debit, then count how many statement debits of that amount fall near each date. A count of 1 is normal, 0 is a missed debit and 2 is a possible double debit. The whole check needs one COUNTIFS formula and about ten minutes.

What do you need before you start?

Two tabs. The first, Schedule, holds one row per month: the due date in column A and the EMI amount in column B, copied from your loan's repayment schedule or sanction letter. The second, Stmt, holds the statement rows with date in column A, narration in column B and debit amount in column C. Make sure the dates on both tabs are real dates and the amounts are numbers, not text.

Which formula counts the debits near each due date?

Debits rarely land on the exact due date, so use a window of three days either side. In Schedule!C2, filled down:

=COUNTIFS(Stmt!$A$2:$A$500, ">="&A2-3, Stmt!$A$2:$A$500, "<="&A2+3, Stmt!$C$2:$C$500, B2)

Each criterion pair is a rule that all must satisfy: date on or after three days before, date on or before three days after, and amount equal to the EMI. Add a status in D2:

=IF(C2=1, "OK", IF(C2=0, "Missing", "Double"))

To see the money rather than the count, add the amount actually debited in the window in column E with =SUMIFS(Stmt!$C$2:$C$500, Stmt!$A$2:$A$500, ">="&A2-3, Stmt!$A$2:$A$500, "<="&A2+3, Stmt!$C$2:$C$500, B2) and the difference in column F as =E2-B2.

What does a made-up four-month check look like?

Say the EMI is ₹12,450 and falls due on the 5th.

MonthExpectedDebits foundDebitedDifferenceStatus
August12,450112,4500OK
September12,45000-12,450Missing
October12,450224,90012,450Double
November12,450112,4500OK

September shows nothing in the window, and October shows the amount twice, one on the 5th and one on the 6th. Maybe the September debit slipped to the 8th, outside your window; widen it to a week before you decide it is missing.

Can you narrow it down with the narration?

Yes, if the lender's name appears consistently in the narration. Put that text in a cell, say H1, and add one more condition at the end of the COUNTIFS:

..., Stmt!$B$2:$B$500, "*"&$H$1&"*")

The asterisks make it a "contains" test. Copy the word from your own statement rather than guessing, since narrations vary by bank and change over time. This stops a ₹12,450 purchase at a shop from being mistaken for your EMI.

What should you do when a month shows Missing or Double?

  • Missing: check the next ten days and the previous statement page first. Then compare with the lender's repayment schedule, because the due date may have moved.
  • Double: look at both narrations. Two identical entries may be one debit and one retry, or two different loans with the same amount.
  • In either case, keep the statement lines and the schedule together before you contact the lender, so you can point to exact dates and amounts.
  • Do not assume a refund or a penalty. Ask what happened and let the lender confirm in writing.

Make it a habit

Run the check once a quarter, when you download statements anyway. If you track several loans, give each its own row set and its own narration keyword. SpendixAI can import a statement in PDF, CSV or Excel and lets you review each row before saving, and its "Possible duplicate charges" report lists the same amount to the same merchant within two days, a useful second opinion on double debits.

SPENDIXAI JOURNAL

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

Explore your reports