Stock dividends can be distributed as cash payments or reinvested to purchase additional stock shares. Because these dividends are converted into shares, the reinvested dividends' future value is a function of stock growth, in addition to any future distributions. Excel's FV function will project the value of regular dividend payments, but it cannot account for future increases in dividend distributions. Constructing a varying annuity formula will allow dividend growth, but even this formula's precision is degraded by the fact that dividend growth typically occurs annually, whereas stock growth occurs continuously. Constructing a detailed table that depicts each reinvested dividend will offer the greatest flexibility and precision.
1. Enter the labels "Number of Shares," "Dividend Payment," "Payments per Year," "Number of Years," "Dividend Growth," "Stock Growth," "Future Value (Constant)" and "Future Value (Growth)" in cells A1 through A8.
2. Enter the number of shares you own, the annual dividend payment per share, number of payments per year and the number of projected investment years in cells A1 through A4. Enter the estimated annual dividend growth rate and annual stock growth rate in cells A5 and A6. This information can be acquired through your stock broker or online investment account.
3. Enter "=FV(B6/B3,B4*B3,B1*B2/4)*-1" without quotes in cell A7 to calculate the future value of all reinvested dividends. This formula does not consider dividend growth or dividends from newly purchased shares.
4. Enter "=IF(B6=B5,(B4*B2*B1)*(1+B6)^(B4-1),(B1*B2)*((1+B6)^B4-(1+B5)^B4)/(B6-B5))" without quotes in cell A8 to calculate the future value of reinvested dividends. This formula does consider stock and dividend growth rates, but it does not consider dividends from newly purchased shares.
1. Enter the data described in the Formula Method section, and add "Future Value (Table)" in cell A9. Enter the column headers "Period," "Payment" and "Future Value" in cells D1 through F1.
2. Enter the period numbers "1," "2," "3," etc, under the Period column header. You need as many periods as are contained in the projection years. This is calculated as the number of periods per year times the number of years.
3. Enter the amount of dividends that are reinvested under the Payment column header for each period. This data should be included on your investment statements. If you wish to estimate it, enter "=($B$2*$B$1/$B$3)*(1+$B$5)^(INT((D2-1)/($B$3)))" without quotes and copy the formula down column D. However, this formula does not count dividends from newly purchased shares.
4. Enter "=E2*(1+$B$6/$B$3)^($B$4*$B$3-D2)" without quotes in cell F2 and copy it down the column. This will estimate the future value of each individual dividend payment.
5. Enter "=SUMIF(D:D,"<="&B4*B3,F:F)" without quotes in cell B9 to estimate the total future value of all reinvested dividends from the table.
- chart image by DXfoto.com from Fotolia.com