How to Estimate Earnings Growth with Excel

Note: Please read the disclaimer. The author is not providing professional investing advice.

One factor strongly affecting a stock’s price is earnings growth. All other things being equal, if a stock’s earnings grow by 10% in a given year, we might expect the stock’s price to also rise by 10% in order to maintain about the same P/E ratio.

So before buying a stock, we’d like to get an idea of how earnings may grow in the next year, two years, or more. One way would be to look up the analysts’ predictions on future earnings-per-share (EPS) in either free or fee publications.

However we’ve all read stories of analysts having historically been overly optimistic, having poor track records, and being pressured by their employers to give high growth estimates for certain companies.

But if you have Excel, you can enter some simple formulas and become your own stock analyst. I’m not claiming Excel will give you a more accurate estimate – we’re trying to predict the future just like the analysts, which isn’t easy. But at least we know Excel’s predictions will only be biased by one thing – the actual historical earnings of the company. 🙂

Past performance may have nothing to do with that of the future! But if we have no other technique for predicting the future, it seems a good place to start…

Let’s begin with a fictional, idealized company – ticker IDEAL – that has been growing earnings at 15% per year historically.


Year 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007
EPS $1.00 $1.15 $1.32 $1.52 $1.75 $2.01 $2.31 $2.66 $3.06 $3.52

est_eps_1

Visually, you can probably guess where you’d estimate the EPS to fall for the next year or two. You’d draw a curve connecting the dots and extend it out to see where future EPSs may land.

But we’d like the computer to draw such a line for us. Especially since for real companies, the historical EPSs probably won’t fall in a predictable simple curve like this first example.

What we want, without getting into higher order polynomials, is for the computer to give us an equation for a simple straight line that is the best fit for the historical data points that we have.

The general equation for a straight line is y=m*x+b, where m is the slope and b is the y-intercept. We might make this equation more intuitive by writing it instead as:

EPS = m*YEAR + b

Once Excel supplies the m and b, our job is to only pick what YEAR we’re interested in and the equation above gives us an estimate of EPS.

In order to get m and b, we put the numbers in our table above into Excel. Let’s start with slope (m).

slope

Note that above the formula field shows exactly how we call Excel’s slope function in order to get the slope of the “best fit” line given our set of data points. In this case, Excel returns a slope (m) of 0.2749.

Let’s now add a call to Excel’s intercept function in order to get the second unknown variable in our equation, the y-intercept (b).

y-intercept

So now we have our EPS predicting equation:

EPS = 0.2749*YEAR – 548.09

If we use that equation and plug in the next two years (2007 and 2008), we get $3.54 and $3.82 respectively. So Excel predicts earnings next year to only grow by 0.5% (calculated from next year’s expected $3.54 divided by last year’s actual $3.52).

Let’s see what our best-fit curve looks like…

eps_est_2

Seeing the plot above may give us pause. Even for our so-called “ideal” company, our best-fit curve is not ideal because it doesn’t pass through all points. And it’s because we’re trying to approximate an exponential curve with a straight line.

We have two options if we’d like to fix this. Assuming we don’t want to replace y=mx+b with a more complicated higher-order polynomial, we can simply take the logarithm of all points which will transform our exponential curve into a straight line.

eps_est_3

Y-axis is now plotting log(EPS)


Note: We can only do this if our historical EPS numbers are greater than 0 (log of a negative number is undefined).

Then we perform our same calls to slope and intercept, predict our future log-EPSs and perform 10log_EPS to get back to the linear domain.

Like so…

log_est

These predictions of future EPS are quite different than our linear ones. Whereas we found earlier that linear predicted a 0.5% earnings growth, log predicts (correctly) our ideal 15% (from next year’s predicted $4.05 divided by last year’s actual $3.52).

Let’s plot our results.

eps_est_4

So our log curve is right on the money whereas the linear is a quite off. Truth be told, for more moderate growth rates, there isn’t as much of a difference between the two.

Ready to try it out on a real stock? How about Abercrombie?

eps_est_5

Hmmm… The linear prediction seems a bit pessimistic whereas the log is perhaps overly optimistic. But we at least have an idea as to where we might place our prediction. Let’s compare what our two curves predict, versus what analysts are currently predicting.


Year Linear Log Analyst
2008 $4.13 $5.27 $5.19
2009 $4.51 $6.45 $5.96


So linear predicts a 10% drop in earnings next year, log predicts an increase of 15%, and the analysts are predicting a similar increase of 13%.

UPDATE (February 15, 2008): ANF just released earnings for last year of $5.20. So our prediction of the linear estimate ($4.13) being too pessimistic and the log estimate ($5.27) being too optimistic turned out to be true after all.

OK, one more. How about the Waltons?

eps_est_6

This time linear feels more believable. Let’s compare our predictions to the experts.


Year Linear Log Analyst
2008 $3.10 $3.60 $3.05
2009 $3.34 $4.15 $3.41


For next year linear predicts a growth of 6%, log predicts 23%, and the analysts say 4%.

Clearly there’s no winner for all situations. It might even be helpful to average predictions from the various methods. But if you’re a value investor looking for margin of safety, you’ll probably want to use the most pessimistic of the predictions.

Voila – you’re now your own stock earnings analyst. 😉

54 thoughts on “How to Estimate Earnings Growth with Excel”

  1. hi ashwag – answer to q1 is yes. there really is no right answer to q2. i picked 10 just because that’s a number people commonly use to make sure your historical data includes both periods of expansion and recession. good luck!

  2. first thank for your answer
    but i need for reference or any book or academic paper that used this method (simple straight line)
    thank for your help

Leave a Reply

Your email address will not be published.