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. 
Comments
30 Responses to “How to Estimate Earnings Growth with Excel”
Leave a Reply
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
Hi Uday,
Thanks for the comments. I actually attended UF in Gainesville – so we’re not so far away!
-Lumilog
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
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
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.
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
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.
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
Whaooooo Ur analysis is superb. I really understand how to calculate the EPS in MS EXCEL.
Keep it up.
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?
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
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.
Hey thanks much Anon! Great to hear back from visitors!
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
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
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
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…..
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.
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
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
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?
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
how do you add the predicted points in your graph?
i just plot it as another series
Hai
Can you tell what are the methods for estimating future earning by using previous years financial statement.
Some Research contributors are doing estimats based on company’s Guidance, for what extent it is supposable, is it reliable .
Thanks
Gangadhar
Hi Gangadhar,
There are too many methods of estimating future earnings based on the past to mention – so many different kinds of models! Apart from the one I listed above (least squares fit), another interesting technique is to model the important Income Statement line items related to earnings (sales, cogs, etc.) as normally distributed random variables with a standard deviation based on the past. Doing it that way, you get a range rather than an exact number for what future earnings are likely to be, which might allow you to predict best and worst case, not just the expectation based on average.
Hope that helps,
-lumi
Hi
What is the 2nd interesting method by which we can get the range rather than the exact number? Can you talk about it detail?
Thanks
Divakar
Hi Div,
Here is an example using the current numbers for what looks to be a highly predictable business, PAYX. Right now, the last 10 years EPS’s were:
.51 .68 .73 .78 .8 .97 1.22 1.35 1.56 1.48
If you compute what the % gain in EPS is each year, you get:
33.3% 7.4% 6.8% 2.6% 21.3% 25.8% 10.7% 15.6% -5.1%
Now the mean of the above % gains is 13.1% and the standard deviation is 12.1%.
If the returns are normally distributed, then 68% of the returns should be between +/- 1 standard deviation from the mean and 95% of the returns should be between +/- 2 standard deviations.
Therefore, using the 68% interval we may predict that next year’s EPS will grow by some % between 13.1% + 12.1% and 13.1% – 12.1%. This give us a range of 25.2% and 1.0%.
So since last year’s EPS was $1.48 we predict next year’s EPS will be between $1.48 x 1.252 and $1.48 x 1.0100, which is an EPS range between $1.85 and $1.49.
For more insight, Instead of doing this on EPS you can do it on all the factors that combine to give EPS (sales, cogs, sg&a, depreciation, etc.) . You might want to build a pdf that matches the actual historical data too, instead of just assuming everything is normally distributed.
Your forecasting methods are useful and straight forward, thanx for that. What forecast methods can you use when you are in a Zimbabwean environment where financial results were reported in the local currency under hyperinflationary conditions. In essence, financials had no relevence. The country has dollarised , and firms have only produced their half years’ in US dollar.What are the ideal forecasting methods in such a scenario?
Thanks for writing Warren.
Hyperinflation gives an interesting twist and I’m only familiar with how to handle it in financial statements at a high level. Of course a major problem with recent statements is that assets may be carried at historical values, and if you translate them into dollars at the current rate they look like they’re worth far less than they really are. To bring balance sheets up to date you have to go through the pain of multiplying items that are carried at historical cost (usually non-monetary) by the change in price index since their time of purchase.
From a pure forecasting EPS point of view, my best guess would be that if you had the historical inflation data, you could subtract it out from EPS growth in order to get the real growth. Then you’d need to add back in your best guess for inflation for the next year or two in order to forecast future EPS. Have you tried something like this?
Best,
Lumi