Calculating Payback Period in Excel with Uneven Cash Flows

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.

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.

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

Calculate Payback Period with Uneven Cash Flow.xlsx