Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel functions that we can use to create formulas. Many banks and other financial institutions use excel files to store important data. They need to calculate various outputs, and itâ€™s easier to do that in excel. This article will show you the step-by-step procedures to calculate the payback period in Excel.

The length of time (Years/Months) needed to recover the initial capital back from an investment is called the Payback Period. This is a capital budgeting term. A shorter payback period is more lucrative in the case of investments contrary to more extended payback periods. After-tax cash flows are taken into consideration only for the calculation of payback periods. Now, go through the steps below carefully to Calculate Payback Period in Excel.

**Table of Contents**Expand

## STEP 1: Calculating Net Cash Flow

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

- Here, weâ€™ll calculate the net/cumulative cash flow.
- For this reason, select cell
**E5**. - Then, type the formula:

`=C5-D5`

- Subsequently, press
**Enter**. - After that, 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. So, learn the process to determine the break-even point.

- Firstly, 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.- Consequently, 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. Weâ€™ll use **the VLOOKUP function** to retrieve that value easily.

- For that purpose, click cell
**D13**. - Here, input the formula:

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

- Afterward, press
**Enter**.

## STEP 4: Using VLOOKUP to Find Next Yearâ€™s Cash FlowÂ

Similarly, weâ€™ll look for the cash flow (**In**) that we have after that last negative cash flow.

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

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

- Hence, press
**Enter**to return the result.

## STEP 5: Computing Fractional Year Value with ABS Function

Weâ€™ll insert **the ABS function** as the fractional value of a year canâ€™t be negative.

- First of all, select cell
**D15**. - Then, insert the formula:

`=ABS(D13/D14)`

- Subsequently, press
**Enter**.

## STEP 6: Calculating Payback Period

Finally, weâ€™ll calculate the payback period.

- In this regard, click cell
**D16**. - Now, type the formula:

`=D12 + D15`

- Next, press
**Enter**. - Thus, youâ€™ll get the accurate payback period in years.

- However, it can be confusing to see
**25**years as a year should be an integer value. - To convert it to months, in cell
**D17**, input the formula:

`=D16*12`

- Lastly, press
**Enter**to get the output in months format.

## STEP 7: Inserting Chart to Show Payback Period in Excel

Moreover, we can insert charts to get the **Payback Period**.

- Choose the ranges
**B5:B10**and**E5:E10**at first.

- Now, go to
**Insert**âž¤**Line Chart**âž¤**2-D Line Chart with Markers**.

- As a result, itâ€™ll return a line chart as shown below.
- In that chart, youâ€™ll see the approximate value where it crosses the
**X-axis**. - That is the payback period.
- But with this chart, we canâ€™t get the exact value as we have shown earlier.

Therefore, our **Payback Period **calculating template in **Excel **is ready to demonstrate. Look at the following picture where our final output is displayed.

**Download Practice Workbook**

Download the following template to practice by yourself.

## Conclusion

Henceforth, you will be able to** Calculate Payback Period **in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

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