The payback period calculates how much time is required to return the initial capital from an investment. It can be calculated from even or uneven cash flows. This article will show **how to calculate the payback period** with uneven cash flows. I hope you find the article very informative and gain lots of knowledge regarding the payback period with uneven cash flows.

## Download Practice Workbook

Download the practice workbook below.

## What Is Uneven Cash Flows?

Uneven cash flows can be defined as the series of unequal payments paid over a given period. Here, the **cash flow** changes from time to time. So, there is no fixed amount. For example, a series of $2000, $5000, $3000, and $2500 over 4 different years can be defined as uneven cash flows. The basic difference between even and uneven cash flows is that in even cash flow, the payment will be equal over a given period whereas, the payment will be unequal in terms of uneven cash flows.

## Overview of Payback Period

The payback period can be defined as the amount of time required to exceed the primary investment by using the cash inflows generated by the primary investment. It would help if you recover the primary investment and make a profit. The period shows you the exact time through which you can recover the initial costs. At the same time, a payback period will help you to evaluate the risks of the project.

There are two types of payback periods – short time payback period and long time payback period. For a short time payback period, you need to have a higher cash inflow in the initial stage. As a result, you can recover your initial investment quite easily and gain some profit. Whereas, the long-time payback period gives you a higher cash inflow in the later stage. So, we need more time to recover your initial investment compared to the short time payback period. In the payback period, the break-even point is another vital element. By using the break-even point, you may know the point of time when you recover your initial investment and finally, start to see the profit.

There are some major advantages of using the payback period. First of all, the calculation of the payback period is very simple and user-friendly. It can identify the risk inherent in a project. The period can also indicate how the project cash inflows are. It would provide a good ranking of projects which would return an early profit. You can also get to know the liquidity of any investment. Finally, it also provides a platform to reinvest and earn a profit in a new project. Investment with a short payback period makes the profit funds available to invest in another business.

The formula to calculate the payback period can be established by knowing the behavior of cash flows whether it is even or uneven. When the cash inflows are uneven, you need to** calculate the cumulative cash flows** for each period and then apply the following formula.

## 2 Easy Methods to Calculate Payback Period with Uneven Cash Flows

To calculate the payback period with uneven cash flows, we have found two different methods through which you can have a clear idea. These two methods include a conventional formula for calculating the payback period and the IF function. Both of them are very user-friendly.

### 1. Using Conventional Formula

Our first method is based on using the conventional formula for calculating the payback period with uneven cash flow. In this method, we will calculate step-by-step, after that we will get the payback period with uneven cash flow. To understand the method, follow the steps.

**Step 1: Calculate Cumulative Cash Flows **

First, we need to create the dataset including cash flows and cumulative cash flows. As our investment is cash outflow, so, we denote it as a negative value. Then, we need to add yearly cash inflow. After that, using these values, we will create the cumulative cash flows column. Follow the steps.

- Select cell
**D6**. - Then, write down the following formula in the formula box.

`=D5+C6`

- Then, press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the column.

**Step 2: Calculate Negative Cash Flow Years**

Then, we want to calculate the number of years in which we have negative cash flows. Where cumulative cash flows are in excess of the primary investment, this is referred to as the break-even point. So, the time needed to go to that point is known as the payback period. That’s why the number of negative cash flows is important. To count the number of negative cash flow years, we use **the COUNTIF function**. Follow the steps.

- First, select cell
**D12**. - Then, write down the following formula

`=COUNTIF(D6:D10,"<0")`

🔎 **Breakdown of the Formula**

**COUNTIF(D6:D10,”<0″): **The **COUNTIF** function returns the total number of a certain value using the range and criteria. Here, we give a range of cumulative cash flows from cell **D6** to **D10. **After that, we set a criterion that the cash flow must be less than zero which means a negative cash flow. So, the **COUNTIF** function takes both range and criteria and returns the total number of negative cash flows.

- Then, press
**Enter**to apply the formula.

**Step 3: Find Final Negative Cash Flow**

After that, we will find the last opposite cash flow. We can do it manually but when the dataset is large then, it takes lots of time. To make it more dynamic, we can use **the VLOOKUP function** and find out the final opposite cash flow in the cumulative cash flows column. Follow the steps.

- First, select cell
**D13**. - Then, write down the following formula.

`=VLOOKUP(D12,B4:D10,3)`

**🔎 Breakdown of the Formula**

**VLOOKUP(D12,B4:D10,3): **The **VLOOKUP** value returns a value using the given range and lookup value. Here, we denote the lookup value cell **D12**. Then, set the range of cell **B4** to **D10** as a table array. After that, we denote column no. of our dataset. The **VLOOKUP** value will return the last negative value from the cumulative cash flows column.

- After that, press
**Enter**to apply the formula.

**Step 4: Estimate Cash Flow for Next Year **

After that, we need to find the cash flow for the year after getting the final negative cash flow. To find this next year’s cash flow, you use the **VLOOKUP** function again. But we do some modifications to have the cash flow in the next year. Follow the steps.

- First, select cell
**D14**. - Then, write down the following formula.

**= VLOOKUP(D12+1,B6:D10,2)**

**🔎 Breakdown of the Formula **

**VLOOKUP(D12+1,B6:D10,2): **The **VLOOKUP** value returns a value using the given range and lookup value. Here, we denote the lookup value cell **D12+1 **because we want to get the next year’s cash flow. Then, set the range of cell **B6** to **D10** as a table array. After that, we denote column no. of our dataset. The **VLOOKUP** value will return the cash flow of the year after getting the last negative cash flow in the cash flows column.

- After that, press
**Enter**to apply the formula.

**Step 5: Calculate Fractional Period **

Then, we need to estimate the fractional period through which you will get the exact time when the payback period is over. The fractional period is the ratio of the last opposite cash flow and cash flow in the year after. As this value denotes the period. So, it can’t be negative. That’s why we utilize **the ABS function** to calculate the fractional period. Follow the steps.

- First, select cell
**D15**. - Then, write down the following formula.

`=ABS(D13/D14)`

- After that, press
**Enter**to apply the formula.

**Step 6: Calculate Payback Period **

Finally, we can find out the total payback period by adding the negative cash flow years and fractional period. The summation of these will give us the payback period with uneven cash flows. Follow the steps.

- Select cell
**D16**. - Then, write down the following formula.

`=D12+D15`

- After that, press
**Enter**to apply the formula.

**Read More: ****How to Calculate Future Value of Uneven Cash Flows in Excel**

### 2. Applying IF Function

Our second method is based on using **the IF function**. In this method, we will take some uneven cash flows and create cumulative cash flows. After that using the** IF** function, we will calculate our desired payback period. To understand the method clearly, follow the steps.

**Steps **

- First, we want to calculate the cumulative cash flows column.
- We need to invest in a business that is why the first column refers to negative cash flow.
- Then, from the first year, we have cash in flow.
- So, using the investment amount and cash inflow, we will create the cumulative cash flows column.
- First, select cell
**D6**.

- Then, write down the following formula in the formula box. Here, the primary investment is negative, so we need to add this with cash inflow.

`=D5+C6`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column.

- Then, we need to create the payback period column.
- Select cell
**E6**. - Then, write down the following formula.

`=IF(AND(D6<0,D7>0),B6+(-D6/C7),"")`

**🔎 Breakdown of the Formula**

**IF(AND(D6<0,D7>0),B6+(-D6/C7),””): **First, the **IF** function checks whether the value of cell **D6** is less than zero and the value of cell **D7** is greater than zero. These two criteria are in the **AND** function. If both conditions meet then it will go to the next step. Otherwise, it will return a blank. Here, both cells **D6** and **D7** are less than zero. So, the **IF** function returns a blank. When the conditions meet, the **IF** function returns the value using the given formula.

- After that, press
**Enter**to apply the formula. - You will get blank in cell
**E6**because it doesn’t match the criteria. - Then, drag the
**Fill Handle**icon down the column. - It will show the required period where it matches the criteria.

- There we have our required payback period with uneven cash flows. See the screenshot.

**Read More: ****How to Create Cash Flow Statement Format in Excel**

## Things to Remember

- To calculate the payback period with uneven cash flows, the cumulative cash flow is a must. Otherwise, you can’t get an accurate answer.
- To get the total payback period, you need to add the total number of negative cash flow years and fractional period while approaching the conventional method.

## Conclusion

To calculate the payback period with uneven cash flow, we have shown two different methods including the conventional formula and by using the IF function. Both of these methods are fairly easy to use. These two methods can easily calculate the payback period. I hope we covered all possible areas regarding the payback period with uneven cash flows. If you have further questions, feel free to ask in the comment box. Don’t forget to visit our **Exceldemy** page.

**Related Articles**

**How to Calculate Incremental Cash Flow in Excel (2 Examples)****Calculate Present Value of Future Cash Flows in Excel****How to Calculate Net Cash Flow in Excel (3 Suitable Examples)****Prepare Daily Cash Flow Statement Format in Excel****How to Calculate Operating Cash Flow in Excel (2 Easy Ways)****Create Cash Flow Statement Format with Indirect Method in Excel****Cash Flow Statement Format in Excel for Construction Company**