Automatic Estimation of Monthly Dividends for Google Sheets
Please go here to get the latest version of this spreadsheet: Dividend Stock Portfolio Spreadsheet on Google Sheets
On my personal dividend portfolio I have a dividend calendar updating automatically as dividends are recorded on the transactions sheet. Here’s what it looks like with only the last couple dividend paying stocks displayed:
The orangish colored cells (manually placed) are the months that the stocks pay. Then any dividends that are paid will automatically fill in for the appropriate stock and month and update the cell color to green to indicate that the dividend has been received. This relies on the dividends being entered as type “Div” on the Transactions sheet and that the correct date is also used.
I then sum up the entire column of monthly dividends to show how much I received that month.
The final row is for the estimated dividends based on each stock’s current dividend payout for that month.
The issue I was running into was keeping the estimated dividends up-to-date. I literally had to reference the cell for each individual annual dividend and then make sure I was dividing it by 4 or 2 if the stock paid quarterly or semi-annually. Troubleshooting was difficult if I added a new stock or sold an entire position.
The complexity of this also meant that I had not included this feature on the publicly shared template…until now!
Today I’m unveiling version 4.2, which incorporates an automated method of estimating monthly dividends that is fairly easy to use.
The newly added sheet is called DivPayoutCalc and here’s what it looks like:
You first type in the company name in column A or pick it from the drop down menu. It must match exactly what you typed on the Portfolio page. Then in Column B “Pay Months” you have to manually type in the months that the stock pays. For example, Apple pays in February, May, August, and November so I entered Feb,May,Aug,Nov. In order for this to work you have to enter the months using the following abbreviations (case-sensitive):
January – Jan
February – Feb
March – Mar
April – Apr
May – May
June – Jun
July – Jul
August – Aug
September – Sep
October – Oct
November – Nov
December – Dec
Column C “Annual Dividends” then grabs the annual dividend for that stock from the Portfolio sheet.
Here’s the formula, in case you are interested:
=if(isblank(A2),,query(Portfolio!$C$5:$O$60,"select O where C='"&A2&"'"))
Column D “Payout #”: This counts the number of times the dividend is paid per year by the number of months you entered in column B. As you can see, BHP Biliton and Disney each pay twice a year so the number “2” is displayed. Here’s this formula:
Column E “Dividend Payout” shows the dividend that can be expected at each payout interval.
Then the monthly dividends that are expected from all of the companies you entered are summed on the column on the right.
=sum(query($B$2:$E$39, "select E where B like '%Jan%'"))
The formula above works by searching the pay months column for “Jan” and then summing the corresponding dividend payouts.
Easiest way to update would be to just grab an entirely new copy of the template and then paste in the manually entered data from the Portfolio and Transactions sheets.
Hope this works well for everyone! Let me know if you have any questions.