The mismatch is not the part to fix first. The first job at month end is to prove whether your books and the bank statement are disagreeing because of timing, a missing entry, a duplicate, or a changed balance from an earlier period.
If you start by adjusting the current month until the numbers agree, you lose the trail. The close may tie today, but the same difference comes back next month because the cause was never identified.
When books don't match bank statement month end, the cause is usually one of five things:
- The opening balance or prior reconciliation changed
- The close period or cutoff is wrong
- A transaction exists in one file but not the other
- A transaction was duplicated
- A transaction was posted with the wrong amount, date, account, or sign
Those causes need different treatment. Timing items are carried and documented. Errors are corrected. The work is to identify which class of difference you have before you change anything.
Lock the Comparison Boundary First
Do not begin with individual rows. Start by proving that the two balances you are comparing belong to the same boundary.
At minimum, capture these checks before editing the books:
| Check | Bank statement | Books or ledger | What it proves |
|---|---|---|---|
| Account | Exact bank account and currency | Same cash or bank account | You are not comparing two different accounts |
| Period | Statement start and end dates | Ledger date range | The month-end cutoff is the same |
| Opening balance | First balance on statement | Prior reconciled closing balance | The starting point still agrees |
| Net movement | Total deposits less withdrawals | Total debits and credits for the period | The current-period activity is in scope |
| Row count | Number of bank lines | Number of ledger cash lines | A file or filter may be excluding rows |
| Reference quality | Blank or repeated references | Blank or repeated references | Matching may be unreliable |
This baseline check matters because a month-end mismatch can be created before the current month starts. If the prior reconciled balance changed, no amount of current-month matching will explain the difference cleanly.
If May closed at $42,180 and June now opens in the books at $42,030, the $150 difference exists before June activity starts. That is not a June transaction problem. Something changed after the May close, and the current month cannot be closed cleanly until that prior movement is isolated.
Classify the Difference Before Hunting Rows
Use the visible symptom to choose the first check. This keeps the investigation narrow.
| Symptom | Likely class | First check | Carry or correct |
|---|---|---|---|
| Opening balance no longer agrees | Changed prior period | Compare prior close report to current opening balance | Correct or restore prior-period change |
| Books include a transaction dated month end that bank shows next month | Timing or cutoff | Check posting date, value date, and statement end date | Carry if valid timing |
| Bank has a fee, interest, refund, or payment not in books | Missing record | Search bank-only rows by amount and date | Correct in books |
| Books have the same payment twice | Duplicate record | Search duplicate references, amounts, and dates | Correct duplicate |
| Same row exists in both places but amount or sign differs | Wrong amount or mapping | Compare amount, debit/credit sign, and account mapping | Correct posting or import rule |
If the opening balance changed, stop there. If the boundary is wrong, fix the boundary. If the period is clean, separate timing items from true errors. Only then should you move into row-by-row matching.
For a recurring amount, treat the difference as a pattern. Start with the same logic used when a bank reconciliation has a small unexplained difference every month: recurring fees, standing transactions, opening-balance drift, and repeated import rules.
Cause 1: The Opening Balance or Prior Close Changed
This is the first thing to rule out because it invalidates the rest of the investigation.
Your month-end reconciliation depends on the last reconciled balance. If that number moved, the current month starts from a false base. The current activity may be perfectly matched and the final balance will still be wrong.
Check these items first:
| Prior-period check | What to compare |
|---|---|
| Prior reconciliation report | Closing reconciled balance from the last completed month |
| Current ledger opening balance | Balance at the first day of the current month |
| Backdated transactions | New or changed entries dated before the current period |
| Voided or deleted transactions | Items removed after the previous close |
| Re-imported source file | Rows that replaced or duplicated prior activity |
The fastest proof is a balance bridge.
Start with the prior reconciled closing balance. Add current-month book activity. Compare that expected book balance to the current book balance. If those two numbers differ, the problem is inside the books before you even compare to the bank.
Example:
| Item | Amount |
|---|---|
| Prior reconciled bank balance | $42,180 |
| Current-month deposits in books | $18,400 |
| Current-month withdrawals in books | -$12,750 |
| Expected book balance | $47,830 |
| Actual book balance | $47,680 |
| Difference before bank comparison | $150 |
That $150 is not a bank statement issue. It is a ledger integrity issue. Find the prior-period or current-period book entry that explains it before touching the bank reconciliation.
Do not post a miscellaneous $150 adjustment to force the close. That hides the break and makes the audit trail weaker.
Cause 2: The Close Period or Cutoff Is Wrong
Cutoff problems are common at month end because the bank and the books do not always recognize activity on the same date.
The bank statement is based on what the bank processed by the statement end date. The books may include payments issued, deposits recorded, processor payouts expected, or receipts entered before the bank processed them. That is not always an error.
Common timing items look like this:
| Timing item | In books | In bank statement | Treatment |
|---|---|---|---|
| Deposit in transit | Recorded before month end | Clears after month end | Carry forward |
| Outstanding check or payment | Issued before month end | Clears after month end | Carry forward |
| Card payout delay | Sale or payout expected | Deposit appears next month | Carry forward if support exists |
| Posted versus pending transaction | Entered from expected activity | Not posted by bank cutoff | Carry forward or remove if not valid |
The key question is not "why do the numbers differ?" It is "does the transaction belong to this month or next month?"
Use the bank statement end date as the bank cutoff. Use the ledger posting date as the book cutoff. Then inspect the items around the boundary:
| Field | What to look for |
|---|---|
| Bank posting date | The date the bank actually posted the transaction |
| Value date | The date the bank uses for availability or settlement |
| Ledger date | The date used in the books |
| Payment issue date | When the payment was created or sent |
| Deposit date | When the receipt was recorded |
If a $2,400 customer payment is in the books on June 30 but the bank posts it on July 1, carry it as a deposit in transit. If the bank never receives it after month end, it is no longer timing. It becomes a missing or failed receipt.
Timing items have evidence. A valid carried item should have a matching amount, a near date, and a later bank posting or clear reason for the delay. If you cannot prove the carry, keep investigating.
Cause 3: A Transaction Exists in One Place but Not the Other
Missing records are the most direct source of a month-end mismatch.
The bank may contain items the books do not show:
- Bank fees
- Interest
- Returned payments
- Processor fees
- Direct debits
- Card charges
- Refunds
- Chargebacks
- Loan payments
- Transfers
The books may also contain items the bank does not show:
- Payments entered but not sent
- Deposits recorded but not made
- Manual journal entries posted to the bank account
- Imported rows from the wrong account
- Expected processor payouts that were never received
Search missing items from strongest evidence to weakest. Start with a reference or transaction ID. Then exact amount and exact date. Then exact amount across nearby dates. Then description text. Then surrounding row gaps.
If one transaction is missing and the file is large, use a focused search order instead of scanning every row. The same approach applies when you need to find a missing transaction between two financial files fast.
A practical missing-record search looks like this:
| Search pass | Match rule | What it finds |
|---|---|---|
| 1 | Exact reference or transaction ID | Rows that should match directly |
| 2 | Exact amount and exact date | Rows with missing or changed references |
| 3 | Exact amount within three to five days | Timing differences and delayed posting |
| 4 | Description contains merchant, customer, or bank text | Rows with weak references |
| 5 | Running balance gap or surrounding date gap | Rows omitted by filter or export scope |
When you find a bank-only row, decide whether it needs posting or belongs outside the current scope. When you find a books-only row, decide whether it is timing, an entry to remove, or an entry posted to the wrong bank account.
Do not treat every unmatched row as an error. Classify it.
Cause 4: A Transaction Was Duplicated
A duplicate can create the same kind of difference as a missing transaction, but in the opposite direction.
Duplicates often appear after import work:
- The same bank CSV was imported twice
- A payment was entered manually and also imported
- A processor payout was recorded as both gross activity and net deposit
- A transfer was recorded on both sides incorrectly
- A copied spreadsheet row was pasted into the working file twice
- A rule created a new entry when it should have matched an existing one
The fastest duplicate check is not visual scanning. Use the fields that should identify a unique row.
| Duplicate test | Use when |
|---|---|
| Same reference, same amount, same date | Stable transaction IDs exist |
| Same amount, same date, same description | Bank references are weak |
| Same amount and nearby dates | Settlement or posting dates vary |
| Same source file and row number | Import file may have been loaded twice |
| Same transfer amount in two bank accounts | Inter-account transfer may be counted twice |
The sign of the difference helps. If the books are higher than the bank by one deposit, look for a duplicated receipt or a deposit that never cleared. If the books are lower than the bank by one payment, look for a duplicated expense or payment entry. Transfers move cash between accounts; they are not income or expense.
When a duplicate is confirmed, correct the duplicate record. Do not offset it with another manual entry unless the accounting system requires a formal reversal trail.
Cause 5: A Transaction Was Posted With the Wrong Amount, Date, Account, or Sign
Sometimes the row exists in both places, but it is not actually the same row anymore.
That happens when a transaction is posted with the wrong detail:
| Error type | Example | Result |
|---|---|---|
| Wrong amount | Bank shows $918.40, books show $981.40 | Amount mismatch |
| Wrong date | Bank posts June 30, books post July 1 | Period mismatch |
| Wrong account | Payment posted to operating account, bank line is in savings | Account mismatch |
| Wrong sign | Withdrawal imported as positive amount | Balance moves the wrong way |
| Wrong mapping | Fee posted to sales or bank account incorrectly | Difference appears in cash or income |
| Edited source row | CSV changed after export | Original comparison is no longer trustworthy |
Amount errors should be tested before formula errors. If the bank says $1,248.60 and the books say $1,284.60, the problem is not that XLOOKUP failed. The values are different.
Sign errors double the visible difference. If a $500 withdrawal is imported as a $500 deposit, the bank and books are off by $1,000 because one side moved down and the other moved up.
File edits can create this class of problem without leaving an obvious mark. Opening a CSV in a spreadsheet can change dates, strip leading zeroes, convert long references, or alter amount formats. If the source file was edited mid-process, re-export the original file and compare structure before continuing:
| Integrity check | Why it matters |
|---|---|
| Row count | Detects omitted or inserted rows |
| Earliest and latest date | Detects changed filters or cutoff |
| Total amount | Detects missing or altered values |
| Blank-key count | Detects damaged references |
| Duplicate-key count | Detects repeated rows |
If the untouched export differs from the working file, rebuild the reconciliation from the untouched export. Repairing the edited file in place is usually slower and less defensible.
Use the Sign of the Difference as Evidence
The direction of the mismatch tells you where to look first.
| Difference pattern | Start here |
|---|---|
| Books are higher than bank | Duplicated receipts, missing withdrawals, deposits in transit, positive sign errors |
| Bank is higher than books | Missing receipts, duplicated withdrawals, outstanding payments, bank interest |
| Difference equals one known amount | Search that amount first |
| Difference is exactly double one known amount | Check for sign reversal |
| Same difference appears every month | Check opening balance, recurring fee, recurring rule, or carried timing item |
| Difference changes after re-export | Check file version, filters, and edited source data |
This does not prove the cause by itself. It gives you the next useful search. If the books are lower than the bank by $35 every month, start with recurring interest, monthly fees, a sign error, or an opening difference. If the books are higher than the bank by one customer deposit dated month end, check whether the bank posted it after the statement cutoff.
Decide What Carries and What Gets Corrected
A month-end reconciliation is not finished when the difference is identified. It is finished when each difference has the right treatment.
Use this rule: deposits in transit, outstanding payments, and settlement delays are carried when later bank evidence supports them. Bank fees, interest, duplicates, wrong amounts, wrong dates, wrong accounts, sign errors, and corrupted source files are corrected or rebuilt from the clean source.
Do not force-balance the reconciliation with a vague adjustment. That turns a known investigation into an unexplained number.
The close-ready output should show:
| Output section | What it should contain |
|---|---|
| Starting point | Prior reconciled balance and current opening balance |
| Current-period movement | Bank movement and book movement |
| Timing items | Deposits in transit, outstanding payments, settlement delays |
| Corrections made | Missing fees, duplicates, wrong postings, wrong signs |
| Remaining exceptions | Items still unresolved and who owns the follow-up |
| Final balance | Reconciled book balance and bank statement balance |
The useful endpoint is not a forced zero. It is a reconciliation where every difference is either carried with evidence, corrected with a trail, or left open with a specific owner and next action.
