In this article, we discuss how to calculate the payback period on an investment with uneven cash flows in Excel.

## What Is Uneven Cash Flow?

**Uneven cash flow** simply means the cash inflow of a current account is not constant and can change over time. Both Fixed and the **Uneven cash flow** amounts help determine the **NPV **(Net Present Value) of an investment. There are many sources of **Uneven Cash flows**, like different types of assets or bonds that don’t return interest regularly, also known as “non-conventional bonds” or “vanilla bonds”.

## Overview of the Payback Period

**Definition**

**The time it takes for a business to recoup its investment is known as the “payback period”.**

**Formula to Calculate the Payback Period**

The basic formula for the **Payback Period** is:

**Features of the Payback Period**

- The
**Payback Period**is a simple calculation of the time it takes to recoup an investment. - It should be used in conjunction with other capital budgeting techniques. Although it can be used independently, the payback period shouldn’t be the sole factor in determining the feasibilityof an investment.
- When funds are limited, the payback period is a crucial consideration because it shows how long it will take to recover all of the money invested in a project.

**Benefits of Using the Payback Period**

**Simple and Easy to Understand**

The simplicity of the technique is its primary benefit. The payback period approach is very useful to a small business with limited financial resources.

**Ability to Evaluate Liquidity and Risk**

An investment’s liquidity and risk may be quickly calculated. One can assess how long funds will be committed to a project and whether or not that amount of time constitutes a risk to that endeavor.

**Comparable to other Measures**

The payback period may be utilized along with other capital budgeting variables like **NPV**, **IRR**, and cash on cash flow to assess an investment’s desirability.

**Averting Obsolescence**

The payback period is a major consideration for sectors that are prone to projects fast becoming outdated. If the investment is prone to obsolescence, a shorter payback time can help investors assess whether an investment can be recouped and a profit made before it becomes obsolete.

**Limitations of Using the Payback Period**

- It neglects the Time Value of Money.
- It only considers the Cash Flows.
- It contains Assumptions.
- It neglects the Lifespan of Assets.
- It doesn’t take into account the Liquidity of the Investor/Company.

## Calculating the Payback Period with Uneven Cash Flows in Excel

We will use the following dataset to demonstrate how to calculate the payback period with Uneven Cash Flows. We’ll apply 2 different methods: the traditional method, and using the **IF** function.

### Method 1 – Using the Conventional Formula

This method makes use of the **COUNTIF** and **VLOOKUP** functions. We’ll derive the **cumulative cash flow** from the given information, then calculate the Payback Period from this cash flow.

**Steps:**

- Select cell
**D5**and enter the following formula:

`=C5`

This inserts the first entry for the **cumulative cash flow** calculation.

- Select cell
**D6**and enter the following formula:

`=D5+C6`

- Drag the
**Fill Handle**down to cell**D10**.

The range **D5:D10** is filled with the **Cumulative Cash Flow **values.

- Select cell
**D12**and enter the following formula:

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

- Select cell
**D13**and enter the following formula:

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

- Select cell
**D14**and enter the following formula:

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

- Select cell
**D15**and enter the following formula using**the ABS function**:

`=ABS(D13/D14)`

- Select cell
**D16**and enter the following formula:

`=D12+D15`

The result of our calculations: the payback period with uneven cash flow is 4.75 years.

**Read More:** How to Calculate Payback Period in Excel

### Method 2 – Combining the IF and AND Functions

Now we’ll use the **IF **and **AND** functions to calculate the Payback Period with Uneven Cash Flows. Again, we’ll need to calculate the **cumulative cash flow** from the given information first.

**Steps:**

- Select cell
**D5**and enter the following formula:

`=C5`

We have the first entry for the cumulative cash flow calculation.

- Select cell
**D6**and enter the following formula:

`=D5+C6`

- Drag the
**Fill Handle**down to cell**D10**.

The range **D5:D10** is filled with the **Cumulative Cash Flow **values.

- Select cell
**E6**and enter the following formula:

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

After entering this formula, no Payback Period value will be displayed in cell **E6**. The formula will only show the** payback period** after the *last* period of the payment cycle.

** Formula Breakdown**

**AND(D6<0,D7>0)**

The **AND **function returns a logical **TRUE** or **FALSE** depending on the conditions given as arguments inside the function. If both of the conditions are met, it will return **TRUE**, otherwise, it will return** FALSE**.

**IF(AND(D6<0,D7>0),B6+(-D6/C7),””)**

The **IF **function checks whether the output returned from the **AND **function is **TRUE**. If it is **TRUE**, then it will execute the next argument of the formula. Otherwise, it will execute the last argument, which is **NULL** here.

- Drag the
**Fill Handle down**to cell**E10**.

The formula searches for the last cash flow period and returns the **Payback Period** at the end of it.

**Interpretation of the Output**

The definition of a good or preferable payback period varies, but the general assumption is that the shorter the period the better, because of the liquidity it provides. However in some cases, a longer payback period is acceptable or even preferred. At the same time, the payback period requires adjustment to coincide with the project completion time.

The preferable payback time is different for **B2B **compared to **B2C **business types. For example:

- A payback period of less than one month, six months, or twelve months is acceptable for a business-to-consumer venture, limiting the risk in recovering their upfront investment.
- For
**B2B**companies selling to small and medium-sized businesses (SMBs), less than six months is ideal, 12 months is acceptable, and even 18 months is acceptable. In exceptional cases, customers might prepay their contract enabling recoupment of all investment costs up-front. - Less than 12 months, 18 months, and 24 months are acceptable for
**B2B**businesses selling to large corporations, since the risk of default or obsolescence is significantly lower.

We didn’t declare in which category our example investment above resides, so the viability of a payback period of 4.5 years needs to be considered against the project completion time and other factors.

**Read More: **How to Calculate Payback Period with Uneven Cash Flows

** Things to Remember**

- When calculating the payback period, keep the cash flow and the cumulative cash flow columns in
**Currency**format. - The cumulative cash flow is essential for determining the payback period when faced with unbalanced cash flows.
- When using the conventional technique, total years of negative cash flow and the fractional period are required to calculate the total payback period.

**Download Practice Workbook**

**Related Articles**

- How to Calculate Discounted Payback Period in Excel
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel
- How to Create a Cash Flow Waterfall Chart in Excel
- How to Calculate Incremental Cash Flow in Excel
- How to Calculate Cumulative Cash Flow in Excel

**<< Go Back to ****Excel Cash Flow Formula** **| ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**