Payback period

How to Calculate Payback Period in Excel Sheet: (with formula)

by Shruti Agarwal on Basic Finance, Investing Basics
  • 1

What is Payback Period?

The payback period helps us to calculate the time taken to recover the initial cost of investment without considering the time value of money.

Also Read Time Value of Money

Note: The payback period does not consider the time value of money. This means that it will not evaluate the project based on present value of money but on the basis of the actual investment made.

Also, the shorter the payback the better it is as we are recovering the initial investment deployed in the business.

This method is mostly used by private companies because they are more concerned about the liquidity.Once they invest in a huge project their capital is blocked. Hence, they want to know the time duration in which they will get back the initial investment made so that they can deploy the same in other ventures.

How to calculate payback period in excel

We will understand the process of calculation  of the payback period with the help of an example:

Calculate Payback Period in Excel: (with formula) 1

In the above excel sheet, you will see that the company earns the cumulative cash flow of Rs. 832 at the end of the fifth year. Hence, the amount yet to be recovered will be the initial cash flow invested (outflow) – total cumulative cash flows (inflow) = 900-832 = Rs. 68, as shown above.

Now, the time taken to recover the balance amount of Rs. 68 i.e the time taken to generate this amount will be 0.22 years (68/308). Hence, the total pay-back period will be 4+0.22 = 4.22 years, as below:

Calculate Payback Period in Excel: (with formula) 2

So now we know that 4.22 is the payback period in which we will recover our initial cost of investment of Rs. 900/-

The calculation of pay-back method is not an accurate method as it ignores the time value of money, which is a very important concept. Also, this period does not consider the cash flows received after the pay-back period. Hence, it is not a very appropriate method on a stand-alone basis.

The time value of money, which is an important element, can be taken care of while calculating the discounted pay-back period.

To understand payback calculation method in a more detailed manner you may check out Certification in Online Finance for Non-Finance Managers.

Let us understand the concept of the discounted pay-back period with the help of the previous example. In the case of discounted pay-back period, we need to calculate the present value of the cash flows.

payback period in excel

payback period excel formula

In the above figure you will see that the cumulative amount (ignoring the decimal) aggregates to Rs. 851, which means that even till 5 years, we have not been able to recover the initial investment made.

Hence, we will get the negative of Rs. 49, as shown below:

NPV = Outflow – Inflow = [-900 – (-851)] = (49)

So in this case, as the project is not recovering my initial cash flow, we are not able to calculate the payback period, but it will surely be 5+ years. So that is the difference when taking the discounting payback period.

To get a better understanding of excel functions and their application in finance you may check out Online Tutorial on Advanced Excel on Elearnmarkets

Once you do not take the time value of money into consideration, the results are positive as we are able to complete the project within the life of the project. On the other hand, if we consider the time value of money, the project goes negative. As seen in the above example, we will not be able to recover the money and will have a loss of Rs. 49/-.

Note: The discounted payback period will always be lower than the payback period because we are using the discounted cash flow which will always be lower than the normal cash flows.

So by now, we have learned the steps for estimating cash flows, few methodologies for estimating our project results. Apart from these, there are also other accounting methodologies that can be used.

  • 1

Disclaimer wants to remind you that all our content is created solely for the purpose of education. No strategy, stock, commodity, fund or any other security discussed here is any way a recommendation for trading or investing. will not be any way responsible for trading losses incurred by any individual or entity for trading with real money. Please take advise of certified financial advisers before trading or investing.

Please leave a comment