How to Calculate Cash Flow in Excel (8 Examples)

Posted on by Musiha Mahfuza Mukta

Below is a dataset with 5 Rows: Discount Rate, Year, Investing CF, Financing CF, and Operating CF.

Dataset of How to Calculate Cash Flow in Excel

 Investing Cash Flow and Financing Cash Flow denote the cash outflows for a business that have negative values (in red).

Method 1 –  Using the NPV Function 

Steps:

  • Select a cell, C10, where you want to see the Net CF.
  • Enter the following formula in cell C10:
=C7+C8+C9
  • Press ENTER.

Use of NPV Function to Calculate Cash Flow in Excel

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D10:H10.

You can see the Net Cash Flow for all those given periods.

  • Select a cell, C12, where you want to keep the Net Present Value.
  • Enter the following formula in cell C12:
=NPV(C4,D10:H10)+C10
  • Press ENTER to get the result.

You will get the Net present value of Cash flow.

Formula Breakdown

The NPV function will return the Net present value based on a discount rate, cash inflows, and cash outflows of an investment.

C4 is the Discount Rate. The data range D10:H10 denotes the cash flows. NPV(C4,D10:H10)—> becomes $48,738.26. I have included the total outflows of the beginning year. As C10 is the outflow amount of the initial year, thus, I have added the value of the C10 cell. So, $48,738.26+(-$20,000)—> turns $28,738.26.

Method 2 – Employing PV Function 

Steps:

  • Select a cell, C10, where you want to see the Net CF.
  • Enter the following formula in cell C10:
=C7+C8+C9
  • Press ENTER to get the result.

Employing PV Function to Calculate Discounted Cash Flow in Excel

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D10:H10.

You will get the Net Cash Flow.

  • Select a cell, C11, where you want to keep the Present Value.
  • Enter the following formula in cell C11:
=PV($C$4,C6,0,C10)
  • Press ENTER to get the result.

You will get the Present value of Cash flow.

Formula Breakdown

Here, the PV function will return the Present Value of an investment.

$C$4 denotes the discount rate. Here, the Dollar sign ($) denotes that the value is fixed. C6 denotes NPER as the period of time. 0 denotes that you don’t know the PMT. C10 denotes the Net Cash flow as Future Value.

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D11:H11.

You will get all the Present value of the Cash flow.

Method 3 – Using a Generic Formula 

Steps:

  • Select a cell, C10, where you want to keep the PV factor.
  • Enter the following formula in cell C10.
=1/(1+$C$4)^C6
  • Press ENTER to get the result.

Using Generic Formula to Calculate Cumulative Cash Flow

Formula Breakdown

Here, I have added 1 with the discount rate. 1+$C$4—> becomes 108%. I have kept the Year as the power function. 108%^C6—> turns 1. I have divided 1 by the previous output. 1/1—> returns 1.

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D10:H10.

You will get all the Present value factors.

  • Select a cell, C11, where you want to keep the Present Value.
  • Enter the following formula in the C11 cell.
=C10*C9

In this formula, I have multiplied the PV factor with the Net Cash flow.

  • Press ENTER to get the result.

You will get the present value of Cash flow.

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D11:H11.

You will get all the Present value cash flow.

  • Enter the following formula in cell C12:
=C11

In this formula, I have used the value of the C11 cell.

  • Press ENTER to get the result.

You will get the 1st Cumulative Cash flow.

  • Enter the following formula in cell D12:
=C12+D11
  • Press ENTER to get the result.

This is the 2nd Cumulative Cash flow.

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells E12:H12.

You will get all the Cumulative Cash flow for the given periods.

Method 4 – Calculating Free Cash Flow

Steps:

  • Select a cell, C9, where you want to keep the Free Cash Flow.
  • Enter the following formula in cell C9:
=C4+C5-C6-C7

In this formula, I have added Net income and Depreciation. From that added value, I have subtracted Working capital and Capital expenditures.

  • Press ENTER to get the result.

You will get the Free Cash Flow.

Calculating Free Cash Flow in Excel

Method 5 – Calculating Operating Cash Flow

Steps:

  • Select a cell, C9, where you want to keep the Operating Cash Flow.
  • Enter the following formula in cell C9:
=C4+C5-C6-C7

In this formula, I have added Operating income and Depreciation. From that added value, I have subtracted Working capital and Taxes.

  • Press ENTER to get the result.

You will get the Operating Cash Flow.

Method 6 – Calculating Cash Flow Forecast

Steps:

  • Select a cell, C8, where you want to keep the Cash Flow Forecast.
  • Enter the following formula in cell C8:
=C4+C5-C6

In this formula, I have added the Beginning Cash and Project Inflows. And, from that added value I have subtracted Project Outflows.

  • Press ENTER to get the result.

You will get the Cash Flow Forecast.

Method 7 – Finding Incremental Cash Flow

Steps:

  • Select a cell, C8, where you want to keep the  Incremental Cash flow.
  • Enter the following formula in cell C8:
=-C4+C5-C6

In this formula, I have subtracted Initial Investment and Rent & Other expenses from the total Cash Inflow.

  • Press ENTER to get the result.

You will get the Incremental Cash flow.

Final Result of Incremental Cash Flow in Excel

Method 8 – Calculating Internal Return Rate

Steps:

  • Select a cell, C9, where you want to see the Net CF.
  • Enter the following formula in cell C9:
=C7+C8

In this formula, I have added all the cash flows to find the net cash flow.

  • Press ENTER to get the result.

Applying IRR Function to Calculate Internal Return Rate of Cash Flow in Excel

  • Drag the Fill Handle icon horizontally to AutoFill the data in the rest of the cells D9:H9.

  • Select another cell, C11, to keep the Internal Rate of Return.
  • Enter the following formula in cell C11:
=IRR(C9:H9)

Here, C9:H9 is the data range for the IRR function.

  • Press ENTER to get the result.

You will get the Internal Rate of Return for the Cash flow.

Things to Remember

  • Here, you must use the negative sign for all the Cash outflows. Otherwise, you have to modify all those given formulas. But, in the case of examples 4 to 7 you don’t need to use minus sign as input.

Practice Section

Now, you can practice.

Practice Section for Calculating Cash Flow in Excel

Download the Practice Workbook

You can download the practice workbook from here:

Excel Cash Flow Formula: Knowledge Hub

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF