How to Estimate Earnings Growth with Excel
Note: Please read the disclaimer. The author is not providing professional investing advice or recommendations.
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 |
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:
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).
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).
So now we have our EPS predicting equation:
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…
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.
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…
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.
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?
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?
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. 
Technorati Tags: Earnings Per Share, Earnings Growth, Investing, Money, Stock Analyst, Stock Market
October 2nd, 2007 at 9:50 am
This is awesome. I am also kind of in the same situation. Have my MS in Industrial Engineering and am preparing to take my Level I CFA exam in June’08. I noticed that you got your degree from USF (am I right?). I work at University of Central Florida in Orlando.
I really enjoyed your analysis on EPS and your web site in general. Keep up the good work.
Thanks!
Uday
October 2nd, 2007 at 10:43 am
Hi Uday,
Thanks for the comments. I actually attended UF in Gainesville - so we’re not so far away!
-Lumilog
October 8th, 2007 at 11:40 pm
Hi what if the EPS for the past years comes in negative as well the slope doesnt seem to work?? Eg: of data (Excel actually showed #N/A when I plot in the figures. So deos that means if there is negative figures the slope cannot be used?
Year EPS
2002 -0.02
2003 -0.0002
2004 1.149
2005 1.155
2006 0.783
October 9th, 2007 at 6:36 am
If the historical EPS data includes negative numbers you have a few options.
(1) Just use the regular linear (but not log) curve-fitting like is done in the first half of the article above. In this case I get a predicted EPS of $1.44 for 2007 and $1.72 for 2008.
(2) Use the log version, but add an offset to all EPSs to make them all positive, perform your curve fitting, and then take the offset back out. I tried this using 0.75 for the offset and got numbers that seem pretty far out ($2.58 for 2007 and $3.28 for 2008) so I don’t really trust this method.
(3) Go back a shorter distance in time so that you have no negative EPSs. In your case, that would mean only using years 2004-2006. If we do this we get a slope showing a decreasing EPS. Linear predicts 2007 will be $0.66 and 2008 will be $0.48. Log predicts a similar $0.69 for 2007 and $0.57 for 2008.
Personally, I only consider these techniques of predicting the future based on the past likely to be somewhat accurate if the stock has shown consistency from year to year - meaning the company may have some sort of edge (good management, economic moat, etc.) - that makes the business predictable from year-to-year.
If the stock’s historical data is all over the map, I skip over it and try to find to find another company that seems to be more predictable for my money.
Hope this helps,
Lumilog
October 11th, 2007 at 5:55 pm
Hi,
I am having trouble making the SLOPE and INTERCEPT functions work. I tried with my own set of data and then I tried with yours. When I used your data I got these numbers: SLOPE=3.53 INTERCEPT=1994.34. Perhaps I am over looking something simple? Such as the setting of these cells?
Thanks for any help you can give, and great entry here!
-John B.
October 11th, 2007 at 9:19 pm
Hi John,
The problem is the order of your arguments.
The slope and intercept functions want the Y-values (EPS) for the 1st argument, X-values (Year) for the 2nd argument and I think you’re doing it the other way around.
I ran into the same problem when I first started using these functions.
-Lumilog
October 19th, 2007 at 9:09 am
I did the calculations for the slope and Y-Int and ended with results as follows: (i) .27503 and (ii) -548.443. Why the different outcomes? Yet, I get the EPS correctly for the years 2007 & 2008.
October 19th, 2007 at 10:50 am
Yes your numbers are very close to my (i) .27485 and (ii) -548.086.
I think it’s round-off error. While the numbers in my table only show 2 decimal places, they actually have higher resolution because each cell is computed as 1.15 times the previous.
For example, the 4th EPS entry that says $1.52 is actually $1.5208750 and so forth.
-Lumilog
November 12th, 2007 at 10:39 am
Whaooooo Ur analysis is superb. I really understand how to calculate the EPS in MS EXCEL.
Keep it up.
November 21st, 2007 at 12:25 am
Do you know of anywhere that can you get 8 years of annual EPS data short of reading 8 annual reports per company and looking up the historical stock price info?
November 21st, 2007 at 7:36 am
Yes MSN Money has it for free. It’s not that easy to find clicking through their menus, so here’s a direct link for Exxon (XOM). Just change the ticker to whatever company you’re interested in:
http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=xom&lstStatement=10YearSummary&stmtView=Ann
December 4th, 2007 at 4:48 am
Dear Lumilog,
Thank you very much for this extremely useful method of forecasting. It’s been a pleasure getting tips from brilliant people like you. I’ve favourited your website as I love reading/getting ideas from brainy people. Once again, thanks a million.
December 4th, 2007 at 7:07 pm
Hey thanks much Anon! Great to hear back from visitors!
December 24th, 2007 at 9:36 am
hey,
its very intresting but one thing please see the following figures
2003 0.96
2004 1.12
2005 1.39
2006 1.58
for that figures i am not able performe log EPS, pls caliculate and revert back with detailed caliculations,
bye
sridhar
January 6th, 2008 at 12:26 pm
Hi Sridhar,
I don’t see any problem with those figures. Excel gives me the following estimates for future EPS:
linear: $1.79 for 2007 and $2.01 for 2008
log: $1.90 for 2007 and $2.26 for 2008
Probably safest to go with the linear prediction in this case - I don’t know how to include a plot in comments but the linear predicted points look more “in line” with the historical data.
- Lumilog
January 13th, 2008 at 11:45 am
Hi Lumilog,
Very helpful article, I was researching about EPS on the web for my Financial Accounting class and came across your article. Its very good.
Thanks
February 5th, 2008 at 8:58 am
Carnival of Everything Finance: # 12 Edition…
Welcome to the February 1, 2008 edition of Carnival of Everything Finance.
We had over 120 really good articles submitted for this edition. Unfortunately I could not include all of them.
Earning Money
Matthew Paulson presents What to Do When You C…..
February 7th, 2008 at 3:29 am
ASlamoalkum,
No doudt this is very fruitful artcile, which rellay minimized some misconceptions about eps and its caluclation. Good job. May Allah bestow you all with rightneous which led you all towrads path of glory.
February 11th, 2008 at 10:53 am
Hi Lumilog ,
how r u doing
thanks for a vivid explanation. I have just one question here , so when the EPS is negative for some years , then what type of analysis would analysts go for so as to get near real values
( i know it is an estimate !) ?
I too understand that u have explained the intercept method and ur conclusion states that it is not a very trustworthy method , if i am right ? so that is the reason I am putting forward this Q .
thanks
February 11th, 2008 at 11:44 am
Thanks for the blessings Brainica.
Neo - when the EPS is negative the linear fit still works, but for log you have to add an offset and then take it back out since the log of a negative number (or 0) is undefined.
In practice, analysts are certainly using something more sophisticated than this “rearview mirror” approach. It’s just a place to start if you have nothing else to go on, and also can give you a visual into how volatile earnings for a particular business have been.
-Lumilog
March 10th, 2008 at 8:52 pm
ellops.. my question is not related to thid section.. but i am really need your help.. i want to seek a correlation between the companies’s share price and their earnings per share (EPS).. however, there is a problem when the data for share price and EPS are in different term; weekly and quarterly respectively.. Does this differences will effect my analysis?
March 13th, 2008 at 4:33 pm
Hi Ameera,
If I’m understanding your question correctly, I think one way around this problem is to possibly extrapolate the EPS to the same timeframe as price-per-share so that you have the same number of data points for both for the correlation computation.
For example, say you had prices on a monthly basis, so for one quarter you have 3 prices (perhaps the open or closing price for each month). However you only have 1 EPS for that quarter, so extrapolate this to 3 points by multiplying that single EPS by 33% for the earnings at the end of month #1, 66% for the earnings at the end of month 2, and the EPS number as is for earnings at the end of the 3rd month (end of the quarter).
Or something along those lines. Hope you find something interesting!
Lumilog