Excel DGI calculator

[earthtodan]

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.