For any business undertaking any kind of decision, especially whether to make an investment in a new venture or reinvest in an old project, **calculating the payback period **can actually help a lot. If you are curious to know how you can calculate the payback period in Excel, then this article may come in handy for you. In this article, we discuss how you complete **calculating the payback period in Excel with uneven cash flows** with elaborate explanations.

## Download Practice Workbook

Download this practice workbook below.

## What Is Uneven Cash Flow?

**Uneven cash flow** simply means the cash inflow of a current account is not constant and can change with respect to time. Both the Fixed and the **Uneven cash flow** are important because they help The auditor will determine the **NPV**(Net Present Value). This **NPV** value will help with important financial decisions. The sources of these** Uneven Cash flows** are many, like different types of assets or bonds that actually do not return interest regularly. These bonds are also known as the “non-conventional bond” or the “vanilla bond”.

## Overview of Payback Period

**Definition**

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

**Formula of Payback Period**

The basic formula for the **Payback Period**.

**Features of Payback Period**

- The
**Payback Period**is a simple calculation of the time it takes for the initial investment to return. The**payback period**is a straightforward calculation of how long it will take for the initial investment to pay off. - In addition to other capital budgeting techniques, it can also be used independently. In spite of its simplicity, the
**payback period**cannot be the sole factor in selecting a project. - With limited funds, the payback period usually preferred since it shows how long it will take to recover all of the money invested in the project.. Due to its ability to demonstrate how long it will take to recoup project funding, the
**payback period**is highly valued by companies with limited available cash.

**Benefits of Using Payback Period**

**Being Simplistic and Easy to Understand**

Its simplicity is the primary benefit. The payback period approach is very beneficial 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 danger 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 factor for sectors that are prone to projects fast becoming outdated.

If the investment is prone to frequent Obsolescence, then a shorter payback time can help investors to recoup the investment and even make a profit before the investment become obsolete.

**Limitations of Using Payback Period**

- Neglecting the Time Value of Money
- Considering Only the Cash Flows
- Assumptions and Evaluation
- Lifespan of Assets
- Liquidity of Investor/Company

## 2 Easy Ways of Calculating Payback Period in Excel with Uneven Cash Flows

We will use the following data set to demonstrate how you can calculate the payback period in Excel with Uneven Cash Flows. Two separate methods, one is the traditional method and another one is using the **IF** function.

### 1. Implementing Conventional Formula

In this method, we are going to implement the conventional method using which we can calculate the Payback Period in Excel with Uneven cash Flows. We will also use the help from the **COUNTIF** and **VLOOKUP** functions.

**Steps**

- We need to calculate the
**cumulative cash flow**of the given information. - To do this, select cell
**D5**and enter the following formula:

`=C5`

- Doing this will enter the first entry for the
**cumulative cash flow**calculation.

- Then select cell
**D6**and enter the following formula:

`=D5+C6`

- Then drag the
**Fill Handle**to cell**D10**. - Doing this will fill the range of cell
**D5:D10**with the**Cumulative Cash Flow**values.

- Then select the cell
**D12**and enter the following formula:

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

- After that, select cell
**D13**and enter the following formula:

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

- After that, select cell
**D14**and enter the following formula:

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

- After that, select cell
**D15**and enter the following formula. Here, we are using**the ABS function**.

`=ABS(D13/D14)`

- After that, select cell
**D16**and enter the following formula:

`=D12+D15`

- In this article, we discuss how you complete
**calculating the payback period**in Excel with**uneven cash flow**using the traditional method with elaborate explanations.

**Read More:** **How to Apply Discounted Cash Flow Formula in Excel**

### 2. Combining IF with AND Function

Here, We will use the help from the **IF **and **AND** functions for calculating the Payback Period in Excel with Uneven Cash Flows.

**Steps**

- We need to calculate the
**cumulative cash flow**of the given information. - To do this, select cell
**D5**and enter the following formula:

`=C5`

- Doing this will enter the first entry for the cumulative cash flow calculation.

- Then select cell
**D6**and enter the following formula:

`=D5+C6`

- Then drag the
**Fill Handle**to cell**D10**. - Doing this will fill the range of cell
**D5:D10**with the**Cumulative Cash Flow**values.

- Right after this, select cell
**E6**and enter the following formula:

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

- Entering this formula will not show any sign of value in cell
**E6,**because the formula will show the**payback period**only at the last period of the payment cycle.

**🔎 Formula Breakdown**

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

In this **AND **function, it returns a logical **TRUE** or **FALSE** depending on the condition given as an argument inside the function. If both of the conditions are present, it will return **TRUE**, otherwise, it will return** FALSE**.

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

Here, the **IF **function will check whether the return 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** in this case.

- Drag the
**Fill Handle**to cell**E10**. - Doing this will make the formula search for the last cash flow period and then return the
**Payback Period**at the end of it.

- In this article, we discuss how you complete
**calculating the payback period**using the**IF**function with elaborate explanations.

**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. A shorter **payback period** is attractive because of the liquidity it provides. But in some cases, a longer **payback period** is okay. At the same moment, the** payback period **requires adjustment to coincide with the project completion time. For the **B2B**, and **B2C** business types, the preferable **payback time** varies. Like the below interpretations.

- A
**payback period**of less than one month, six months, or twelve months is acceptable for a**business-to-consumer**venture. Additionally, in rare circumstances, they may be able to recover 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 18 months is acceptable. And similarly, the exceptional cases get their customers to prepay their contract and recoup all acquisition costs up-front. - Less than 12 months, 18 months, and 24 months are acceptable for
**B2B**businesses selling to large corporations. Six months is a reasonable time frame for exceptional cases to recoup their investment.

We didn’t declare in which category our investment resides. The payback period of 4.5 years needs to be accessed by the project completion time first. Then we can determine its validity according to the features mentioned above.

**Read More:**** How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)**

**💬 Things to Remember**

- While calculating the
**payback period**, you need to 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. If you don’t, you won’t be able to obtain an accurate response. - Total years of negative cash flow and fractional period are needed to calculate the total
**payback period**when using the traditional technique.

## Conclusion

To sum it up, the issue of how you can calculate the Payback period in Excel is presented here with 2 examples.

For this problem, a workbook is available for this problem where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.

**Related Articles**

**Create Cash Flow Statement Format Using Direct Method in Excel****Create Cash Flow Statement Format with Indirect Method in Excel****How to Create Cash Flow Statement Format in Excel****Cash Flow Statement Format in Excel for Construction Company****How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)****How to Calculate Discounted Cash Flow in Excel (with Easy Steps)**