How to find the payback period in Excel?

The payback period is the time in which your initial investment is expected to be recovered from the cash inflows that the investment will generate in future or the coming years. 

It is one of the simplest techniques to know the feasibility of any investment or the project.

• The project is preferred over another if its payback period is shorter than that of the second project.

It is very easy to calculate the payback period of any investment using Excel. 

​Let us learn to calculate it in different situations:
​

Cash flows are even & exact multiple

Please look at the following example:

• There is a project which requires an investment of $5,00,000 (Cash outflow), producing $ 1,00,000 (cash inflow) every year for the next 6 years.

Payback period calculation when cash flows are even and the exact multiple of the initial investment

You can draw the table shown above and write the expected cash flows for the subsequent years. As the cash flows are equal, the payback period calculation is simple and can be written as:

• Payback period = Initial investment/Cash inflow per period
  

In the above case, the payback period is:

• 5,00,000/$ 1,00,000 = 5 years

It means that it is going to take 5 years to recover your initial investment of $ 5,00,000.

​The project will achieve the break-even at the end of the 6th year because the cash inflows will start from 2nd year on wards and thereafter it will take 5 years to achieve the break-even.

Equal cash flows but not exact multiple

In the following example:
​

• The initial investment is $ 4,00,000 and the subsequent cash flows are $ 70,000 every year which are not an exact multiple of the initial investment of $ 4,00,000.
  

Here also, the formula will remain the same, the only difference is that in this case the payback period is not the exact number but it is in decimals, 5.71 years. 

Equal cash flows but not exact multiple of the initial investment

• In the above screenshot the payback period is 5.71 years.

​

When the cash flows are not equal

When the cash flows are uneven as shown in the following example then it becomes difficult to know the payback period simply by dividing the cash investment by the cash inflow per period

Here we need to use the concept of the cumulative cash inflows.

• The cumulative cash flows are the sum of all the cash flows during the life of the project.

The moment cumulative cash flows exceed the initial investment is called the break-even point of the project (The break-even point is the point of no profit and no loss).

​And the time taken to reach the break-even point is the payback period of the investment.

When the cash flows are not equal

The formula, in this case, is as follows:

• Payback period = The value of the year in which last negative cumulative cash flow occurred + (value of the cumulative cash flow in that year divided by the cash inflow in the next year)

Referring to the above screenshot, you can write as follows:

• Payback = 5 + ABS (-60,000/80,000) = 5 + 0.75 = 5.75 years

Therefore, the payback period of the project is 5.75 years.

(*The ABS function is used to get the absolute value)

But this a manual method to find the payback period, as you need to calculate the payback period manually every time when considering the new project.

• We can design the simple calculator to find the payback period using the COUNTIF and the VLOOKUP function.

The steps involved in designing this simple calculator are as follows:

• Step 1: Count the number of years, using COUNTIF function, till the last negative cumulative cash flow
• Step 2: Look for the last negative value of the cumulative cash flow using VLOOKUP function
• Step 3: Look for the next year’s cash inflow using VLOOKUP function
• Step 4: Divide the value of step 2 by the value of step 3
• Step 5: Take the absolute value, using ABS function, of the result obtained in step 4
• Step 6: Add the output obtained in the step 4 with the output of step 1 and you get the payback period of the investment

Step 1:

In the previous example, first, count the number of years in which the cumulative cash flows are negative using COUNTIF function.


= COUNTIF(C2:C8,”<0”)


Upon writing this formula, it searches for the condition to be true within the range you have specified and returns the answer, 5.

Counting the number of years till the cumulative cash flows are negative

It means that there are 5 years which are having the negative cumulative cashflows.

Step 2: 

VLOOKUP to find the value of the last negative cumulative cash flow:
​
This function retrieves the data from the specific column based on the condition specified by the user.

Finding the value of last negative cumulative cash flow using VLOOKUP function

The syntax of the VLOOKUP functions is as follows:

= VLOOKUP (Lookup value, Table array, Column index, [Range lookup])

The lookup value is the value which you have obtained using the COUNTIF function whose output is in the cell D9, 5 years.

In the Table array selects the whole table from A1:C8.

In the column index write that column number in which your desired value exists like in this case, the desired value is $ -60,000 and it is in column 3.

The VLOOKUP function will look for this value in the 3rd column corresponding to the 5th year.

• (*Kindly ignore the [Range lookup]

Step 3: 

VLOOKUP to find the cash inflow in the next year:

Similarly, we can look for the cash inflow for the next year that is 5 + 1 = 6th year.

This can be done as follows:

= VLOOKUP (D9+1, A1:C8,2)
​
This will look for the value in column 2 corresponding to the 6th year and produce the result $ 80,000

Finding the value of cash flow using the VLOOKUP function

Step 4 & 5 

​Finding the fractional value and extracting the absolute value:

This can be done as follows:

Finding the fractional value of the payback period

Step 6:
​
​The payback period:

Finding the payback period of the investment

We can further reduce the number of cells by combining the outputs of VLOOKUP function into one cell.

Combining VLOOKUP functions into one cell:
​
=VLOOKUP(D9,A1:C8,3)/VLOOKUP(D9+1,A1:C8,2)

Combing VLOOKUP function into one cell

Combining the absolute value and the fractional value in one cell:

• =COUNTIF(C2:C8,"<0")+ABS(VLOOKUP(COUNTIF(C2:C8," <0"),A1:C8,3)/VLOOKUP(COUNTIF(C2:C8,"<0")+1,A1:C8,2))

Combing absolute value and fractional value to get the payback period

Now, this has become a simple calculator which automatically calculates the payback period of the investment or the project.

​You can consider different projects with different cash flows but the life of the project should be 7 years as I have designed for the project life of 7 years.

Conclusion

​
Although it is one of the easiest methods to determine the feasibility of the investment but it doesn’t consider the time value of money while determining the payback period.
​
Therefore, it can’t be used alone for decision making. It is the crude method to shortlist the project. The project having a shorter payback period is often considered desirable than the one having the longer payback period.