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.

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

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

**Read More:** **How to Calculate Dividend Yield in Excel (with Easy Steps)**

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

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.

**Read More: How to Calculate Share Price Volatility in Excel (2 Easy Methods)**

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

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

**Read More:** **How to Calculate Dividend Per Share in Excel (with 3 Easy Examples)**

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

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

**Read More:** **How to Calculate Intrinsic Value of a Share 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!