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

** 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**.

- 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.

- 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.

**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**.

### 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**.

## 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.

- 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.

**Download the Practice Workbook**

You can download the practice workbook from here:

## Excel Cash Flow Formula: Knowledge Hub

- How to Calculate Annual Cash Flow in Excel
- How to Calculate Incremental Cash Flow in Excel
- How to Calculate Discounted Cash Flow in Excel
- How to Forecast Cash Flow in Excel
- How to Calculate Free Cash Flow in Excel
- How to Calculate Cumulative Cash Flow in Excel
- How to Draw a Cash Flow Diagram in Excel
- How to Track Cash Flow in Excel
- How to Create a Personal Cash Flow Statement in Excel
- How to Calculate Operating Cash Flow Using Formula in Excel
- How to Calculate Payback Period in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- Calculating Payback Period in Excel with Uneven Cash Flows
- How to Calculate Discounted Payback Period in Excel
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel
- How to Create a Cash Flow Waterfall Chart in Excel

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