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.

**Table of Contents**Expand

## 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 Calculate Payback Period 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 Payback Period with Uneven Cash Flows

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

**Download Practice Workbook**

Download this practice workbook below.

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

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