Online Investing Hacks

Prevent Growth Calculations from Blowing Up

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.

Calculating Growth Between Start and End Dates

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 for estimating ...