How do I set up your Excel formulas?

Rule #1 investors can use Excel formulas for figuring out three things:

   1. The future EPS
   2. The future value per share
   3. The present value per share (or what I call the Sticker Price)

Start by collecting three ingredients (just like baking a cake):

1. Current twelve month EPS - On Investools it's automatically on the valuation page. On MSN Money go to Company Report and find it on the Fundamental Data list. It's also available on the first page you see after searching for a stock symbol, to the right of the intraday chart.
2. 10 year average PE (or 5 year if 10 years aren't available) - On Investools it's automatically on the valuation page. On MSN Money go to Financial Results, Key Ratios, Price Ratios.
3. Estimated EPS growth rate - On Investools the analyst estimate is automatically on the valuation page. Change it if you think it's too high. On MSN go to earnings estimates, earnings growth rates and you'll see the analysts' average estimate. Use a lower one if it's too high.

To figure out Future EPS:

Using the Excel =FV formula -- which you get by selecting a cell and typing =FV( -- you are asked by Excel for these things:

=fv(rate, nper, pmt, [pv], [type]}

• For rate, put in the estimated growth rate as a percentage, like 20%.
• For nper, put in 9 (Representing a 9-year span between year 1 and year 10 of data; if you only have 5 years of data, you'd put in a 4.)
• For pmt put in nothing but leave the comma.
• For [pv] put in the current TTM EPS with a minus sign in front and no dollar sign, like this: -1.04
• Hit enter and it will calculate the future EPS out ten years.

Next, you have to calculate the Future Stock Price out ten years using the future EPS and the PE you decided on.

1. Use whichever is lower, the historical PE you got, or 2 times the growth rate you used above.
   
   
2. Multiply the future EPS by the PE and you get the future stock price out ten years.
   
   
3. Now calculate the present value or Sticker Price (retail, intrinsic value, "what it's worth", etc.)

For this calculation you need the future stock price and the Minimum Acceptable Rate of Return (which for me is always 15%).

Using the Excel formula =PV() you'll need to plug in the following:

    * rate - Minimum acceptable rate of return - 15%
    * nper - 10 (to bring the future back to today, since we went ten years out)
    * pmt - skip but leave the comma
    * [fv] - the future stock price we got as a negative, like this: -88.43
    * [type] - skip
    * Hit enter and you'll get the Sticker Price.

Since I use 15% and 15% always compounds with two doubles in ten years, I can do this Excel step in my head. I just divide the future stock price by 4.

So for me, if the future stock price is 88, I immediately see the Sticker is $22. Try it with the formula and you'll see what I mean. The answer will be the same either way.

Final step:

Divide the Sticker by half and you get the MOS. Margin of Safety.

And you're done.

For more information including a visual Tutorial, register here and visit the Excel Formulas guide in the Resources section.