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.
Formula Method
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.
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.
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.
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.
Table Method
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.
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.
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.
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.
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.
References
- The Dividend Guy: Calculating Future Value Using Excel
- Meta Filter: How do I get the Future Value if the payments are growing by a fixed %?
- U.S. Securities and Exchange Commission. "Dividend." Accessed June 17, 2020.
- Fidelity Investments. "What Are Dividends?" Accessed June 17, 2020.
- Corporate Finance Institute. "Important Dividend Dates." Accessed June 17, 2020.
- Corporate Finance Institute. "Dividend." Accessed June 17, 2020.
- Fidelity Investments. "Preferred Stock." Accessed June 17, 2020.
- Rice University. "Record Transactions and the Effects on Financial Statements for Cash Dividends, Property Dividends, Stock Dividends, and Stock Splits." Accessed June 17, 2020.
- Corporate Finance Institute. "Special Dividend." Accessed June 17, 2020.
- Pennsylvania Department of Revenue. "Dividends." Accessed June 17, 2020.
- Corporate Finance Institute. "Dividend Payout Ratio." Accessed June 17, 2020.
- U.S. Securities and Exchange Commission. "Form 10-K Coca Cola Co." Accessed June 17, 2020.
- Charles Schwab & Co. "Dividend Yield and Dividend Growth: Fundamental Value Analytics." Accessed June 17, 2020.
- Internal Revenue Service. "Publication 550 (2019): Investment Income and Expenses (Including Capital Gains and Losses)," Page 19. Accessed June 17, 2020.
- Value Line. "Dividends Come Out of Cash Flow, Not Earnings." Accessed June 17, 2020.
- Pillsbury Law. "SEC Disclosure Update and Simplification." Accessed June 17, 2020.
- Corporate Finance Institute. "Dividend Reinvestment Plan (DRIP)." Accessed June 17, 2020.
- Robinhood. "What Is a Dividend Reinvestment Plan (DRIP)?" Accessed June 17, 2020.
- U.S. Securities and Exchange Commission. "American Financial Group Inc. - Dividend Reinvestment Plan." Accessed June 17, 2020.
Writer Bio
C. Taylor embarked on a professional writing career in 2009 and frequently writes about technology, science, business, finance, martial arts and the great outdoors. He writes for both online and offline publications, including the Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym and more. He received a Master of Science degree in wildlife biology from Clemson University and a Bachelor of Arts in biological sciences at College of Charleston. He also holds minors in statistics, physics and visual arts.