Cash Book / Day Book Template in Excel

⬇  Download the free cash book template (Excel) — no sign-up

Last updated: 27 June 2026

A cash book is the primary book of entry for every cash and bank transaction your business makes. For Indian SMBs, a double-column cash book in Excel — tracking both the cash drawer and the bank account on a single sheet with a running balance formula — covers most day-to-day bookkeeping needs without specialist software. Download Ankeshan's free template (no sign-up required) and follow the steps below to set it up correctly.

Key takeaways

• A cash book doubles as both a journal and a ledger for cash and bank transactions — you do not need a separate cash ledger.
• The double-column format (cash + bank) is the recommended starting point for most Indian SMBs; it tracks both sides in one view.
• Excel formula =H1+D2+E2-F2-G2 calculates a running balance after every entry, eliminating manual totalling errors.
• Under Section 44AA, a cash book is one of the specified books of accounts that prescribed businesses must maintain.
• Under Section 40A(3), any cash payment exceeding ₹10,000 to a single person in a single day is disallowed as a deductible expense — your cash book is the first place a tax auditor checks.
• Keep petty cash on a separate sheet or sub-table; merge totals into the main book only at month-end.
• Add a GST Invoice No. column in Narration so each entry is traceable to its return filing.

What exactly is a cash book, and how does it differ from a day book?

A cash book records only cash and bank transactions. A day book (also called a journal) records every transaction — cash, credit, adjustments — before posting to ledgers. In practice, many small businesses use the terms interchangeably because, for a cash-only operation, the cash book is the day book.

The key distinction matters at tax time: if your auditor asks for the "day book," producing a complete cash book plus a sales/purchase register together satisfies the requirement for most small traders.

What are the three types of cash books?

The double-column format is the practical default. It lets you reconcile your bank statement directly against the book and flags discrepancies within seconds.

What does the recommended double-column layout look like?

Set up one row per transaction with the following columns:

Row 1 holds headers. Row 2 onward holds transactions. Put opening balances in a dedicated row (label it "Opening Balance") so the formula chain starts cleanly.

How do I build the running balance formula?

Step-by-step

1. In H2 (cash balance), enter the opening cash balance as a plain number.
2. In I2 (bank balance), enter the opening bank balance as a plain number.
3. From H3 onward, enter:

=H2+D3-F3

This reads: previous cash balance + cash received − cash paid.

4. From I3 onward, enter:

=I2+E3-G3

This reads: previous bank balance + bank receipt − bank payment.

5. If you want a single combined formula that handles both columns in one go (useful when you merge cash and bank into one balance column), use:

=H1+D2+E2-F2-G2

6. Copy both formulas down the entire sheet. Excel will auto-adjust the row references.

Fact box. Section 44AA of the Income Tax Act lists the books of account that specified professionals and businesses must maintain. A cash book is explicitly named. Failure to maintain prescribed books can attract a penalty of up to ₹25,000 under Section 271A.

Protecting the formula columns

Lock columns H and I (Format Cells → Protection → Locked, then Review → Protect Sheet) so an accidental keystroke does not overwrite a balance mid-month. Leave columns A–G and J unlocked for daily data entry.

How do I separate petty cash from the main cash book?

Petty cash is a small float — typically around ₹2,000–₹5,000 — kept in a tin or drawer for minor daily expenses. Mixing it with the main cash book distorts your running balance.

Recommended approach in Excel:

1. Create a second sheet named Petty Cash with an identical column layout.
2. At the start of each week (or fortnight), record a single entry in the main cash book: "Petty Cash Imprest — Cr Cash" for the float amount handed over.
3. The petty cashier records every small spend in the Petty Cash sheet.
4. At month-end, total all petty cash payments and bring them into the main book as a single summary line: "Petty Cash Expenses — Dr various heads".

This keeps the main book clean while still giving you a full audit trail on the petty cash sheet.

How does the cash book help me comply with Section 40A(3)?

Section 40A(3) of the Income Tax Act disallows the deduction for any payment exceeding ₹10,000 in cash to a single person on a single day. The limit applies per person per day, not per invoice.

Your cash book is where this gets checked — a tax auditor will scan the Cash Cr column for large single-day entries against one party.

Practical tips:

• Add a conditional formatting rule: highlight any Cash Cr entry above ₹10,000 in amber.
• In the Narration column, always note the payee PAN when the cash payment is between ₹5,000 and ₹10,000 — good habit before you cross the threshold.
• For any payment above ₹10,000 that you genuinely had to make in cash (for example, in areas with no banking), note the exception and keep documentary evidence; certain exceptions are allowed under Rule 6DD of the Income Tax Rules.

Fact box. Section 40A(3) disallows cash payments exceeding ₹10,000 to a single person in a single day as a business expense deduction. For payments made for plying, hiring, or leasing goods carriages (transporters), the limit is ₹35,000 per day. Tax audit is triggered when turnover exceeds ₹1 crore (or ₹10 crore if 95% or more of receipts and payments are through banking channels).

How do I make my cash book GST-ready?

GST does not require a separate cash book format, but linking each entry to its GST invoice number saves hours at return time.

Minimum additions:

• Column J — GST Inv / Bill No.: Enter the supplier invoice number for purchase payments and your own invoice number for receipts.
• Column K — GST Rate: Enter Nil / 5% / 18% / 40% as applicable (the four GST slabs since 22 September 2025, when the 12% and 28% slabs were abolished; 40% applies to a notified list of sin/luxury goods).
• Column L — GST Amount: Enter the GST component of the transaction; this helps reconcile GSTR-2B credits.

When you run your monthly GSTR-3B, you can filter this sheet by GST Rate and sum column L to cross-check your input and output tax totals before filing.

Frequently asked questions

What is the difference between a cash book and a bank statement? A bank statement is issued by your bank and shows only transactions through your bank account from the bank's point of view. A cash book is your own record and includes both physical cash transactions and bank transactions. The bank balance in your cash book should match the bank statement after adjusting for outstanding cheques and deposits in transit — that reconciliation process is called a bank reconciliation.

How often should I update my cash book? Update it daily. A cash book that is three days behind is almost useless for catching errors or spotting a missing payment. Set aside ten minutes at the end of each business day to post that day's receipts and payments.

Is a day book the same as a cash book? Not exactly, but for a small cash-and-carry business they are often used interchangeably. A day book covers all transactions; a cash book covers only cash and bank. If your business also sells on credit, maintain a separate sales day book and purchase day book alongside the cash book.

What voucher numbering system should I use? Use a simple sequential prefix system: CB-2526-001, CB-2526-002 (CB = cash book, 2526 = financial year 2025–26). Reset the sequence each April 1. Keep physical vouchers in a numbered folder matching these references — auditors expect to trace every cash book entry back to a signed voucher.

Do I need a separate cash book for each bank account? You can add extra columns — Bank 2 Dr / Bank 2 Cr / Balance Bank 2 — for a second account, or create a second sheet. Most SMBs find a separate sheet cleaner once they have more than one operating account.

Can the cash book serve as proof during a GST audit? It is supporting evidence, not primary proof. GST auditors will rely primarily on your invoices, GSTR returns, and e-way bills. However, a well-maintained cash book that matches your bank statements strengthens your overall record-keeping credibility significantly.

How Ankeshan helps

Ankeshan's Excel add-in includes a pre-built double-column cash book with the running balance formula already in place, petty cash sheet, and a one-click month-end summary. The cash book links directly to the bank reconciliation module so discrepancies surface automatically. If you want early access, join the waitlist at ankeshan.com/waitlist.

Sources and disclaimer

• Income Tax Act 1961, Section 44AA (books of account for prescribed persons)
• Income Tax Act 1961, Section 40A(3) (disallowance of cash payments)
• Income Tax Rules 1962, Rule 6DD (exceptions to Section 40A(3))
• CBDT guidance on tax audit thresholds

Disclaimer: This article is for general information only and does not constitute legal, tax, or accounting advice. Tax rules change; verify figures with a qualified CA before acting on them.

Related reading

• Accounting in Excel — hub
• Double-entry bookkeeping in Excel for Indian SMBs
• Petty cash book template in Excel
• Bank reconciliation statement in Excel