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!
Shoot, I just looked at my dividend income YTD on Vanguard and woot!
Hopelessly indecipherable, lol. Far from it.
Earning money by doing nothing at all is pretty awesome, right? 🙂 And you don’t need an extensive dividend tracker like mine because you only invest in some ETFs.
And here I thought I was good at excel because I get paid to do it haha. Very nice job, love how you focus on presentation too!
Thanks, Henry! I also get paid to work in Excel, otherwise I would not have been able to make this spreadsheet.
Hope you found something you found useful enough to add to your own spreadsheets!
You can actually import automatically updated currency exchange rates directly into your spreadsheet. This link will tell you how.
Similarly you can import automatically the latest share prices for the companies you own.
That’s awesome! I knew that was possible in Google Docs, so I was trying to convert my spreadsheet to a Google sheet, but didn’t know Excel had a solution too.
Automation, here I come!
Thanks for visiting and for the tip,
How do you think this compares against Mint.com or PersonalCapital? Personally, I love both tools and the different views they give. This seems a bit too niche though for a large investor.
Haven’t got a clue since I don’t have access to Mint and haven’t used PersonalCapital! Thanks for pointing me towards PersonalCapital though, I’m definitely going to try that app out.
You’re right that this Excel sheet is probably a bit too niche, but that’s because it is not aimed at reaching the biggest of audiences. Mostly I just want to share what I find useful and maybe someone learns something new by looking at the formulas I’ve used.
And some people just like doing stuff manually! 🙂
Thanks for your input,
Thanks NMW. I was looking for an easy way to see my weights by sector.
I’m glad you find it useful, DFG!
Nice looking spreadsheet visually, however you should be able to automate a lot of this spreadsheet by looking up prices, streamlining the conversion from “home” currency to foreign, and cleaning up some formula issues (Ex. The totaling of foreign currency values in the currency exchange box, column D). Either way, the clean look and visual appeal is tremendous.
Thank you, W2R!
Someone else already mentioned that is is possible to import prices and currencies in Excel – I didn’t know. That would streamline the experience by a lot, like you said. I’ll also look into the formulas, too bad I’m not an Excel wiz.
Thanks for stopping by and for your great input,
I haven’t had a chance to look at this (on break at work), but I can’t wait to give it a spin at home– especially since we’re starting from scratch and will be able to watch our dividends roll in from here on out!
Thanks for making this available!
No problem, CMC!
Some commenters above pointed out some minor flaws with the spreadsheat, so I’ll probably correct those tonight. In the mean time you can already get started and see what’s what.
I hope this spreadsheet is of any use to you!
Thanks for stopping by,
Hello, I’ve only recently started purchasing individual stocks, am about to get my first dividend payment (a small one but I’m excited!) and was wondering how I could track my future dividends, so your tracker is perfect, thanks very much!
“When a year is over, just change the number in cell O1 again,”
I was about to ask you what would happen if I changed this cell O1 to “2015” but just playing around with it , I see that it all works beautifully! No idea how you’ve linked the cell but I don’t need to know how it works, just that it works!
You’re welcome, I’m glad you find this spreadsheet useful!
As for cell O1: it counts all the dividends on the ‘dividend income’ page of a specific year. You can change it back to 2014 and it’ll recalculate everything again for 2014.
Thanks for stopping by and good luck on your dividend quest,
Hi No More Waffles,
Thanks for providing this spreadsheet. I used your example of a currency converter to put together an international dividend investing template on Google Sheets with a transactions sheet. It is only in version 1.1.1 but the currency conversion is pretty slick.
Thanks again for providing the idea.
Glad to hear you found this spreadsheet useful! There’s also an updated version available through Google Sheets: http://www.nomorewaffles.com/2014/11/using-google-spreadsheets-to-track-your-dividend-portfolio-updated-template/
Be sure to link back to the site!
Yep, you’re already in my blogroll!