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

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

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

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

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.

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.

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.

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.

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).

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.

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.

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

Yours Truly,

European Dividend Growth Investor