Inspiration

Free International Dividend Portfolio Tracker | Google Finance Template

Dividend Portfolio Tracker

Are you looking for a free Dividend Portfolio Tracker in Google Sheets? Then check out my personal dividend portfolio tracker which I’ve been using for several years already.

One of the major benefits of this dividend portfolio tracker is the ability to process your international portfolio holdings and converting everything back into your local dividend portfolio currency.

Today I decided to share it with you, because I think it might be useful for you as well.

I really think that it can save you a lot of time, because you don’t need to start building this from scratch anymore. At the same time it should also provide you with all the basics you need as a dividend growth investor.

Hence, would you like to know more about my free Dividend Portfolio Tracker?

Then you’re at the right place, because in this article I will describe everything it contains and how you can use it.

This Google finance template also allows you to make your own adjustments based on your own specific needs. Just consider it some kind of form of open source.

Hence, if you feel that your adjustments could be of interest to other dividend growth investors in the community, then please leave your suggestion in a comment below and I will consider including it.

Remember, Sharing is Caring 🀘

Note: Access to this simple and free International Dividend Portfolio Tracker requires sign-up to my blog via email.



No spam, just 1 email per week to notify you about my latest blog posts


Introduction to the Dividend Portfolio Tracker

I have tried many online tools already, but in the end I keep on going back to my own Google Sheets template. There are two simple reasons for that:

  • Existing dividend portfolio trackers lack flexibility to create additional views and graphs
  • Data, and especially earnings and dividend data about European companies are often incorrect

Unfortunately, my broker neither provides me with the necessary insights. They are simply focused on other kind of investors and not on dividend growth investors.

As an example, I’m personally not interested in the daily swings of my holdings, so I neither want to see it in my basic portfolio view. I can’t change these views…

On the other hand, I’m also having shares at 3 stock brokers. This doesn’t make it any easier and it also requires me to look for an external tool to create a single source-of-truth.

I think you get it already, there’s just no way I seem to be able to escape from having my own Dividend Portfolio Tracker template.

But there’s good news though!

Because with a little bit of work you will be able to create your full-blown individual dividend portfolio tracker which is fully tailored to your own needs.

The introduction of free cloud office products with access to rich API’s and a rich library of extensions has really made our life easier πŸ’ͺ

Hence, this Dividend Portfolio Tracker is powered by Google and I have been using it already for many years.

Actually, just recently I have enhanced it quite significantly with several graphs for my dashboard which I think you might feel very interesting.

As an example, the following graphs are now part of the main dashboard:

  • Basic Statistics
    • The FIRE Meter | It tells you how much your projected annual dividend income is (PADI)
    • Your dividend portfolio yield (pre-tax)
    • Total amount of cash deposited
    • Your unrealized gains
  • Dividend Related
    • Year-over-Year annual dividend income
    • Year-over-Year monthly dividend income
    • Monthly dividend income since the start of your journey
  • Portfolio Related
    • Portfolio value growth over time
    • Projected Annual Dividend Income per sector
    • Portfolio value per sector
    • Top-Down overview of largest holdings
    • Tax-adjusted dividend yield per share overview
    • Total dividends received per holding overview
    • Portfolio goal progress

And last but not least, one of the best features which should be really interesting for European dividend growth investors:

All the values are automatically converted into your local currency (i.e. EUR).

I have a feeling that you might find this dividend growth portfolio tracking quite interesting 😎


No spam, just 1 email per week to notify you about my latest blog posts


The reason for choosing Google Sheets

Over the years I have found Google Sheets very useful. Firstly, it comes for free with my Google account πŸ™.

Secondly it has a great GOOGLEFINANCE() function which allows us to pull a decent amount of stock data directly into the Dividend Portfolio Tracker.

Thirdly, it’s great for collaboration and sharing with a wider community.

But no worries, if you are rather an Excel type of person then you can also download the template into MS Excel format.

I’m just not sure whether you should change some of the formulas as I have never tried it before. I haven’t got any licenses in MS Office anymore.


I will now explain you step-by-step how you can use this Dividend Portfolio Tracker by yourself.

Just consider it as a short manual.

However, you may off course decide to use it directly without reading the manual, but in that case please don’t reach out to me to help you fix it 😁

Though, I don’t think you will run into any issues as it should be quite self-explanatory.

1. Configure the Reference Data worksheet

The first step you should do is to go to the 3️⃣ Reference Data worksheet to set up the dividend portfolio tracker.

There are actually few things to do here:

  1. List the tickers of all your holdings in column A – it is important to use google recognized tickers here
  2. Select your portfolio currency in column B- this will be used to convert all other currencies into your portfolio currency.
  3. List your stock brokers in column C- it allows you to create an overview per stock broker
  4. Define your portfolio target value in column D – it allows you to calculate how far you are on your journey
Dividend Portfolio Tracker - Reference Data worksheet configuration
Dividend Portfolio Tracker – Reference Data worksheet configuration

That’s all in this worksheet. All the rest is rather static and wouldn’t require your adjustments.

2. Add the Stocks you own into the Dividend Portfolio worksheet

The second step you should do is to go to the πŸ’° Dividend Portfolio 🌴 worksheet and list the stocks you own.

I didn’t automate this step on purpose, because you would scr*w up your data when sorting if it would be a direct reference from the reference data sheet.

Hence, only the drop-down list is automated, but you would need to select the stocks you would like to see here (a simple way to do that would be to just copy/paste the list from the Reference Data worksheet).

The things you need to do on this worksheet are:

  1. List the ticker symbols that are part of your portfolio
  2. Select the country where the ticker is listed – it is used for determining the tax rate
  3. Define the sector they belong to – it will be used in the dashboard
Dividend Growth Portfolio - Main sheet
Dividend Growth Portfolio – Main sheet

All other data in this worksheet is automated. A lot of it depends on the transactions entered in the transaction worksheet.

Did you know?
Column S lists the tax adjusted yield. This means [Tax – Country Dividend Tax rate]. Using this figure will give you the net dividend yield which I consider a more reliable number.

3. Add the Dividends per Share for each Dividend Portfolio Stock

The third step you should do is to go to the 2️⃣ Portfolio Dividends worksheet and list the annual dividends per share per each of your holdings.

What you need to do here:

  1. List the ticker symbols that are part of your portfolio – see note above on why this isn’t automated
  2. Insert the Annual Dividend – a good source for US companies is Nasdaq.com and for European companies I advise to go to the respective Investor Relations website and look up for their latest announcement (i.e. annual report)
  3. Select the current the dividend is paid in – this is not automated, because some companies pay dividends in a different currency than what they are listed in (i.e. Royal Dutch Shell – A shares)
  4. Select the dividend distribution schedule – bi-annually is to be selected when you own a company that pays an interim and final dividend

The name of the company and the dividend in [your portfolio currency] is automated in this worksheet

Portfolio Dividends per share
Portfolio Dividends per share

Did you know?
There is an opportunity to automate most of this by using for instance the XHTML() function to data scrape dividend data from a website or by using the Yahoo google sheets extension. However, I have not done this as I find most of the dividend data from European companies unreliable. This data has to be accurate for me, so I rather update this manually as soon as the companies announce their new dividends per share.

4. Add every portfolio transaction

This is where the fun starts, because entering your data in the 1️⃣ Transactions worksheet will start populating the dividend portfolio worksheet with your total overview and it populates most of the charts in the πŸ“Š Dashboard worksheet.

In the below paragraph I will provide instructions for each possible transaction.

a. How to add cash deposited at your brokerage account

When you deposit cash into your brokerage account you can add it as a transaction.

It allows you to see how much cash you have contributed in your entire portfolio life without blurring it with incoming dividends.

To add a cash transaction, do the following:

  1. Select the correct date in Column A
  2. Select Cash Transfer as type in Column B
  3. Keep Column C empty and select the broker to which you transferred the money in Column D
  4. Enter the amount of cash deposited in Column L
Add a cash transfer into the dividend portfolio tracker
Add a cash transfer into the dividend portfolio tracker

b. How to add a new stock buy

This is one of the nicest steps to do: adding new shares and especially when it’s an entirely new position

To add a new stock buy, do the following:

  1. Select the correct date in Column A
  2. Select Buy as transaction type in Column B
  3. Select the ticker in Column C
  4. Select the broker in which you purchased the shares in Column D
  5. Enter the number of acquired shares in Column G
  6. Enter the price per share in Column H
  7. Enter the commission and other possible related costs in Column I

Keep Column E and F empty. It will auto-calculate based on the selected ticker.

The total amount of money invested in those shares will be visible in column J and automatically calculated using your portfolio currency.

Add a new stock buy into the dividend growth portfolio template
Add a new stock buy into the dividend growth portfolio template

c. How to add a new stock sell

Adding a transaction to reflect a sale in shares is quite similar to adding a stock buy. The only thing different is the type you chose and filling in a negative number for the amount of shares.

To add a new stock buy, do the following:

  1. Select the correct date in Column A
  2. Select Sell as transaction type in Column B
  3. Select the ticker in Column C
  4. Select the broker in which you sold the shares in Column D
  5. Enter the number of shares sold in Column G. Make sure it’s a negative number by preceding it with a –
  6. Enter the price per share in Column H
  7. Enter the commission and other possible related costs in Column I

Keep Column E and F empty. It will auto-calculate based on the selected ticker.

The total amount of money representing the shares sold will be visible in column J and automatically calculated using your portfolio currency.

Add a sell transaction into the dividend growth portfolio tracker
Add a sell transaction into the dividend growth portfolio tracker

d. How to add dividend payments

Pulling up my broker statements to see the cash from dividends on my account is one of the best things that exist in life 😍

Cash is truly king!

Hence, truly the best moment is to register the dividends received from our great dividend paying companies.

To add a new dividend payment, do the following:

  1. Select the correct date in Column A
  2. Select Dividend as transaction type in Column B
  3. Select the ticker in Column C
  4. Select the broker in which you received the dividends in Column D
  5. Enter the total amount of dividend after tax received in Column K. Do this in the currency in which the dividend was paid.

The conversion into your portfolio currency happens automatically in column M.

Add a dividend payment to the dividend portfolio tracker
Add a dividend payment to the dividend portfolio tracker

Did you know?
That I am not capturing withheld dividend tax. The information that I’m capturing in column K is the dividend paid after tax. If you have a need to add the dividend tax paid, then I suggest to insert a new column after it and use the “conditional formatting” approach applied to column K.

e. How to register a dividend reinvestment (DRIP) transaction

You can also register dividends which you are automatically reinvesting and the process for that is quite simple.

Firstly, repeat step D and register the dividend. Secondly, repeat step B on how to buy a stock with the amount of shares that you have received on your brokerage account.

Your broker will typically list the repurchase price in the transaction statement.

And that’s all. The money left after repurchasing shares will naturally stay on your cash account and can be used for your next stock purchase.

f. How to add stock splits

To be fair, this is a function that doesn’t work well yet. The reason for that is the fact that Google recalculates the historical prices based on the share split.

Example
Imagine that Unilever’s shares were split in 2 as per 1 January 2021. Just before the split they were trading at 50 Euro per share and after the split they were trading at 25 Euro per share. Google will then recalculate all the historical share prices from before 1 January 2021 and split them by 2 (thus it became 25 Euro, not 50 Euro anymore)

The good thing is, share splits happen only once in a while.

But in case it does happen, what you can do in this template is to record a share price split as follows:

  1. Select the correct date in Column A
  2. Select Split as transaction type in Column B
  3. Select the ticker in Column C
  4. Select the broker in which the share split happened in Column D
  5. Enter the total amount of new shares received in Column K. Don’t include the shares you already owned.

You will now see the new total amount of shares listed in the πŸ’° Dividend Portfolio 🌴 worksheet. The cost basis in column H should not have changed.

Register a share split in the transaction worksheet
Register a share split in the transaction worksheet

Did you know?
There is one issue we have now and that relates to the Portfolio Value chart in the πŸ“Š Dashboard. The root cause for the issue is in the πŸ“ˆ Portfolio Value (quarterly) worksheet.

You will now have to manually change the historical value at a certain date for that portfolio position. However, this can easily be done by increasing the value of the cells before the date of the share split. I.e. if your shares were split in 2, then all you need to do is to multiply those values by 2 up till the share split date. I will show you in the video below how to do that.

5. Configure the Dividend Portfolio dashboard

The dashboard is pre-configured with several charts that I have found useful over the years. These charts are focused on the key insights I want to have when evaluating the performance of my portfolio.

What you will see is that the dashboard is split up into 3 sections.

Section 1 – the ultimate figures.

The most important value it lists in here is the total amount of projected annual dividend income (PADI).

The other values like total portfolio value and total cash deposited provide you with insights regarding the wealth you have generated so far.

Almost all the charts in this dashboard are automatically populated once you have done step 1 to 4 already. I will soon show you one chart where this is not entirely the case and where some manual intervention is required.

Another interesting metric which you will probably want to know is your personal dividend portfolio yield (pre-tax).

The ultimate dividend portfolio metrics
The ultimate dividend portfolio metrics chart

Section 2 – Dividend Income

This section shows you several charts and three of them require configuration (see chart configuration section below the screenshot πŸ‘‡).

Firstly it shows you year-over-year the total amount of annual dividend income received on your accounts.

Secondly it shows you the year-over-year dividends received in each month. This is an important chart to show you how your progress is going and it also shows you which are the low-dividend-income months versus the high-dividend-income months.

Thirdly it provides insights in the total amount of dividends received per month for your entire portfolio lifetime. I find this a very interesting chart which shows the exponential nature of dividend growth investing.

Lastly, it also provides you with the year-over-year quarterly dividend income for the last 3 years.

Dividend Income charts in the dividend portfolio dashboard
Dividend Income charts in the dividend portfolio dashboard

Chart configuration
The starting year for three of these charts need to be defined in the 4️⃣ Raw Data Statistics worksheet. Select the starting year in cell C2 to configure the Year over Year – Monthly Dividend Income chart. Select the starting year in Cell B20 to configure the Annual Dividend Income chart.

Section 3 – Portfolio

This section holds several different charts related to the specifics of your dividend portfolio tracker.

Firstly it will show you the portfolio value over time. This chart requires special attention and configuration for it to work well (see chart configuration section below the screenshot πŸ‘‡).

Secondly you will see two pie charts related to your sector diversification. The first one shows the projected annual dividend income per sector and the second one shows your general exposure to each sector in the portfolio sector allocation pie-chart.

Thirdly you will see three horizontal bar charts listing all your stocks. All these three bar charts are sorted (Z-A) based on the total portfolio value in the πŸ’° Dividend Portfolio 🌴 in column H. That’s also where you can change the sorting to change the sorting of these charts.

The three different horizontal bar charts presented in here are the Holding Value per share, the Tax adjusted dividend yield per share and the Total dividends received per share for its entire lifetime.

Last but not least, at the bottom you will find the Portfolio goal progress chart. This chart shows your portfolio value % compared to your portfolio FIRE value defined in the 3️⃣ Reference Data worksheet (cell D2).

This chart requires the exact same chart configuration as the first Portfolio value chart.

Portfolio related charts on the dividend portfolio dashboard
Portfolio related charts on the dividend portfolio dashboard

Chart configuration
The first and the last chart both require configuration in the πŸ“ˆ Portfolio Value (quarterly) worksheet. The only configuration required are the dates in cell C2 until for instance Z2..

I have personally chosen to have these charts providing insights per quarter since the start of my dividend growth portfolio. Hence, entering the first ever portfolio quarter ending date can be done in cell C2. In cell D2 and so on you can enter the next dates depending on whether you want it to be displayed in months, quarters or years.

Instructions in Video format

Will be released soon


No spam, just 1 email per week to notify you about my latest blog posts


Yours Truly,

European Dividend Growth Investor


Disclaimer

I’m not a certified financial planner/advisor nor a certified financial analyst nor an economist nor a CPA nor an accountant nor a lawyer. I’m not a finance professional through formal education. I’m a person who believes and takes pride in a sense of freedom, satisfaction, fulfillment and empowerment that I get from being financially competent and being conscious managing my personal money. The contents on this blog are for informational and entertainment purposes only and does not constitute financial, accounting, or legal advice. I can’t promise that the information shared on my blog is appropriate for you or anyone else. By reading this blog, you agree to hold me harmless from any ramifications, financial or otherwise, that occur to you as a result of acting on information provided on this blog.

5 2 votes
Article Rating

It's my desire to retire early via Dividend Growth Investing as a passive income stream. This is not easy and especially when living in Europe. That's why I started this blog and share my journey: to give you a European perspective.

Subscribe
Notify of
guest
7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Guillermo PΓ©rez-Bas Gil
Guillermo PΓ©rez-Bas Gil
Reply to  European DGI
1 month ago

Hi European! congrats for the template! I’m trying to adapt it to my portfolio but I’m having some trouble with the tab portfolio value (quarterly) with the GBP stocks…For some reason, the value is being multiplied by100 and I cannot find where the error is.

As a suggestion, It would be great if you can include a CSV import since the typical brokers…Degiro, IB. I think that there are a lot of people who don’t change their templates just for the huge efforts to move their positions.

Thank you again!!!

Guillermo
Guillermo
Reply to  Guillermo PΓ©rez-Bas Gil
1 month ago

I finally got it!

Diogo
Diogo
2 months ago

I made my own portfolio trackers several times, only to have them remade several times, mostly due to having stocks in both euros and usd. I just couldn’t get the numbers to match lol. To make matters worse, I mix IBKR with DeGiro and on DeGiro, regardless of wether you buy a usd or euro stock, some fees come in euros and some come in usd…

Can’t thank you enough, for this tracker!! πŸ˜‰ Thanks!!

Valesoto
Valesoto
2 months ago

I love your new template! Incredibly useful and well designed! Truly appreciated!

7
0
Would love your thoughts, please comment.x
()
x
%d bloggers like this: