Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel DGI calculator
#2
I modeled idealistic scenarios for a few well known companies with different growth and yield profiles to see how an initial investment of $1000 does in each one. Taxes are ignored (the account is a Roth IRA, or taxes are paid from income). We'll call the benchmark model GIS, which currently yields 3%, and suppose it offers 7% growth and 7% dividend growth in perpetuity.

Benchmark model: GIS
Annual growth: 7%
Yield: 3%
DGR: 7%

After 10 years, $1000 on DRIP turns into $2,548 in principal paying $76/year in dividends.
After 20 years, $1000 on DRIP turns into $6,491 in principal paying $195/year in dividends.
After 30 years, $1000 on DRIP turns into $16,539 in principal paying $496/year in dividends.


Next comparison is a low yield, high DGR stock we'll call SBUX. The starting yield is 1.3%. According to Morningstar, SBUX should be able to maintain "at least mid-teens dividend growth over the next decade."
We'll say the dividend grows 20%/year for 5 years, then 15%/year for 5 years, then 12%/year for 5 years, then 10%/year for 5 years, then 7%/year in perpetuity.
We'll say the stock grows slower and the growth decelerates faster, at 15%/year for 4 years, followed by 12%/4 years, followed by 10%/4 years, followed by 7% in perpetuity. After 20 years the yield reaches a permanent 2.5%.

After 10 years, $1000 on DRIP turns into $3,770 in principal paying $73/year in dividends.
After 20 years, $1000 on DRIP turns into $9,510 in principal paying $254/year in dividends.
After 30 years, $1000 on DRIP turns into $23,645 in principal paying $637/year in dividends.


Next comparison is a high-ish yield, low growth, low risk stock we'll call T. Say the yield is 5%, and growth and DGR are both a perpetual 4%.

After 10 years, $1000 on DRIP turns into $2,326 in principal paying $116/year in dividends.
After 20 years, $1000 on DRIP turns into $5,410 in principal paying $270/year in dividends.
After 30 years, $1000 on DRIP turns into $12,583 in principal paying $629/year in dividends.


Next comparison is a high yield, low growth, higher risk company we'll call ARCP. Say the yield is 8%, growth and DGR are both 3%. However in year 5, Red Lobster goes out of business, interest rates spike, the dividend is cut by 30%, and the stock price also falls 30%. For 5 years following, the DGR and growth stagnate at 1%. Afterward the default growth rates continue.

After 10 years, $1000 on DRIP turns into $1,893 in principal paying $148/year in dividends.
After 20 years, $1000 on DRIP turns into $5,118 in principal paying $405/year in dividends.
After 30 years, $1000 on DRIP turns into $14,154 in principal paying $1,127/year in dividends.


A screenshot of SBUX is attached.

   
Reply


Messages In This Thread
Excel DGI calculator - by earthtodan - 06-22-2014, 11:32 PM
RE: Excel DGI calculator - by earthtodan - 06-27-2014, 11:37 PM
RE: Excel DGI calculator - by EricL - 06-28-2014, 01:10 PM
RE: Excel DGI calculator - by earthtodan - 06-28-2014, 02:02 PM
RE: Excel DGI calculator - by Jason - 06-29-2014, 09:07 AM
RE: Excel DGI calculator - by earthtodan - 07-04-2014, 10:15 PM
RE: Excel DGI calculator - by Roadmap2Retire - 11-12-2014, 01:55 PM
RE: Excel DGI calculator - by robgcon - 12-31-2014, 03:32 PM
RE: Excel DGI calculator - by hendi_alex - 12-31-2014, 06:33 PM



Users browsing this thread: 1 Guest(s)