Last Updated: December 27, 2008
This post provides several examples on how to use my D4L-PreScreen.xls model for prescreening dividen stocks. I have tried to select a diverse group to cover multiple outcomes. For your convenience, all the sample data is included on the Database tab. I am assuming you are familiar with Excel and are comfortable with basics such as copying, pasting, entering data, etc.
Example #1 EX1: On the Screen Tab enter "EX1" as the symbol in cell C6. Enter the Current Yield in cell C8 and the last 11 annual dividends in cells J9 to T9 - all this information is available on the Database tab on the EX1 row beginning in column H (H9 to T9). If cell B48 is (1,758), then you have everything entered correctly.
The first thing you will notice is the "Do NOT Buy!" in cell D6. In this case, the "Do NOT Buy!" warning is a result of the NPV of MMA Differential in cell B48 being less than the minimum in cell C48. You can easily see why this is negative by looking at the MMA Differential line starting at C22 and C23. It will be 2023 before an investment in EX1 will produce more income that a MMA, cumulatively it will take until 2029. The way discounting works, the earlier years are more heavily weighted. You should never buy an income investment with a negative NPV of MMA Differential.
Note that cell D7 tells you that EX1 has been previously evaluated and should be reevaluated in 2011. This is confirmed by looking at cell B66.
Example #2 EX2: On the Screen Tab enter "EX2" as the symbol in cell C6. Enter the Current Yield in cell C8 and the last 11 annual dividends in cells J9 to T9 - all this information is available on the Database tab on the EX2 row beginning in column H (H10 to T10). If cell B48 is 41,354, then you have everything entered correctly.
Again, you get the "Do NOT Buy!" in cell D6. This time the "Do NOT Buy!" warning is a result of the Metric 2, consecutive years of dividend increases, in cell B54 being less than the minimum in cell C54. Looking at cells J9 and K9, you can see the dividend was flat in 2009. In spite of its great yield and 10+ years without dropping a dividend, my pre-defined rules say that I will pass on this company.
As with EX1, cell D7 tells you that EX2 has been previously evaluated and should be reevaluated in 2016. This is confirmed by looking at cell B66.
Example #3 EX3: On the Screen Tab enter "EX3" as the symbol in cell C6. Enter the Current Yield in cell C8 and the last 11 annual dividends in cells J9 to T9 - all this information is available on the Database tab on the EX3 row beginning in column H (H11 to T11). If cell B48 is 28,540, then you have everything entered correctly.
Once again we are greeted with the the "Do NOT Buy!" in cell D6. This time the "Do NOT Buy!" warning is a result of the Metrics 2 and 3. In the example we will focus on Metric 3 consecutive years without decreasing a dividend. This stock is considered a "Do NOT Buy!" since cell B55 is less than the minimum in cell C55. Looking at cell O9, you can see the dividend dropped in 2004. In spite of its great yield, my pre-defined rules say that I will pass on this company.
As with the earlier examples, cell D7 tells you that EX3 has been previously evaluated and should be reevaluated in 2016. This is confirmed by looking at cell B66.
Example #4 EX4: On the Screen Tab enter "EX4" as the symbol in cell C6. Enter the Current Yield in cell C8 and the last 11 annual dividends in cells J9 to T9 - all this information is available on the Database tab on the EX4 row beginning in column H (H12 to T12). If cell B48 is 2,437, then you have everything entered correctly.
Like Example #1, EX4 greets you with a "Do NOT Buy!" in cell D6 as a result of the NPV of MMA Differential in cell B48 being less than the minimum in cell C48. This time it is positive and close to our acceptable minimum.
So, what would it take to make EX4 worthy of additional consideration? Two inputs will increase the NPV of MMA Differential, 1.) a higher dividend growth rate or 2.) a higher current yield. I built the functionality in the model to determine each.
To calculate the minimum dividend growth rate to break even, press the button in cell D13. This will plug the override dividend growth rate in cell C13 until cells B48 and C48 equal. In this case it took just 0.5% or an override value of 11.5%.
To calculate the minimum dividend yield to break even, first enter 0 in cell C13, then press the button in cell D8. This will plug the dividend yield in cell C8 until cells B48 and C48 equal. In this case it took just 0.10% or an override value of 2.15%. Cell C9 shows you at what price EX4 must trade to yield the needed 2.15%.
Both of the above values are so close, EX4 could be worth a closer look.
Example #5 EX5: On the Screen Tab enter "EX5" as the symbol in cell C6. Enter the Current Yield in cell C8 and the last 11 annual dividends in cells J9 to T9 - all this information is available on the Database tab on the EX5 row beginning in column H (H7 to T7). If cell B48 is 6,199, then you have everything entered correctly.
Finally, a "Worthy of additional consideration" stock. That means this stock has not yet disqualified itself, so you can now probe deeper to determine if it is a buy. That process will be covered in other posts.
As a side note, you can calculate the minimum dividend growth rate and the minimum dividend yield to break even, as we did in Example 4. In this case, it will be lower since the NPV of MMA Differential is in excess of the minimum.
One additional feature I need to mention is the "Stocks to Pre-Screen" section around cell B50. Whenever someone mentions a stock that I would like to evaluate as a potential dividend investment, I will enter the symbol in this section. If it is not in the database #N/A will appear, if it is flagged as a reject "---DELETE >>>" will appear and if the stock is on the to consider list then "ok" will appear. When I evaluate the Aristocrats and the Achievers, I copy them to this section and eliminate the deletes.
As always, I hope you find this model entertaining and useful. Please let me know if you come across any bugs. It took a lot of hacking to extract it from my two master financial spreadsheets.
Related Articles:
________________________________________________________________
Subscribe to:
Post Comments (Atom)
0 comments
Post a Comment
Post a Comment
Note: Only a member of this blog may post a comment.