Bank reconciliation template you can download in Excel
A clean bank reconciliation template in Excel, laid out the way accountants expect: adjusted bank balance on one side, adjusted book balance on the other, and the difference that has to reach zero.
bank-reconciliation-template.xlsx
XLSX| A | B | |
|---|---|---|
| 1 | Balance per bank statement | 24,860.00 |
| 2 | Add deposits in transit | 3,150.00 |
| 3 | Less outstanding checks | 2,355.50 |
| 4 | Adjusted bank balance | 25,654.50 |
| 5 | Balance per books | 25,532.10 |
| 6 | Add interest and collections | 12.40 |
| 7 | Less fees and NSF checks | 540.00 |
| 8 | Book errors | 650.00 |
| 9 | Adjusted book balance | 25,654.50 |
| 10 | Difference | 0.00 |
Download the bank reconciliation template
One XLSX file with two sheets: the reconciliation form and an item list for outstanding checks, deposits in transit and adjustments. Formulas are in place, you type the numbers.
Bank reconciliation format, section by section
| Section | What goes in |
|---|---|
| Header | Company, bank account, statement period, preparer, date |
| Balance per bank statement | Closing balance printed on the statement |
| Add deposits in transit | Deposits recorded in the books but not yet on the statement |
| Less outstanding checks | Checks written and recorded but not yet cleared |
| Bank errors | Amounts the bank recorded incorrectly |
| Adjusted bank balance | Result of the lines above |
| Balance per books | Cash account balance in the ledger at period end |
| Add interest and collections | Amounts the bank credited that are not in the books |
| Less fees and NSF checks | Charges and returned checks not yet recorded |
| Book errors | Amounts entered incorrectly in the books |
| Adjusted book balance | Result of the lines above |
| Difference | Adjusted bank balance minus adjusted book balance, which must be zero |
How to fill in the template
- Start with the closing balance from the statement and the cash account balance from your books for the same date.
- Tick each statement line against the ledger and list everything that is on one side only.
- Put each unmatched item in its row: deposits in transit and outstanding checks on the bank side, fees, interest and NSF checks on the book side.
- When the difference is zero, record the book side adjustments as journal entries.
Or let the software fill it in
The slow part of any template is the ticking. BankReconciler reads the statement and the ledger, does the matching and fills the same layout for you, then exports it as a monthly reconciliation report in PDF and XLSX.
Learn the layout with numbers
See a filled version with real arithmetic in our bank reconciliation example, or read the bank reconciliation definition first.
Template questions
Does the template work in Google Sheets?
Yes. Open the XLSX file in Google Sheets or any spreadsheet program, the formulas carry over.
Is the template enough for an audit?
It is a valid reconciliation format. Auditors also want the list of items behind each total, which is why the template has a second sheet for the item list.
Can I use the template for a credit card?
Yes. Use the card statement balance on the bank side and the card liability account on the book side. See credit card reconciliation.