How to Prepare a Bank Reconciliation Statement in Excel

A bank reconciliation statement (BRS) starts with the balance in your cash book, adds and subtracts the items that are in only one of the two records, and ends at the balance on the bank statement. When the two agree, you know every transaction is accounted for.

Below is the method step by step, the four kinds of difference you'll meet, a worked example and how to set it up in Excel so next month takes minutes.

Why the two balances differ

• Cheques issued but not yet presented: you've recorded the payment; the bank hasn't paid it yet.
• Deposits not yet credited: you've recorded a receipt (a cheque deposited on the 31st); the bank credits it next month.
• Bank entries not yet in your books: bank charges, interest, NEFT or UPI receipts, ECS debits for EMIs.
• Errors: a wrong amount, a duplicate entry or a transaction posted to the wrong bank account – on either side.

Step by step

1. Get the bank statement for the month and the bank ledger (cash book) from your accounts for the same dates.
2. Tick off every entry that appears in both, matching by amount and reference (cheque number, UTR).
3. List what's left in the ledger but not on the statement – usually unpresented cheques and uncredited deposits.
4. List what's left on the statement but not in the ledger – bank charges, interest, direct receipts and debits.
5. Pass entries in your books for the bank-only items that are genuine (charges, interest, receipts). These are the adjusting entries.
6. Prepare the BRS from the adjusted ledger balance, adding unpresented cheques and subtracting uncredited deposits. It must equal the statement balance.
7. Investigate anything that doesn't fit – those are errors to correct, not items to carry forward.

Worked example

At 31 October the bank ledger shows ₹2,45,600, but the bank statement shows ₹2,71,450. After ticking off, the leftovers are: cheques issued but not presented ₹38,000; a cheque deposited on 31 October not yet credited ₹15,000; bank charges ₹590 and interest ₹1,440 not booked; and a customer's NEFT of ₹12,000 received but not recorded.

1. Adjust the ledger: ₹2,45,600 − ₹590 + ₹1,440 + ₹12,000 = ₹2,58,450.
2. Add cheques not yet presented: ₹2,58,450 + ₹38,000 = ₹2,96,450.
3. Subtract the deposit not yet credited: ₹2,96,450 − ₹15,000 = ₹2,81,450.
4. Compare with the statement: ₹2,71,450. There is still a ₹10,000 difference, so something is wrong.
5. Search for ₹10,000 in both records: a customer receipt of ₹10,000 was entered twice in the ledger. Reverse the duplicate and the adjusted ledger becomes ₹2,48,450 – and the BRS agrees.

Tips for faster matching in Excel

• Paste the bank statement and the ledger side by side and use a match key: amount plus cheque number or UTR.
• Use COUNTIFS to flag amounts that appear more than once – duplicates are the most common error.
• Look at the age of reconciling items: a cheque unpresented for more than three months needs follow-up, and one older than its validity should be reversed.
• Reconcile every month, not at year end. Fifty items are easy to trace; six hundred are not.
• Keep the BRS with the bank statement and sign-off – auditors and lenders ask for it.

A BRS template that does the arithmetic

Our free Bank Reconciliation Statement template has sheets for the ledger and statement entries, automatic matching, the reconciliation in the standard format and a check that turns ✔ when the balances agree. The Pro edition adds ageing of open items and a multi-bank summary. If you're closing the month, the Finance & Accounts Kit bundles it with the ageing, fixed asset and final accounts templates.

For receipts and payments that also need GST matching, see the GSTR-2B reconciliation template.