The basic cash flow formula in Excel is cash inflows minus cash outflows. If inflows are in B2 and outflows are in C2, enter =B2-C2 in D2; for example, $1,200 received minus $850 paid returns a net cash flow of $350.
What cash flow means in a spreadsheet
Cash flow measures money entering and leaving a business, household, project, or account over a period. A positive net cash flow means more cash came in than went out during that period; a negative result means outflows were greater. The formula is simple, but a useful spreadsheet depends on defining the period and recording the transactions consistently.
In a basic tracker, each row represents one transaction or one time period, and columns record the date, cash received, cash paid, and resulting net flow. You can use the same structure for daily transactions, weekly summaries, or monthly forecasts. A transaction-level sheet is more detailed; a monthly sheet is easier to scan but hides individual payments unless you keep a separate transaction list.
Cash flow is not the same as profit. Profit can include revenue that has not yet been collected and expenses that have not yet been paid. A cash flow tracker focuses on the movement of cash, so it is useful for monitoring whether available funds are sufficient to cover upcoming payments.
The basic formula and what it calculates
The calculation for a period is:
- Net cash flow = cash inflows − cash outflows
- A receipt or other cash addition is an inflow.
- A payment or other cash reduction is an outflow.
In Excel, put inflows and outflows in separate columns and subtract the outflow column from the inflow column. With inflows in B2 and outflows in C2, enter =B2-C2 in D2. If B2 is 1,200 and C2 is 850, D2 returns 350. If outflows are 1,400 and inflows are 1,000, the formula returns -400, indicating a net cash decrease of 400.
Use positive numbers for both inflows and outflows in this layout. The subtraction in the formula handles the direction. If you instead enter payments as negative numbers, adding inflows and outflows may be appropriate, but mixing the two conventions can reverse results or double-count payments. Choose one convention and label the columns clearly.
Set up a cash flow worksheet
In a blank Excel worksheet, create these headings in row 1: Date in A1, Cash In in B1, Cash Out in C1, and Net Cash Flow in D1. Enter each transaction or period on its own row, using real Excel dates in column A and amounts in the relevant cash columns. Leave the unused cash column blank or enter zero.
- Enter dates and amounts in rows 2 onward.
- In D2, calculate the first row’s net flow with
=B2-C2. - Copy D2 down the column using the fill handle, the small square at the selected cell’s lower-right corner.
- Format the date column as a date and the money columns as Currency or Accounting using Home > Number.
For example, if a business receives 1,200 and pays 850 on the first date, the net flow is 350. On the next date, if it receives 400 and pays 125, net flow is 275. A third period with no receipts and 300 in payments produces -300. These row-by-row results show the change in cash for each entry or period, not the cash balance in the account.
Worked example: total cash flow for a period
Suppose the three example rows contain inflows of 1,200, 400, and 0, and outflows of 850, 125, and 300. Total inflows are 1,600, total outflows are 1,275, and the net cash flow for all three rows is 325. You can calculate the totals separately or calculate net flow directly from the two columns.
With inflows in B2:B4, enter =SUM(B2:B4) in B5. It returns 1,600. With outflows in C2:C4, enter =SUM(C2:C4) in C5. It returns 1,275. These totals make it easier to check the underlying figures.
With inflows in B2:B4 and outflows in C2:C4, enter =SUM(B2:B4)-SUM(C2:C4) in D5 to calculate total net cash flow. It returns 325 because 1,600 minus 1,275 equals 325. This should match the sum of the three net-flow amounts in D2:D4: 350 + 275 − 300 = 325. Comparing the two methods is a practical way to catch a missing or misaligned row.
Calculate a running cash balance
Net cash flow tells you how much cash changed in a period; a running balance estimates how much cash remains after each period. Add an Opening Balance heading in E1 and enter the starting amount in E2, or place the starting amount in a clearly labeled cell above the transactions. Keep the opening balance separate from the period’s inflow and outflow so the two measures are not confused.
For a simple row-by-row example, place the opening cash balance of 2,000 in E1. With the first net cash flow in D2, enter =E1+D2 in E2. The result is 2,350. With the next period’s net flow in D3 and the previous closing balance in E2, enter =E2+D3 in E3. The result is 2,625. With the third period’s net flow in D4 and the previous balance in E3, enter =E3+D4 in E4. The result is 2,325.
This balance assumes there are no other cash movements outside the rows included in the sheet. If the spreadsheet tracks only selected transactions, the calculated balance will not necessarily equal a bank balance. Reconcile the beginning balance and listed activity to a reliable account record before relying on the ending figure.
Choose a layout for actuals or a forecast
A historical tracker records transactions that have already happened. A forecast estimates future receipts and payments. Both can use the same inflow-minus-outflow formula, but they should be distinguished with labels such as Actual and Forecast, or kept on separate worksheets. Forecast amounts are assumptions, not confirmed cash, so identify estimates and expected dates.
For a monthly forecast, one row per month keeps the summary readable. For a cash plan that depends on exact payment dates, one row per transaction is more informative. Useful columns may include category, description, expected or actual date, amount, and status. Avoid putting unrelated figures in the same amount column unless each row is clearly classified; otherwise, it becomes harder to verify what the total represents.
When adding categories, remember that the total cash flow should still include each relevant receipt and payment only once. A transfer between two accounts you own may appear as an outflow in one account and an inflow in another, but it is not new income or spending for the combined accounts. Decide whether the sheet tracks one account or the overall cash position and treat transfers accordingly.
Cash flow in a financial statement
The simple spreadsheet formula is useful for cash tracking, but formal cash flow statements group cash movements into operating, investing, and financing activities. Operating activities relate to the organization’s main operations. Investing activities generally concern long-term assets or investments, while financing activities concern borrowing, repayment, and owner or shareholder funding. The categories provide context that a single net figure cannot show.
Formal statements may use the direct method, which presents major classes of cash receipts and payments, or the indirect method, which starts with net income and adjusts for non-cash items and changes in working capital. Therefore, net income minus expenses is not a universal cash flow formula, and a basic Excel tracker should not be treated as a complete financial statement. Reporting requirements and classification depend on the organization and applicable accounting rules; consult a qualified accounting professional for reporting decisions.
Common errors and ways to avoid them
- Reversing the subtraction: Keep the headings clear and calculate Cash In minus Cash Out. Check a row with a known example: 1,200 in and 850 out should produce 350.
- Entering outflows as negatives in a subtraction layout: If an outflow is -850 in C2,
=B2-C2adds 850 instead. Use positive outflow amounts with this formula, or redesign the formula and data convention consistently. - Omitting a period or transaction: Check that summary ranges include the first and last relevant rows. A total that stops at row 20 will not include new transactions added in row 21.
- Mixing cash and non-cash items: An unpaid invoice is not a cash receipt just because it has been issued. Decide whether the sheet records cash movement or another accounting measure.
- Confusing net flow with a balance: A positive monthly result does not by itself tell you the account balance. Add the period’s net flow to a verified starting balance to estimate the closing balance.
- Using text instead of numeric amounts: Typing currency symbols or words into a value can make it harder for Excel to calculate. Enter numbers and apply a number format, then check that totals include every intended row.
When a result looks wrong, inspect the referenced cells and test a small row manually. Also check for duplicate transactions, dates assigned to the wrong period, and payments that have been recorded both in a transaction list and in a separate adjustment. A spreadsheet can calculate accurately while still reflecting incomplete or inconsistent inputs.
Frequently Asked Questions
What is the simplest cash flow formula in Excel?
Subtract cash outflows from cash inflows. If inflows are in B2 and outflows are in C2, use =B2-C2. A result above zero is a net increase for that row or period; a result below zero is a net decrease.
How do I calculate monthly cash flow in Excel?
List or total the month’s cash receipts and payments, then subtract total payments from total receipts. If the monthly inflow totals are in B2 and outflow totals are in C2, use =B2-C2. Make sure the totals include only transactions dated within the month.
How do I calculate a cash balance from cash flow?
Add the period’s net cash flow to the verified opening balance. For example, an opening balance of 2,000 plus net cash flow of 325 gives a closing balance of 2,325. For multiple periods, each closing balance becomes the next period’s opening balance.
Can cash flow be negative?
Yes. A negative result means cash outflows exceeded inflows in the period. It describes the direction and size of the change, not automatically whether a business is profitable or in financial trouble; context, timing, and available cash also matter.
Is cash flow the same as profit?
No. Profit is calculated using accounting rules and may include amounts not yet received or paid. A cash flow calculation tracks cash receipts and payments. The two measures answer different questions and may differ substantially in a given period.
Does this formula work in Excel for Mac or the web?
Yes. Basic arithmetic and SUM formulas work in Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon locations and some formatting details can differ by platform, but the formulas shown here do not require a platform-specific feature.
Related
- Blank Checklist Template Excel: Create One That Works
- Bloomberg Excel Add-In Repair and Troubleshooting
- Bill Paying Checklist Excel Template: Build One That Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Between Two Sheets in Excel?Yes. To look up a value on one Excel sheet and return a related value from another, use…
- Remove Filter From an Excel Table: Steps and OptionsTo remove the filter arrows from an Excel table in Microsoft 365 for Windows, click a cell in…
- SUMIF Criteria Examples: Text, Dates, Numbers, and MoreIn Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in…
- XLOOKUP Examples for Beginners: Formulas, Tips, and FixesXLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product…
- Excel Align Left Shortcut: Windows and Mac GuideIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, then H, A,…
- Date Difference in Minutes in Excel: Formulas That WorkIf A2 contains a start date and time and B2 contains an end date and time, enter =(B2-A2)*1440…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.