A daily cash flow report template in Excel lists each day’s opening cash, money received, money paid, net cash movement, and closing cash. In a basic worksheet, enter receipts in column C and payments in column D; in E5 use =C5-D5 for net cash flow, and in F5 use =B5+E5 for closing cash. This gives you a practical starting template to build, adapt, and use in Microsoft Excel for Microsoft 365 on Windows.
What a daily cash flow report shows
A cash flow report tracks when money actually enters or leaves an account or cash pool. Its purpose is to show available cash by date, rather than to measure whether a business is profitable. A sale made on credit may count as revenue in an accounting report but does not become a cash receipt until a customer pays. Likewise, paying a supplier may reduce cash on the payment date even if the expense was recorded earlier.
This timing matters for day-to-day decisions: whether there is enough available cash for payroll, rent, supplier payments, or an upcoming loan payment. A report can also help identify a shortfall early, while there is still time to follow up on overdue invoices or schedule a payment appropriately.
Decide what cash the report covers before building it. It might track one bank account, a cash drawer, or the combined balance of several accounts. If it combines accounts, use consistent dates and include only transfers that change the total cash being tracked. A transfer between two included accounts is not a new receipt or payment for the combined total.
Suggested Excel template layout
Start a worksheet named Daily Cash Flow. Put the reporting period and the cash account or accounts covered near the top. Use row 4 for column headings and begin daily entries on row 5. This layout works for a month with up to 31 daily rows:
- A: Date
- B: Opening cash
- C: Cash receipts
- D: Cash payments
- E: Net cash flow
- F: Closing cash
- G: Notes or source, such as bank deposit, card settlement, or supplier payment
Use one row per calendar day, including days with no activity if a continuous daily balance is useful. Enter zero rather than leaving a blank in receipts or payments when there was no movement. That makes the report easier to scan and helps distinguish a genuine zero from missing information. Format columns B:F as currency, and format column A as a date. A clear date format such as 3-Nov-2026 can reduce ambiguity when the workbook is shared.
For the example below, suppose opening cash on the first day is $12,000. The business receives $3,500 and pays $4,200 that day. On the following day, it receives $1,000 and pays $1,600. The report should show closing balances of $11,300 and $10,700, respectively.
Enter the formulas and check the arithmetic
With the dates in A5:A35, opening balances in B5:B35, receipts in C5:C35, payments in D5:D35, net movement in E5:E35, and closing balances in F5:F35, enter the first day’s opening balance manually in B5. For the example, enter 12000. Enter 3500 in C5 and 4200 in D5.
With that layout and those first-day values, enter this in E5 to calculate receipts minus payments. The result is -700, because $3,500 minus $4,200 equals -$700.
=C5-D5
With the opening cash in B5 and net cash flow in E5, enter this in F5 to calculate the closing balance. The result is $11,300, because $12,000 plus the -$700 net movement equals $11,300.
=B5+E5
On the next day, enter 1000 in C6 and 1600 in D6. With the prior day’s closing balance in F5, enter this in B6 to carry the balance forward. It returns $11,300.
=F5
With the second day’s receipts and payments in C6 and D6, enter this in E6 to calculate the second day’s net movement. It returns -$600.
=C6-D6
With the second day’s opening balance in B6 and net movement in E6, enter this in F6. It returns $10,700: $11,300 minus $600. Copy the formulas in E5:F5 down for the remaining dates; for B6 and the rows below, copy the opening-balance formula down as well. In Excel for Microsoft 365 on Windows, use the fill handle at the lower-right corner of a selected cell to copy a formula into adjacent rows. Excel for Mac has the same fill-handle method.
Build the worksheet in Excel
In Excel for Microsoft 365 on Windows, type the headings into row 4, then select the heading row and the rows where you plan to enter data. Choose Home > Format as Table and select a style. Confirm that the table has headers. A table can make it easier to filter by date or notes and can extend formatting when you add rows. If you prefer a plain range, you can still use the formulas and copy them down.
Enter dates as actual Excel dates, not text that merely looks like a date. You can set the display using Home > Number Format, or right-click the date cells and choose Format Cells. Use a consistent currency format, with two decimal places if cents matter. If your organization uses multiple currencies, do not add them together as if they were the same currency; keep separate totals or convert them using an explicitly documented exchange-rate method.
For a monthly report, enter the dates in order and verify that there is one row per day in scope. A date series can be filled by entering the first two dates and dragging the fill handle, then checking that Excel continued the daily sequence. Leave a note if the reporting period starts or ends partway through a day, or if the balance is captured at a particular cut-off time. The cut-off affects which transactions belong to each date.
Daily actuals and cash forecasts
A report can contain actual transactions, expected cash movements, or both, but label them clearly. Actuals are amounts confirmed from a receipt, payment record, or account statement. Forecasts are estimates, such as an invoice expected to be paid on Friday. Treating a forecast as confirmed cash can make the closing balance appear more certain than it is.
One straightforward option is to keep two worksheets: Daily Actuals and Cash Forecast. Use the same column structure on each, but label the forecast dates and assumptions. For example, a customer’s expected $2,000 payment could be entered as a forecast receipt with a note identifying the invoice and expected payment date. When the money arrives, record it in actuals and update or remove the forecast entry to avoid counting it twice.
If you prefer one worksheet, add a Type column and label each movement Actual or Forecast. Keep the two categories distinguishable with filters or formatting, and make sure any totals you report state whether they include estimates. A forecast is most useful when it is updated as new information arrives; it is not a promise that cash will arrive or a substitute for checking the account.
Totals, review, and useful formatting
At the bottom of a reporting period, total receipts and payments separately. With receipts in C5:C35 and payments in D5:D35, the total receipts formula is =SUM(C5:C35) and the total payments formula is =SUM(D5:D35). For example, if those ranges contain the two days in the example and the remaining cells are zero, the results are $4,500 of receipts and $5,800 of payments. The net movement for those days is -$1,300.
The final closing balance should reconcile to the opening balance plus the period’s net cash movement, provided the report includes all relevant cash movements and does not double-count transfers. Compare the last reported closing balance with the bank or cash records for the same account and cut-off time. Investigate differences such as bank fees, pending card settlements, checks that have not cleared, or a transaction entered on the wrong date.
Use restrained formatting to make exceptions visible. You can select the closing-balance cells and choose Home > Conditional Formatting > Highlight Cells Rules to flag balances below a threshold you set. The threshold should reflect the organization’s own operating needs; there is no universal minimum cash balance. Keep the underlying values visible, and do not rely on color alone because the worksheet may be printed or viewed by someone with color-vision limitations.
Common mistakes and how to prevent them
- Confusing cash flow with profit: Profit and cash balance are different measures. A profitable period can still have a cash shortfall if customers pay late or substantial bills fall due first.
- Entering a payment as a positive receipt: Put payments in the payments column as positive amounts; the net formula subtracts that column. This consistent convention is easier to review than mixing positive and negative entries.
- Counting transfers twice: If the report covers both accounts involved in a transfer, the transfer does not change combined cash. If it covers only one account, show the transfer out or in as appropriate and document the scope.
- Leaving unexplained gaps: A missing date can look like a day with no activity. Include a row for each date or clearly state which dates are excluded.
- Overwriting formulas: Protect or visually distinguish formula columns, and periodically check that copied formulas still refer to the intended row. Keep a separate backup before making major structural changes.
- Ignoring pending transactions: A bank’s displayed balance and an internal available-cash figure may differ because of pending or uncleared items. Define which balance the report uses and apply the same rule consistently.
When Excel is enough and when to use another tool
Excel is a reasonable choice for a small operation or a personal cash tracker when one person can enter and review transactions, the report is not too complex, and the workbook can be maintained reliably. Its advantages are flexibility, familiar formulas, and the ability to adapt columns to the organization’s workflow. A simple worksheet also makes assumptions easy to inspect.
Spreadsheets require careful manual upkeep. As transaction volume, staff access, or reporting requirements grow, an accounting or cash-management system may provide a more dependable connection to bank activity, clearer approval controls, and a history of changes. Whether a particular system provides those features depends on its product and plan. A move to software does not remove the need to reconcile balances or define what the report includes.
There is no single required format for every daily cash report. A shop with cash and card settlements may need separate source notes; a service business may focus on customer receipts and payroll; a household tracker may need fewer categories. Keep the core balance calculation consistent, and add detail only when it helps someone understand or act on the cash position.
Frequently Asked Questions
Is there a free daily cash flow report template in Excel?
You can create a basic template in a blank workbook using the columns and formulas above. Microsoft also offers templates through Excel’s template selection, but availability and names can change; check the options shown in your own Excel installation. Review any downloaded template’s formulas and assumptions before relying on its totals.
How do I calculate daily closing cash in Excel?
Add the day’s net cash flow to its opening balance. If opening cash is in B5 and net movement is in E5, use =B5+E5 in F5. Calculate net movement as receipts minus payments, then carry the closing balance forward as the next day’s opening balance.
Should cash payments be entered as negative numbers?
In the layout described here, enter payments as positive numbers in a dedicated payments column. The net cash flow formula subtracts payments from receipts. Using negative payments as well would reverse the sign twice or make the calculation confusing unless the formulas were deliberately designed for that convention.
Can a daily cash flow report include expected income?
Yes, but mark expected amounts as forecasts rather than actual receipts. Identify the reason and expected date, update the entry when circumstances change, and avoid counting it again when the payment is received.
Why does my Excel closing balance not match the bank?
Check that both balances use the same account scope and cut-off time. Then look for omitted fees, uncleared payments, pending deposits, duplicate entries, transfers, or dates assigned to the wrong day. Reconcile each difference rather than changing a formula just to force the totals to match.
Related
- Checklist in Excel: Template Setup and Practical Tips
- Convert Percentage to Text in Excel: Methods and Tips
- Convert Text to Numbers in Excel: Methods That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Named Data Range: Create, Use, and Manage NamesAn Excel named data range is a descriptive name assigned to a cell or range of cells, so…
- Vendor Quotation Comparison Template in ExcelTo make a vendor quotation comparison template in Excel, list suppliers in columns B through D and put…
- How to Make Columns the Same Width in a Word TableTo make every column in a Word table the same width, select the whole table, then choose Layout…
- Excel Named Range Won’t Delete? Causes and Fixes to TryNo—Excel can delete most named ranges, which it calls defined names. In Excel for Microsoft 365 on Windows,…
- Excel Row Limit: How Many Rows Can a Worksheet Hold?In modern Microsoft Excel, one worksheet can hold a maximum of 1,048,576 rows, numbered 1 through 1,048,576. That…
- Google Sheets Duplicate Drawing: How to Copy a DrawingTo duplicate a drawing in Google Sheets, click the drawing once to select it, then press Ctrl+C and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.