# How to Calculate Payback Period with Uneven Cash Flows

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.

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.

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.

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.

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+C6`

The 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.

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

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF