How to reconcile a bank statement

Reconciling a bank statement means proving that two records of the same money agree: what the bank says happened and what your books say happened. When they agree, you can trust the account balance in your reports. When they do not, the difference points to a missing transaction, a duplicate, a typo or a timing gap.

The one formula that matters

Every statement obeys the same arithmetic:

opening balance + money in − money out = closing balance

Check this on the statement alone before comparing anything with your books. If the arithmetic fails on the statement data you are working from, the problem is in the data, not in the books. That happens more than people expect when transactions were typed in by hand or copied out of a PDF, because a skipped line or a dropped minus sign breaks the total.

A worked example. Opening balance 2,450.00. Money in during the month: 3,200.00 and 180.00. Money out: 1,150.00, 89.99 and 640.00. Then 2,450.00 + 3,380.00 − 1,879.99 = 3,950.01. If the statement shows a closing balance of 3,950.01, the period’s lines are complete. If it shows 3,860.02, your figure is 89.99 too high, which is the size of one payment: look for a second 89.99 payment on the statement that was skipped. If it shows 3,770.03, the gap is 179.98, twice 89.99, which usually means the 89.99 payment was counted as money in.

Steps

1. Collect the two records. Get the bank statement for the period and the matching account register from your books or spreadsheet, covering the same start and end dates.
2. Check the opening balance. Make sure the opening balance in your books equals the opening balance printed on the statement. If it does not, the previous period is not reconciled yet; fix that first.
3. Test the statement on its own. Add all money in to the opening balance and subtract all money out. The result must equal the closing balance on the statement. If it does not, a row is missing or misread.
4. Walk the running balance. Go line by line and keep a running balance. The first line where your running balance differs from the balance printed on the statement is where the problem starts.
5. Match each statement line to your books. Tick off every statement line against a transaction in your books with the same date range and amount. List what is left over on each side.
6. Explain every difference. Each leftover item is a timing difference, such as an uncleared cheque or a deposit in transit, or an error to correct. Record corrections, then confirm the adjusted balances agree.

Using the running balance to find the bad row

Most statements print a balance after every line or every day. Start from the opening balance, apply each line in order, and compare your running figure with the printed one. The first place they disagree tells you where to look: that line, or one just before it, is missing, duplicated, misread or has the wrong sign.

Three patterns are worth knowing:

• The gap equals one transaction amount. A line is missing or duplicated.
• The gap is twice one transaction amount. A line has the wrong sign: a payment recorded as a deposit, or the other way round.
• The gap is divisible by 9. Two digits were probably swapped when the amount was typed, for example 54.10 entered as 45.10, a difference of 9.00.

Usual reasons the books and the bank disagree

• Timing. A cheque you wrote has not cleared yet, or a deposit you recorded on the 31st reached the bank on the 2nd. These are not errors. They go on the reconciliation as outstanding items and should clear next period.
• Bank charges and interest. Fees, interest and card charges often appear on the statement before anyone records them. Add them to your books.
• Pending transactions. Card payments that show as pending in online banking may not be on the statement yet, and the amount can change when they post.
• Duplicates from imports. Importing the same period twice from two files, such as a bank feed and a manual upload, creates double entries. The guides on importing into Xero and QuickBooks Online explain how to avoid overlapping date ranges.
• Wrong period. The statement runs from the 15th to the 14th, but the books were filtered by calendar month.

Checking a PDF statement before you reconcile

If your statement is a PDF, the slow part is getting it into rows you can check. The Rowkeel converter reads the PDF in your browser, turns it into a table, and checks the result against the statement: opening balance plus all transactions must equal the closing balance printed on the statement. You see straight away whether the rows add up, before you export to Excel, CSV or an accounting format. The file is not uploaded to a server.

A standalone reconciliation checker, where you paste a table of transactions with an opening and closing balance, is planned but not live yet. Until then, the converter does the same balance check on PDF statements.

When the gap will not go away

If you have walked the running balance and still cannot find the difference, check the starting point again. A reconciliation can only be as good as the previous one. An opening balance that was forced to match months ago will keep producing a gap that no current transaction explains. In that case, go back to the last period that reconciled cleanly and work forward from there, one statement at a time.