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.


Download Sample Workbook

You can download our sample workbook from here for free!


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%


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

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

Dividend Data to Create Dividend Reinvestment Calculator with Monthly Contributions in Excel

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

New Column to Find Month & Year of Dates

  • Subsequently, click on the C5 cell and insert the formula below which contains the TEXT function. Following, press the Enter button.
=TEXT(B5,"mmyy")

Using the TEXT Function to Extract Month and Year from a Date

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

Drag Fill Handle to Copy Same Formula

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

Complete Dividend Dataset


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

Date & Share prices at Different Months

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

Using VLOOKUP Function to Create Dividend Reinvestment Calculator with Monthly Contributions in Excel

🔎 Formula Explanation:

  • TEXT(B9,”mmyy”)

This returns the value of the B9 cell’s date as 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 for 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 for avoiding errors. You can do this by putting 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.

Drag the Fill Handle to Find All Dividend data

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.

Inputs of a Dividend Reinvestment Calculator with Monthly Contributions in Excel


📌 Step 3: Calculate Monthly Dividend Reinvestment

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

  • For doing this, at the very beginning, put your initial investment at the F4 cell of the sheet.

Record the Initial Investment

  • 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

Calculate Number of Shares at the Beginning of the Investment

  • 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

Calculate Reinvestment for the First Month

  • 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

Calculate the Ending Reinvestment Balance

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

Calculate Number of Shares for the Second Month

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

Drag Fill Handle to Copy Formula

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

Drag Fill Handle to Calculate All Reinvestments

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

Drag Fill Handle to Calculate All Ending Reinvest Balance

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

Dividend Reinvestment Calculator with Monthly Contributions in Excel


📌 Step 4: Calculate the Return of Reinvestment

Lastly, you will have to calculate the return of 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.

Ending Balance of Reinvestment

  • 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

Dividend Reinvestment Calculator with Monthly Contributions in Excel

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

Dividend Reinvestment Calculator with Monthly Contributions in Excel


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.
And, visit ExcelDemy for many more articles like this. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo