A bank reconciliation answers one question: does the money your books say you have match the money the bank says you have? This free Excel template does it in two sheets, and it checks the statement itself on the way.
Download the bank reconciliation template (.xlsx)
There is no email to give. It opens in Excel, Google Sheets, Numbers and LibreOffice.
What is in it
- Statement. Paste your bank statement's rows: Date, Description, Money
in, Money out and Balance. The Check column tests each balance against the
row before it, and says
okorbreak. - Reconciliation. The statement's closing balance, the deposits and payments your books have that the bank has not yet, and the balance in your books. The difference should be 0.00.
- How to use. The steps below, inside the file.
Yellow cells are for you to fill. Every other cell is a formula.
How to use it
- On the Statement sheet, delete the example rows.
- Put the opening balance in row 2, under Balance.
- Paste the statement's transactions from row 3 down, one per row, with money in and money out in their own columns.
- Filter the Check column for
break. Each one is a row to compare with the PDF, before you go further. - On the Reconciliation sheet, type the statement's closing balance and its date.
- List the deposits in transit: money in your books that the bank had not received by that date, such as a cheque paid in late on the last day.
- List the unpresented payments: money out in your books that had not left the bank, such as a cheque not yet cashed.
- Type the balance your books show on the same date.
When the difference is 0.00, the sheet says Reconciled.
When the difference is not zero
Look for these, most likely first:
- A row missed or typed twice when the statement went into the sheet. The
Check column finds most of these: a balance that does not follow is a
break. - A bank charge or interest that is on the statement but not yet in your books.
- A payment in your books on the wrong date, so it falls on the other side of the statement's closing date.
- A figure with two digits swapped, such as 54 for 45. Then the difference divides by 9.
Getting the statement into rows
Typing a statement in by hand is where most differences start. If your bank gives you a PDF, convert it: BankPDFtoXLS turns the PDF into Excel with the same columns, and its download carries a Ledger column that runs the same check as this template's Check column on every row. Page one converts free, on the home page, with no email and no card.
Why a balance breaks
explains what a break usually means and how to find the row.