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.
Again, many thanks for a great spreadsheet. Makes my life much easier. The estimated dividend function is really useful.
I did have a one issue though. I got an error on the Annual Dividends and Dividend Payout columns on the DivPayoutCalc page. The problem seems to be Annual Dividends formula is using Portfolio C column which is the company name. But the Dividend Paying Stocks column is using ReferenceData column A which is the Stock Symbol, not Stock Name. I changed the Data validation for the Dividend Paying Stocks column to use ReferenceData column B and it works. Any chance the template you posted was the wrong one?
Good catch, Tom! Thanks. I did forget to update the validation. It is fixed now.
The Estimated Dividend row on the DivCalendar also had referenced the wrong cells. This is now updated too.
I think I’m having the same issue as TOM J, but I’m not as excel savvy to change up the formula myself. On the DivPayoutCal, the Annual Dividend and Divident Payout is giving me an error. I have 3/11 stocks that works though. If I switch around those 3 stocks they work, but all the other stocks keeps giving me an error
I wanna try this but not good on formulas we will see might never get it to work lol
Doug recently posted…Dividend Stock profile
Haha. Let me know if you need any help. It should just be a matter of copying over the orange colored cells from the Portfolio and Transactions pages, if you’re using an older version of the template.
Very nice work. We have a spreadsheet that we use to keep track of our dividends stock portfolio but will certainly give this a try. Many thanks for sharing this new tool with the DGI community!
Best wishes! AFFJ
A Frugal Family’s Journey recently posted…Retirement Accounts (Update) – Sept 2016
No problem, AFFJ. It’s been fun to make it! Let me know if you run into any issues.
Wow, really great spreadsheet!
Thanks, thedividendlife! It’s been fun to see it evolve over the last couple years.
Awesome Dividend Spreadsheet!
Hi Chet, I’m glad you like it. Thanks for commenting! I usually think of something that I’d like for my own portfolio spreadsheet and then add it to the public template.
If there’s anything that anyone would want, please let me know and I’ll see what I can do. In the next month or so I will release a way to compare portfolio performance to that of the S&P500 (or any other stock/ETF). It is working okay right now but is still a work in progress!
Nice work Scott. I do like manually entering this information, but there are times when I wish the spreadsheet would do it for me. I’m telling you man, sell this baby. I’ve gotten so much out of this spreadsheet, I’d happily pay you for updates.
I’m using a previous version, actually two versions back. Do you have any advice on how to easily upgrade to the latest version without losing existing data?
Investment Hunting recently posted…Buying 100 Shares for Options Trading
Thanks, Nathan. I’m glad you like it. I have thought about selling it but decided against it. Right now I just do it for fun. It already takes up quite a bit of time (just check how many comments are on its main page!) If I were to sell it I’d feel obligated to spend even more time working on it. Issues have sprung up in the past (from things that Yahoo or Google changed on their side). I got hundreds of emails during that mess and if it was a paid spreadsheet I could rightly see people getting quite upset. I have other more lucrative things that I can devote my time to. However, in lieu of paying for the spreadsheet, I will be including some donation links. 🙂
Two versions back…is that one that includes the Transactions sheet still? I can’t remember. If it does then converting to the new one is straightforward. Just copy over all the data in the orangish colored cells from the Portfolio, DivCalender, and Transactions sheets. The light green contains formulas that you can overwrite if you know what you’re doing. The DivPayoutCalc is the latest addition and its use is described above.
Let me know if you have any questions.
I get an error on the divpayoutcalc tab for annualdiv column –> #N/A. How do I resolve this error ?
I also have this problem
Hello TwoInvesting, thank you for sharing portfolio spreadsheet. Are you aware how to get dividend income for a stocks for period of time. Example T since: 2016-05-10 to: 2019-02-01 (today). Result: total dividend eligibility in $. In a sheet, I understood needs to be done by entering manually all the divs into Transactions.
I pay a quick visit daily some sites and blogs
to read posts, except this webpage offers quality based posts.
berrykitavip.com recently posted…berrykitavip.com
This paragraph offers clear idea in support of the
new people of blogging, that actually how to do blogging.
Karl recently posted…Karl
Is there a way to screen scrape the quarterly dividend rate and payment date without using IEX? None of the old methods work anymore.