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. Here’s the complete spreadsheet, but you’ll need to read the entire article to understand the method.
We’ll assume that we’re 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
We’ll need the following data (all the data were correct at the time of writing – March 17th 2013)
- Current share price. XOM’s 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. Exxon’s current PE is 9.22 and forward PE is 10.93. So to be conservative, we’ll assume that the PE for the next three years will be 10.
- EPS Growth. According to the FT, XOM’s 5-year EPS growth is 5.96% (so let’s call it an even 6%).
Enter all the data into a spreadsheet like so.
Step 2 – Calculate EPS Over the Holding Period
Now we’ll 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 XOM’s shares, given our assumptions and parameters.
First, let’s look at the calculations in Excel, and then we’ll 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. That’s 11.54 x 10 = 115.41.
The dividend payout ratio is the current dividend per share divided by the EPS in year 3. That’s 2.28/11.54 =0.2. We’re 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 let’s 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. That’s 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. That’s 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. That’s a buy signal!
However, it goes without saying that this is simply an indicator. Don’t buy or sell simply based on this rather simple analysis. You may also want to contrast this approach with Graham’s Formula for valuing shares.
You can download the complete spreadsheet at the link below.
Download Excel Spreadsheet to Calculate Present Fair Value of a Stock
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.
Is there an Australian version using rhe AXS?
Hi,
AS per FRE’s comment above I would also like to know if I can use this spreadsheet to analyse Australian stock and Mutual Funds via the Australian Indices on the ASX stock exchange ?
I know that use the SMF ADD in, but how have you done this as :
(1) I cannot see any SMF function/code in the cells on the data sheet
(2) On the stock comparison sheet there is no code also.
Is it possible for you for you to advise how this was done/coded ?
Regards,
Ron.
Hi Sameer,
Saw some great work on your website. Really, very impressive.
I’m curious to know if there’s a way to get data such as “live” stock prices (a few minutes, even upto 15-20 mins will do), volumes, change on day, etc in EXCEL, specifically for the Indian stock market.
Any pointers from you in this direction would be very helpful!
Keep up the good work!
Hi Samir,
You REALLY need an area where people can just leave you comments thanking you for everything you’ve done on this site!!
From the VBA historic stock data download to the valuation and pricing this is stuff which is so useful!! It has saved me hours of googling and coding and a really good base for me to develop from!
This is just a BIG BIG thank you because I didn’t have your email address so had to post here! Keep up the good work!!
Kit
Hi Samir,
Love your work, was also curious if you had adapted this for ASX data?
Would also like to know from where the forward P/E ratio is derived?
Thank you very much, keep up the brilliant work.
Samir, could you please – when you have the time, create one for TSX? TYVM in advance!!!
Hi. It seems this ss will not work on a Mac version of excel. It works on the PC version.
This is a cool ss. Thanks for doing it.
I join the chorus of those who say thank you and appreciate your work.
When I use ticker symbol YHOO, it populates all the cells with #N/A. There are many others that do likewise. YHOO is in the data table. Any ideas?
Keith
I noticed that using the first excel spreadsheet, BBSI is rated undervalued, however using the automatic spreadsheet (second one) it shows it as overvalued. This is as of Oct. 17, 2014. Any ideas? Thanks,
Hi Samir, I downloaded the file last night but it still has 2013 data. I tried to update the data by clicking download the data, but seems it not working. What is the problem at my end? thanks
I just updated the data – works fine on my end. Using Excel 2010 & Windows 7
Can you include BSE and NSE (indian sensex) to the list pls.
TIA
Has anyone adapted this for the ASX and can provide a link/download?
Great work on your spreadsheets and sharing them. For the spreadsheet on “How to Calculate if a Stock is Undervalued or Overvalued” — When I clicked the “Download Updated Data” button, all I get is “#N/A”
Read the comments
I too have the same problem..can’t update the Data sheet..getting #NA on every field ..using Excel 2010 and Vista. AAPl is at 428.00 per share
..It is a fantastic tool .. would purchase if I could update.
Read the comments on the download page
HI Samir,
The tool looks great. Is it a must we have to use 2010. I have the N/a issue using 2013? Is there an easy fix for 2013? IF not i will down grade just dont want to if i dont have too. Thanks again
Hi Samir,
You have done some great work, on your site, it is very impressive that you find the time to do all this and then give a large chunk of it away for free!
My problem is that all your sheets that use finviz, report the error :
“Your browser is no longer supported. Please upgrade your browser” in cell A1 of the data sheet.
Your help would be greatly appreciated. I am using the latest browsers so i don’t think that this is the problem( I have tried IE, firefox & chrome).
Kind Regards,
M Afzal Ahmad
Good program, but unfortunately it does not work. The error message “Your browser is no longer supported. Please upgrade your browser.” is misleading, as my browser is also up-to-date. Wish it worked, as I find it better than anything I’ve seen so far, for what I need. Any ideas Samir?
Thanks,
Shahriar
I am a retail investor, trying to link the knowledge of Financial sector for my investments. Can you help me to know, if I am using it for the indian market, then how will i upload the latest data in the excel sheet. where I can find all this data and how to upload it to your sheet
Rgds
Meenakshi
Hi Samir,
This template is very amazing & useful. Appreciate all of your hard work.
By the way, is it possible to have 2 country datas together ( Malaysia – KLCI & HK – HKED ) ?
How to replace the data with KLCI & HKEX data ?
Appreciate your assisting.
Thank you very much.
This spreadsheet doesn’t work – the data tab refreshes with a webpage, not stock data, thus the sheet returns all #N/A errors.
Hello Samir,
First of all special thanks for taking time to update the data in a painstaking manner. However, I was trying to look at stock ticker symbols and trying to match the P/E ratio, P/B ratio, Analyst recommendations, ER date etc with that on several websites like money.msn.com, google.com/finance etc and these values did not match. Even after clicking Download updated data, these values differed. If you can look into this and provide a fix with the latest update, I would really appreciate it.