MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure

How to find the payback period in Excel?

14/3/2019

1 Comment

 
calculate payback period
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 in Excel
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
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.

Cash flows are not equal
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.
COUNTIF function
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.
VLOOKUP function in Excel
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 cash flow using VLOOKUP
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:
Fractional value of payback period
Finding the fractional value of the payback period
Step 6:
​

​The payback period:
Payback period in Excel
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)
Combining VLOOKUP function
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))
Absolute and fraction value
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.
1 Comment
paper writing service australia link
26/9/2019 01:05:35 pm

Excel is one of the programs that you need to be good at. I mean, it is one of those software that will allow you to get a lot of work done. Most of its functions are for calculating and date entry, though. It will not hurt to at least try learning it, you know. I really encourage people to go and learn it as soon as they can. This can be an advantage for you on your next job interview.

Reply



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    May 2019
    April 2019
    March 2019
    February 2019


    Categories

    All
    Grow Your Business


    RSS Feed

Copyright Mend My Ladder @ 2021
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure