Bank reconciliation

You prove that the cash the books say the business has is the cash the bank says it has, and you explain every difference with a named item. A reconciliation is finished only when the difference is exactly 0.00, every reconciling item is listed with its date and age, and nothing has been forced to balance with a plug.

When to use

• "Reconcile the bank for September."
• "Xero says we have 12,400 but the bank says 11,950. Why?"
• "The card account hasn't been reconciled since spring."
• Month-end close needs every cash, card, loan and processor account reconciled.
• Before a management pack, a tax return or an accountant's year-end visit.

What you need

• The bank statement for the period with its opening and closing balance, as PDF or CSV from the bank's website. The statement is the source of truth, not the bank feed.
• The ledger account transactions for the same account and period, and the ledger balance at the statement date.
• The previous reconciliation and its list of outstanding items.
• Access, best first: a connected app (Xero, QuickBooks, Stripe) on the Connections page; your own browser, signed in by the owner to the accounting software or the bank's website (read and download only, never start a payment or change a setting); or exports the owner gives you. If none is there, ask with clarify for the statement and the ledger report by name and date range.

Method

1. Fix the date and the two balances. Write down: statement closing balance at date D, and ledger balance for the same account at date D. Note the currency. Everything after this explains the gap between them.
2. Tie the opening balance. The statement opening balance must equal the previous reconciliation's closing statement balance. If it does not, there is a missing statement, a gap in the feed, or the prior reconciliation was wrong. Stop and resolve that first, or every later number is off.
3. Check the feed against the statement (when the ledger uses a bank feed). Count lines and total them by month. Duplicated or missing feed lines are common; the statement wins.
4. Match, in passes. With execute_code (pandas):
   • Pass 1, exact: same amount, same sign, date within 0 to 5 days, reference or payee similar. Where several candidates fit, take the nearest date and lock each line once.
   • Pass 2, many to one: one bank deposit equal to several ledger receipts (a batched deposit, a processor payout), or one ledger payment run equal to several bank debits. Search combinations within a 7-day window, at most 6 items per combination.
   • Pass 3, by reference: invoice numbers, cheque numbers or payment references that match despite different amounts. These are usually errors; flag them. The matching code is in references/reconciliation-workbook.md.
5. Classify everything left unmatched into three buckets:
   • Timing (no entry needed, clears by itself): payments recorded in the books but not yet through the bank (unpresented), receipts recorded but not yet credited (deposits in transit).
   • Adjustment needed (the books are missing something): bank charges, interest, direct debits, standing orders, receipts never recorded, duplicates in the ledger, wrong amounts, wrong account.
   • Investigate (cannot explain yet): unknown items, disputed items, anything old.
6. Find errors with arithmetic.
   • Difference divisible by 9: likely a transposition (54.00 keyed as 45.00).
   • Difference equal to one item: that item is missing on one side.
   • Difference equal to twice an item: that item was posted with the wrong sign.
   • Difference that is a round number: a missing transfer, cash deposit or opening balance error.
   • Same date, amount and description twice: a duplicate.
7. Build the reconciliation statement (format in references/reconciliation-workbook.md): adjusted bank balance on one side, adjusted ledger balance on the other, difference 0.00. If it is not 0.00, you are not done. Go back to step 4.
8. Age the timing items. Deposits in transit usually clear in 1 to 5 business days and card or transfer payments in a few days; anything older needs a reason. Use these buckets for every outstanding item: 0 to 30 days monitor, 31 to 60 investigate, 61 to 90 escalate to the owner, over 90 ask the owner and accountant whether it should be reversed or written off. Cheques have their own stale-date rules, which vary by country and bank: check.
9. Propose adjusting entries for every item in the adjustment bucket: date, account, amount, tax code, narrative, and the evidence. Post them in the ledger only if the owner has said you may; never post into a locked period.
10. Payment processors and cards. Reconcile Stripe or PayPal clearing accounts to the processor's balance report the same way: gross sales less fees less refunds less payouts equals the processor balance. Reconcile a credit card to the card statement balance, not to the amount paid off.

Worked example

Statement closing balance 11,950.00; ledger balance 12,400.00; gap 450.00.

• Pass 1 matched 212 of 219 bank lines. Pass 2 matched one 1,250.00 deposit to three ledger receipts (400.00 + 500.00 + 350.00) dated the same week.
• Left unmatched on the bank side: a bank charge of 18.00 and an insurance direct debit of 852.00; the books are missing both. On the ledger side: a receipt of 820.00 from Acme dated 29 Sep (deposit in transit) and a payment of 1,240.00 to Office Co dated 28 Sep (unpresented).
• Statement side: 11,950.00 + 820.00 minus 1,240.00 = 11,530.00. Ledger side: 12,400.00 minus 18.00 minus 852.00 = 11,530.00. Difference 0.00.
• Proposed entries: bank charges 18.00 (tax treatment per the local rule, checked and cited); insurance 852.00 to prepayments or insurance expense depending on the policy period. The two timing items carry to next month's outstanding list with their original dates.
• Error finder hints, had the gap remained: 450.00 is divisible by 9, so look for a transposition such as 1,540.00 keyed as 1,090.00 before looking anywhere else.

Account types and what "reconciled" means for each

Account	Reconcile to	Watch for
Current and savings	Statement closing balance	Feed gaps around month end; duplicated feed lines
Credit card	Card statement closing balance (what is owed)	Reconciling to the amount paid off, which ignores new spend
Loan	Lender's statement (principal outstanding)	Interest and fees charged to the loan by the lender
Stripe, PayPal, Square	Processor balance report: gross sales minus fees minus refunds minus payouts	Payouts booked as sales; disputes and reserves held back
Petty cash	Physical count on the date	Spend with no receipt
Foreign currency	Statement in that currency first, then revalue at the month-end rate	Mixing the revaluation into the reconciliation
Undeposited funds or clearing	Zero, or each line listed and explained	Old lines that were never cleared

Output

• A workbook bank-rec-<account>-<date>.xlsx with sheets: Statement (the reconciliation), Matched, Unmatched bank, Unmatched ledger, Outstanding items (with age and bucket), Proposed entries, Checks.
• A three-line summary for the owner on a show_card: statement balance, ledger balance, difference explained (0.00 after N timing items and M proposed entries), plus anything over 60 days old.
• A short question list for items in the Investigate bucket.

Checks before you finish

• Statement opening balance equals the prior reconciliation's closing balance.
• Statement closing balance in your workbook equals the PDF or CSV statement, to the cent.
• Ledger balance in your workbook equals the ledger report at the same date, to the cent.
• Adjusted bank balance minus adjusted ledger balance equals exactly 0.00.
• Every bank line is either matched once or listed as an unmatched item; same for every ledger line. Counts on both sides add up.
• No plug, no "rounding" line, no unexplained entry to suspense.
• Every outstanding item carried from last month has either cleared or is still listed, with its original date.

Pitfalls

• Reconciling to the feed balance. The feed can be wrong. Use the statement.
• Forcing it to balance. A plug hides an error that grows. Find the item.
• Creating new spend or receive entries to match bank lines that should settle an invoice or bill. That duplicates income or cost. Match to the open invoice or bill.
• Carrying stale items forever. An unpresented payment from six months ago is either a lost payment or a booking error. Raise it.
• Ignoring currency. A foreign currency account reconciles in its own currency first; revaluation differences are a separate entry.
• Unticking reconciled lines in prior periods. That changes closed periods. Ask the owner, and the accountant if the period has been reported.

See also

• categorise-bank-transactions, to code lines before reconciling.
• month-end-close, which needs every cash account reconciled.