Monday, January 18, 2016

D4s Valuation Tool Google Spreadsheet


A few weeks ago, I received an email asking how I value my companies in my purchase reports. I used to link my post describing the DDRM, Yield theory and PE methods, but I guess I have been lazy lately. In any case, sometimes it's difficult to go through several spreadsheets. Plus, it's a pain to update the web scraping functions in multiple sheets.


So here it is - one Google Spreadsheet with my valuation methods. This current version has the following:
  • Yield Theory Valuation Method -
  • DDRM total return Method
  • P/E earnings multiple Method with EOY 2016 estimate


Please read the previous articles for more information.





D4s Valuation Tool Spreadsheet




Note1: You need to make a local copy of the spreadsheet
Note2: Focus only on columns 1&2. Modify only the Yellow Shaded cells
Note3: For yield theory, you may need to modify manually the dividends to take account of splits etc.


Most Important Note: These are my methods for valuations, and not investment ideas. The spreadsheet is provided for informational purposes only.


Example - JNJ


1. Yield Theory


  1. Enter the Ticker in B1




2. Using the “Price Yield” Tab determine the high and low yield points. You can eye-ball this.




Estimate for High Yield around 3.75% and Low Yield around 2.50%




The Mid Price gives you an approximate of the “fair price” per the Yield Theory Method.


Current Price:
97
Current Div:
3.00
Current Yield:
3.09%
Tot DIV years
45
Current Year:
2016
High Price:
120.00
Mid Price:
100.00
Low Price:
80.00


Note: The original purpose of the Yield Theory is to buy at the bottom 25% point - from 80-90. Clearly, JNJ is at the fair value point, and not yet at the buy point.





2. DDRM


  1. Fill in the Core Growth Estimate, Future Estimated ROE, Future estimated EPS. You can use Historical Information from Yahoo for your estimate.



Result


DDRM

Dividend Rate ($)
2.94
Divided by: share price
97
Current yield (%)
3.03
Core Growth Estimate (%)
7.00
Divided by: ROE (%)
20
Multiplied by: EPS ($)
5.00
Cost of Growth (%)
1.75
Earnings per Share ($)
5.00
Minus: Dividend
2.94
Minus: Cost of Growth
1.75
Funding Gap ($)
0.31
Divided by: Share Price ($)
97
Share Change (%)
0.32
Core Growth (%)
7.00
Plus: Share Change (%)
0.32
Total Dividend Growth (%)
7.32
Plus: Dividend Yield (%)
3.03
Projected Total Return (%)
10.35


Dividend Rate ($)
2.94
Required Return (%)
10.00
Growth (%)
7.32
Price
109.69


Dividend Rate ($)
2.94
Required Return (%)
10.00
Growth (%)
7.00
Price
97.97


Average
103.83


Note: The original intent of the DDRM is to provide an estimated ROI of your investment. However, with a simple math trick, we can estimate the price based on the Growth. If we use the Dividend Rate & Dividend Growth, you can get the “Fair Price”. Another method is to use your Growth Rate to get a lower “Fair Price”. The average of the two is typically what I use as mid point.
3. PE Earnings Multiple


  1. Fill in the High and Low PEs. You can use the information provided. I’ve allowed the possibility to adjust these values.




2. The tool will return the Fair Price (Mid Price). I’ve included the 2016 fair price based on data from GuruFocus and Yahoo.


P/E High and Low

High P/E
24.46
Mid P/E
18.45
Low P/E
12.44


High Price
122.30
Mid Price
92.25
Low Price
62.20


P/E 5 Yr High
24.46
P/E 5 Yr Low
12.44


P/E 5 Yr Median
18.74


Current P/E
18.61


2016 P/E Est - Yahoo
15.16
2016 E/S Est - Guru
6.52
2016 Est Price
98.84


For me, I’ve decided to load up on JNJ whenever it dips below 100. This is based on my investment situation. Your situation is very likely to be different.


Anyway, that’s it for now. I hope my spreadsheet is useful for you.

D4s

Disclosure: Long JNJ

8 comments:

  1. Thanks for sharing your spreadsheet. I love pulling back to covers and seeing the logic behind decisions. I'll test drive this spreadsheet and reply again with my thoughts.

    Thanks

    ReplyDelete
    Replies
    1. Investment Hunting,
      Thanks for test driving my spreadsheet. Please let me know what I can improve on. BTW - I have more to come :)
      D4s

      Delete
  2. Hi D4s,
    I am now looking at this spread sheet and want to bring something to your notice. On summary sheet, for DDRM, the target price values (in cells B54 & B59) are becoming Negative, when growth rate (B53) number is greater than Required return% (B52) and similarly when B58 > B57. For example, try CVS as symbol, enter growth rate as 13%, current yr EPS as 5.86, and required return as 10% and notice price targets in B54 and B59.

    ReplyDelete
    Replies
    1. Srini, It is a case of Garbage In Garbage Out. For CVS, the growth rate at 13% is very high. In the book, they actually talk about using reasonable values for input. Moreover negative or nonsense numbers are possible if the input is incorrect.
      For me to value CVS, I set the Growth = 6 to 8% range, ROE at 13 and EPS at around 4.5. These are based on Guru's data as well as M* & S&P CapIQ data. At 6% growth, I get a price of around $70. At 8% growth, I get a price of around $85. So, based on this alone, the ballpark is around 75-85. Yield Theory gives me a price around 100. PE analysis gives me a price of around 80. So, for me an estimate in 80s is probably fair. What I do next is to correlate this with the estimates from M* and S&P if available.
      In any case, thanks for using the tool. Let me know if there are other problems you see.
      D4s

      Delete
    2. understand and agree about garbage in.
      Just want to make sure calculations are correct. In general, given the formula, if entered growth rate is > required return, then we are going to see -ve numbers. Also, when growth rate = ror (10%), take a look at B54.

      Delete
    3. Also, i notice you are taking EPS for ttm, than cur yr estimate. Is that you being cautious/conservative? For cur year, it is ~5.86.

      Delete
    4. Srini, yes - I try to be conservative for the input parameters.
      D4s

      Delete
    5. For your first point, DDRM is also based on the Gordon Growth Model -- P = D/(R-G) where R=Discount Rate, G=Growth Rate. So, if Growth>Discount Rate, then you'll have a negative price.
      D4s

      Delete