A practical cash flow format in Excel lists opening cash, cash inflows, cash outflows, net cash flow, and closing cash for each period. For a monthly summary with those values in columns B through F, calculate net cash flow as =C2-D2 and closing cash as =B2+E2; this article shows how to build the sheet, enter the formulas, and adapt it for a simple forecast or transaction log.
What a cash flow spreadsheet shows
A cash flow spreadsheet tracks when money actually enters or leaves an account. It helps answer practical questions such as whether there will be enough cash to cover next month’s bills, which months tend to be tight, and whether planned spending fits expected receipts. It is not the same as a profit report: a sale made on credit may count as revenue before the customer pays, while a cash flow sheet records the money when it arrives.
A useful basic format has one row per month and these columns:
- Month: the period being summarized, ideally entered as a real Excel date such as January 1, 2026, and displayed as “Jan 2026.”
- Opening cash: the cash available at the start of the month.
- Cash inflows: receipts such as customer payments, owner contributions, or loan proceeds.
- Cash outflows: payments such as rent, payroll, supplies, taxes, or debt repayments.
- Net cash flow: inflows minus outflows for the period.
- Closing cash: opening cash plus net cash flow.
This compact layout is suitable for a personal budget, small-business cash forecast, or first-pass planning worksheet. For a formal financial statement, cash flows are usually grouped into operating, investing, and financing activities; those categories are explained below.
Build the monthly format in Excel
In Microsoft Excel for Microsoft 365 on Windows, enter the six headings in cells A1:F1: Month, Opening Cash, Cash Inflows, Cash Outflows, Net Cash Flow, and Closing Cash. Put each month in column A, and enter the starting cash balance in B2. Enter the expected or actual monthly totals in columns C and D. Keep inflows and outflows as positive numbers so the formulas remain easy to read.
For an illustrative January, suppose opening cash is $5,000, inflows total $12,000, and outflows total $9,500. With those values in B2, C2, and D2, calculate net cash flow in E2 using =C2-D2. It returns $2,500, because $12,000 minus $9,500 equals $2,500. With opening cash in B2 and net cash flow in E2, calculate closing cash in F2 using =B2+E2. It returns $7,500, the $5,000 opening balance plus $2,500 net cash flow.
For February, the opening balance should match January’s closing balance. With January’s closing cash in F2, enter =F2 in B3; if January ends at $7,500, February opens at $7,500. Suppose February inflows are $11,000 and outflows are $10,500. With those amounts in C3 and D3, enter =C3-D3 in E3; the result is $500. With February’s opening cash in B3 and net cash flow in E3, enter =B3+E3 in F3; the result is $8,000.
After entering the first row’s formulas, copy them down for later months. Select the formula cells and drag the small fill handle at the lower-right corner of the selection, or copy and paste them into the rows below. Check that the references advance by row: the next month’s opening balance should refer to the previous month’s closing balance, not to a fixed cell.
Use a transaction log to calculate monthly totals
Typing one total per month is simple, but a transaction log makes it easier to trace totals back to individual payments. Create a worksheet named Transactions with Date in column A, Description in B, Category in C, Type in D, and Amount in E. Enter each transaction on its own row, use “Inflow” or “Outflow” consistently in column D, and enter amounts as positive numbers. For example, a customer payment is an inflow and a rent payment is an outflow.
On the monthly summary sheet, enter each month’s first day as an actual date in A2, such as January 1, 2026. With transaction dates in Transactions!A2:A1000, transaction types in Transactions!D2:D1000, amounts in Transactions!E2:E1000, and the month start date in A2, calculate monthly inflows in C2 with =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&$A2,Transactions!$A$2:$A$1000,"<"&EDATE($A2,1),Transactions!$D$2:$D$1000,"Inflow"). The formula adds amounts dated on or after the first day of the month and before the first day of the following month, where Type is “Inflow.” If qualifying January entries total $12,000, C2 returns $12,000.
With the same transaction layout and month-start date in A2, calculate monthly outflows in D2 using =SUMIFS(Transactions!$E$2:$E$1000,Transactions!$A$2:$A$1000,">="&$A2,Transactions!$A$2:$A$1000,"<"&EDATE($A2,1),Transactions!$D$2:$D$1000,"Outflow"). If January’s matching outflow entries total $9,500, D2 returns $9,500. Extend the transaction ranges if your log grows beyond row 1000, and copy both summary formulas down for additional months. The date-range criteria avoid relying on the number of days in each month.
Choose actual cash flow or a forecast
The same layout can show past activity or estimate future balances, but label which one it represents. For an actual cash report, use transactions that have cleared or otherwise been received or paid, and reconcile the opening and closing balances with the relevant bank or cash records. For a forecast, enter expected dates and amounts for future receipts and payments. Forecast figures are estimates, not confirmed balances.
A useful forecast records assumptions explicitly. For example, if a customer usually pays several weeks after an invoice, estimate the receipt in the expected payment month rather than the invoice month. If a bill is due monthly, record its expected payment date and amount. Update estimates when payment dates, planned purchases, or other assumptions change. A forecast can expose a possible shortfall early, but it cannot guarantee that a customer will pay on time or that costs will stay fixed.
How this differs from a cash flow statement
A simple cash budget groups money into broad inflow and outflow totals for planning. A cash flow statement is a financial statement that organizes cash movements into operating, investing, and financing activities. Operating activities relate to the entity’s ordinary operations; investing activities include certain purchases or sales of long-term assets; financing activities concern transactions such as borrowing, repaying principal, or owner financing. Exact presentation depends on the reporting framework and circumstances.
For this reason, a basic spreadsheet should not automatically be treated as a formal statement. A loan payment, for example, may include both principal and interest, which can be presented differently in formal reporting. Likewise, a purchase of equipment may belong in an investing category rather than being grouped with routine operating expenses. If the spreadsheet is for external reporting, follow the applicable accounting requirements and consult a qualified accounting professional where necessary.
Make the sheet easier to read and maintain
Good formatting helps reveal problems without changing the underlying math. Select the amount columns and use Home > Number Format to apply a currency or number format consistently. Format the month column as a date, and use a clear display such as “Jan 2026.” Keep actuals and forecasts visually distinct with labels or separate columns, and note the date through which actual transactions have been entered.
- Use one currency throughout a sheet, or clearly identify the currency if multiple currencies are involved.
- Use consistent transaction categories and type labels; a spelling variation such as “In flow” will not match a formula criterion of “Inflow.”
- Keep source transactions rather than overwriting monthly totals, so you can review what makes up each number.
- Check that each transaction appears once, has the correct date and type, and is entered as a positive amount.
- Compare closing cash against an independent balance record for actual reporting, and investigate differences instead of forcing the spreadsheet to match.
For quick navigation on Windows, Ctrl+C copies selected cells and Ctrl+V pastes them. On Mac, use Command+C and Command+V. The formulas and worksheet structure described here work in Excel for Microsoft 365 on both platforms, though ribbon placement and some keyboard shortcuts differ. Excel for the web also supports these standard formulas, although its interface may differ from the Windows desktop ribbon.
Common formula problems and checks
If a monthly total is zero when you expect a value, first check the transaction date cells. Dates stored as text may not meet the date criteria in a SUMIFS formula. Also check that the month-start cell is a real date, the type label matches exactly, and the amount cells contain numbers rather than text. A quick review of a few source transactions can help identify whether the error is in the data or in the summary setup.
A negative net cash flow is not automatically a formula error. It means outflows exceeded inflows for that period. The closing cash formula then reduces the opening balance. If closing cash becomes negative, the model indicates a projected or recorded shortfall under the entries provided; it does not mean the sheet has arranged an overdraft or confirmed that funds are available.
Another common mistake is entering outflows as negative amounts and then subtracting them in the net formula. That would add the negative number in effect and can produce an incorrect total. Choose one convention and use it consistently. The format here uses positive amounts for both types, then subtracts outflows from inflows. Do not mix it with a convention that stores outflows as negative values.
Frequently Asked Questions
What is the formula for net cash flow in Excel?
With cash inflows in C2 and cash outflows in D2, enter =C2-D2 in E2. The result is positive when inflows exceed outflows and negative when outflows exceed inflows.
How do I calculate closing cash?
With opening cash in B2 and net cash flow in E2, enter =B2+E2 in F2. For the next month, use the prior month’s closing cash as the new opening balance.
Should cash outflows be entered as negative numbers?
Not in the format described here. Enter both inflows and outflows as positive amounts, then subtract the outflow total from the inflow total. Using a consistent sign convention prevents accidental double-negation.
Can I use this format for a personal budget?
Yes. Replace business labels with personal categories such as salary, rent, groceries, utilities, and savings transfers. Keep the same opening-balance, inflow, outflow, and closing-balance logic, and distinguish planned amounts from transactions that actually occurred.
Does a positive net cash flow mean the business made a profit?
No. Cash flow and profit measure different things. Timing differences, credit sales, unpaid bills, asset purchases, loan principal, and other accounting treatments can make cash movement differ from profit. Use the right report for the question being asked.
Related
- Calculate Time-Weighted Average in Excel with Examples
- Calculate ROI in Excel Over Multiple Years
- How to Calculate a Monthly Loan Payment in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Beginning of Month: Dates Made EasyIf A2 contains an Excel date, use =EOMONTH(A2,-1)+1 to return the first day of that date’s month. For…
- Oracle String Functions Explained with ExamplesOracle string functions operate on character data in SQL and PL/SQL. For everyday work, use functions such as…
- Excel Data Validation for a Unique ListYes—but Excel’s Data Validation list does not automatically remove duplicates from its source. In Microsoft 365 for Windows,…
- How to Count Names in Excel: Formulas and ExamplesTo count how often a particular name appears in Excel, use COUNTIF: with names in A2:A100 and the…
- Pop-Up Calendar Excel Formula: What WorksExcel has no formula that opens a true pop-up date picker when you select a cell. For a…
- How to Sort an Excel Table by One Column Without ErrorsTo sort an Excel table by one column, click the arrow in that column’s header and choose an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.