HOW TO SET UP EXCEL FORMULAS

I've had a lot of emails come in asking how to set up formulas in Excel. (Here's a link to an online Excel tutorial for beginners.) Basically, we 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 list of stuff.)
2. 10 year average PE (or 5 year) - (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.)

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 10 (for ten years)
• 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.

Now you have to calculate the future stock price out ten years using the future eps and the PE you decided on.  Use whichever is lower, the historical PE you got, or 2 times the growth rate you used above. Multiply the future eps by the PE and you get the future stock price out ten years.

 

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.

CAUTION: This is ONLY to be relied on if the business is PREDICTABLE (which means it has meaning, moat and management) from demonstrated CONSISTENCY AND DURABILITY. Otherwise you're just playing head games.

 

And thank God for great technical tools that track the big guys moving big money.  Don't forget to watch and respond.  And in no case should you become so convinced in your superior analyzing skill that you ignore what's going on in the market.  Remember that the big guys know way more than we do about any business we're investing in and will take action to get out if something is going wrong -- something that the general public hasn't sniffed out yet.  When they start leaving the theater it may be because they're smelling smoke -- and you'd best depart with them if you don't want to burn.

 

Now go play.

Phil