How to reconcile a bank statement against your books
Match every line on the statement to a line in your bank ledger on amount, direction and date — then read the two leftover piles, because what is left on the statement is what you never entered, and what is left in the books is what has not cleared yet.
12 min read · 7 steps · 6 ways it goes wrong
What you need before you start
- The bank statement for the period, as Excel or CSV rather than a PDF. Almost every bank offers it; a PDF has to be converted first and conversions of bank PDFs are where stray characters come from.
- The bank account’s ledger from your books for the same period, exported with dates, particulars and amounts. In Tally it is the ledger for that bank account; in most other packages it is the account transaction report.
- The closing balance on both sides, written down before you start. They will not agree, and the gap is the thing you are about to explain.
- Last month’s reconciliation, or at least the knowledge that last month’s closing balance was agreed. Reconciling onto an opening balance nobody ever checked is the commonest way to spend a day on a ten-minute job.
Take both sides for the same window, with a margin
Pull the statement for the month and the ledger for the month plus a few days either side. A cheque written on the twenty-eighth and presented on the third of the following month exists in both sets of records, in two different months, and a ledger cut exactly on the month end will not have it when the statement does.
Write down the two closing balances now, before anything is matched. The whole exercise ends by explaining the difference between those two numbers, and it is much less satisfying to work out what they were afterwards.
Get the direction right, because the two sides are mirrors
This trips up people who have done it for years, because the words are the same and the meaning is opposite. On the bank’s statement a credit is money arriving in your account. In your books, money arriving in the bank account is a debit to that account. The bank is describing its liability to you; your ledger is describing your asset.
money in on the statement (credit) = money in in the books (debit to bank)
So map "money in" and "money out" on each side by what actually happened to the cash, not by the column heading, and everything downstream follows. A ledger with a single signed amount column is fine too: map it as money in, leave money out empty, and negatives are taken as money out.
The tool for this step: Bank Reconciliation — matches the two sides on amount, direction and date, and returns the matched pairs, the unmatched on each side, and the closing balances side by side.
Match on amount and direction first, date second
A match needs the same amount to the penny, the same direction, and dates within a window — three days covers cheques and weekend clearing on most accounts. Where several entries have the same amount, a reference that appears in both descriptions decides which pairs with which: an invoice number, a transaction reference, a cheque number. Otherwise the nearest date wins, and each entry can match only once.
Widen the window for an account with slow-clearing instruments; narrow it if you start seeing pairs that are obviously not each other. A window that is too wide is worse than one that is too narrow, because a false match hides two real problems instead of showing them.
Read the leftovers on the statement side
These are things the bank knows about and your books do not. There are only a few kinds, and after one month you will recognise them by shape.
- Bank charges, account fees, card fees and the tax on them. Nobody enters these because nobody is told about them.
- Interest received or paid.
- Direct debits and standing orders that were set up once and never entered again.
- Receipts from customers who paid without telling you, which is the useful half of this list — that is cash you have and did not know about, often against an invoice you are busy chasing.
- A payment taken twice, or a payment you do not recognise at all. Rare, and the reason this is worth doing monthly rather than annually.
Everything in this pile needs an entry in the books. That is the point of the pile: it is your list of journals to post.
Read the leftovers on the books side
These are things you have recorded that the bank has not seen.
- Cheques issued but not yet presented. Perfectly normal, and they clear next month.
- Deposits in transit — cash or cheques banked near the period end that had not credited by the cut-off.
- An entry posted to the wrong bank account, which will sit here every month until somebody notices.
- An entry with the wrong date or the wrong amount. A transposition — two digits swapped — produces a difference divisible by nine, which is a genuinely useful thing to test for when a single stubborn entry will not match.
- An old cheque that has been here for months. It was never presented, and at some point it needs to be written back rather than carried forever.
Prove the balance, and write the proof down
The reconciliation is not the list of unmatched entries. It is the statement that the two balances differ by exactly those entries and nothing else.
balance per books + unpresented cheques − deposits in transit = balance per statement
Made-up figures, to show the shape of the proof rather than any real account.
| Amount | |
|---|---|
| Closing balance per books | 84,200 |
| Add: cheques issued, not yet presented | 6,500 |
| Less: deposit banked on the last day, not yet credited | (2,700) |
| Less: bank charges not entered | (320) |
| Closing balance per statement | 87,680 |
If the two sides do not meet, do not adjust anything to make them. An unexplained difference that has been plugged is an unexplained difference that will never be found. Leave it visible, name it, and go looking.
Post what you found, and do it again next month
The charges, the interest and the direct debits become journals. The customer receipt you did not know about becomes a receipt allocated against an invoice — and probably comes off a chasing list somewhere. The entry in the wrong account gets moved.
The tool for this step: Bookkeeping: Trial Balance, P&L and Balance Sheet — takes a bank statement or a set of journals into a real double-entry ledger, so the entries you find here have somewhere to go and the trial balance follows from them.
Then agree the closing balance, and keep the reconciliation. Next month starts from it. A bank account that has been reconciled every month is an account where a difference can only have arisen this month, which is why the job stays ten minutes long.
Where this usually goes wrong
6 things that actually happen, rather than a note asking you to be careful.
- A signed amount column mapped as two columns. Many bank exports have one Amount column with negatives for money out, while your ledger has separate Debit and Credit columns. Map that single column as money in and also map something as money out, and every payment is counted twice — once as a negative in, once as a positive out — so the totals are wrong in a way that still looks orderly. The rule is one or the other: a signed column goes in the money-in slot with money out left empty, and the negatives take care of themselves.
- The cheque that clears outside the window, counted as two problems. A date window of a few days is right for almost everything, and completely wrong for a cheque presented a fortnight later. That one transaction then appears on both leftover lists: as "payment not entered" on the statement side and as "cheque issued, not yet presented" on the books side. It looks like two unexplained items of equal and opposite amount, which is exactly what a real error looks like. Before widening the window, check whether the amounts on your two leftover lists cancel out in pairs.
- Reconciling onto an opening balance that was never agreed. If last month was not reconciled, this month’s difference contains last month’s difference, and possibly the one before that. You can spend hours looking for a transaction in this month’s data that is not in this month’s data. The tell is a difference that is not divisible by anything sensible and matches no combination of the unmatched entries. Go back to the last month that was agreed and work forward; it is faster, even when it is three months.
- Two identical amounts on the same day. Two payments of exactly the same amount to the same supplier on the same day are matched by whichever rule fires first, and the pairing may be backwards. Usually this does not matter, because the effect on the totals is nil. It matters when one of them was entered against the wrong invoice, or when one is a duplicate payment — in which case the pairing decides which entry is reported as unmatched, and you go looking at the wrong one. A reference in the narration, on either side, fixes it permanently.
- Rows the import quietly dropped. A reconciliation only sees the rows it could read. Balance-only rows, opening and closing balance lines, a row with no date, a row whose amount is zero, and headers repeated in the middle of the file because the statement was downloaded page by page — all are dropped or skipped, and the totals of what was read can be smaller than the file. Check the row count that was read against the row count in the file. If a statement of 240 lines came in as 231, find the nine before you start matching.
- Bank charges with tax on them, entered as one figure. Bank charges frequently arrive with GST or VAT applied to them, and the statement shows one combined debit or, worse, two debits a day apart. Entered as a single expense, the tax is never reclaimed and the expense is overstated; entered by guesswork, the tax account stops agreeing with the return. The bank’s own charge statement or the monthly charge advice has the split, and it is worth getting once and setting up as a recurring entry rather than deciding again every month.
How long this should take
Half an hour for a first month on a small account, most of it spent working out which of your columns is money in. Ten minutes a month afterwards. If a reconciliation is taking hours, the usual cause is that the opening balance was never agreed, in which case you are not reconciling this month — you are reconciling every month since the last one that was right, and it is quicker to admit that and go back.
Frequently asked questions
What actually counts as a match?
The same amount to the penny, the same direction, dates within the window you set, and — where more than one candidate qualifies — the entry that shares a reference with the description, then the nearest date. Every entry matches at most once, on either side.
My books have one column with negatives. Is that a problem?
No. Map it as money in and leave money out unmapped; negative values are read as money out. The same applies to a statement that comes that way. What does not work is mapping a signed column and a separate money-out column at the same time.
How wide should the date window be?
Three days suits most current accounts. Widen it for an account that still sees cheques or that clears slowly, and narrow it if you notice pairs being matched that are plainly not each other. Before widening it, check whether the unmatched amounts on the two sides cancel in pairs — that is the same transaction seen twice, not a missing one.
Is the statement uploaded anywhere?
No. Both files are read and compared by your browser and the report is written there. Nothing leaves the device, which matters more for a bank statement than for almost any other file a business has.
The tools this uses
Each one described in its own words, read from its own page. Everything here runs in your browser unless it says otherwise.