Dividend Growth Stocks: * The Winning Score

As discussed in yesterday's post, one of the metrics that I use to score an individual stock's performance is Internal Rate of Return (IRR). In simple terms, IRR is the interest rate you would need to earn to make the same money off of an interest bearing account assuming the same investment.

I have extracted out of my two massive financial spreadsheets a sample model [D4L-Portfolio.xls] from the portfolio section. This model contains several elements worthy of discussion, but today we will focus on how to calculate an IRR for an individual stock investment.

First some general notes. At the top of the Portfolio tab is a summary section were I summarize each investment on a single line. Within the summary section, the inputs are in columns H and L. Specifically for BAC the most recent price is entered in cell I7 and the annual dividend is entered in cell L7. In my spreadsheet these are linked to another tab where I cut and paste information from my MSN portfolio.

Go to cell Q31 if there is a #VALUE! or #NAME? error you likely do not have the the Analysis ToolPak add-in activated. For instructions on activating this add-in, please refer to the Analysis Toolpak Help tab within the spreadsheet. Once the add-in is functioning, the value in cell Q31 will be 0.72%.

Pressing [F2] with the cell pointer in cell Q31 reveals the following formula:

=XIRR(P12:P31,O12:O31)

The XIRR function calculates an IRR with uneven cash flows and periods - kind of the way life happens. The syntax for XIRR() is as follows:

XIRR(Values,Dates,Guess[optional])

Values: A series of cash flows with additions (purchases) of the security shown as positive, and reductions (including dividends) shown as negatives.

Dates: The date of the cash flow using the Excel Date(year,month,day) function.

Guess: your best guess at what the answer will be. This is optional and meant to speed up the calculation.

Cell Q31 calculates the life-to-date (LTD) IRR, while cell U31 calculates the year-to-date (YTD) return. These calculated values are also shown in cells F7 and E7, respectively. To get a feel for what will happen as the share price changes go to cell I7 and enter some values:

At $50 - YTD return = 601.45%; LTD Return = 7.79%
At $40 - YTD return = -26.98%; LTD Return = -4.69%

Note that in cells Q16, Q22 and Q29 I hardcoded the LTD return at that point in time. In cells U16, U22 and U29 I hardcoded the YTD return at the end of each year prior to rolling to the new year. This allows me to go back and quickly review the performance of each of my securities.

I hardcoded the date in cell A1 to =DATE(2008,2,4). If you decide to use some variation of this model, you will want to change the formula in cell A1 to =NOW(). This will populate the cell with the current date, which in turn will constantly update the YTD and LTD IRR calculations. To see the effect of time on the IRR, edit A1 to =DATE(2008,12,4). All other things being equal, this drops the YTD return to 0.49% and the LTD return to 7.56%.

As always, I hope you find this model entertaining and useful.

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.

Related Articles:

Dividend Growth Stocks

Pages

Thursday, February 7, 2008

* The Winning Score - Part 2 of 2