Bank Reconciliation Template
Bank reconciliation is the process of matching your accounting records with your bank statement to ensure accuracy. This free Excel template includes step-by-step instructions, formulas, common adjustments, and journal entries. Or automate the entire process with Zera Books AI-powered reconciliation—99.6% accuracy, unlimited conversions for $79/month.
The Reconciliation Formula
Bank Side
Ending Bank Balance
+ Deposits in Transit
− Outstanding Checks
= Adjusted Bank Balance
Book Side
Ending Book Balance
+ Interest, Direct Deposits
− Fees, NSF Checks
= Adjusted Book Balance
Adjusted Bank = Adjusted Book ✓
Bank Reconciliation Template Structure
A proper bank reconciliation has two sides: adjusting the bank balance and adjusting the book balance until they match.
Bank Statement Balance
Start with bank statement ending balance
Starting Point
Ending Balance per Bank Statement
$XX,XXX.XX
Add:
Deposits in Transit
Deposits recorded in books but not yet on bank statement
Bank Errors (Understatements)
Errors by bank that understated your balance
Subtract:
Outstanding Checks
Checks issued but not yet cleared at bank
Bank Errors (Overstatements)
Errors by bank that overstated your balance
Book (Ledger) Balance
Start with general ledger cash balance
Starting Point
Ending Balance per Company Books
$XX,XXX.XX
Add:
Interest Earned
DR: Cash / CR: Interest Income
Notes Receivable Collected
DR: Cash / CR: Notes Receivable
Direct Deposits
DR: Cash / CR: Accounts Receivable
Subtract:
Bank Service Charges
DR: Bank Service Expense / CR: Cash
NSF Checks (Returned Checks)
DR: Accounts Receivable / CR: Cash
Automatic Payments
DR: Appropriate Expense / CR: Cash
Step-by-Step Reconciliation Process
Follow these 7 steps to complete a bank reconciliation. Total time: 45-90 minutes manually, or 10-15 minutes with AI automation.
Gather Documents
5-10 minutes
Collect bank statement and accounting records
- Bank statement (PDF or online)
- General ledger cash account
- Check register / check images
Compare Deposits
10-15 minutes
Match bank deposits to recorded deposits
- Verify each deposit on bank statement is in books
- Identify deposits in transit (in books, not on statement)
- Note any discrepancies in amounts
Compare Withdrawals
15-20 minutes
Match bank withdrawals to recorded checks/payments
- Compare cleared checks to check register
- Identify outstanding checks (issued, not cleared)
- Match electronic payments (ACH, wire transfers)
Identify Bank Adjustments
5-10 minutes
Note bank-side items not in your records
- Interest earned (credit)
- Bank fees and charges (debit)
- NSF/returned checks (debit)
Complete Reconciliation Form
10-15 minutes
Calculate adjusted balances for both sides
- Start with ending bank balance
- Add deposits in transit
- Subtract outstanding checks
Record Adjusting Entries
5-10 minutes
Update books for bank-initiated transactions
- Journalize interest income
- Record bank service charges
- Reverse NSF checks to A/R
Verify and Document
5 minutes
Ensure balances match and file documentation
- Confirm adjusted bank = adjusted book
- Sign and date reconciliation
- Attach supporting documents
Which Items Require Journal Entries?
Understanding which reconciling items need journal entries is crucial for proper accounting.
Bank-Side Items
No Journal Entry Required
Already recorded in books, waiting to appear on bank statement
Example: $5,240 deposited on Dec 31, appears on January statement
Already recorded in books when check was written
Example: Check #4521 for $1,200 mailed but not yet cashed
Bank must correct; notify bank immediately
Example: Bank charged wrong account for $500 wire fee
Why no entry? These items are already in your books. You're adjusting the bank balance to match, not your records.
Book-Side Items
Journal Entry Required
Bank deducted but company hasn't recorded yet
DR: Bank Service Expense $35
CR: Cash $35Customer's check bounced; must reverse the deposit
DR: Accounts Receivable $750
CR: Cash $750Bank credited interest not yet recorded
DR: Cash $45.22
CR: Interest Income $45.22Customer paid directly to bank; not yet recorded
DR: Cash $1,500
CR: Accounts Receivable $1,500Why entry required? The bank has recorded these transactions, but your books haven't. You must update your records.
Common Reconciliation Errors & How to Find Them
Transposition Error
Digits swapped when recording amounts
Example:
Recording $1,540 as $1,450 (difference always divisible by 9)
Detection: If difference is divisible by 9, likely transposition
Fix: Review all entries for swapped digits
Slide Error
Decimal point placed incorrectly
Example:
Recording $150.00 as $15.00 or $1,500.00
Detection: Difference is 10x or 0.1x of an amount
Fix: Check entries for misplaced decimals
Omission Error
Transaction completely missed
Example:
Forgot to record a check or deposit
Detection: Item on bank statement with no book entry
Fix: Record missing transaction
Duplication Error
Same transaction recorded twice
Example:
Deposit entered on both deposit date and clearance date
Detection: Total seems inflated; duplicate reference numbers
Fix: Void duplicate entry
Wrong Amount Error
Correct transaction, incorrect amount
Example:
Check for $523.00 recorded as $532.00
Detection: Compare check images to register
Fix: Correct the amount in books
Wrong Account Error
Transaction posted to wrong account
Example:
Utility payment coded to supplies expense
Detection: Review unusual account balances
Fix: Journal entry to correct account
Bank Reconciliation Best Practices
Follow these internal control best practices to ensure accurate reconciliations and prevent fraud.
Segregation of Duties
Different people for recording, reconciling, and approving
Why it matters:
Prevents fraud and catches errors
How to implement: Accountant records, bookkeeper reconciles, manager reviews
Monthly Reconciliation
Complete reconciliation within 5 business days of month-end
Why it matters:
Timely detection of errors and fraud
How to implement: Set calendar reminders; include in month-end close checklist
Management Review
Supervisor reviews and signs off on reconciliation
Why it matters:
Adds accountability and catches preparer errors
How to implement: Require signature and date from reviewer
Outstanding Check Follow-up
Investigate checks outstanding over 90 days
Why it matters:
Prevents stale check issues; identifies lost checks
How to implement: Monthly aging report of outstanding checks
Document Retention
Keep reconciliations and supporting documents for 7 years
Why it matters:
Audit trail and tax documentation
How to implement: Organized digital or physical filing system
Bank Statement Verification
Verify bank statement is authentic and complete
Why it matters:
Prevents fraud through altered statements
How to implement: Download directly from bank portal; verify page count
Excel Template Specification
Technical specification for creating your own bank reconciliation template in Excel, including all formulas and formatting.
Column Structure
Key Formulas
=SUM(C4:C5)=SUM(C8:C12)=C2+C6-C13=C16+C17+C18-C19-C20-C21=C24-C25Conditional Formatting
Apply to cell C26 (Difference): Red background if ≠ 0, Green if = 0
Frequently Asked Questions
What is bank reconciliation and why is it important?
Bank reconciliation is the process of comparing your internal accounting records (book balance) with your bank statement to identify and explain differences. It's crucial for detecting errors, preventing fraud, ensuring accurate financial statements, and maintaining internal controls. Monthly reconciliation is a standard accounting practice required for proper cash management.
What causes the bank balance and book balance to differ?
Common causes include: (1) Timing differences - deposits in transit and outstanding checks that haven't cleared; (2) Bank-initiated transactions - service charges, interest, NSF fees; (3) Errors - either by the bank or in your records; (4) Unrecorded transactions - automatic payments or direct deposits not yet entered in books.
What is a deposit in transit?
A deposit in transit is money you've recorded as a deposit in your books but that hasn't yet appeared on your bank statement. This typically happens with deposits made near month-end that the bank processes after the statement date. Add deposits in transit to the bank balance when reconciling.
What is an outstanding check?
An outstanding check is a check you've written and recorded in your books, but hasn't been cashed or cleared by the bank yet. The payee hasn't deposited it, or it's still processing. Subtract outstanding checks from the bank balance. If checks are outstanding for 6+ months, consider contacting the payee.
Which reconciling items require journal entries?
Only book-side adjustments require journal entries: bank service charges, NSF checks, interest earned, electronic transfers not recorded, and errors in your books. Bank-side items (deposits in transit, outstanding checks, bank errors) don't require entries because they're already in your books or are the bank's responsibility.
How long should bank reconciliation take?
Manual reconciliation typically takes 45-90 minutes per bank account per month, depending on transaction volume. With AI-powered tools like Zera Books that automatically match transactions, reconciliation can be reduced to 10-15 minutes. Complex accounts with many transactions may take longer.
What do I do if the reconciliation doesn't balance?
First, verify your math and formulas. Check for transposition errors (differences divisible by 9). Review outstanding items from the prior month. Compare deposit and check totals. Look for transactions recorded twice or not at all. Verify amounts on check images match your register. If still unbalanced, contact the bank for detailed transaction records.
How often should bank reconciliation be performed?
Monthly reconciliation is the standard practice and minimum requirement for good internal controls. High-volume accounts or businesses with fraud risk may benefit from weekly reconciliation. Daily reconciliation is recommended for cash-intensive businesses like retail or restaurants. Never go more than one month without reconciling.
Related Resources
Explore more reconciliation solutions and tools to optimize your accounting workflow.
Best Bank Reconciliation Software
Compare all reconciliation tools
Bank Reconciliation Automation
Automated reconciliation workflows
Bank Reconciliation Tool
Essential reconciliation tools
Bank Reconciliation Platform
Complete platform features
Month-End Close
Streamline month-end workflows
How to Reconcile Bank Statements
Step-by-step guide
AI Categorization
Auto-categorize transactions
Bank Statement Converter
Convert PDF statements to Excel
Pricing
Plans and pricing details
Automate Bank Reconciliation with AI
Convert PDF bank statements to Excel and let AI match transactions automatically. Reduce 45-90 minute reconciliations to under 15 minutes.