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.
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.
Read More: How to Calculate Payback Period in Excel
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.
Read More: Calculating Payback Period in Excel with Uneven Cash Flows
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.
Read More: How to Calculate Discounted Payback Period in Excel
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.
Read More: How to Apply Discounted Cash Flow Formula in Excel
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.
Read More: How to Calculate Operating Cash Flow in Excel
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 Net Cash Flow 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 a Cash Flow Waterfall Chart 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.
Download Practice Workbook
Download the practice workbook below.
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.
Related Articles
<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
