Add Dividends to Your Portfolio Spreadsheet in Excel (or Google Sheets)
Check out the other support videos for the stock portfolio spreadsheet:
https://investsomemoney.com/support-f...
Complementary post for my Create an Amazing Stock Portfolio Spreadsheet (Excel) video:
https://investsomemoney.com/how-to-ma...
and, the video itself:
https://investsomemoney.com/video-tra...
Plus, the Dividend Tracking post/spreadsheet
https://investsomemoney.com/create-an...
====================
Dividends are an important part of overall returns so that’s why I included a column on my Create an Amazing Stock Portfolio Spreadsheet which I’ll link down in the description.
That’s why I included a column for total dividends received as you can see. That’s entered manually here.
Entering dividends manually
I talk a little bit about why the reason it has to be manual is that its difficult information to automate, unfortunately. Total dividends received figures into the net current value. Which is what is used to calculate total gain and loss, dollars, percentage, and, ultimately annualized gain and loss.
Like I mentioned in the post and in the Create an Amazing Stock Portfolio Spreadsheet video (I’ll also link that in the description) is the total dividends received should be entered for each individual stock.
If you decide to enter your lots separately like say DEO here. You purchased 100 shares at one point and 30 shares at another point. Rather than 130 shares all at once. Then you want to break those dividends down by lot just to get an accurate total gain and loss for each lot.
Dividends from broker
Hopefully, that makes sense. So where do you go about retrieving this information? As I mentioned in the post the ideal place is from your broker. Now I give a little screenshot here of my account from TD Ameritrade. For this video, I tried to chase down where to find this information for some of the bigger brokers. Your E-trades, Schwabs, what is it, Fidelity. In them and surprisingly this information was kind of hard to find you know. Because I wanted to be able to say if you have any trading account go click, here, here, and here. And um yeah like I said it was uh not easy information to get a hold of.
So I can only show you firsthand for TD Ameritrade. You know hopefully it’s not too hard to find for your particular broker and it should follow more or less the same logic. It should be readily available information. Anyhow uh on TD Ameritrade as I show here you just click on the transactions tab, select dividends, from the list, and then you’ll have to enter your symbol. Which is fine. And enter a date range and that’s it.
You know it brings up a search and the total amount of dividends. You got to kind of calculate them manually but not too much trouble.
Dividends from other sources
Now if for whatever reason your broker doesn’t do that then I recommend going to the Nasdaq dividend history page. This is the best tool that I’ve found. You can enter a symbol here. You want to click on dividend history here.
Okay, so here you can find a dividend history going back a very long time. So this is of course going to be per share and then you’re going to have to do a little work on your own to figure it out.
So you know if we look here back on the portfolio spreadsheet, VLY we can see we purchased it on July 16, 2014. 200 shares. So in this case we would know we want to look at the ex-dividend date and pick the ex-dividend date that’s after that purchase date.
Okay, so July of 2014. So it’s basically every dividend from here forward you want to copy it. You should be able to copy that information and you can create a new tab. I would probably say Ctrl+Shift+V just drop in the information.
Calculating dividend history from other sources
There you go and then all we’re going to do here is say equals per share dividend amount × the number of shares we have. It looks like it was $0.11 per share consistently ever since. So we just type in equals the column with the dividend times our 200 shares.
Copy that up and there we go. We’ve got our total here, some $594.
As I mentioned the spreadsheet was a little dated at this point. But we would just update that and there we go.
It’s going to update our amounts are net earned value, total gain, and loss, etc. Again the ideal way is to get it from your broker. The backup way is to go to nasdaq.com and look at dividend history or some similar site.
You know just take your amount of shares times the per-share dividend. There you go. It’s a little labor-intensive. It would be ideal for it to be a little more automated. But you know we work with the tools we have.
If there’s a better way I please feel free to comment. Let me know I’d love to know it.
