when the starting point is a negative number…
Calculating CAGR is not difficult, all we need is the starting value, ending value and the number of periods. Then we use the formula:
CAGR = (Ending Value / Beginning Value ) ^ (1 / N) -1 where N is the number of periods to reach the ending period
CAGR stands for Compound Annual Growth Rate. The formula does not require any values in between because it does not matter. It is a “backward” calculation for the “average” annual growth with known figures. In other words, if I have $100 on the first year and it magically becomes $300 by the end of the fourth year , and CAGR will be: 31.61%.
To validate if the result is correct, I work “forward” by calculating the value of each year by applying the growth rate of 31.61%. We can do it in Excel easily:
Note: the second formula (in C3) is referencing to C2, not B1. From C3, you may copy the formula down.
I always do this kind of validation in order to double click the result. I am not in doubt with the formula (which has been proven correct); I am in doubt with myself… As human, it’s so easy to make “human” error.
Well, the validation gives me peace of mind by returning consistent results, even with different values input…
Everything works fine until…. a negative value is input:
Well, if you are a finance (or math) people, you may pinpoint that CAGR cannot be computed from a negative starting value… we should start from first positive value and adjust the number of period accordingly. Totally agree.
However, in real world situation, the requester (probably your boss) is not a finance nor a math people. S/he just gives you three values and asks for the answer. So the question is: Can we still do the calculation is an efficient way with Excel, when the starting value is a negative number? Of course.