Spreadsheets

Find invoices paid in the bank but missing from your ledger

List bank debits that match an invoice number or amount but have no ledger entry, rank them by amount, and see what to do with each payment in the books.

Editorial illustration for Find invoices paid in the bank but missing from your ledger

To find payments in the bank that are not in your books, take each bank debit and look for a ledger entry with the same invoice number or the same amount. Debits with no match are your unrecorded payments. Sort them from the largest down, and work through them in that order, because a few big ones usually explain most of the gap.

How do you set up the check?

Put bank debits on a tab called Bank: date in A, narration in B, debit amount in C. Put the ledger on a tab called Ledger: date in A, party in B, invoice number in C, amount in D. Add two helper columns on the Bank tab.

Which formulas find the missing entries?

First, the amount test in D2, filled down:

=COUNTIFS(Ledger!$D$2:$D$500, C2)

This counts ledger entries with exactly the same amount. Then the final flag in E2:

=IF(C2="", "", IF(D2=0, "Not in ledger", "Found"))

Amount alone can mislead if two different payments share an amount, so add an invoice check when your narrations carry the number. In F2, with a list of invoice numbers on the Ledger tab:

=SUMPRODUCT(--ISNUMBER(SEARCH(Ledger!$C$2:$C$500, B2)), --(Ledger!$C$2:$C$500<>""))

This counts ledger invoice numbers found inside the narration. A bank row with F above 0 mentions an invoice that is in the ledger even if the amount differs, for instance after a bank charge. Treat those as Amount differs rather than missing.

How do you rank the unmatched payments?

In Excel 365 or 2021, on a new tab:

=SORT(FILTER(Bank!A2:C500, Bank!E2:E500="Not in ledger", "None"), 3, -1)

In older Excel, filter column E for Not in ledger and sort the Debit column from largest to smallest. Say five debits are unmatched in a made-up month:

Bank debitAmountRunning total
Payment to supplier48,00048,000
Rent transfer12,50060,500
Courier and freight7,20067,700
Software renewal2,36070,060
Small vendor59070,650

The first two debits alone make up ₹60,500 of the ₹70,650 unmatched, which is about 86 percent. Two phone calls or two searches through email could clear most of the gap.

What do you do with each unmatched payment?

  • A real business payment with no entry: record it, with the invoice number and the date the bank shows.
  • A payment booked under a different amount: correct the entry, and note the difference, such as a bank charge.
  • A personal expense paid from a business account: treat it as a drawing or personal use under your usual rule, and ask your CA how to record it.
  • A payment you do not recognise: check with the person who runs the account, then with the bank if it is still unknown.
  • A duplicate debit: collect the two statement lines and ask the payee or bank to explain.

Why start with the large amounts? Reconciliation effort is not evenly spread. A ₹590 vendor payment and a ₹48,000 one both take a few minutes to chase, but only one changes your picture of the month. Spending the first half hour on the top of the list gives the most clarity for the least time, and the tail can wait for a quieter day.

How often should you run this?

Weekly is easier than monthly, because you still remember the payments. If you pay many small vendors through UPI, sort by amount but also filter by narration text to group repeat payees, so one recurring vendor is a single task rather than twelve rows.

Finishing touches

  1. Mark each row you resolve, with a short reason.
  2. Re-run the formulas after posting, and confirm the count of Not in ledger falls.
  3. Save the list of leftovers for your accountant at month-end.
SPENDIXAI JOURNAL

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

Explore your reports