Advertisements

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:

Payback Period - Question

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

Payback Period - Solution

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?

Payback Period excel example (19037 downloads)
Advertisements

14 Comments

MiguelVG · December 1, 2014 at 3:44 pm

This post is fantastic. I have saved a lot of time thanks to your example.

SB · January 20, 2016 at 9:42 am

it doesn’t work if the payback is less than 1 year

    Ajay · January 24, 2016 at 9:37 am

    But you wouldn’t need a formula for that one.

Socrates-Finance · May 13, 2016 at 12:10 am

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.

Keith Gozon · May 20, 2016 at 6:48 am

Where can I download the excel file?

    Ajay · May 20, 2016 at 9:50 am

    I’ve fixed the link. You should be able to download the file now.

Henry · December 15, 2016 at 1:38 pm

Ajay,

Any chance you can update time value of money?

    Ajay · December 17, 2016 at 7:51 pm

    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

Yermek · January 26, 2017 at 8:31 am

thank you

Hafsa · March 13, 2017 at 11:44 am

can anyone plz tel me why we add +1 in the whole formula ?

    Ajay · March 25, 2017 at 5:28 pm

    That gets the first positive number because we’re initially searching for numbers less than 0

D · July 25, 2017 at 4:52 am

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.

Z · February 8, 2019 at 11:33 am

Thank you! Excellent tutorial!

Alex · April 12, 2019 at 9:56 am

I was getting an error using the formula if Period 1 is already positive, so I added this line as a pre-condition

=IF(D10>0,ABS(C10/D8)

…And ended up with :

=IF(D10>0,ABS(C10/D8), COUNTIF(D10:J10,”<0")+ABS(INDEX(D10:J10,,COUNTIF(D10:J10,"<0")))/INDEX(D8:J8,,COUNTIF(D10:J10,"<0")+1))

Solved my problem but can anybody comment if I made the correct adjustment?

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.