Because I received my first dividend payment last week, it was high time that I created an Excel spreadsheet to keep track of all my stock purchases and incoming dividends. So that’s just what I did the last few days. As per usual, I’ll also share the end result so you can profit from my work too!
Tracking your dividend income is important to establish the return and yield of your investments, both in the short and long term. You can furthermore keep tabs on the growth of your dividends on a stock per stock basis, but also for your entire portfolio. To measure is to know, of course.
So, what did I come up with?
My spreadsheet is focussed on international investors, but you can also use it if you invest in only one currency like the US Dollar. I’ve made my dividend tracker that way because I want to keep an eye on both the capital appreciation of my international stocks and the incoming dividends. Dividends, however, can only be tracked in your home currency.
As such, you’ll have to enter the cost basis of each position in your home currency and provide your dividends in your home currency, even though the spreadsheet will automatically calculate the current underlying value of your portfolio to your home currency based on the last-known exchange rate as entered by yourself. Afterwards it will provide the value gain of your holdings and the yield.
If you’re scratching your head by now, don’t worry. I’ll explain!
The top left of the first worksheet provides a handy currency exchange calculator and pie graph to represent the associated weight of every currency. You can change the currencies you invest in to your heart’s contempt, but I went with Euros, Dollars, the Pound-Sterling and the Swiss Franc. Make sure you enter the right symbol and exchange rate.
In the big table below, where you should enter your different stocks, column E provides you with a couple of cells where you should enter the corresponding currency symbol of every investment. Columns I and J will do all the remaining work in calculating any foreign currencies to your home currency.
The first workbook of my dividend spreadsheet also provides a nice graph showing how diversified your investments are. I’m using ten basic sectors, which you can also change to your liking. The table on the right takes the data from the large portfolio table in te middle and aggregates the numbers based on the information entered in column D.
On the second tab you can find an overview of all dividends you’ve received thusfar. If you’ve used my budget tracker you’ll know how this works. Cell O1 makes it so that all dividends from a particular year are collected from the ‘Income’ tab an displayed in a nice overview, graphs included.
The only thing you have to do to make this sheet work is add a ticker to column B and enter your dividend income in worksheet ‘Income’ by date, ticker and the amount you’ve received. The dividends entered there will also show on the first page. When a year is over, just change the number in cell O1 again, or change it whenever you want to see any of the previous years again.
That’s about it!
Still not sure if you understand what I’m trying to say because I’m hopelessly indecipherable? Then download my dividend tracker with some example values for yourself and see what’s what!
Feel free to play around with the Excel formulas and change the spreadsheet to your liking. Be sure to let me know in the comments, though, what you would like to see differently so that I can keep adding functionality and make this a truly powerful tool to track your dividend progress.
Update: be sure to check out the updated version, which offers more automation!