+- Dividend Growth Forum (http://DividendGrowthForum.com)
+-- Forum: Dividend Growth Investing (http://DividendGrowthForum.com/forumdisplay.php?fid=15)
+--- Forum: Resources for Dividend Growth Investors (http://DividendGrowthForum.com/forumdisplay.php?fid=36)
+--- Thread: Excel DGI calculator (/showthread.php?tid=600)
Excel DGI calculator - earthtodan - 06-22-2014
Thanks to my compulsive DIY approach to nearly everything, I created an Excel spreadsheet to model dividend growth investing. Going through this exercise helped me understand the math more intuitively, and the comparison between high yield/low growth, low yield/high growth, etc. I hope that other DGIs can benefit as well by playing around with the inputs and assumptions.
Download it from my Google Drive. You may have to go into the File menu and "Save As" to make this work. After you download and open it, click "enable editing" at the top.
Basics
Cells in yellow are for user input (the rest are protected). There are two calculators side by side for comparison. These steps address the one on the left.
- Enter your age in cell A6
- Enter the assumed growth, yield, and DGR of your investment or account
- Select Taxable or Roth IRA (I didn't add Traditional IRA as this would present extra complications)
- If Taxable, select whether you plan to pay dividend tax from dividends or from your income. If you select "Dividends," the spreadsheet will model reinvesting only the remaining dividend after tax.
- Enter your planned retirement age. The spreadsheet will model dividend reinvesting until that age, followed by dividend withdrawal
- Enter your assumed pre-retirement and post-retirement LTCG tax rates
- Enter a starting investment and an annual contribution in column D. You can input only the first year to model the returns from an initial investment, or you can enter contributions for each following year to model a hypothetical lifetime of investing.
Deep dive
- Columns F and G can be used to override capital appreciation and dividend growth for individual years. If left blank, it will default to the values you entered at the top. If you enter a number, it will use that instead. The changes will be reflected in the dividend compounding. For example, you can model what will happen if there's a correction in the market and the DGR stays constant, or if there are changes in the DGR.
- You can also use this feature to model what happens to a low yield, high DGR stock that slows down over time. If for example you want to model a 1.2% yielding growth stock that ends up as a 3.5% yielding blue chip in 20 years, you can use the yield in column H to help track your assumptions as you build the model.
- You can model what happens if you withdraw money from your account by entering a negative number in column D. This assumes you sold stock and took a long term capital gain, and it will be reflected in your tax for that year. You can also model what happens if you sell stock every year during retirement.
Limitations
1. Dividend reinvestment is modeled annually rather than quarterly, which makes the result slightly conservative.
2. Reinvestment is modeled as a DRIP, with 100% of dividends reinvested into their source.
3. Inflation is ignored.
Observations
- If the long term growth rate and the dividend growth rate are not the same, the yield will get out of whack (column H). This supports the premise of the Chowder rule.
- Net Investment Income (column L) is how much you spent on contributions and taxes for that year.
- When you retire and start living off dividends, net investment income turns positive
- Cumulative Cash Flow simply keeps a running total of NII, and shows how much you've spent in contributions and taxes.
- When "Cumulative CF" turns positive, it means your investments have completely paid themselves back, in absolute dollar terms.
RE: Excel DGI calculator - earthtodan - 06-27-2014
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.
[attachment=72]
RE: Excel DGI calculator - EricL - 06-28-2014
I love your spreadsheets Dan, thanks for sharing.
Interesting how well ARCP performs even with the 30% haircut. Goes to show how powerful the compounding on an 8% initial yield can be.
RE: Excel DGI calculator - earthtodan - 06-28-2014
Thank Eric. Seriously, I don't know how people got anything done before Excel.
RE: Excel DGI calculator - Jason - 06-29-2014
I had been using a similar calculator that I found on Dividend 4 Life's sight. I was always wondering how accurate it was. The results 20 years from now seemed to good to be true. But when I ran the same numbers on your calculator the results were pretty close. Thanks for posting to google drive. Jason
RE: Excel DGI calculator - earthtodan - 07-04-2014
Come to think of it, 3% DGR is probably too generous an assumption for ARCP. The Red Lobster lease escalator is 2% for 25 years, and that represents 12% of the portfolio, so I will model 2% DGR instead. This turns out to make a big difference over time.
As before, 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,817 in principal paying $143/year in dividends.
After 20 years, $1000 on DRIP turns into $4,563 in principal paying $362/year in dividends.
After 30 years, $1000 on DRIP turns into $11,614 in principal paying $925/year in dividends.
RE: Excel DGI calculator - Roadmap2Retire - 11-12-2014
Thanks for sharing Dan. I have been thinking of doing an exercise in similar spreadsheets on some of my holdings but Ive been putting it off. I'll download your sheet and let you know if I have any feedback.
cheers
RE: Excel DGI calculator - robgcon - 12-31-2014
This is fantastic. Thank you very much for sharing. That's a lot of work!
RE: Excel DGI calculator - Main Street Stock Investor - 12-31-2014
Dan,
Thanks for sharing. I'm a big fan of Excel also. I track all my portfolios in Excel Workbooks tracking dividend income, trading profit, option profit and appreciation.
M$$I
RE: Excel DGI calculator - hendi_alex - 12-31-2014
I'm at the distribution phase, have a limited investment horizon and don't pay much attention to growth metrics. Am mostly interested in current yield with some potential for growth. We are taking an approximate 10% distribution per year from the IRA. Are channeling much of SS into funding a regular account. The goal right now is to have at least 5% growth in portfolio value when new funding is added to portfolio earnings. This year after withdrawing 10%, earnings of 10.5% plus $8000 in new funding grew the portfolio 1.5%. The math for those calculations is very, very simple, using just a few cells on my spreadsheet. In two years withdrawals will drop to 4%-5% and portfolio growth should accelerate.