Free template · Excel · Bookkeeping and reports
Free bank reconciliation template for Excel
A one-page bank reconciliation. It adjusts the statement for deposits in transit and outstanding cheques, adjusts your books for fees and interest, and shows the difference, which should be zero.
Bank reconciliation template
Excel workbook (.xlsx) · 8 KB · 2 sheets
| Amount | |
|---|---|
| 1. Bank side | |
| Ending balance on the bank statement | 12,480.55 |
| Add: deposits in transit | 1,450.00 |
| Less: outstanding cheques and payments | (2,230.40) |
| Adjusted bank balance | 11,700.15 |
| 2. Book side | |
| Balance in your books on the statement date | 11,711.95 |
| Add interest, less bank fees | (11.80) |
| Adjusted book balance | 11,700.15 |
| DIFFERENCE (should be zero) | 0.00 |
What it is
A bank reconciliation proves that your books and the bank agree. The two rarely match on the day, because some cheques haven't cleared and some fees aren't in your books yet; the reconciliation explains every difference.
This template lays out both sides, totals the outstanding lists for you and tells you when you're done.
Who it's for
- Anyone keeping a cheque register or spreadsheet ledger
- Bookkeepers who want a printed reconciliation for the file
- Owners checking their own books each month
What's in the file
| Sheet | What it holds |
|---|---|
| Reconciliation | Bank side, book side, the difference with a plain-language result, a prepared-by sign-off, and lists for deposits in transit (13 rows) and outstanding cheques and payments (24 rows). |
| Instructions | Step by step, plus tips for finding a difference. |
How to use it
- Fill in the account and statement date, and clear the samples.
- Enter the ending balance from the bank statement.
- Tick off every item that appears on both the statement and in your books.
- List deposits and cheques in your books that haven't reached the statement yet.
- Enter your book balance, then the items only the bank knows about: interest, fees, NSF cheques, corrections.
- When the difference is zero, record those bank-only items in your books, sign and date it, and file it with the statement.
Tip: If the difference divides evenly by 9, look for two digits swapped (54 typed as 45). If it's twice an amount, look for an item entered with the wrong sign.
Pale yellow cells are for your entries; white cells calculate. Nothing is locked, so you can rename lines, add rows inside a section, or change the layout. Each file has an Instructions sheet with these steps.
Download bank reconciliation template (.xlsx)
Questions
How often should I reconcile?
Every month, for every bank and credit card account, soon after the statement arrives. Small differences are easy to find within a month and very hard to find a year later.
What is a deposit in transit?
Money you've recorded as deposited that hadn't reached the bank by the statement date, such as cheques deposited on the last afternoon of the month. It will appear on next month's statement.
What if a cheque never clears?
Contact the payee. A cheque that's been outstanding for months may have been lost. Rules on unclaimed property vary by state and province, so don't simply delete it from your books; ask your accountant.
Related
- How to reconcile a bank account
- Monthly closing checklist
- Recording card payments and fees
- All free templates
General information, not tax, legal or accounting advice. Check with your accountant before relying on a template for a tax return.
AcboBooks
When the spreadsheet stops being enough
AcboBooks reconciles inside your books: tick off cleared items against the statement and it shows the difference as you go.
More free templates
Get started
Outgrown the spreadsheet?
AcboBooks is desktop accounting for US and Canadian small businesses. Bought once, with your company file on your own PC.
No card. No account. Nothing to cancel.