Discover a simple technique to calculate if a stock is a good buy, based on easily-found financial data.

This approach is similar to the techniques pioneered by Warren Buffet and Benjamin Graham.

This guide teaches you how to calculate if a stock is undervalued or overvalued, given a set of conservative assumptions about its future growth prospects, and your desired return. If the current share price is lower than a calculated fair value, then the stock is a good buy.

This indicator is, however, a rough guide, and should only be used in the context of a wider analysis and your financial goals.

All the calculations are illustrated in Excel. Heres  the complete spreadsheet, but youll need to read the entire article to understand the method.

Well assume that were trying to value Exxon Mobile (ticker: XOM) and we plan to hold the shares for 3 years. (You can easily extend the approach below to any other stock and holding period).

Step 1 Collect Your Data

Well need the following data (all the data were correct at the time of writing March 17th 2013)

• Current share price. XOMs current share price is 89.37
• Current dividend per share. XOM have paid a quarterly dividend of 57 cents per share for the last 4 quarters. Hence their yearly dividend per share is 2.28.
• Current EPS. XOM have a trailing twelve month (ttm) EPS of 9.69. This is their diluted value, which accounts for outstanding options that would reduce the price.

We also need to make several assumptions

• Forward PE . Exxons current PE is 9.22 and forward PE is 10.93. So to be conservative, well assume that the PE for the next three years will be 10.
• EPS Growth. According to the FT. XOMs 5-year EPS growth is 5.96% (so lets call it an even 6%).

Enter all the data into a spreadsheet like so.

Step 2 Calculate EPS Over the Holding Period

Now well need to calculate the EPS for every year that we hold XOM, given our growth rate. So we simply take our current EPS of 9.69, and consecutively multiply it by 6% for each year.

The total EPS over the holding period of 3 years is simply the EPS in Year 1, 2 and 3 added together.

These calculations are entered into Excel as follows.

So at the end of Year 3, we have a total EPS of 32.70.

Step 3 Calculate Present Fair Value

So now comes the tricky part calculating the present fair value of XOMs shares, given our assumptions and parameters.

First, lets look at the calculations in Excel, and then well discuss them one by one.

The expected share price at the end of our holding period of 3 years is the EPS in Year 3 times the  forward PE assumption of 11. Thats 11.54 x 10 = 115.41.

The dividend payout ratio is the current dividend per share divided by the EPS in year 3. Thats 2.28/11.54 =0.2. Were using the EPS in Year 3 in order to be conservative and calculate a lower dividend payout ratio. (Using the current EPS would give a higher dividend payout ratio, but lets err on the side of safety.)

The total dividends per share over the entire 3 years is the dividend payout ratio times the total EPS over the 3 years. Thats 0.2 x 32.70 = 6.46.

Hence the expected share value at the end of the 3 year holding period is  the total dividends received over the 3 years plus the expected share price at the end of the 3 years. Thats 6.46 + 115.41 = 121.87.

Now, we need to discount the share value to the present day. If we expect to received a 10% yearly return for holding XOM shares for 3 years, then the present share value is 121.87/(1+0.10)^3 = 91.56

Since the current share price of 89.37 is lower than our fair value of 91.56, then XOM is undervalued. Thats a buy signal!

However, it goes without saying that this is simply an indicator. Dont buy or sell simply based on this rather simple analysis. You may also want to contrast this approach with Grahams Formula for valuing shares .

You can download the complete spreadsheet at the link below.

Automatically Screen for Undervalued Stocks in Excel

This Excel stock screener automatically calculates if a stock is undervalued or overvalued, using the most recent market data available at Finviz.

It downloads financial data for over 6800 stocks from Finviz. You simply enter up to ten stock tickers, and the spreadsheet fills with over 60 items of financial data for each ticker.

The spreadsheet then automatically calculates if the stock is undervalued or overvalued using the technique outlined above.