How to Calculate the Value of Reinvested Dividends Using Excel

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.

Step 1

Type "0" into cell A1 to represent your initial investment.

Step 2

Type "=A1+1" into cell A2 and press "Enter." The cell will display "1," which represents your investment after one term

Step 3

Click 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 4

Type your investment's principal into cell B1.

Step 5

Type your rate of return into cell E1.

Step 6

Type "=B1*$E$1" into cell C1 to display your first term's returns in cell C1.

Step 7

Type "=B1+C1" into cell B2 to display your principal after one term in cell B2

Step 8

Click the black square in the lower right corner of cell B2 and drag it downward to fill the cells below it.

Step 9

Click 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 10

Type "=B1-($B$1+$C$1 * A1)" into cell D1.

Step 11

Click 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.

Tip

  • 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.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images

About the Author

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.

Zacks Investment Research

is an A+ Rated BBB

Accredited Business.