How to Calculate the Value of Reinvested Dividends Using Excel
Excel performs dozens of financial calculations instantly.
Stockbyte/Stockbyte/Getty Images
When you reinvest dividends into a new fund, you purchase additional shares that earn dividends of their own. The fund's rate of return now acts on a continually growing principal, producing increasingly large dividends. You can calculate your total profit from reinvesting using a compound interest formula, but an Excel spreadsheet can offer greater insight. The spreadsheet will show you your principal and returns at each period of the investment, easily showing you the time needed to reach investment goals.
Type "0" into cell A1 to represent your initial investment.
Step 2Type "=A1+1" into cell A2 and press "Enter." The cell will display "1," which represents your investment after one term
Step 3Click the black square in the lower right corner of cell A2 and drag it downward to fill the lower cells with additional values. For example, if you will hold on to your investment for 20 years, drag and release to fill cells with the numbers 2 to 20.
Step 4Type your investment's principal into cell B1.
Step 5Type your rate of return into cell E1.
Step 6Type "=B1*$E$1" into cell C1 to display your first term's returns in cell C1.
Step 7Type "=B1+C1" into cell B2 to display your principal after one term in cell B2
Step 8Click the black square in the lower right corner of cell B2 and drag it downward to fill the cells below it.
Step 9Click the black square in the lower right corner of cell C1 and drag it downward to fill the cells below it, filling the remaining rows with each term's principal and returns.
Step 10Type "=B1-($B$1+$C$1 * A1)" into cell D1.
Step 11Click the black square in the lower right corner of cell D1 and drag it downward to fill the cells below it. Column D now states the accrued value of your reinvested dividends at each period in your investment.
References
Resources
Tips
- If you are assuming an internal rate of return based on a previous series of cash flows, use Excel's IRR function to calculate it. Type the cash flows into cells F1 to F10 and then type "=IRR(F1:F10)" into cell E1.
Writer Bio
Ryan Menezes is a professional writer and blogger. He has a Bachelor of Science in journalism from Boston University and has written for the American Civil Liberties Union, the marketing firm InSegment and the project management service Assembla. He is also a member of Mensa and the American Parliamentary Debate Association.