Bank Reconciliation Statement in Excel

Last updated: 27 June 2026

A bank reconciliation statement (BRS) explains the difference between your cash book's bank-column balance and your bank statement balance at the same date. Every Indian SMB should prepare one monthly — at minimum — to catch errors, track pending items, and keep books audit-ready.

Key takeaways

• A BRS starts from either the cash book balance or the bank statement balance, then adjusts for timing differences to arrive at the other figure.
• Common reconciling items: outstanding cheques, deposits in transit, bank charges, GST on bank charges, TDS on FD interest, and ECS/NEFT transactions not yet in the books.
• Excel speeds up matching: download your bank statement as CSV, export your cash book, use VLOOKUP or MATCH to isolate unmatched rows.
• Bank charges carry GST at 18% — recording only the net debit may understate your ITC eligibility.
• TDS deducted on bank FD interest (10% under Section 194A, on annual interest above ₹50,000 for general depositors and ₹1,00,000 for senior citizens for FY 2025-26) often goes unrecorded until the statement arrives.
• A completed monthly BRS is primary evidence during an income tax or GST audit that your books are accurate.

What is a BRS and why do balances differ?

Your cash book records transactions when you write cheques or make entries. Your bank processes them on its own schedule. The gap on any date is normal — but it must be explained.

Fact box. A BRS is not optional. During a tax audit under Section 44AB or a GST audit, the officer will ask to see monthly reconciliations. A complete BRS is treated as evidence that your cash book accurately reflects your bank account — its absence raises a red flag.

What is the standard BRS format?

Two approaches are equally valid. Most Indian accountants start from the cash book balance.

Approach A — Start from the cash book balance

Approach B — Start from the bank statement balance

Both approaches must reach the same closing figure. If they do not, there is an error in the cash book, the statement, or the reconciling items listed.

Fact box. Outstanding cheques are the most common reconciling item for Indian SMBs that issue physical cheques. A cheque you wrote and recorded in your cash book may sit with the payee for days. It is in your cash book but absent from the bank statement until it clears.

How do I build a BRS in Excel, step by step?

1. Download the bank statement. Most Indian banks (SBI, HDFC, ICICI, Axis, Kotak) let you export a CSV or XLS from net banking. You need: date, narration, debit, credit, balance.
2. Export your cash book. From your accounting software or Excel cash book, extract all entries for the same period with the same columns.
3. Create two sheets in one workbook. Name them CashBook and BankStatement. Keep columns in the same order across both sheets.
4. Add a VLOOKUP match column. In CashBook column E, enter: =IFERROR(VLOOKUP(A2&"|"&C2,BankStatement!$A:$E,5,0),"NOT MATCHED") This concatenates the date and amount as a lookup key. Drag down for all rows, then mirror the formula in BankStatement looking up against CashBook.
5. Apply conditional formatting. Select the match column → Conditional Formatting → Highlight "NOT MATCHED" in red. Unmatched rows surface immediately.
6. Classify unmatched rows. Items unmatched on the bank statement only = add to cash book. Items unmatched in the cash book only = timing differences (outstanding cheques, deposits in transit).
7. Build the BRS summary sheet. Use SUMIF to total each reconciling category, then populate the format table. The final cell must show zero difference.

What are the most common reconciling items for Indian SMBs?

1. Bank service charges — debited quarterly or monthly; easily missed until the statement arrives.
2. GST on bank charges — 18% on most service fees, shown separately or bundled depending on the bank.
3. TDS on FD interest — 10% deducted by the bank under Section 194A when annual interest crosses ₹50,000 (₹1,00,000 for senior citizens) in FY 2025-26; appears on the statement but rarely in the cash book.
4. NEFT/IMPS/RTGS timing differences — transfers initiated late in the day often carry the next working day as the value date on the statement.
5. Dishonoured cheques — a bounced inward cheque triggers a reversal on the statement; both the original deposit and the reversal must be reflected.
6. ECS debits — standing instructions for EMIs, insurance, or utilities are easy to miss in the cash book.

Frequently asked questions

How often should I do a bank reconciliation? Monthly is the minimum — close your BRS within the first week of the following month. Businesses with high volumes benefit from weekly reconciliation.

My BRS doesn't balance — where do I start? First verify that the opening balance on both sides matches last month's closing reconciled balance. Then search for the exact difference as an unmatched item — a transposed amount (e.g., ₹1,254 booked as ₹1,524) is the most common culprit.

What are "deposits in transit"? Cash or cheques you have deposited and recorded in your cash book but that have not yet been processed by the bank — typically one to two working days for cheques.

Do I need to reconcile every bank account separately? Yes. Each account — current, savings, cash credit, overdraft — must be reconciled independently. One BRS cannot cover multiple accounts.

Can I use MATCH instead of VLOOKUP? Yes. =MATCH(A2,BankStatement!$A:$A,0) returns the row number of the match or an error if absent. INDEX/MATCH is more flexible for multi-column matching (date plus amount).

Does GST on bank charges give me input tax credit? Generally yes, if you are GST-registered and the charges relate to business use. The GST amount must appear clearly on the statement or a tax invoice from the bank before you claim ITC.

How Ankeshan helps

Ankeshan's Excel-based accounting module includes a pre-built BRS template with the VLOOKUP matching formula, conditional formatting, and a summary sheet already wired up. Import your bank CSV and cash book export, and unmatched rows surface automatically. Join the waitlist at ankeshan.com/waitlist to get early access.

Sources and disclaimer

• Institute of Chartered Accountants of India (ICAI) — guidance on bank reconciliation as part of internal controls
• Income Tax Act, 1961 — tax audit documentation requirements (Section 44AB)
• GST notifications on financial services — 18% rate on bank service charges
• Reserve Bank of India — NEFT/RTGS settlement timings

This article is for general information only and does not constitute accounting, legal, or tax advice. Consult a qualified chartered accountant for guidance specific to your business.

Related links

• Accounting in Excel — overview
• Cash book format in Excel
• General ledger template in Excel
• Financial year-end closing checklist