Dividend Reinvestment Calculator with Monthly Contributions in Excel

We can earn dividends when investing in the stock market or some business. Now, we can encash the dividend or reinvest the dividends in the stock market or business again. To compare the best return, you need a dividend reinvestment calculator. I will show you how to create a dividend reinvestment calculator with monthly contributions in Excel in this article.

What Is Dividend Reinvestment Calculator with Monthly Contributions?

A dividend reinvestment calculator is a calculator that calculates the ending reinvestment balance if you don’t encash your dividend but invest it again in the same market. Here, you need to know the share prices of the stock market or business both at the beginning of the month and at the end of the month. Following, if you can get the dividend data of the company or stock market, you can calculate the ending reinvestment balance through dividend reinvesting.

A dividend reinvestment calculator with monthly contributions is just another dividend reinvestment calculator where the dividend data is collected or calculated monthly. So, with this calculator, you will get the ending reinvestment balance every month through your dividends.

Dividend Reinvestment Formulas

To calculate the dividend reinvestment properly, you will need the following inputs and will get the outputs through the following formulas.

Inputs:

Initial Balance: This is the balance you have at the beginning of your calculation of dividend reinvestment.
Dividend Data: This is the dividend that you get from the stock market or business every month, quarter, or year.
Stock/Share Price: Stock price or business’s share price at the beginning and end of a month, quarter, or year.

Outputs:

Shares Beginning: This is the number of shares that you have at the beginning of the calculation.

For the very first month,

Shares Beginning = (Initial Balance/Share price at the beginning of the month)

For all the next months,

Shares Beginning = (Shares Beginning at the previous month + Reinvestment of the previous month)

Reinvestment: This is the number of shares that you reinvested through your dividends.

Reinvestment = [(Dividend×Shares Beginning)/Share price at the end of the month]

Ending Reinvest Balance: Thighs is the ending balance of your principal after reinvesting your dividends.

Ending Reinvest Balance = [(Shares Beginning+Reinvestment)×Share price at the end of the month]

Ending Balance: This is the last ending reinvestment balance of your calculation.
Cumulative Return: This is the cumulative return for reinvesting the dividends.

Cumulative Return = [(Initial Investment/Ending Balance) -1]×100%

How to Create a Dividend Reinvestment Calculator with Monthly Contributions in Excel: with Easy Steps

📌 Step 1: Record Dividend Data

First and foremost, you need to record the monthly dividend data.

To do this, first, create a worksheet named the Dividend Database. Following, record the dividend data as per dates.

Now, as the data are at irregular dates, you will need to extract the following month and year of each data. To do this, insert a column between Date and Dividend columns named Month & Year.

Subsequently, click on the C5 cell and insert the formula below which contains the TEXT function. Following, press the Enter button.

=TEXT(B5,"mmyy")

Next, place your cursor in the bottom right position of the cell. When the fill handle appears, drag it down to copy the formula for all other dates.

Thus, you will get an organized dividend dataset. For example, it should look like this.

📌 Step 2: Organize Share Prices & Dividends

The second thing you need to do is to organize the share prices and dividends.

To do this, initially, record the starting date of every month along with the share price at the beginning and the end of the month.

Now, you need to find the dividend for each following month. To do this, click on the E9 cell and insert the following formula which involves the VLOOKUP function and the TEXT function. Subsequently, press the Enter button.

=VLOOKUP(TEXT(B9,"mmyy"),'Dividend Database'!$C$5:$D$16,2,FALSE)

🔎 Formula Explanation:

TEXT(B9,”mmyy”)

This returns the value of the B9 cell’s date in text format with the date’s month and year.

Result: 0521

=VLOOKUP(TEXT(B9,”mmyy”),’Dividend Database’!$C$5:$D$16,2,FALSE)

This looks up the previous result in the Dividend Database worksheet’s C5:D16 range and returns the 2nd column value where the lookup value is found.

Result: 0.617

Notes:

Here, the data range should be made absolute to avoid errors. You can do this by putting a Dollar sign ($) or else you can simply press the F4 key.
The column containing the lookup value should be the first column of the table array as you have used the VLOOKUP function. Otherwise, errors will occur.

As a result, you have found the dividend for the following month. Now, place your cursor on the bottom right position of your cell and as a result, the Fill Handle will appear. Drag it downward to copy the formula for all the other cells below.

Thus, you can find and record the dates, dividends, and the beginning and ending share price of the following month. Finally, the outcome should look like this.

📌 Step 3: Calculate Monthly Dividend Reinvestment

After recording the inputs, you need to calculate the dividend reinvestment monthly now.

To do this, at the very beginning, put your initial investment at the F4 cell of the sheet.

Next, click on the F9 cell and write the following formula to calculate the number of shares at the beginning of your investment. Subsequently, press the Enter button.

=F4/C9

At this time, click on the G9 cell and write the following formula to find the reinvestment for the following month. Finally, press the Enter button.

=E9*F9/D9

Last but not least, you need to find the ending reinvest balance now. To do this, click on the H9 cell and insert the following formula which involves the SUM function. Subsequently, press the Enter button.

=SUM(F9,G9)*D9

As a result, you have calculated all the required things for the first month of your reinvestment. Now, for calculating the second month’s shares beginning, insert the following formula at the F10 cell and press the Enter button.

=SUM(F9,G9)

Next, place your cursor in the bottom right position of the cell which results in the appearance of a black Fill Handle. Following, drag it below to copy the formula for all the other cells below.

Subsequently, for all the other months’ reinvestment, place your cursor in the bottom right position of the G9 cell and drag the Fill Handle downward when it appears.

Similarly, for all the other months’ ending reinvest balance, place your cursor in the bottom right position of the H9 cell. Following, drag it down to copy the formula for all the cells below.

Finally, you will calculate all the months’ dividend reinvest balance. For instance, the result would look like this.

📌 Step 4: Calculate the Return of Reinvestment

Lastly, you will have to calculate the return on the reinvestment.

To do this, click on the F5 cell and refer to the H20 cell’s value. As the H20 cell is the last cell of our ending reinvest balance calculation, this is the ending balance.

At this time, click on the F6 cell and insert the formula below. Subsequently, press the Enter button. Make this cell’s number format as Percentage.

=F5/F4-1

Finally, you can see that your dividend reinvestment calculator with monthly contributions is completed. And for example, the outcome should look like this.

Download Sample Workbook

You can download our sample workbook from here for free!

Dividend Reinvestment Calculator with Monthly Contributions.xlsx

Conclusion

To conclude, in this article, I have shown you all the detailed steps to make a dividend reinvestment calculator wth monthly contributions in Excel. I would suggest you go through the full article carefully and practice with our sample workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!