A Spreadsheet For DGI Investors

|
Includes: GIS, JNJ, KO, MCD, MO, PEP, PG, WMT, XOM
by: Aristofanis Papadatos

Summary

It is quite challenging to perform all the calculations correctly to build up a DGI portfolio that will succeed in meeting the retirement goal for individual investors.

The spreadsheet provided in this article should help DGI investors set up reasonable expectations for the future value of their portfolio.

It should also help them pursue the right mix of initial dividend yield and dividend growth rate to meet their future goals.

The use of the spreadsheet also provides some other interesting results, such as the quantitative effect of reinvestment of the dividends.

Dividend growth investing [DGI] is an extremely popular strategy on Seeking Alpha thanks to its great characteristics, such as no need to pay attention to temporary dives of the market. To be sure, SA has published numerous articles related to the strategy and many of them have made it to the TOP 10. In this article, I will provide a spreadsheet for DGI investors, which will help investors set up their goals and build up reasonable expectations for their portfolios. I will also share some very interesting results derived from the spreadsheet.

First of all, DGI investors should have a long-term horizon. Therefore, as this horizon may range from 10 to 40 years, it is quite difficult to perform all the required calculations correctly and set up reasonable estimates for the value of the portfolio after so many years. For instance, there are numerous investors who wonder what dividend yield and what dividend growth rate they should pursue to meet their goal for retirement. The spreadsheet provided in this article calculates the future value of the portfolio based on the initial capital that is invested, the initial average dividend yield of the portfolio and the average annual dividend growth rate. The latter has been assumed to be constant for simplicity and hence investors should enter an average dividend growth rate they expect to receive. The spreadsheet has been built for 30 years, but it can be easily adjusted for a different period by adding or deleting rows and columns. In addition, it is assumed that the dividends collected every year are reinvested, with an initial yield equal to the initial dividend yield of the portfolio. Then the reinvested dividends enjoy an increasing yield, which grows at the same rate as the dividend yield of the rest of the portfolio. For simplicity the spreadsheet does not involve additional amounts of capital every year. If readers are interested in adding this parameter, I will be more than willing to add this option and revert with a follow-up article.

The results of the spreadsheet are shown below for the value of a portfolio with initial capital $100,000 after 30 years. The initial dividend yield ranges from 1% to 4% while the dividend growth rate ranges from 2% to 12%.

Growth rate/Initial Yield

1%

1.50%

2%

2.50%

3%

3.50%

4%

2%

146,922

175,148

206,551

241,130

278,887

319,820

363,930

4%

165,454

205,207

249,644

298,766

352,573

411,063

474,238

6%

192,974

249,897

313,779

384,618

462,415

547,169

638,882

8%

234,063

316,678

409,684

513,079

626,864

751,039

885,603

10%

295,634

416,806

553,548

705,859

873,741

1,057,193

1,256,214

12%

388,096

567,218

769,720

995,605

1,244,872

1,517,521

1,813,551

Click to enlarge

From the table it is evident that investors can achieve the same goal in many different ways. More specifically, they can either pursue a high initial dividend yield and a relatively low dividend growth rate or a low initial dividend yield with a high dividend growth rate. For instance, the future value of a DGI portfolio with a 2.5% initial dividend yield and a 4% dividend growth rate ($298,766) is almost equal to that of a DGI portfolio with a 1% initial dividend yield and a 10% dividend growth rate ($295,634). Thus, investors need not exclude great stocks that have low current dividend yields from their portfolio as long as they expect high growth rates in the future. In addition, they can use the results of the table to determine what current dividend yield and dividend growth rate are sufficient for them to achieve their retirement goal. The results of the table are also depicted on a 3-D chart:

Click to enlarge

As some investors may wonder about the current dividend yield and the average 5-year dividend growth rate of some stocks, the table below provides these values for some stocks that are very common in DGI portfolios, namely Exxon Mobil (NYSE:XOM), Procter & Gamble (NYSE:PG), McDonald's (NYSE:MCD), Wal-Mart (NYSE:WMT), Coca-Cola (NYSE:KO), PepsiCo (NYSE:PEP), General Mills (NYSE:GIS), Johnson & Johnson (NYSE:JNJ) and Altria (NYSE:MO):

Stock

Dividend yield

Average Dividend Growth Rate

XOM

3.91%

10.7%

PG

3.49%

6.6%

MCD

3.08%

7.8%

WMT

3.08%

10.1%

KO

3.18%

8.4%

PEP

2.89%

7.9%

GIS

3.18%

9.5%

JNJ

3.06%

6.8%

MO

3.88%

8.3%

Click to enlarge

It is also worth noting that the spreadsheet is so analytical that it shows investors what portion of the future value of their portfolio is expected to come from the initial capital and what portion is expected to come from the reinvestment of dividends. For instance, a DGI portfolio with initial capital $100,000, a 3% initial dividend yield and a 5% dividend growth rate is expected to be worth $401,998 after 30 years, with about half of its future value (202,681) resulting from the reinvestment of the dividends. Thus, investors can draw useful conclusions regarding their investment strategy and realize the value of each component of their portfolio.

To sum up, it is quite challenging to perform all the calculations correctly to build up a DGI portfolio that will succeed in meeting the retirement goal for individual investors. To this end, the spreadsheet provided in this article should be very helpful. In addition, its results show all the different combinations of initial dividend yield and dividend growth rate that achieve similar results. Moreover, investors can also determine how much they are likely to earn from the reinvestment of dividends and how much from their initial capital.

Disclosure: I/we have no positions in any stocks mentioned, and no plans to initiate any positions within the next 72 hours.

I wrote this article myself, and it expresses my own opinions. I am not receiving compensation for it (other than from Seeking Alpha). I have no business relationship with any company whose stock is mentioned in this article.