## Step 1 – Calculating Net Cash Flow

- Input data. In this example, weâ€™ll type
**Cash Inflows**and**Cash Outflows**of**6 years.**See the picture below.

- Calculate the net/cumulative cash flow. Select cell
**E5**. - Type the formula:

`=C5-D5`

- Press
**Enter**. - Use
**AutoFill**to complete the rest.

## Step 2 – Determining Break-Even Point

The point of no profit and no loss is the break-even point. We obtain the break-even point of a project when the net cash flows exceed the initial investment.

- Select cell
**D12**. - Insert the formula:

`=COUNTIF(E5:E10,"<0")`

**The COUNTIF function** counts the number of years where the net cash flow is negative.

- Press
**Enter**to get the break-even point.

## Step 3: Obtaining Last Negative Cash Flow

If our dataset is large, we wonâ€™t be able to find the last negative cash flow manually.

- Click on cell
**D13**. - Input the formula:

`=VLOOKUP(D12, B5:E10, 4)`

- Press
**Enter**.

## Step 4 – Using VLOOKUP to Find Next Year’s Cash FlowÂ

- Choose cell
**D14**. - Type the formula:

`=VLOOKUP(D12+1, B5:E10, 2)`

- Press
**Enter**to return the result.

## Step 5 – Computing Fractional Year Value with ABS Function

- Select cell
**D15**. - Insert the formula:

`=ABS(D13/D14)`

- Press
**Enter**.

## Step 6 – Calculating Payback Period

- Click cell
**D16**. - Type the formula:

`=D12 + D15`

- Press
**Enter**. - Youâ€™ll get the accurate payback period in years.

- To convert it to months, in cell
**D17**, input the formula:

`=D16*12`

- Press
**Enter**to get the output in months format.

## Step 7 – Inserting Chart to Show Payback Period in Excel

- Choose the ranges
**B5:B10**and**E5:E10 (hold Ctrl while selecting).**

- Go to
**Insert**, selectÂ**Line Chart,**and chooseÂ**2-D Line Chart with Markers**.

- In that chart, youâ€™ll see the approximate value where the series crosses the
**X-axis**which is the payback period. - But with this chart, we canâ€™t get the exact value as we have shown earlier.

Here’s how final output is displayed.

**Download Practice Workbook**

Download the following template to practice by yourself.

## Related Articles

- How to Calculate Operating Cash Flow in Excel
- How to Create a Cash Flow Waterfall Chart in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- Calculating Payback Period in Excel with Uneven Cash Flows
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Discounted Payback Period in Excel
- How to Calculate Incremental Cash Flow in Excel
- How to Forecast Cash Flow in Excel

**<< Go Back to ****Excel Cash Flow Formula** **| ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**

Hello, the computation of the fraction year value doesn’t work if the first positive CF has a smaller absolute value than the last negative CF. How do I adjust the formula?

Hello I would like to thank you for your effort, and invet you to visit Qatar

Hello

Nafez,You are most welcome and thanks for you invitation to visit Qatar.

Regards

ExcelDemy