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

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