Dividend Reinvestment and Growth Spreadsheet
The DCA spreadsheet is based off the template available here: The DRiP Investing Resource Center – Tools
Dividend Reinvestment and Growth Calculator
This template gives you the ability to project anticipated value of a stock over a 30 year period if dividends are reinvested and allows for factoring in additional monthly, quarterly, or annual contributions. Great for tracking scenarios where dollar cost averaging would be advisable, such as with frequent contributions to an Individual Retirement Account (IRA) or a dividend reinvestment plan (DRIP).
These spreadsheets allow you calculate how various factors such as ongoing contributions, stock appreciation, dividend yield, and dividend growth can affect cumulative value over a 30-year period. For example, I used $4,000 as the initial investment amount, contributed $50 per month, expected 3% annual stock price appreciation, an initial 2.75% dividend yield, and an expected annual dividend increase of 7%. All dividends were reinvested and no commission fees were assessed. At the end of 20-years, I would have invested a total of $15,950 and reinvested $16,253 in dividends giving a total cost basis of $32,203. Capital gain would be $10,785 and the total account value would have risen to $42,987. It would also be generating $2,269 a year in dividends.
In 30 years, assuming the same parameters hold true, the portfolio would have had cumulative contributions of $21,950 and the total value of the portfolio would be $125,376.55, giving an annual dividend of $9,585. That’s all from just under $22,000 invested over a 30-year period. Just imagine what would happen if you invest more initially and increase the monthly ongoing investment! That’s the magic of compounding!
For a live version that you can experiment with please see Crazy Visa Statistics.
There seems to be a weird correlation between annual appreciation and dividends. The higher the annual appreciation the lower the dividends (although the entries for the dividends didn’t change). E.g. (using your numbers) with an annual appreciation of 1%, I get $18,875.92 annual dividends in year 30, whereas with 10% annual appreciation I only get $2,976.46… something seems not to check out…. or at least I don’t understand the outcome.
Thanks for the comment. At work now, but I’ll get back to you this weekend. It’s a good question.
What values are you putting in for dividend yield and annual dividend increase percent? Essentially, with a very high annual stock appreciation of 10%, the reinvested dividends can buy less and less. With a lower stock appreciation, the dividends (especially if you left the dividend growth rate at 7%) can buy more shares each time. However, take a look at the total value. A 10% annual stock appreciation will result in a higher overall portfolio value than the 1% appreciation rate.
Please let me know if you have any more questions.
Great spreadsheet to give an individual an idea of what a DRIP, Stock Appreciation, and regular investing how it would grow. Thank you.