Excel has no single built-in cash flow diagram formula. If periods are in A2:A6 and signed cash flows are in B2:B6, enter this in C2 and fill down to calculate the running balance: =SUM($B$2:B2). To show an up or down arrow beside each amount, use a separate label formula; the steps below explain how to turn the results into a readable diagram.
What a cash flow diagram shows
A cash flow diagram places time periods along a horizontal line and shows money moving in or out at each period. It is common in project comparisons, loan calculations, and engineering economics. A receipt or other inflow is usually shown above the line; a payment or outflow is shown below it. The diagram makes the timing and direction of cash flows visible, not just their total.
That distinction matters: receiving $1,000 today is not financially equivalent to receiving $1,000 several years from now when interest, discounting, or a project’s timing is relevant. A cash flow diagram organizes the inputs so you can inspect them and, if needed, calculate a measure such as net present value (NPV). It does not by itself decide whether a project is worthwhile.
In Excel, keep the actual cash flow as a number in its own cell. You can then calculate cumulative totals, create a chart, or format a separate label. A diagram label containing an arrow and a currency amount is text, so it should not replace the numeric amount used in calculations.
Set up the timeline and cash flows
Create a simple table with one row for each point in time. For example, use the following illustrative project cash flows: an initial investment of $1,000, followed by receipts of $300, $400, $500, and $200. Enter the initial outlay as a negative number and receipts as positive numbers.
- Put a heading such as Period in A1 and Cash flow in B1.
- Enter 0, 1, 2, 3, and 4 in A2:A6, or use actual dates if the intervals are not equal.
- Enter -1000, 300, 400, 500, and 200 in B2:B6.
- Use a currency number format on B2:B6 so the values are easy to read without turning them into text.
The signs are a convention, but consistency is essential. In this example, money paid out is negative and money received is positive. If you reverse the convention, every related calculation and interpretation must follow that choice. Period 0 represents the starting point; it is not the same as the first future period.
Calculate the running cash balance
With the period labels in A2:A6 and the signed amounts in B2:B6, put this formula in C2 and copy it down through C6: =SUM($B$2:B2). The dollar signs keep the starting cell fixed as the formula is copied, while the final B2 reference expands to B3, B4, and so on.
For the example, the running balances are -$1,000, -$700, -$300, $200, and $400. The balance becomes positive after the $500 receipt in period 3. This running total is useful for showing how accumulated cash changes over time, but it is not the same thing as NPV: it does not discount future amounts.
If you want to display a cumulative balance only after each period, this method works directly. If you need the balance before each period’s transaction, shift the calculation by one row or include a separate opening balance. Be explicit about which convention your diagram uses, especially when comparing a spreadsheet with a report or another person’s model.
Create arrow labels with a formula
With signed numeric cash flows in B2:B6, enter this formula in D2 and fill it down through D6 to create a text label for each arrow: =IF(B2>0,"↑ "&TEXT(B2,"$#,##0.00"),IF(B2<0,"↓ "&TEXT(ABS(B2),"$#,##0.00"),"—")). A positive amount produces an up arrow and formatted amount; a negative amount produces a down arrow and the absolute amount; zero produces a dash.
For the sample, the labels are ↓ $1,000.00, ↑ $300.00, ↑ $400.00, ↑ $500.00, and ↑ $200.00. The formula uses TEXT to format a value as a readable string and ABS to remove the minus sign from the displayed outflow. The original values in column B remain numeric, so they can still be summed and charted.
Arrow symbols generally display in current Excel for Microsoft 365 on Windows, but the appearance depends on the selected font. If a symbol renders as a box, try a font with broader symbol support or use words such as Inflow and Outflow in the label instead. The formula’s currency format uses a dollar sign; change the format string if your model uses another currency. TEXT produces text, so do not sum the labels in column D.
Turn the table into a visual diagram
A formula can prepare the labels and calculations, but Excel does not automatically convert those formulas into a textbook-style timeline with proportional arrows. For a quick visual, create a column chart from the period and cash flow columns. With the headings and values in A1:B6, select that range and choose Insert > Insert Column or Bar Chart > Clustered Column. Positive bars extend above the horizontal axis and negative bars below it, making the direction and relative size visible.
To make the chart easier to interpret, add a descriptive chart title, keep the period labels in chronological order, and format the vertical axis as currency. The axis crossing at zero is especially useful because it separates inflows from outflows. If Excel treats period numbers as a continuous value axis or omits a label, check the chart’s horizontal axis labels and select the cells in column A.
A chart is a practical cash flow visualization, but it is not identical to a hand-drawn diagram with arrows above and below a timeline. If the precise arrow layout is important for a presentation or class assignment, use the chart for the numeric scale and add shapes or text boxes for annotations. Shapes are manual objects and may need repositioning if rows, periods, or values change. Keep the underlying table as the source of truth.
Use NPV only when the timing fits
A frequent related question is whether the cash flow diagram formula is the NPV formula. They serve different purposes. The diagram lays out cash flows by time; NPV discounts future cash flows to a chosen starting date using a rate. In Excel, the NPV function treats the first value in its list as occurring at the end of period 1. Therefore, an initial investment at time 0 is normally added separately.
With the period-0 investment in B2, end-of-period cash flows for periods 1 through 4 in B3:B6, and an illustrative annual discount rate in E1 of 10%, enter this formula in another cell: =NPV(E1,B3:B6)+B2. The result is approximately $115.57. This is the present value of the four future receipts, about $1,115.57, less the $1,000 initial outlay. It is an illustrative calculation, not financial or investment advice.
The rate and cash flow frequency must match. If the cash flows are monthly, a rate expressed as an annual effective rate should not simply be used as though it were a monthly rate. Check the assumptions and calculation method against the actual terms and, for financial, tax, investment, or legal decisions, consult the lender or a qualified professional.
If the cash flows occur on irregular dates, ordinary NPV’s equal-period assumption may not fit. With dates in A2:A6, matching cash flows in B2:B6, and a discount rate in E1, enter =XNPV(E1,B2:B6,A2:A6) in a result cell. XNPV uses the supplied dates to account for uneven timing; ensure the first date represents the valuation date and that each amount is paired with its correct date. XNPV is available in Microsoft 365 and Excel 2021 or later.
Common formula and chart problems
- All arrows point the wrong way: Check the sign convention in column B. The label formula interprets positive values as inflows and negative values as outflows.
- The label shows a strange currency or decimals: The TEXT format string controls the display. Change the currency symbol and number of decimal places to suit the workbook; the underlying value is not changed.
- The cumulative balance repeats or starts incorrectly: Check that the first amount is in B2 and that the formula begins with the fixed reference $B$2. Confirm that the formula was filled down and that cash flow cells contain numbers, not text.
- The chart has no visible negative bars: Verify that outflows are negative numeric values, not text such as “($1,000)”. Check the vertical-axis limits and make sure the chart includes the cash flow column.
- NPV is off by one period: Confirm whether the initial amount is at time 0. Excel’s NPV function discounts its first listed amount by one period, so a time-0 amount is generally kept outside the NPV list.
- Dates are not recognized: Cells that look like dates may actually be text. Convert them to real Excel dates and verify that each date aligns with the corresponding amount before using XNPV.
Regional settings can affect how formulas are entered. The examples use commas between function arguments, as requested for standard English-language Excel syntax. Some installations configured for different regional conventions may display or expect semicolons instead. Excel for the web has the same core formulas, while exact chart editing and shape placement can vary by browser and available ribbon controls. On Mac, the formula logic is the same; ribbon placement and some keyboard shortcuts differ from Windows.
Frequently Asked Questions
What formula shows positive and negative cash flows as arrows?
Use an IF formula with the sign of the numeric cash flow to choose an up arrow or down arrow, and TEXT to format the amount. Keep the numeric cash flow in a separate cell because the arrow label is text.
Should an investment be entered as positive or negative?
Usually, an investment paid out is entered as a negative cash flow and money received is positive. This is a common convention, not a rule that Excel enforces. Use one convention consistently throughout the diagram and any related calculations.
Is a cash flow diagram the same as an NPV calculation?
No. A diagram shows the amount, direction, and timing of cash flows. NPV applies a discount rate to future amounts to express them in present-value terms. A diagram can provide the inputs for NPV, but it does not perform discounting by itself.
Can Excel draw arrows automatically from cash flow values?
A formula can generate arrow characters in cells, and a column chart can show positive and negative values around a zero line. Excel does not automatically create a fully arranged textbook diagram of proportional arrows from a formula alone; shapes or chart formatting may be needed for that presentation.
Why does Excel NPV exclude the initial investment?
Excel assumes the first cash flow supplied to NPV occurs one period after the valuation date. When an initial investment occurs at time 0, it is normally added separately. For uneven cash flow dates, XNPV may better represent the timing, provided the dates and rate are appropriate.
Related
- Excel Border Shortcuts: Apply, Change, and Remove Borders
- Bluebeam to Excel: Export Markups and Quantities
- Bluebeam Quantity Link to Excel: Setup and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
- Round Excel to 2 Decimal Places: Formula or FormatTo round a number to two decimal places in Excel, use the ROUND function: with the value to…
- Bing Chat Excel Formula Not Updating? Fixes That WorkIf a formula from Bing Chat is not updating in Excel, first check that it was entered as…
- Add a Subtotal Row to an Excel Table the Right WayTo add a total row to an Excel table in Microsoft 365 for Windows, click anywhere in the…
- Excel COUNTIF Not Text? Use COUNTIF with WildcardsTo count cells that do not contain text in Excel, use =COUNTIF(range,"*") — this counts numbers, dates, and…
- How to Get the Highest Number in Excel with the MAX FunctionTo get the highest number in a range, use Excel’s MAX function. With sample values in A2:A8, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.