CAGR Edge Cases: calculating compound growth with negative numbers and zeros

Have you ever worked on a business or finance project and ran into a situation where you were analyzing compound growth rates only to be faced with negative starting/ending values, or even worse, zeros? These values break the most widely used CAGR formula, leaving analysts with no option other than assigning their calculations a value of n.a. or nm, which contribute little to no analytical value. I have encountered this situation many times in the last few years, and after getting tired of losing my analysis to this issue, I decided to do something about it.

In this article, I present custom formulas that are designed to tackle the issue of calculating CAGRs with negative numbers and zeros, as well as a detailed discussion of their underlying mathematics. I have included VBA code for a custom formula that allows Excel to do these calculations for every combination of positive/negative numbers and zeros in one user friendly package. You can download it in the text file below or find it embedded within the body of the article. I hope you find these resources and discussion to be helpful or, at the very least, interesting.

Calculating CAGR with negative numbers and Zeros.pdf

Download PDF • 237KB

CAGR Adusted Formula.txt

Download TXT • 1KB