Bank Reconciliation Step by Step

Your books show one bank balance and the bank statement shows another. Both can be right at the same time, and a bank reconciliation is how you prove it. In this lesson we take one month of a small Indian company, match its cash book with its bank statement, explain every difference and pass the journal entries that correct the books. Then we do the same work in Excel and see how an ERP helps.

What you will learn

• What a bank reconciliation is and why it is done every month
• The three kinds of differences: timing differences, bank-first items and errors
• How to prepare a reconciliation statement, with a full worked example in rupees
• Which differences need a journal entry and which do not
• How to match the two lists in Excel with COUNTIFS, XLOOKUP, SUMIFS and conditional formatting
• How SAP and Oracle handle it, and what a reviewer checks

What a bank reconciliation is

A cash book (or the bank account in your general ledger) is your own record of money going in and out of a bank account. A bank statement is the bank’s record of the same account. A bank reconciliation compares the two on the same date and explains every difference, so you know the true bank balance.

It catches typing mistakes, shows charges and receipts you have not booked yet, and spots fraud early, like a cheque you never wrote being paid. Auditors test it first. In the Record to Report cycle it is a month-end close step, done before the trial balance is final.

Why the two balances are different

Every difference falls into one of three groups.

1. Timing differences. You recorded it, the bank has not processed it yet. A deposit in transit is a cheque you deposited on the last days of the month that the bank credits next month. An outstanding cheque is a cheque you issued that the vendor has not presented yet. No journal entry. The bank will catch up.
2. Bank-first items. The bank recorded it before you knew: bank charges, NACH auto debits (like loan EMIs or utility bills), a customer paying straight into your account by NEFT, or a cheque you deposited that bounced (a dishonoured cheque). Your books must catch up with a journal entry.
3. Errors. If the error is in your books (a cheque for ₹6,750 entered as ₹6,570), pass a correcting entry. If the error is the bank’s (it debits another customer’s cheque to you), do not pass an entry. List it, write to the bank and check it is reversed.

Type of difference	Journal entry in your books?	Stays on the reconciliation?
Deposit in transit	No	Yes, add to bank balance
Outstanding cheque	No	Yes, subtract from bank balance
Bank charges, NACH debits	Yes	No, once booked
Direct credit by a customer	Yes	No, once booked
Dishonoured cheque	Yes	No, once booked
Error in your books	Yes, a correcting entry	No, once booked
Error by the bank	No	Yes, until the bank reverses it

Reading the flowcharts in this lesson

Every flowchart here uses standard symbols. Read each chart from top to bottom. On a decision, Yes continues down and No goes to the side.

Symbol	Name	What it means
Rounded box	Start or End	Where the process begins or finishes
Rectangle	Step	One action by a person or the system
Diamond	Decision	A yes or no question
Slanted box	Input or Output	Data coming in or going out, like a downloaded statement
Box with a wavy bottom	Document	A report or form, like the signed reconciliation
Cylinder	System or Data	A system or database, like SAP or Oracle

This chart shows how to sort any unmatched line.

Lines that appear only on the bank statement follow this chart.

The full process

This is what a finance team does every month for every bank account.

1. Make sure every receipt and payment of the month is in the cash book.
2. Download the statement for exactly the same dates, in Excel or CSV, not PDF.
3. Check last month’s reconciling items cleared this month.
4. Tick off matching lines on amount, and on cheque number or reference where you have one.
5. Sort what is left into the three groups, pass the entries and prepare the statement.

Worked example: Sharma Traders, September 2026

Sharma Traders Pvt Ltd has a current account with a bank. On 1 September 2026, the cash book and the statement both show ₹2,50,000, so there are no old items.

The cash book (bank column)

Date	Reference	Details	Receipt (₹)	Payment (₹)
01-Sep		Opening balance	2,50,000
03-Sep	NEFT	Gupta Electricals, customer	1,20,000
05-Sep	NEFT	Office rent		75,000
10-Sep	Chq 004501	Kapoor Suppliers		48,000
12-Sep	Deposit	Cheque from Mehta Stores	85,000
14-Sep	Chq 004502	Electricity bill		6,570
18-Sep	Chq 004503	Verma Packaging		32,000
22-Sep	Cash	Cash sales deposited	40,000
25-Sep	NEFT	Salaries		1,10,000
28-Sep	Chq 004504	Joshi Logistics		18,500
29-Sep	Deposit	Cheque from Rao Enterprises	60,000
		Totals	5,55,000	2,90,070
30-Sep		Closing balance	2,64,930

Check: ₹5,55,000 minus ₹2,90,070 is ₹2,64,930.

The bank statement

On a statement, a debit takes money out and a credit puts money in.

Date	Details	Debit (₹)	Credit (₹)	Balance (₹)
01-Sep	Opening balance			2,50,000
03-Sep	NEFT Gupta Electricals		1,20,000	3,70,000
05-Sep	NEFT Office rent	75,000		2,95,000
13-Sep	Clearing, cheque Mehta Stores		85,000	3,80,000
13-Sep	Chq 004501 paid	48,000		3,32,000
16-Sep	Cheque returned unpaid, Mehta Stores	85,000		2,47,000
17-Sep	Chq 004502 paid	6,750		2,40,250
20-Sep	NACH debit, water charges	1,240		2,39,010
22-Sep	Cash deposit		40,000	2,79,010
25-Sep	NEFT Salaries	1,10,000		1,69,010
26-Sep	NEFT Iyer and Sons		25,000	1,94,010
27-Sep	Chq 118834 paid	12,000		1,82,010
30-Sep	Service charges including GST	590		1,81,420
	Totals	3,38,580	2,70,000	1,81,420

Check: ₹2,50,000 plus ₹2,70,000 minus ₹3,38,580 is ₹1,81,420.

The books say ₹2,64,930, the bank says ₹1,81,420. We must explain a gap of ₹83,510.

Step 1: Tick off and sort

These match on both sides: Gupta Electricals, rent, cheque 004501, the Mehta Stores deposit, the cash deposit and salaries. Everything else is a reconciling item.

No.	Item	Group	Action
1	Rao Enterprises cheque ₹60,000, books only	Deposit in transit	List, no entry
2	Chq 004503 Verma Packaging ₹32,000, books only	Outstanding cheque	List, no entry
3	Chq 004504 Joshi Logistics ₹18,500, books only	Outstanding cheque	List, no entry
4	Mehta Stores cheque returned ₹85,000	Dishonoured cheque	Journal entry
5	NACH water charges ₹1,240	Bank-first item	Journal entry
6	NEFT from Iyer and Sons ₹25,000	Direct credit	Journal entry
7	Service charges ₹590	Bank charges	Journal entry
8	Chq 004502: ₹6,750 in bank, ₹6,570 in books	Error in books, ₹180	Journal entry
9	Chq 118834 ₹12,000	Error by bank	List, write to bank

For item 8, the cheque counterfoil and the electricity bill both say ₹6,750, so the clerk swapped two digits. For item 9, the company’s cheque book runs from 004501 to 004550, so 118834 is someone else’s cheque.

Step 2: Correct the cash book

Adjusted cash book balance	₹	₹
Balance as per cash book		2,64,930
Add: NEFT from Iyer and Sons		25,000
Less: Mehta Stores cheque dishonoured	85,000
Less: NACH water charges	1,240
Less: Bank service charges	590
Less: Cheque 004502 entered short	180
Total to subtract		87,010
Correct balance for the books		2,02,920

Check: ₹2,64,930 plus ₹25,000 minus ₹87,010 is ₹2,02,920.

Step 3: Adjust the bank balance

Adjusted bank balance	₹	₹
Balance as per bank statement		1,81,420
Add: Deposit in transit, Rao Enterprises	60,000
Add: Wrong debit by bank, cheque 118834	12,000
Total to add		72,000
Less: Cheque 004503, Verma Packaging	32,000
Less: Cheque 004504, Joshi Logistics	18,500
Total to subtract		50,500
Adjusted bank balance		2,02,920

Check: ₹1,81,420 plus ₹72,000 minus ₹50,500 is ₹2,02,920.

Both sides show ₹2,02,920, so the account is reconciled. This is the bank balance that goes into the balance sheet on 30 September.

The single-column format

Many Indian textbooks start from the cash book balance and arrive at the bank balance in one column. Same items, same answer.

Bank reconciliation statement as on 30 September 2026	₹	₹
Balance as per cash book		2,64,930
Add: Cheques issued but not presented (32,000 + 18,500)	50,500
Add: Direct credit by Iyer and Sons	25,000
Total to add		75,500
Less: Cheque deposited but not credited	60,000
Less: Cheque dishonoured, Mehta Stores	85,000
Less: NACH water charges	1,240
Less: Bank charges	590
Less: Cheque 004502 entered short	180
Less: Wrong debit by bank	12,000
Total to subtract		1,59,010
Balance as per bank statement		1,81,420

Check: ₹2,64,930 plus ₹75,500 minus ₹1,59,010 is ₹1,81,420, which is the statement balance.

Journal entries for the adjustments

Only bank-first items and our own error go into the books.

Entry	Account	Debit (₹)	Credit (₹)
1. Direct credit	Bank account	25,000
	Iyer and Sons (trade receivable)		25,000
2. Dishonoured cheque	Mehta Stores (trade receivable)	85,000
	Bank account		85,000
3. NACH debit	Water charges expense	1,240
	Bank account		1,240
4. Bank charges	Bank charges expense	500
	Input GST receivable	90
	Bank account		590
5. Correction	Electricity expense	180
	Bank account		180
	Totals	1,12,010	1,12,010

Entry 2 puts the ₹85,000 back on Mehta Stores’ account, because the customer owes it again. In entry 4, the ₹590 is ₹500 of charges plus 18% GST of ₹90.

Proof: the bank account gets ₹25,000 of debits and ₹87,010 of credits, so it moves from ₹2,64,930 to ₹2,02,920, the reconciled figure. The bank’s error (cheque 118834) gets no entry, only a letter to the bank.

Following up old items

A deposit in transit should be credited in a few working days. If not, ask the bank at once, the cheque may be lost or returned. A cheque in India is valid for three months from its date. After that the bank will not pay it and it is a stale cheque.

If cheque 004503 to Verma Packaging is still open after three months, reverse it. You still owe the vendor.

Account	Debit (₹)	Credit (₹)
Bank account	32,000
Verma Packaging (trade payable)		32,000

Doing it in Excel

Copy the cash book lines (without the opening balance) to a sheet called Book and the statement lines to a sheet called Bank. Use four columns on both: A Date, B Ref (cheque number, or NEFT, DEP, CASH and so on), C Details, D Amount. Sign amounts the same way on both sheets: money in is positive, money out is negative. Book data sits in rows 2 to 11, bank data in rows 2 to 13.

Step 1: Find unmatched lines with COUNTIFS

1. On Bank, type In book? in E1.
2. In E2 type =COUNTIFS(Book!$D$2:$D$11,D2) and copy it down to E13.
3. On Book, type In bank? in E1, then =COUNTIFS(Bank!$D$2:$D$13,D2) in E2, copied down to E11.

A 0 means “not on the other side”. On Bank you get 0 against −85,000, −6,750, −1,240, 25,000, −12,000 and −590. On Book you get 0 against −6,570, −32,000, −18,500 and 60,000. That is our list of reconciling items.

Step 2: Find amount differences with XLOOKUP

1. On Bank, type Book amount in F1.
2. In F2 type =XLOOKUP(B2,Book!$B$2:$B$11,Book!$D$2:$D$11,"Not found") and copy it down.
3. For cheque 004502 (row 7), type =D7-F7 in G7. It shows −180.

XLOOKUP finds the same cheque number in the books. The last argument is shown when there is no match, instead of an error. Use this only for cheque numbers, because a reference like NEFT appears many times.

Step 3: Colour the unmatched lines

1. On Bank, select A2:E13.
2. Select Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format and type =$E2=0.
4. Select Format, choose a light red fill, then OK and OK.

Repeat on Book. The $ before E keeps every column of the row checking column E.

Step 4: Prove it with SUMIFS

• On Bank: =SUMIFS(D2:D13,E2:E13,0) gives −80,580.
• On Book: =SUMIFS(D2:D11,E2:E11,0) gives 2,930.

Both sides started at ₹2,50,000 and share the same matched lines, so removing the unmatched lines must give the same figure. Book: 2,64,930 − 2,930 = 2,62,000. Bank: 1,81,420 + 80,580 = 2,62,000. They agree, so nothing was missed. If they do not, a line matched the wrong partner.

Bank reconciliation in an ERP

In an ERP, the bank sends the statement as a file, the system matches most lines using rules, and you only work on what is left.

• SAP FF_5 imports an electronic bank statement file.
• SAP FF67 is for entering a statement by hand.
• SAP FEBAN shows imported statement lines so you can finish the ones that did not post or clear.
• Oracle Cash Management loads bank statements, reconciles them automatically with matching rules, and lets you reconcile the rest by hand.

Many SAP setups use a main bank GL account plus clearing accounts, for example one for outgoing cheques. A payment sits in the clearing account until it appears on the statement, so what is left there at month end is your list of timing differences.

Controls and review

• Every account is reconciled within the days set in the close calendar.
• The preparer does not make payments or handle cash (segregation of duties).
• Preparer and reviewer both sign and date it, with the statement and proof attached.
• No unexplained difference, not even one rupee.
• Items older than a month have an owner and a note. Watch cheques nearing three months.
• Unusual items are questioned: a cheque number outside your cheque book, or a NACH mandate nobody knows.

Common mistakes

• Using a statement for different dates than the books.
• Passing entries for deposits in transit or outstanding cheques.
• Passing an entry for the bank’s error.
• Treating a dishonoured cheque as an expense instead of putting it back on the customer.
• Plugging a small gap to “miscellaneous”.
• Mixing up signs between the two sheets in Excel.

Quick recap

• A bank reconciliation explains every difference between the cash book and the statement on one date.
• Timing differences need no entry, bank-first items do, and errors need one only if they are yours.
• Adjust both balances until they agree. That figure goes into the balance sheet.
• Every adjusting entry balances, debits equal to credits.
• In Excel, COUNTIFS finds unmatched lines, XLOOKUP finds amount differences, conditional formatting shows them and SUMIFS proves you found them all.

Try it yourself

Krishna Foods Pvt Ltd has a cash book balance of ₹1,48,600 on 31 October 2026. The bank statement shows ₹1,46,576. You find:

1. Cheque deposited on 31 October, not yet credited: ₹22,000
2. Cheque 002210 issued on 29 October, not yet presented: ₹15,500
3. Bank charges ₹354 (₹300 plus 18% GST of ₹54), not in the books
4. A customer paid ₹9,000 by NEFT, not in the books
5. A NACH loan EMI debit of ₹4,800, not in the books (treat it all as loan repayment)
6. Cheque 002205 for ₹7,290 was entered in the cash book as ₹7,920

Prepare both adjusted balances and the journal entries.

Answers

Item 6: the books took off ₹7,920 but only ₹7,290 left the bank, so add back ₹630.

Adjusted cash book balance	₹
Balance as per cash book	1,48,600
Add: NEFT from customer	9,000
Add: Cheque 002205 entered ₹630 too high	630
Less: Bank charges	354
Less: NACH loan EMI	4,800
Adjusted cash book balance	1,53,076

Adjusted bank balance	₹
Balance as per bank statement	1,46,576
Add: Deposit in transit	22,000
Less: Outstanding cheque 002210	15,500
Adjusted bank balance	1,53,076

Check: 1,48,600 + 9,000 + 630 − 354 − 4,800 = 1,53,076, and 1,46,576 + 22,000 − 15,500 = 1,53,076. They agree.

Entry	Account	Debit (₹)	Credit (₹)
a	Bank account	9,000
a	Customer (trade receivable)		9,000
b	Bank account	630
b	Vendor of cheque 002205 (trade payable)		630
c	Bank charges expense	300
c	Input GST receivable	54
c	Bank account		354
d	Loan account	4,800
d	Bank account		4,800
	Totals	14,784	14,784

Entry b credits the vendor because the vendor was debited ₹7,920 when the payment was booked, but was paid only ₹7,290.

Where to go next

• Record to Report RTR: where bank reconciliation fits in the month-end close.
• Three-Way Match in Procure to Pay: the check on a vendor bill before the payment that later shows on your statement.
• AI for Month-End Close: A Practical Guide: how AI can speed up matching, and what still needs a person.
• Lookup function: more practice with lookups.