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 & the exact multiple of the initial investment
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.
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 of the initial investment
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.
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.
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:
In the previous example, first, count the number of years in which the cumulative cash flows are negative using COUNTIF function.
Upon writing this formula, it searches for the condition to be true within the range you have specified and returns the answer, 5.
It means that there are 5 years which are having the negative cumulative cashflows.
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.
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]
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
Step 4 & 5
Finding the fractional value and extracting the absolute value:
This can be done as follows:
The payback period:
We can further reduce the number of cells by combining the outputs of VLOOKUP function into one cell.
Combining VLOOKUP functions into one cell:
Combining the absolute value and the fractional value in one cell:
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.
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.