How to calculate the annual compounded growth in the case of negative growth? Pls check the following, A company has following Net Profit for the five years, Year PAT 2003 12 2004 13 2005 09 2006 03 2007 -01 The moment you do typical CAGR calculation with (^.25) it throws up error. Both GP(adding the yearly growth with 1) and CAGR(taking the start and end figure) calculation fail. What is the way out?

The answer should be -39%. I calculated this excel using the RATE function. =RATE(5,0,12,-1). Use PMT=0. If the beg and end values have different signs, CAGR can not be calculated. You can also use XIRR to get the result.

Maybe I’m completely lost here, but why wouldn’t GP work? 1.12x1.13x1.09x1.03x.99 = 1.4067 1.4067^(1/5) - 1 = 7.06% ?

First of all GP to be calculated on the growth, meaning (13-12)/12. Second the period to be used is 1/4 and 1/5 as there are only 4 interim periods in between. Try using ^(1/4) and then see what happens

Reposting as there was connectivity issue, First of all GP to be calculated on the growth, meaning (13-12)/12 and then 1 need to be added. But between 4th and 5th year the growth becomes negative, so even adding 1 will not make it positive. Secondly, the period to be used is ^1/4 and not ^1/5 as there are only 4 interim periods. Try using ^(1/4) and then see what happens. If 12 becomes -1 in five years, then that cannot be 7.06% growth!!!

Growth can never found out with the help of RATE or XIRR or for that matter any similar NPV kind of calculation, as there the underlying assumption is that there are investments and the return from it. Here, we are merely trying to find out the year on year growth (positive or negative). In the example given, the negative growth can be calculated by adding 2 to yearly growth instead of 1 and multiply and square rooting them and then reducing 2 from the final result. The negative growth in this case is around -66%.