Cash Flow Formula in Excel: A Step-by-Step Tutorial

Formulas & functions · Updated November 19, 2026

In Excel, net cash flow for a period is cash inflows minus cash outflows. If inflows are in column B and outflows are in column C, enter =B2-C2 in D2 and fill it down; to track the cash balance, add each period’s net cash flow to the previous balance.

What cash flow means

Cash flow measures money moving into and out of a person’s or organization’s accounts over a period. A positive net cash flow means more cash came in than went out during that period; a negative result means outflows exceeded inflows. It is not the same as profit: a business can record revenue before it receives payment, and expenses can be recognized in a different period from when cash is paid.

Cash flow is often grouped into three categories: operating activities, such as customer receipts and supplier payments; investing activities, such as buying or selling equipment; and financing activities, such as borrowing, repaying debt, or issuing shares. A simple worksheet can calculate net movement from whatever categories matter to the user. For formal financial statements, classification and reporting rules can be more specific than a basic spreadsheet.

There is no single built-in Excel function called CASHFLOW that calculates every kind of cash flow. In ordinary cash tracking, a subtraction formula is usually the clearest starting point. Excel also has investment functions such as NPV and XNPV, but those answer different questions: they estimate the present value of dated or periodic future cash flows rather than simply subtracting this month’s spending from this month’s receipts.

Set up a cash flow worksheet

Start with one row per period, such as a month, and use consistent categories. For a simple monthly tracker, set up these columns:

  • A: Month or period
  • B: Cash inflows
  • C: Cash outflows
  • D: Net cash flow
  • E: Ending cash balance

For example, enter Jan, Feb, Mar, and Apr in A2:A5. Enter inflows of 5000, 4700, 6200, and 5300 in B2:B5, and outflows of 4200, 5100, 5500, and 6000 in C2:C5. Put the opening cash balance, 2000, in F1. These are illustrative amounts, not a forecast or a recommendation; format the amounts as currency if that makes the sheet easier to read.

Use one unit and one sign convention throughout. In this layout, inflows and outflows are both entered as positive amounts, and the formula subtracts outflows. Do not enter outflows as negative numbers as well, or subtracting them would add them back to the result. If you prefer to represent each cash movement with its own positive or negative sign, you can sum those movements instead, but avoid mixing conventions.

Calculate net cash flow for each period

With the months in A2:A5, inflows in B2:B5, outflows in C2:C5, and net cash flow in D2:D5, enter this formula in D2:

=B2-C2

For January, the result is 5000 minus 4200, or 800. Copy or fill the formula down through D5; Excel adjusts the row references automatically. February returns -400, March returns 700, and April returns -700. The figures show the change in cash during each month, not the total amount held in the account.

To fill the formula, select D2 and drag the small fill handle at the cell’s lower-right corner down to D5. You can also copy D2, select D3:D5, and paste. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. The formula bar is useful for checking that each row refers to its own inflow and outflow cells.

Calculate the running cash balance

A period’s net cash flow is different from the cash balance at the end of that period. The balance carries forward prior cash, so a negative month does not necessarily mean the account is overdrawn; it depends on the opening balance and earlier cash movements.

With the opening balance in F1, January net cash flow in D2, and ending balance in E2, enter this formula in E2:

=$F$1+D2

The dollar signs keep the opening-balance reference fixed if the formula is copied. With an opening balance of 2000 and January net cash flow of 800, the ending balance is 2800.

With the previous month’s ending balance in E2 and February net cash flow in D3, enter this formula in E3:

=E2+D3

Copy E3 down for the remaining months. The balances are 2400 at the end of February, 3100 at the end of March, and 2400 at the end of April. The four monthly net flows total 400, so the final balance is 2000 plus 400, or 2400. If the worksheet starts with no opening balance, enter zero in F1; do not leave it blank if you want the assumptions to be clear.

Summarize and check the results

A summary row can make the total movement easy to compare with the starting and ending balances. With inflows in B2:B5 and outflows in C2:C5, enter this formula in a summary cell:

=SUM(B2:B5)-SUM(C2:C5)

For the example, total inflows are 21,200 and total outflows are 20,800, so the formula returns 400. That agrees with the sum of the four monthly net flows and with the change from the 2000 opening balance to the 2400 ending balance. If these checks do not agree, look for a missing row, an incorrect range, or a number stored as text.

Useful checks before relying on a worksheet include:

  • Confirm that every transaction belongs to the intended period and is included once.
  • Check that all entries use the same currency and the same inflow/outflow convention.
  • Compare the final balance with a bank or cash-account record, allowing for timing differences such as outstanding payments.
  • Inspect formula references after inserting rows or extending the date range.
  • Use a separate category column or separate inflow and outflow columns if you need to explain what caused a change.

For larger transaction lists, a transaction-level table can be more flexible than manually entering monthly totals. A date, description, category, and amount for each movement can be filtered or summarized by period. The core idea remains the same: define which movements count, group them consistently, and check that the calculated change reconciles to the balances.

When to use Excel NPV instead

Net cash flow answers how much cash changed during a period. NPV is for a different purpose: it discounts future cash flows to estimate their value today at a chosen rate. In Excel, NPV assumes the listed cash flows occur at regular period intervals, typically at each period’s end. A cash flow paid immediately at the start is not discounted as a future period, so it is added separately.

With an initial payment of -10000 in B2 and three expected end-of-period receipts of 4000 in B3:B5, and a discount rate of 10% entered directly in the formula, enter:

=NPV(10%,B3:B5)+B2

The result is approximately -52.59. The NPV of the three future receipts is about 9947.41; adding the initial outlay gives a slightly negative result under these illustrative assumptions. This does not make the formula a decision by itself: the rate, timing, and cash-flow estimates are inputs that require judgment. Check real contract terms and consult a qualified professional for financial, tax, investment, or legal decisions.

If dates are irregular, XNPV can discount cash flows using actual dates instead of equal period spacing. Its inputs need a cash-flow range and a matching date range, with the initial investment generally included as a negative cash flow on its date. Use it only when the dates and assumptions are appropriate; it is not a replacement for a simple monthly cash tracker.

Common mistakes and useful distinctions

A frequent mistake is calling a monthly net flow a cash balance. A result of -700 in April means cash fell by 700 during April; if the balance before April was 3100, the ending balance is still 2400. Another mistake is treating accounting profit as cash received. Sales on credit, unpaid bills, inventory purchases, loan principal, and depreciation can all make profit and cash movement differ.

Be cautious with blanks and incomplete periods. A blank cell may mean zero, unknown, or not yet entered, and those meanings are not interchangeable in a financial model. Agree on what blanks mean, and use zero only when there truly was no movement. Also check date boundaries: a payment dated on the first day of a month may be assigned differently depending on whether the tracker uses transaction date, processing date, or statement date.

Finally, a cash flow worksheet is only as complete as its inputs. It may omit pending transactions, fees, taxes, or transfers between accounts. Transfers can be cash outflows from one account and inflows to another, but they are not necessarily new income or spending when tracking all accounts together. Decide whether the sheet tracks one account or a whole organization before categorizing transfers.

Frequently Asked Questions

What is the basic cash flow formula in Excel?
For a row where inflows are in B2 and outflows are in C2, subtract outflows from inflows in the net-flow cell. If the layout differs, adjust the cell references to match your columns.

How do I calculate monthly cash flow in Excel?
Put each month’s total inflows and outflows on the same row, calculate the difference for each row, and fill the formula down. Use one row per month and include all relevant transactions consistently.

How do I calculate ending cash balance?
Add the period’s net cash flow to the opening balance for the first period. For each later period, add that period’s net flow to the previous period’s ending balance.

Is cash flow the same as profit?
No. Profit is an accounting measure that can include revenue not yet collected and expenses not yet paid. Cash flow tracks actual cash movements, so the timing and totals can differ.

Should I use NPV or a subtraction formula?
Use subtraction to calculate ordinary period-by-period cash movement. Use NPV when you need to discount evenly spaced future cash flows at a specified rate; for irregular dates, consider XNPV. These functions answer valuation questions, not basic account-balance tracking.

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.