**Excel’s regression functions
are great for calculating compound growth rates unless
there’s a zero or negative value in the mix;
conditional statements prevent these values from destroying your
calculations**.

Arguably, the two most important
metrics
for the long-term, fundamental investor are the strength of a
company’s earnings growth and the stability of that
growth over time. Growth is usually expressed as an annualized,
compounded rate, because that makes it easier to compare growth rates
between companies. Stability is most often measured using
the coefficient of determination or
R-Squared
**[Hack #35]**
.
You can handily generate both of these metrics using functions in
Excel as long as the values you want to evaluate are positive.
However, introduce a zero, a negative number, or a null value into
the data and the calculation generates an error message of one sort
or another, because it is, in fact, mathematically impossible to
calculate a compound annual growth rate from either a zero or a
negative value. Because companies do have bad years and data does
contain zeros and negative numbers, you can use any of several
workarounds to prevent errors when you calculate growth.

One way to overcome
these error messages is to
calculate the growth rate by distributing the change in value between
a beginning date and an end date. To estimate the average
**[Hack #26]**
.

Example 2-14. Formula ...

Start Free Trial

No credit card required