Financial
Bank statement reconciliation formula: Adjusted Bank vs Adjusted Book balance
Reconcile month-end bank statements against general-ledger cash by adjusting for deposits in transit, outstanding checks, fees, interest, and NSF items.
Why raw bank and book cash balances rarely match at month-end
Timing differences split month-end cash into two sides: the bank does not yet know about Deposits in Transit or uncashed Outstanding Checks, while the company ledger may not yet reflect Bank Interest Earned, Monthly Service Fees, or bounced NSF (Non-Sufficient Funds) checks.
Adjusted Bank Balance equals BankEnding + DepositsInTransit - OutstandingChecks. Adjusted Book Balance equals BookEnding + InterestEarned - ServiceFees - NSFChecks. Reconciliation succeeds strictly when ROUND(AdjustedBank - AdjustedBook, 2) = 0.
| Bank Side | Amount ($) | Book (GL) Side | Amount ($) |
|---|---|---|---|
| Ending Bank Statement (A2) | 42,500 | Unadjusted Book Cash (D2) | 44,420 |
| + Deposits in Transit (B2) | +3,800 | + Interest Earned (E2) | +80 |
| - Outstanding Checks (C2) | -2,150 | - Service Fees & NSF (F2+G2) | -350 (35 + 315) |
| Adjusted Bank Balance | 44,150 | Adjusted Book Balance | 44,150 |
Adjusted balances and rounded difference
Compare the difference rounded to two decimals for this illustrative currency example. This is a tolerance check, not exact decimal equality.
=LET(adjBank, A2 + B2 - C2, adjBook, D2 + E2 - F2 - G2, diff, ROUND(adjBank - adjBook, 2), IF(diff = 0, "Reconciled", "Unreconciled: " & diff))Adjusted bank balance in this example
This computes the bank-side adjusted balance; agreement alone does not prove all entries are correct.
=A2 + B2 - C2Inputs and interpretation
Use the same account, currency and cutoff, with numeric adjustments not already included in the balances. Investigate errors and unmatched transactions separately. Rounding may hide sub-cent differences; choose the precision appropriate to the records.
Try the example
Use the original values or change them to test your understanding.