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
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 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:
= 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
The above screenshot gives you the formulae that I have used to determine the Payback period in Excel.
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.
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?
Download “Payback Period excel example”Payback-period-example.zip – Downloaded 29100 times – 9 KB
This post is fantastic. I have saved a lot of time thanks to your example.
it doesn’t work if the payback is less than 1 year
But you wouldn’t need a formula for that one.
This post does not factor in the time value of money; consider using NPER function in excel if the firm-wide discount rate is known.
Where can I download the excel file?
I’ve fixed the link. You should be able to download the file now.
Any chance you can update time value of money?
Not sure I understand. The time value wouldn’t apply to the payback period given it’s a check to see how long can you recover your investment dollar for dollar
can anyone plz tel me why we add +1 in the whole formula ?
That gets the first positive number because we’re initially searching for numbers less than 0
Thanks – this is a useful formula.
Wold be great if the bonus calculation did show the fraction of a year when the payback is less than one year.
Thank you! Excellent tutorial!
I was getting an error using the formula if Period 1 is already positive, so I added this line as a pre-condition
…And ended up with :
Solved my problem but can anybody comment if I made the correct adjustment?