A daily cash flow template in Excel tracks cash coming in and going out each day, then calculates the cash balance left at day’s end. For a simple Microsoft 365 worksheet, use columns for Date, Opening Cash, Cash In, Cash Out, Net Cash Flow, and Closing Cash; enter the starting balance and daily transactions, then use formulas to calculate net flow and carry each closing balance into the next day.
What a daily cash flow template shows
Cash flow is the movement of money into and out of an account or business during a period. A daily view makes the timing visible: a bill due on Tuesday can matter even if a customer payment is expected on Friday. The template is therefore useful for monitoring liquidity—the cash available to meet near-term payments—not just for summarizing whether a business made a profit.
A basic daily sheet records one row per date. You can record one combined figure for all receipts and payments that day, or add a transaction log with one row per movement. The combined approach is quick to update; a transaction log provides more detail for checking where a total came from. Either approach should make it possible to reconcile the worksheet with bank activity or another reliable record.
Cash flow is not the same as profit. Profit may include sales made on credit before the customer pays, and expenses recognized before cash leaves the bank. A daily cash tracker focuses on when cash is received or paid. It is not a substitute for accounting statements, tax records, or a bank reconciliation.
Set up the worksheet
In a blank workbook, create these headings in row 1: Date, Opening Cash, Cash In, Cash Out, Net Cash Flow, and Closing Cash. Put the first date in A2 and the cash available at the start of that date in B2. Enter actual or forecast receipts in C and payments in D. Use one consistent currency and decide whether the sheet tracks one bank account, several accounts together, or cash on hand as well.
With the headings in A1:F1, dates in column A, and receipts and payments entered in C2 and D2, enter this in E2 to calculate that date’s net movement: =C2-D2. If receipts are 1,200 and payments are 1,700, the result is -500, meaning cash fell by 500 that day.
With the opening balance in B2 and the net movement calculated in E2, enter this in F2 to calculate the closing balance: =B2+E2. If the opening balance is 5,000 and net cash flow is -500, the closing balance is 4,500. The arithmetic is 5,000 minus 500.
With the prior day’s closing balance in F2, enter this in B3 so the next row starts with the previous day’s ending cash: =F2. Copy the formulas in E2, F2, and B3 down the rows you need. For each later row, the opening balance should refer to the preceding row’s closing balance. Enter the first opening amount as a value, then let the linked formula carry it forward.
In Excel for Microsoft 365 on Windows, format the date column as a date and the money columns as currency or number with two decimal places, as appropriate. Select the heading row and use Home > Bold to distinguish it. You can use View > Freeze Panes > Freeze Top Row to keep headings visible while scrolling. On Mac, the ribbon may look different, but the same worksheet concepts and formulas apply; use the corresponding View and Home controls.
Choose a daily tracking method
There are two practical ways to enter transactions. Choose one based on the detail you need and the time available to maintain the workbook.
- Daily totals: Enter one total for receipts and one for payments per date. This is simple for a personal cash check or a small operation with few transactions, but it does not identify each payment.
- Transaction log: Record each transaction with its date, description, category, and amount, then summarize the entries by day. This creates a useful audit trail, but requires more consistent data entry and a summary method.
- Separate accounts: Track each bank account or cash pool on its own sheet or in clearly labeled columns. Avoid combining balances unless you can reliably distinguish transfers between accounts from genuine income or spending.
If you want to total a transaction log, keep the log’s date, category, and amount fields consistent. In a simple daily-total design with dates in A2:A31, receipts in C2:C31, and payments in D2:D31, this formula in C32 totals the receipt entries: =SUM(C2:C31). If the entries are 1,200, 800, and 500 and the rest of the cells are blank or zero, the result is 2,500. With payments in D2:D31, this formula in D32 totals those payments: =SUM(D2:D31). For payments of 1,700 and 300, it returns 2,000. These totals can help check a period, but they do not replace the daily balances.
Separate actual cash from forecasts
A cash flow template becomes more useful when it distinguishes money already received or paid from amounts that are only expected. A forecast can include scheduled invoices, payroll, rent, loan payments, and planned purchases, but the expected date may change. Label entries as Actual or Forecast, or maintain separate actual and forecast columns, and update forecast entries when the transaction clears.
For example, suppose the opening cash on Monday is 5,000. You expect 1,200 from a customer and 1,700 in bills, so the forecast closing balance is 4,500. If the customer pays only 900 that day, actual closing cash is 4,200, assuming the bills were paid in full. Keep the expected 1,200 and actual 900 distinguishable; otherwise, the worksheet may imply that cash is available before it arrives.
For a multi-day forecast, enter dates for calendar days or only for operating days, but be consistent. Calendar-day rows can show weekends when automatic payments may still occur; business-day rows are easier to scan but can conceal weekend timing. Record the actual transaction date when known, rather than silently shifting a payment to make the schedule look tidy.
Make the template easier to maintain
Good structure prevents many spreadsheet errors. Use one row per date in the daily summary, keep dates in chronological order, and avoid inserting explanatory notes among the numeric rows. Put assumptions—such as the opening cash amount or the forecast period—in clearly labeled cells above or beside the table. Give every transaction a short description in a separate log rather than typing text into a cash amount cell.
Useful checks include:
- Compare the calculated closing balance with the bank or cash record at the end of each day or reporting period.
- Look for blank dates, duplicate dates, or gaps that could omit a transaction day.
- Check whether an outflow was entered as a positive amount in Cash Out. The net formula subtracts that column, so entering an outflow as a negative number would incorrectly increase net cash flow.
- Confirm that transfers between accounts are not counted as new income or spending when both accounts are included in one combined view.
- Keep a saved copy before making major structural changes, and protect or clearly mark formula cells so routine data entry does not overwrite them.
You can format a range as a table using Home > Format as Table in Excel for Windows, which helps keep headings visible and extends formatting as rows are added. If using that feature, verify that formulas fill into new rows as expected. Conditional formatting can call attention to low or negative balances, but it does not change the underlying figures. Set any warning threshold to a level that makes sense for the account being tracked.
Common problems and how to avoid them
A frequent mistake is treating a positive forecast as guaranteed cash. A scheduled customer payment is not cash in the bank until it is received. Another is mixing cash movements with accounting categories: a sale may be recorded when invoiced, while the cash arrives later. Use expected and actual labels, and update the worksheet from reliable records rather than relying on memory.
Formula errors often come from inconsistent row references, overwritten formulas, or an incorrect sign convention. In the layout above, Cash In and Cash Out are entered as positive amounts, and Net Cash Flow subtracts Cash Out from Cash In. If your organization uses a different convention, adjust the formula and label the columns clearly. Avoid manually typing a new closing balance each day, because that can break the link between one day and the next.
Dates can also cause confusion. A date displayed as 4/5 may mean April 5 or May 4 depending on regional settings. Use an unambiguous date format where possible and confirm that Excel recognizes the entry as a date, not text. Blank days may be legitimate, but deciding whether to include them makes it easier to compare balances and spot missing records.
Template options and alternatives
You can build the worksheet yourself, adapt an existing Excel cash flow template, or use bookkeeping software that records transactions and produces cash reports. A blank workbook offers control over categories and layout, but you must create and check the formulas. A prebuilt template saves setup time, though its categories, assumptions, and formulas may not match your needs. Inspect formulas and sample data before relying on any downloaded workbook.
A transaction log in Excel can work well when you need flexible, small-scale tracking. Bookkeeping or cash-management software may be more appropriate when multiple people enter transactions, several accounts must be reconciled, or you need integrations and controlled access. Excel does not automatically know whether a transaction has cleared unless you import or enter that information. Do not assume a template is connected to your bank simply because it contains a balance column.
When evaluating a template, look for editable categories, a clear distinction between actuals and forecasts, visible opening and closing balances, and formulas that can be traced. Consider how often it needs updating and who will maintain it. The best layout is one that can be kept current and checked against source records, rather than the most elaborate design.
Frequently Asked Questions
Is there a daily cash flow template in Excel?
Excel may offer templates through its template gallery, but availability can vary. You can also create a basic daily tracker with Date, Opening Cash, Cash In, Cash Out, Net Cash Flow, and Closing Cash columns. Check a downloaded template’s formulas and assumptions before entering real information.
What formula calculates daily cash flow?
With daily receipts in C2 and payments in D2, =C2-D2 calculates the net cash movement for that row. Enter receipts and payments as positive values under the layout described above. A negative result means payments exceeded receipts for the day.
How do I carry the balance forward to the next day?
With the prior day’s closing balance in F2, use =F2 in B3 as the next day’s opening balance. The closing balance for a day is its opening balance plus that day’s net cash flow, so check that the row references remain aligned when copying formulas.
Can I use the template for personal finances?
Yes. You can track cash movements for a household by using personal categories such as pay, bills, groceries, and transfers. Decide whether the sheet covers one account or all household cash accounts, and avoid counting transfers between included accounts as income or spending twice.
What is the difference between cash flow and a budget?
A budget sets planned income and spending over a period; a cash flow schedule focuses on when money is expected to arrive or leave and what balance remains. A daily cash flow sheet can include budget expectations, but it should mark estimates clearly and compare them with actual transactions.
How often should I update a daily cash flow spreadsheet?
Update it often enough that the balance is useful for decisions—daily for close monitoring, or after transactions for a busy account. Reconcile the figures with bank records regularly. The right frequency depends on transaction volume and how quickly the information becomes stale.
A daily cash flow template is most dependable when its date range, account scope, and actual-versus-forecast rules are clear. Start with the simple six-column layout, verify the formulas against a small example, and compare recorded balances with source records before using the worksheet to plan around upcoming receipts or payments.
Related
- Convert Excel to ZIP: How to Compress and Share a Workbook
- Convert an Excel Binary Worksheet (.XLSB) to Excel
- Content Calendar Template Excel: Create and Use One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Array Formulas: How They Work and When to UseIn Google Sheets, an array formula processes a range of cells and can return multiple results from one…
- Shortcut Key to Insert a Checkbox in Microsoft WordIn Word for Microsoft 365 on Windows, type 2610 and press Alt+X to insert an empty checkbox symbol…
- Excel Merge Multiple Cells Into One Cell: A Clear GuideTo merge cells in Excel, select the range, then choose Home > Merge & Center ▼ > Merge…
- Attendance Point Tracking Excel Template?An attendance point tracking Excel template is a workbook that logs each attendance event, assigns the point value…
- Excel Import CSV Without Formatting: Keep Data RawTo import a CSV into Excel without unwanted formatting, use Data > From Text/CSV, choose Transform Data, set…
- How to Add a Black Border in Excel to Cells and TablesTo add a black border in Excel, select the cell or range, open Home > Borders > More…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.