Payback function/formula with a growth rate

Hi all,

I am trying to figure out how to use a simple function to determine the payback time (in years) of a investment. The trick is that I expect the earnings to grow every year, however, this growth will be at a determined rate. I have tried the NPER function and it is not producing the correct answer as I have calculated it mannualy to determine if my function would work. Here is my example:

If I were to purchase a company for $17,500,000 (market cap), whose earnings are currently $2,300,000, and which I expect those earnings to grow at an annual rate of 12%. I want to know how many years it would take me to earn back the $17,500,000 with the $2,300,000 annual earnings with compunded growth.

I manually calculated this to be 5.25 years which you will see in the spreadsheet (more than 5 years but less then 6 years). NPER function gave me 21.55 years. Anyone have any ideas??

Payback_Growth Rate.xls

I apologize my formula was not correct it would need to be constructed so that: 17,500,000 =(2300000*(1.12^1))+(2300000*(1.12^2).....)

I guess I am even having trouble putting together the algebraic equation, which is why I included my desired answer in the spreadsheet. Thanks again for the help

This calculation seems to work, however, since $2,300,000 is year 0 it should not be counted towards the payback. The $2,300,000 is a trailing number and is just used as a base to determine year 1 and so on. Can you figure out a way to negate the year 0 amount of $2,300,000 from affecting the payback?

This is more complicated then I originally thought

Originally Posted by WorldBridge

In my formula the initial 2,300,000 is not used except to solve for x.

My formula returns the correct value...

If you look at my spreadsheet you will see that the cumulative annual income amount does not reach $17,500,000 until after year 5, so the answer has to be greater than 5 years and less then 6. The only reason why I trust my answer is because I can see it laid out on the spreadsheet and follow the logic. Do you see my discrepency?

Please don't take this as disrespect or me be argumentative, I really apprciate you helping me out. I am just trying to get this formula to match my calculations

Thanks WorldBridge, this seems to have solved the equation. Just out of curiosity why is 9.33333 added in the numerator and denominator of the "number" part of the function?

Originally Posted by MoneyMaker

=nper(rate, pmt, pv, fv, type)
=nper(12%, -2.3, 0, 17.5, 1)
5.26085

This is how Excel would do the calculations internally

[ log(FV * RATE + PMT * (1 + RATE) ) - log(PMT * (1 + RATE)) ] / log(1+ RATE)
[ log(17.5 * 0.12 + 2.3 * ( 1.12 ) ) - log(2.3 * ( 1.12 )) ] / log( 1.12)
[ log(4.676) - log(2.3 * ( 1.12 )) ] / log( 1.12)
[ log(4.676) - log(2.576) ] / log( 1.12)
[ 1.542443043609822 - log(2.576) ] / log( 1.12)
[ 1.542443043609822 - 0.9462378082421072 ] / log( 1.12)
[ 1.542443043609822 - 0.9462378082421072 ] / 0.11332868530700327
0.5962052353677147 / 0.11332868530700327
NPER = 5.260850187687403

Originally Posted by MoneyMaker

=nper(rate, pmt, pv, fv, type)
=nper(12%, -2.3, 0, 17.5, 1)
5.26085

The NPER is 5.26085 when interest is compounded discretely as in periodic compounding.

If the interest were to be compounded continuously, the NPER will be shorter as the interest factor used in compounding is higher.

You would then use the following Excel function

=tadNPER(RATE, PMT, PV, FV, TYPE, Compounding)
=tadNPER(12%, -2.3, 0, 17.5, 1, 1)
5.17321

My apologizes MoneyMaker, I did not see your responses as I was not receiving email alerts when this thread was responded to. I was able to solve this equation by using a logarithm (which I will post later) but your solution is definitely much more simplistic and was the direction I was originally headed in. Thanks so much!