Cash Flow Formula in Excel: Build and Read a Sheet

Formulas & functions · Updated November 21, 2026

The basic cash flow formula is cash received minus cash paid. In a sheet with cash inflows in C2 and cash outflows in D2, enter =C2-D2 to calculate net cash flow; add that result to opening cash to find the closing balance.

What a cash flow formula measures

Cash flow tracks money entering and leaving a person’s or organization’s accounts over a period. The key calculation is net cash flow: compare cash inflows with cash outflows for a week, month, quarter, or other defined period. A positive result means more cash came in than went out during that period; a negative result means outflows were greater.

Net cash flow is not the same as profit. A business can record a sale as revenue before a customer pays, while a cash flow sheet records the money when it is received. Likewise, a loan payment may use cash even though paying down loan principal is not an expense in the same way as an operating cost. For a useful tracker, decide whether you are recording cash actually received and paid or projecting expected amounts, and label the sheet accordingly.

A simple cash flow worksheet usually shows three related figures: opening cash, net cash flow during the period, and closing cash. The relationship is straightforward: closing cash equals opening cash plus net cash flow. The closing balance for one period should normally become the opening balance for the next. This running balance helps reveal when cash may become tight, even if the overall total for several months is positive.

Set up a simple monthly Excel sheet

In Excel for Microsoft 365 on Windows, create a blank workbook and enter a header row. One practical layout is to use columns A through F for Month, Opening Cash, Inflows, Outflows, Net Cash Flow, and Closing Cash. Put the first month on row 2 and later months on the rows below it. Enter the starting bank or cash balance in B2, then enter the period’s receipts and payments in C2 and D2.

For example, suppose January starts with $2,500, brings in $6,200, and has $7,100 in payments. Those values produce net cash flow of negative $900 and a closing cash balance of $1,600. February should start with that $1,600 closing balance, not with the original $2,500. In the sheet, enter amounts as numbers; apply currency formatting to display them as money.

  • Use one consistent time period in every row, such as calendar months.
  • Keep inflows and outflows in separate columns so the direction of each amount is easy to inspect.
  • Use a clear sign convention: enter receipts as positive amounts in the inflow column and payments as positive amounts in the outflow column.
  • Record the opening balance for the first period, then link later opening balances to the preceding closing balances.

This layout is intentionally compact. A detailed business cash flow statement may separate cash from operating, investing, and financing activities, but a basic planning sheet can begin with total inflows and outflows. If you later add categories, retain the same distinction between money received and money paid.

Enter the net and closing cash formulas

With inflows in C2 and outflows in D2, enter this formula in E2 to calculate January’s net cash flow: =C2-D2. For the example amounts, Excel returns -900. Format the cell as currency or accounting style so the negative value is clear. Copy the formula down column E for the remaining periods; each copied row will calculate that month’s inflows less its outflows.

With opening cash in B2 and net cash flow in E2, enter this formula in F2 to calculate the closing balance: =B2+E2. With the example figures, the result is $1,600. For the next month, with the previous closing balance in F2, enter this formula in B3: =F2. Copy that opening-balance link down column B, and copy the closing formula from F2 down column F. Check that each later row’s opening cash matches the preceding row’s closing cash.

If you have several receipt or payment categories in a row, SUM can total them before the net calculation. For example, assuming sales receipts are in C2, other receipts are in D2, and the amount of cash paid is in E2, enter this formula in F2: =SUM(C2:D2)-E2. If those cells contain $6,000, $200, and $7,100 respectively, the net is -$900. Adjust the cell references to match your own headings rather than copying the example blindly.

Build a transaction ledger for more detail

A monthly summary is quick to maintain, but it does not explain what makes up the totals. For more detail, keep a transaction ledger on a separate worksheet. A useful structure is Date in column A, Description in B, Category in C, Inflow in D, and Outflow in E. Enter each cash receipt or payment on its own row, using a real Excel date in column A. Assign a transaction to either the inflow or outflow column, not both.

You can summarize the ledger by month with SUMIFS. Suppose the ledger is on a sheet named Ledger, its dates are in A2:A500, inflows are in D2:D500, and the first day of the month being summarized is in G2. With those ranges and the month start date in G2, enter this formula to total that month’s inflows: =SUMIFS(Ledger!D2:D500,Ledger!A2:A500,">="&G2,Ledger!A2:A500,"<"&EDATE(G2,1)). If January’s ledger receipts add to $6,200, the formula returns 6,200. The end-date condition excludes transactions on the first day of the next month, so it works for months of different lengths.

With the same ledger dates, outflows in E2:E500, and the month start date in G2, enter this formula for that month’s payments: =SUMIFS(Ledger!E2:E500,Ledger!A2:A500,">="&G2,Ledger!A2:A500,"<"&EDATE(G2,1)). If the qualifying payments total $7,100, it returns 7,100. Keep the ledger’s dates as true date values rather than text that merely looks like a date. SUMIFS and EDATE are available in current desktop Excel; the same formulas can also be used in Excel for the web.

Use the sheet for a cash forecast

A cash flow forecast uses estimated future receipts and payments instead of completed transactions. The formula structure stays the same, but the inputs are assumptions: expected customer payments, payroll, rent, subscriptions, taxes, equipment purchases, or other cash movements. Add notes or a separate assumptions column to show where estimates came from and when they should be reviewed. A forecast is more useful when it distinguishes confirmed amounts from uncertain ones.

For instance, if a future month has an opening balance of $1,600, expected receipts of $5,000, and planned payments of $5,800, its projected net cash flow is -$800 and its projected closing balance is $800. Those numbers describe the example’s assumptions, not a guarantee of the balance that will occur. Late payments, unexpected costs, and changes in timing can alter the result. Compare forecast figures with actual cash each month, then update future rows using what you have learned.

You can also build cautious and optimistic scenarios by keeping separate input columns or worksheets, while leaving the calculation structure consistent. Do not mix actual results and projections without labeling them: otherwise, readers may mistake an estimate for a completed cash movement.

Make totals readable and check the workbook

Good formatting makes errors easier to spot. Select the money cells and use Home > Number to apply Currency or Accounting formatting. Use a consistent number of decimal places, and consider displaying negative values with a minus sign or parentheses. Format dates consistently as well. Freeze the header row with View > Freeze Panes > Freeze Top Row if you have many months or ledger entries.

Before relying on a result, check a few rows by hand. Confirm that the opening balance plus inflows less outflows equals the displayed closing balance, and verify that the next row begins with that same balance. Compare ledger totals with bank or cash records for completed periods. In a forecast, review whether each expected item belongs in the period when cash is likely to move, rather than the period when an invoice is issued or a cost is recognized.

Useful checks include looking for blank amounts, duplicate transactions, dates outside the period, and entries recorded in the wrong direction. A receipt entered as an outflow can distort both the net result and the running balance. If the sheet is shared, label units and period dates clearly, and protect formulas only if the people using the workbook understand how to enter data without overwriting them.

Common cash flow spreadsheet problems

  • Confusing cash flow with profit: Profit and cash movement answer different questions. Timing of receipts and payments matters to cash flow, even when the related revenue or expense belongs to another accounting period.
  • Using the wrong opening balance: Starting every month with the same balance breaks the running total. Link each period’s opening amount to the prior period’s closing amount.
  • Mixing signs: If outflows are entered as negative numbers and then subtracted in the formula, Excel effectively adds them back. Choose one convention and use it consistently. The examples here enter outflows as positive amounts and subtract them.
  • Dates stored as text: A date-looking text entry may not match date criteria in a summary formula. Enter recognized Excel dates and check the cell’s value and format if a monthly total is unexpectedly zero.
  • Counting transfers as new cash: Moving money between two accounts you track can appear as an outflow from one and an inflow to the other. For a combined cash view, classify internal transfers so they do not falsely increase or reduce total cash.
  • Overlooking noncash items: Depreciation and unpaid invoices are not cash received or paid at the time they are recorded. A simple cash tracker should include actual cash movements, while a formal statement may require more structured accounting information.

Simple tracker or formal cash flow statement?

A basic Excel tracker is suited to monitoring balances, household spending, a small project, or a short-term cash plan. It is flexible and easy to customize, but its accuracy depends on complete inputs and clear definitions. It does not automatically become a formal financial statement just because it uses cash flow terminology.

Formal business reporting may classify cash movements into operating, investing, and financing activities and follow applicable accounting requirements. If a bank, investor, tax authority, or business reporting process requires a formal statement, a simple inflow-minus-outflow sheet may not meet that need. Check the relevant reporting requirements with a qualified accounting professional. For basic cash planning, though, a transparent monthly tracker can make the timing and direction of cash movements easier to understand.

Frequently Asked Questions

What is the basic cash flow formula in Excel?
Subtract cash outflows from cash inflows to get net cash flow. To find the period’s closing cash, add net cash flow to the opening balance. Keep inflows and outflows in clearly labeled columns so the calculation’s direction is unambiguous.

How do I calculate a running cash balance in Excel?
Calculate each period’s closing balance from its opening balance and net cash flow, then use that closing balance as the next period’s opening balance. Check that the two linked values match from row to row.

Can Excel calculate monthly cash flow from a transaction list?
Yes. Keep dates and inflows or outflows in separate ledger columns, then summarize amounts between the first day of a month and the first day of the next month with SUMIFS. Make sure the ledger dates are real Excel dates.

Why is my cash flow total negative?
A negative net result means the entered outflows exceed the entered inflows for the selected period. Check the dates, amounts, and sign convention first. A negative result is a calculation, not by itself proof of an error.

Is cash flow the same as net income?
No. Net income follows accounting rules for revenue and expenses, while a cash flow tracker focuses on cash received and paid. Differences in payment timing, credit sales, loan principal, and noncash expenses can make the figures differ.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.