How to calculate Payback Period in Excel

Payback period in capital budgeting refers to the period of time required for the return on an investment to “repay” the sum of the original investment. For example, a $1000 investment which returned $500 per year would have a two year payback period. The time value of money is not taken into account. Payback period intuitively measures how long something takes to “pay for itself.” All else being equal, shorter payback periods are preferable to longer payback periods. Payback period is widely used because of its ease of use despite the recognized limitations described below.

– via Wikipedia

The Question

That’s the definition and theory behind this. I find that calculating the payback on paper is actually quite easy. Let’s look at the following example:

In this example, the initial investment is made in 2013 with cash flows received from the investment from 2014 onwards.

Since payback period doesn’t care about time value of money, a quick look at the Cumulative cash flows line will tell you that the payback period is between 2017 and 2018, i.e. when the Cumulative cash flows exceed the Initial investment.

This is good for an approximate idea of the payback period. But, in most likelihood, you’ll want a more accurate number.

The solution

The next part just walks you through the excel example. If you wish, you can skip ahead to the bottom of this post to grab the excel example.

When calculating the payback period, you will also need to calculate the fraction of the year. In our example it is a number between 4 and 5. If we had to do this on paper, we would calculate this as follows:

Payback period
= No. of years before first positive cumulative cash flow + (Absolute value of last negative cumulative cash flow / Cash flow in the year of first positive cumulative cash flow)
= 4 + (|-138| / 243 )
= 4 + 0.57
= 4.57

The above screenshot gives you the formulae that I have used to determine the Payback period in Excel.

The Integer

This one is a simple COUNTIF formula. It counts all the number of negative cash flows in the Cumulative cash flows line before it turns positive from years 2014 to 2020.

This gives us the number 4.

The Fraction

This one is a bit complicated and consists of two parts. The first part involves calculating the fraction for each of the years which you will find in the Fraction calculations line and the second option is to use INDEX to pick the right year of the fraction.

The excel file containing the example has all the calculations that you need. I’ve also included an additional example to calculate the payback period within a single cell i.e. doing away with the Fraction calculations line.

How do you calculate Payback period? Do you use any other method?