Calculating Payback Period in Excel with Uneven Cash Flows

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.

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.

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.

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

Calculate Payback Period with Uneven Cash Flow.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!