Forecast future dividends on shares of Russian companies
[Free Excel Template]

How to forecast future dividends on multiple brokerage accounts at once

I have multiple brokerage accounts. When buying shares, I primarily consider how well the company grows, how often and how much dividends it pays out, and whether the dividends increase from year to year. Currently I have shares of about 35 Russian companies in my portfolio.

I also hold shares of American companies, but the data sources for Russian and foreign companies are fragmented, so in this article I’ll talk only about working with Russian companies. Write in the comments if you’d be interested in a similar article about American companies.

Having worked with VTB and Tinkoff brokers, I didn’t find an easy way to see the overall dividend forecast for the entire portfolio at a one-year horizon. You can only see already approved dividends for a specific company, or dividends within one brokerage account. Unfortunately, there is just no way to see the forecast for the entire portfolio (i.e. for all shares on multiple brokerage accounts) and predict the expected amount of dividends even before they are approved.

Dividend forecasts can be found on the website dohod.ru (a well-known investment resource in Russia). But I didn’t want to calculate manually the dividends on shares of each of the 35 companies. Excel and its built-in Power Query functionality came to the rescue.

So I created a helpful Excel template, which is quite easy to use and it can quickly get you the result you need.

Overview of the dividend forecast template in Excel

This template for forecasting the upcoming (within one year) dividends on shares of Russian companies will be useful to those investors who are focused on the dividend strategy and long-term investments.

Information from the dohod.ru website is used as a source of forecasts.

I want to point out that the template shows the FORECAST and it doesn’t guarantee that dividends will be paid out at all, or will be paid out in the exact amount that was predicted. Nevertheless, this information will be useful when planning investments for up to a year. Learn more about the forecast calculation method.

Step 1. Download the dividend forecast template

The template is free to use, so go ahead and click the link below to download it. Don’t forget to save the file on your computer, so that you can repeat all the steps further in this article yourself.

Download the Dividend Forecast Excel Template

The document contains four worksheets:

• Portfolio
• Dividend Forecast
• Russian Companies
• About Template

Step 2. Fill in the data on the Portfolio worksheet

1. Open the Portfolio sheet Stretch the table down for as many companies as you need.
2. Select the companies you want from the list Set the total number of shares for each company on all your accounts.

You don’t need to edit the value in the Ticker field, it is automatically pulled from the Russian Companies worksheet.

Step 3. Update the Dividend Forecast worksheet

1. After filling in the portfolio data, go to the Dividend Forecast sheet.
2. On Excel ribbon, open the Data tab Click Refresh All.

In the background and in just a few seconds Excel will load the forecast for your portfolio and will update the data in the “DividendForecast” table:

The long-term goal of my investments is to reach a certain level of passive income (from dividends) that would suit me. This template allows me to predict the amount of dividends for the year ahead and use the forecast to rebalance my portfolio and make new purchases.

Do you make dividend forecasts for your portfolio? If you do, what’s your approach? Write in the comments!