How to Calculate the Payback Period in Excel Sheet with formula?

Listen to this:

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

This means that it will not evaluate the project based on the 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 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.

Types of Payback Period

There are two types of payback period that are popularly used –

Non-discounted payback period – This is the general payback period. It does not take into account the time value of money while calculating the time taken to recover the initial cost of investment.

Discounted Payback Period – Discounted payback period is the time taken to recover the initial cost of investment, but it is calculated by discounting all the future cash flows. This method of calculation does take the time value of money into the account.

Features of the Payback Period Formula

The payback period formula has some unique features which make it a preferred tool for valuation. Some of these are –

1. This formula is one of the easiest ways for businesses to understand the time it’ll take for their operations to reach break-even point (where there is no profit and no loss).
2. This method is very suitable for small businesses with small cash balances, as it is a simple formula that requires minimal and basic information.
3. Out of the various factors which affect a business’s ability to reach the break-even point, it focuses most on the speed of recovery of investment.
   

Advantages and Disadvantages of Payback Period formula

Advantages

1. As mentioned before, it is the most straightforward way to calculate the time taken to reach the break-even point.
2. This formula can be applied towards any kind of project, irrespective of time duration, capital size, etc.

Disadvantages

1. The biggest disadvantage of this formula is that it originally does not take the time value of money into the account. This disadvantage is countered by the discounted payback period formula.
2. The formula does not take into account any risk. It only considers the speed with which we can recover our initial capital.
3. Since the formula does not consider the capital size, it can only be used in combination with other techniques when it comes to capital budgeting.

How to calculate in excel?

Just like many other valuation techniques, the payback period can be calculated with the help of MS Excel. Calculating it through this method is the easiest way to do it for finance and non-finance managers.

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

In the above excel sheet, you will see that the company earns a cumulative cash flow of Rs. 832 at the end of the fifth year.

Learn advanced excel concepts with Advanced Excel Tutorial Online by Market Experts

Hence, the amount yet to be recovered –

Initial cash flow invested (outflow) – total cumulative cash flows (inflow) = 900-832 = Rs. 68

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:

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 the payback 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 payback period.

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

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.

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/-.

Frequently Asked Questions

Conclusion

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.

Learn more about various concepts of excel using the advanced excel course.