Updated Dividend Stock Pre-Screen Model

Posted by D4L | Saturday, August 16, 2008 | , | 3 comments »

In January 2008 I released my Dividend Stock Pre-Screen Model. Since then I've modified the model several times to add additional functionality, correct bugs and update annual information such as the default MMA Yield. The updated model is linked on the Tools page as D4L-PreScreen.xls.

The file contains the following tabs:

  • Screen: Is where you enter information about a stock and a recommendation is generated.

  • Database: Over time I keep "rediscovering" the same stocks. The database tab allows me to keep up with what stocks I have previously screened, the results of the screen and when the stock is eligible to be screened again.

  • Revisions: History of changes.
The Screen tab is divided the following five sections:

I. Input: All cells requiring your input are shaded yellow. They include:
  • Symbol: Enter the stock's symbol here.
  • Year: Enter the last year in which annual dividend data is available.
  • Current Yield: Enter the stock's dividend yield.
  • Calculated Price: The calculated price based on Current Yield and Current Dividend [NOT AN INPUT]
  • Calc. Div. Growth: This field calculates the stock's dividend growth [NOT AN INPUT]
  • MMA Yield: Enter estimated average money market account yield for the next 20 years.
  • Max Div. Growth: Enter here the cap (maximum) for Calc. Div. Growth.
  • Override Div. Gro: Enter here an override rate for Calc. Div. Growth.
  • Annual Dividend/Share: Enter here historic annual dividend information.
II. Projected Information: This section calculates 20 years of balances for two hypothetical $1,000 investments; one in a MMA earning the yield input above, and another in the stock entered above. Both the dividends and interest are reinvested. The MMA differential is the difference between the two investments. Proj. Yield on Cost is the projected yield on cost based on the stock's dividend growing at Calc. Div. Growth or Override Div. Gro. above and the stock's original cost.

Three scenarios are considered:
  1. No Price Appreciation This model assumes that the share price remains constant, thus the dividend yield grows each year based on the dividend growth rate. It also assumes all dividends and interest are reinvested.
  2. Price Appreciation = Dividend Growth
    This model assumes that the share price grows at the dividend growth rate, thus the dividend yield remains constant. It also assumes all dividends and interest are reinvested.
  3. Dividends/Interest Not Reinvested
    This model is identical to the No Price Appreciation model, except the dividends and interest are not reinvested.
Of the various models, I think that the Price Appreciation = Dividend Growth model provides the most realist results, but I generally do not use it because it includes capital growth, which is not accessible unless you sell the security. This runs counter to a income-based buy-and-hold philosophy.

III. Interpretative Analysis: Calculates several relevant pieces of information and allows you to set a minimum threshold on certain items. The items calculated are:
  • NPV of MMA Differential: This is the net present value of the MMA Differential calculated in the projected information section above for each of the scenarios and for the cumulative scenario. You can enter the minimum acceptable level for the cumulative scenario in column C.

  • Sum of MMA Differential: This is a simple sum of the annual values calculated in the projected information section above for each of the scenarios.

  • Metrics 1-5: Are specifically defined within the worksheet. Metrics 2 and 3 allow you to enter the minimum acceptable level in column C.
IV. Recommended Action: Based on the information entered above this section provides you with one of two possible recommendations 1.) This security should not be purchased or 2.) This security is worthy of additional consideration. Additional commentary is provided in this section along with a recommend year for reevaluation if the recommendation is to not purchase.

V. Disclaimer:
Too many attorneys with not enough work for my liking....

The Database tab allows you to keep up with what stocks have been pre-screened along with the results of the of the screening and the recommended year for the next screening. I have it divided into three sections:
  1. Stocks To Consider (green): These are stocks that you currently own or would consider buying in the future.

  2. Reconsider Later (gray): These are stocks that have previously failed the pre-screen. They are listed here to let you know that they have been screened before and when they are due to be screened again.

  3. Never Consider (red): If you are opposed to stock and know you will never consider owning it, listing it in this section ensures it will always be rejected.
For each stock there are three fields of information:
  1. The stock symbol (column A): The ticker symbol is used to look up information that is stored on the Database tab, but is displayed on the Screen tab.

  2. Flag (column B): A "X" in this column will flag the stock as rejected and display the comment on the Screen tab in cell D7.

  3. Comment: Whatever you want to say about the stock. As noted above, the comment is displayed on the Screen tab in cell D7 when there is an "X" in the Flag field.
Disclaimer: This model is for illustrative and educational purposes only. The author and Dividends4Life makes no claims or assertions as to the model's accuracy, completeness, appropriateness of use, or any other claim or assertion. You should not rely on this model or base any financial decisions on it.

The examples in Part III have been updated to match the changes made in the model. These examples will walk you through several different situations and point out things to look for.

(Photo: Steve Woods)

Related Articles:

Click here to have future posts delivered to you for free!

_____________________________________________________________________

3 comments

  1. Anonymous // August 20, 2008 at 7:00 PM

    Hi, I made a quick change to make the dividend data populate automatically from the "Database" sheet. Place this in cell J9 and copy it to the right.
    =INDEX(Dividend_History,MATCH(Stock_Ticker,Tickers,0),MATCH(J$7,Years_Available,0))

    Now, you'll have to select the dividend history in the Database sheet (J5:T12) and Name the range Dividend_History, likewise for Tickers (A5:A12), Years_Available (J4:T4), and finally back on the Screen sheet name cell C6 Stock_Ticker. Put in the stock ticker into cell C6 and the dividend info should update automatically.

    For the ambitious, one can fill in all the necessary data in the Database sheet for monitoring purposes. And if you really go nuts, a simple macro can scan the list of tickers and compile a list of the worthy ones for you.

    Cheers,

    V

  2. Dividends4Life // August 20, 2008 at 7:04 PM

    Anon: Nice change. I do something similar in my production spreadsheet where I house the data for companies that pass the pre-screen.

    Thanks for commenting.

    Best Wishes,
    D4L

  3. Anonymous // August 20, 2008 at 7:34 PM

    D4L, one more... I changed the check for the 4 yr. average hurdle rate to:
    AND(AVERAGE($J$10:$M$10)>$C$33,AVERAGE($K$10:$N$10)>$C$33,AVERAGE($L$10:$O$10)>$C$33,AVERAGE($M$10:$P$10)>$C$33,AVERAGE($N$10:$Q$10)>$C$33,AVERAGE($O$10:$R$10)>$C$33)

    Then in C33 I place the hurdle rate I want, maybe not as high as 15%. In the long run, if you can consistently beat inflation from your dividenders you should do ok. Anyway, this way it's easy to see what hurdle rate applies.

    BTW, I did find a misplaced paren. in the original formula. The last two averages are nested. Probably doesn't mean much.

    Cheers,

    V

Post a Comment

~

Latest From Dividend Growth Stocks

Popular Posts Last 30 Days