Wednesday, June 24, 2015

Using P/E for Dividend Stock Valuation (P/E & Earnings Multiple Google Spreadsheet)


This is Div4Son. 

While P/E is one of the most popular valuation methods.

P/E = Market Price per Share / Earnings per Share

In general, one can use the current P/E or forward P/E (based on forward earnings/share) to compare with the market's P/E (around 19). In addition, the P/E can be compared with companies within the same sector and industry. Also, the historical P/E can be used to compare how favorable the current P/E with the past.

With my continuous quest to learn new things, I wanted to see how I can apply the P/E in my evaluations, more than just comparing with the P/E of an index or other company's P/E (which I do today as a quick check). Moreover, I wanted to 'visualize' the impacts of the earnings per share multiple. And lastly, I wanted to derive a high/low/fair price points.

Earnings and Outstanding Shares

The first thing is to plot the Earnings in relation with the number of outstanding shares - which are the two components in the EPS equation. I used WMT as an example. This is interesting to see the impacts of rising (or decreasing) earnings vs shares (in regards to buybacks or dilution).

The earnings and outstanding shares provide insights on the EPS - e.g if it it increasing because of increasing income or decreasing number of shares.

Price and EPS

The next thing is to plot the price in relation with the earnings the share - which are the two components in the P/E equation. 

It has been said that the EPS provides a good prediction of the stock price. As you can see, there is a general correlation between the rising EPS and the rising price.

Note that in some studies, the use of the EPS as a predictor of share price generated mixed results. 

In general, I like to look in the rise of the EPS to determine the quality of a company, not necessarily the price of the underlying stock.

Price and P/E

The next is to plot the price vs P/E over time.

For me, this plot is only useful to determine the high P/E and low P/E. i.e. If the current P/E of a company is close to the high P/E then it may not be necessarily good value.

Earnings per share multiples 

The P/E is basically a multiple of the earnings per share. The higher the P/E, the higher the price and vice versa. 

If I use the previous plot, I can map out the high P/E and low P/Es of a stock. 

Next, I multiply the high and low P/Es with the associated EPS for the year and compare with the price over time to get a sense of the price boundaries I am working with. 

I can then use the multiple to give you an estimation of high, low and fair prices. 

Using the WMT example, I get a fair price around low 70s.

The takeaways for me are:

1) the earnings and outstanding shares provide insights on the EPS - e.g if it it increasing because of increasing income or decreasing number of shares.
2) it is useful to use the EPS to gauge the growth of a company rather than just the underlying stock price. 
3) the historical P/E provides you a view of the high and low P/E of a stock 
4) you can apply the historical P/E to determine the earnings multiple to estimate the fair value (price) of a stock

Moreover, the forward earnings multiple can be used for comparisons for subsequent years.

It is important to treat the P/E and EPS multiples as smalls pieces in the investment equation to find good quality companies with good dividend growth & yield at good valuations.

Lastly, remember that the P/E and other methods only provide an estimate of valuation.

P/E and EPS google spreadsheet

As usual, I've included my spreadsheet for you to play with.

I believe you need to make a local copy in your own google drive.


Just enter the ticker in the yellow highlighted box. 


Using the high and low P/Es, enter your own values to compare. 

High P/E17.80
Mid P/E14.60
Low P/E11.40

P/E 5 Yr High17.83
P/E 5 Yr Low11.42

Check out the estimated high, low and medium (fair) prices. e.g. I rounded to 17.80 and 11.40 for high and low P/E respectively.

High Price87.22
Mid Price71.54
Low Price55.86

The spreadsheet will calculate the High, Low and Fair price points.

The spreadsheet is not ideal. The information is extracted from third party sites (google & vuru). Anyway, hopefully this is a useful tool.


That's it all now! What do you think of the P/E indicator? Do you use the P/E indicator for valuation? Let me know what you think of the spreadsheet. 



  1. Interesting post, Div4Son.

    The other part of the EPS that would be interesting to throw onto those charts in addition to the price and earnings, is the number of shares (which is the 'ps' part of 'eps').
    As we know, buybacks are all the rage these, we have to compare and see how/why the EPS is trending in a certain way. Is it because of buybacks? Wheres that money coming from? From increased revenue or more debt?

    I understand that you are sharing this one aspect of things, but IMO, PE is a bit of a short-hand that shows only a part of the whole picture. As with any metric in investing, other surrounding things should be considered (which I think you already do in your dividend stock analysis posts).

    Thanks for sharing the spreadsheet.

    1. R2R,

      This is a very good comment. I've updated the article to include a plot on earnings and outstanding shares. The earnings and outstanding shares provide insights on the EPS - e.g if it it increasing because of increasing income or decreasing number of shares.

      For your other point, I agree completely. The PE is just another metric to use in the investment analysis.

      Thanks for your comment.


  2. Nice article and graphs! Thanks for sharing.


  3. Hello: I'm new to Google sheets. I tried to enter another stock symbol (overwrite he WMT symbol) and was prevented from doing so. Help.

    1. Please make a local copy, and then make changes to your spreadsheet.
      If this doesn't wit let me know and I'll create a copy for you.

  4. Beautiful post I want to continue it as much good you can. Thank you.
    digital marketing services in india