This article will demonstrate two methods how to calculate the payback period in the case of uneven cash flows.
What Are Uneven Cash Flows?
Uneven cash flows can be defined as a series of unequal payments paid over a given period. The cash flow changes from time to time, so there is no fixed repayment amount. For example, a series of payments 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 flows, the payments are equal over a given period, whereas the payment amounts can be different in uneven cash flows.
Overview of Payback Period
The payback period can be defined as the amount of time required to repay the primary investment by using the cash inflows it generates. The value indicates the exact time it will take to recover initial costs, and helps 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, a higher cash inflow is required in the initial stage. As a result, initial investment is recovered quite quickly. Conversely, the long-time payback period provides a higher cash inflow at a later stage, so more time is required to recover initial investment in comparison. The break-even point (the point at which initial investment is recovered and profitability is attained) is an important factor.
Some of the advantages of using a payback period are:
- The calculation of the payback period is very simple and user-friendly.
- It can identify the risk inherent in a project.
- It can indicate the size and quality of the project cash inflows.
- It can provide a good ranking of projects which would return an early profit.
- It indicates the liquidity of any investment.
The formula to calculate the payback period can be established by knowing the behavior of cash flows whether they be even or uneven. When the cash inflows are uneven, we’ll 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
Method 1 – Using the Conventional Formula
Step 1 – Calculate Cumulative Cash Flows
First, we need to create the dataset including cash flows and cumulative cash flows. As our initial investment is a cash outflow, we denote it as a negative value. To this we add the yearly cash inflows. Using these values, we can create the cumulative cash flows column.
Steps:
- Select cell D6.
- Enter the following formula in the formula box:
=D5+C6
- Press Enter to apply the formula.
- Drag the Fill Handle icon down to fill the values for the rest of the column.
Read More: How to Calculate Payback Period in Excel
Step 2 – Calculate Negative Cash Flow Years
Next, we need to calculate the number of years in which we have negative cash flows. The point at which cumulative cash flows equal or are greater than the primary investment is referred to as the break-even point. The time needed to arrive at that point is known as the payback period. To count the number of negative cash flow years, we use the COUNTIF function.
- Select cell D12.
- Enter the following formula:
=COUNTIF(D6:D10,"<0")
Breakdown of the Formula
COUNTIF(D6:D10,”<0″):Â The COUNTIF function finds the number of occurrences of values that meet a criterion in a range. Here, we provide a range of cumulative cash flows from cell D6 to D10. We set a criterion that the cash flow must be less than zero (negative cash flow).
- Press Enter to apply the formula.
Read More: Calculating Payback Period in Excel with Uneven Cash Flows
Step 3 – Find the Final Negative Cash Flow
Next we identify the last negative cash flow. This can be done manually, but this approach is not practical when the dataset is large. Instead, we can use the VLOOKUP function.
- Select cell D13.
- Enter the following formula:
=VLOOKUP(D12,B4:D10,3)
Breakdown of the Formula
VLOOKUP(D12,B4:D10,3): The VLOOKUP function returns a value using the given range and lookup value. Here, we denote the lookup value cell as D12. We set the range of cell B4 to D10 as the table array. Then we denote the column number of our dataset from which to return the result values. The VLOOKUP function will return the last negative value from the Cumulative Cash Flows column.
- Press Enter to apply the formula.
Read More: How to Calculate Discounted Payback Period in Excel
Step 4 – Estimate Cash Flow for Next Year
Next, we’ll find the cash flow for the next year by using the VLOOKUP function again.
- Select cell D14.
- Enter the following formula:
=VLOOKUP(D12+1,B6:D10,2)
Breakdown of the Formula
VLOOKUP(D12+1,B6:D10,2): We denote the lookup value cell D12+1 because we want to get the next year’s cash flow. We set the range B6 to D10 as the table array, and the column number from which to return the result. The VLOOKUP function will return the last cash flow of the year.
- Press Enter to apply the formula.
Read More: How to Apply Discounted Cash Flow Formula in Excel
Step 5 – Calculate the Fractional Period
The fractional period is the ratio of the last negative cash flow against the cash flow in the year after. As this value denotes a period, it can’t be negative, so we utilize the ABS function to calculate the fractional period.
- Select cell D15.
- Enter the following formula:
=ABS(D13/D14)
- Press Enter to apply the formula.
Read More: How to Calculate Operating Cash Flow in Excel
Step 6 – Calculate the Payback Period
Finally, we can determine the total payback period by adding the negative cash flow years and fractional period.
- Select cell D16.
- Enter the following formula:
=D12+D15
- Press Enter to apply the formula.
Method 2 – Using the IF Function
We can also use the IF function. We will take some uneven cash flows and create cumulative cash flows. Then, using the IF function, we will calculate our desired payback period.
Steps:
First, we need to calculate the cumulative cash flows column.
The first column contains a negative cash flow, indicating the initial investment cash outflow. From the first year onwards, we have cash inflows. Using the investment amount and cash inflows, we will create the cumulative cash flows column.
- Select cell D6.
- Enter the following formula in the formula box:
=D5+C6The primary investment is negative, so we need to add this to the cash inflow.
- Press Enter to apply the formula.
- Drag the Fill Handle down the column.
Now we create the payback period column.
- Select cell E6.
- Enter 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),””): 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 are met then the function will go to the next step, otherwise, it will return a blank. Here, since both cells D6 and D7Â are less than zero, the IF function returns a blank.
- Press Enter to apply the formula.
A blank is returned in cell E6 because it doesn’t match the criteria.
- Drag the Fill Handle icon down the column.
The required period is shown where it matches the criteria.
We have our required payback period with uneven cash flows.
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 when using the conventional method, add the total number of negative cash flow years and the fractional period.
Download Practice Workbook
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!
