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

  2. Hi Uday,

    Thanks for the comments. I actually attended UF in Gainesville – so we’re not so far away!

    -Lumilog

  3. 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

  4. 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

  5. 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.

  6. 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

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

  8. 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

  9. 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?

  10. 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.

  11. 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

  12. 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

  13. 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

  14. Pingback: Everything Finance
  15. 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.

  16. 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

  17. 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

  18. 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?

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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.

  24. 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?

  25. 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

  26. hello lumi,

    i think i’m a bit late comer on this great article but i found this very informative. however, i just don’t get to work to get some values on my spreadsheet with that of the values posted here like the log ๐Ÿ™ can you help me with this? thanks in advance!

  27. I am from Mumbai, India. Where can one get past 10 years EPS and historical dividend payout ratio.

    Thank you

  28. hello lumi,

    i’m using your data in my spreadsheet onething that is not working for me is the log(EPS) column c in your screen shot here what’s the formula for that column please let me know. many thanks!

  29. Shimi – for example, for cell C5 the value should be:

    =log(B5)

    You’ll run into problems if the value in the B column is negative, in which case you have to add an offset (and later remove it) to be able to do log( ).

    Hope this helps!

  30. Hello, Lumi,
    Very nice job. I am also like you, turn form engineering area to finance area, or you can say it is still engineering, which is financial engineering.

    This artical is about estimate EPS, so I want to ask how can we use logistic regression method to estimate the EPS? I present this method is because it was used very well by someone to predict political election and even Oscar and world cup winner.

    I appreaciate a lot.

  31. Thanks for the comment Ian. I’m not overly familiar with logistic regression, but isn’t its answer usually just a Yes or No? Or maybe just the probability of something happening or not (you were thinking probability of missing what the street is expecting?). It’s certainly an interesting idea – maybe one to be solved more by multifactor models tuned over historical data in Matlab rather than using Excel’s linear regression. By the way, if you’re into predicting election results, Oscar winners, etc., you should check out the prediction markets at Intrade. Fascinating stuff…

    – Lumi

  32. I am confused since the intercept and slope remain the same for all future periods then by just changing the year we the results will be very close to each other. I am very new to this! i did it for a company i am analysing and this is what i got EPS growth rate from 2005 to 2018. how can you explain such big differences in the growth rate for the period we have data for but then EPS changes at almost constant rate thereafter. Thanks for your help ๐Ÿ™‚

    13.79
    36.36
    -3.33
    -21.84
    10.29
    10.40
    3.04
    2.95
    2.86
    2.78
    2.71
    2.64
    2.57

  33. Dear Lumilog

    Thanks your article I shall try to apply this at about 100 companies data listed in karachi stock exchange. But there is problem of negative EPS in between of 10 years data.

  34. Thanks for the comments, Tunio. Yes, negative EPS can be a problem. You might be better off changing the spreadsheet to work with future sales per share instead of EPS. Also have it estimate future net margin based on the 10-yr history the same way. Finally, multiple the estimate of future sales per share by future net margin to get an estimate of future EPS.

  35. Hi there,

    In column A of my excel file, I have the number of applications I received daily and in column B I have the Dates. Something like this:

    Column A Column B
    3 01/3/2012
    6 01/04/2012
    5 01/05/2012
    9 01/07/2012
    and so on for the last 3 months.

    Can I use the same steps you outlined above to predict the number of applications that I will anticipate to receive daily over the next 90 days based on the numbers that I have received daily over the last 90 days.

    Thank you so much in advance for your help!

    I also am interested in learning how to use Matlab if you do offer the lesson for a fee.

  36. Good day.
    Thank you so much for sharing such a valuable article. I truly appreciate it. Unfortunately, I’m a bit green still in excel so I have a question if you don’t mind. First how do you produce the graph? I can’t seem to find it in excel and I also have a problem plugging the numbers. In the YEAR, do you actually put “2007” for example or just the count, like “11”. Thank you so much in advance. More power.

  37. I have the same question as Israel, what exactly is the value that was plugged into YEAR? EPS = 0.2749*YEAR โ€“ 548.09

    After trying several values I am not coming up with the answer

    Thank you

  38. @Israel I think I figured it out, it is exactly as it says EPS = 0.2749*YEAR โ€“ 548.09

    I entered it like this =(0.2749*2007+ โ€“ 548.09)

    Ofcource you can click into the cells with the pre determined vales for 0.2749 and -548.09 since we already calculated those.

    Good luck

  39. Hi,

    Such a great article,
    Is this works with Quarterly data as well. If i take last 8/12 quarters EPS to forecast upcoming quarters EPS.

    Thanks in advance

    Regards,
    Nirav

  40. Shahid – the equation is very sensitive to rounding.

    For example:
    .27*2007 -548 = -6.11
    .275*2007 – 548.1 = 3.83
    .2749*2007 – 548.09 = 3.63
    .27485*2007 – 548.086 = 3.54

    So let Excel keep all the precision and compute it for you.

  41. my q about year in model
    EPS = m*YEAR + b
    if i want to calculate forecasting eps for year 2007 so I need to replace
    YEAR to 2007.
    second q about how many year I need to have for eps ?
    in your example we hold 10 year eps to calculate 2007 as I see

Leave a Reply

Your email address will not be published.