Spreadsheets

Cash book with opening and closing balance formulas in Excel

Build a daily cash book in Excel where each day's opening balance carries over from yesterday's closing, with SUMIFS by date and a cash-in-hand check.

Editorial illustration for Cash book with opening and closing balance formulas in Excel

A cash book needs just three numbers per day: opening balance, what came in, what went out. Closing balance is opening plus receipts minus payments, and tomorrow's opening is simply today's closing. Two small sheets and a handful of formulas do it.

How is the workbook laid out?

Sheet one, called Entries, is the log: column A date, B particulars, C receipts, D payments. You type a row for every cash movement, one line each, for example cash received from a customer or cash paid for milk. Sheet two, called Daily, has one row per day: A date, B opening, C receipts, D payments, E closing, F counted cash, G difference.

What are the daily formulas?

Type the first date and the starting cash in B2 by hand. Everything after that is formula-driven. With dates in A2 downwards on the Daily sheet:

  • Receipts, C2: =SUMIFS(Entries!$C$2:$C$1000,Entries!$A$2:$A$1000,A2)
  • Payments, D2: =SUMIFS(Entries!$D$2:$D$1000,Entries!$A$2:$A$1000,A2)
  • Closing, E2: =B2+C2-D2
  • Next day's opening, B3: =E2
  • Next date, A3: =A2+1

Select B3:E3 and fill down for as many days as you need. Each day's opening picks up the previous closing without any retyping, so one mistake on day one does not silently spread.

A hypothetical week to test with: day one opens at ₹5,000, receives ₹2,500 and pays ₹1,840, so it closes at ₹5,660. Day two opens at ₹5,660, receives nothing and pays ₹920, so it closes at ₹4,740. Day three opens at ₹4,740, receives ₹3,000 and pays ₹1,250, so it closes at ₹6,490.

DayOpening (₹)Receipts (₹)Payments (₹)Closing (₹)
15,0002,5001,8405,660
25,66009204,740
34,7403,0001,2506,490

Why does SUMIFS by date sometimes show zero?

Because the date in Entries carries a time, or is text. SUMIFS matches the date exactly, and 08-Nov-2026 10:30 is not equal to 08-Nov-2026. Type dates without times, or clean existing ones with =INT(A2) in a helper column and point SUMIFS at that. If a typed date looks left-aligned, it is probably text.

How does the cash in hand check work?

At the end of the day, count the notes and coins and type the figure in column F. Then G2 is =F2-E2. Zero means the book and the box agree. A negative figure means cash is missing, or a payment was not written down; positive means a receipt was not written. For the example, if day three's counted cash is ₹6,450, the difference is 6,450 minus 6,490, so minus ₹40, which is worth chasing the same evening while you still remember.

Is there a one-sheet version?

Yes, for a household or a tiny stall. Add a Balance column E, use the first row for an Opening balance line with the amount in column E, then from the next row enter =E2+C3-D3 and fill down, so each balance is the previous one plus the receipt minus the payment. It is quicker, but you lose the daily summary and the neat check by date. Use it if you have just a few entries a day.

At the year end, the closing balance on the last day of March becomes the opening balance on 1 April. In the new file, type that figure once as a plain value, since there is no earlier sheet to link to. Note the source in a cell comment, so the figure can be traced later.

A short checklist

  • Opening balance typed once, never again.
  • One entry per cash movement, same day.
  • Count cash at the same time daily.
  • Never overwrite a formula in column B.
  • Back up the file weekly.

This book covers cash only. For card and UPI spending, SpendixAI takes bank and card statement files, and you can add cash expenses by hand, singly or several at once, so cash and statements end up in one place.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports