How to track dividends in Portfolio Slicer

In this article we will use term “dividend” when we will be describing “Dividends” or “Distributions”. Although “Dividends” and “Distributions” could be describing different things, for the purpose of this article we assume that they are the same.

Dividends can have substantial impact to your portfolio performance. This article explains how you can track dividends with Portfolio Slicer.

Dividend Type

With Portfolio Slicer you can track 4 different dividend types:

  • Regular Dividend. This is usually cash payments that you receive into your account. Portfolio Slicer uses transactions types “Div” and “DivTA” to record these dividends.
  • DRIPs (Dividend Reinvestment Plan).  For some symbols you could setup plan where FULL AMOUNT that is received as dividend will be used to purchase additional shares/units for the same symbol. Usually this is possible just for mutual funds because to use full amount received you should be able to buy partial shares. Portfolio Slicer uses transaction type “DRIP’ to record these dividends.
    Note: You can technically setup DRIPs for any symbol. But if your DRIPs can buy just full shares, then in Portfolio Slicer you would record this using 2 transactions: 1st transaction will record regular dividend received and then 2nd transaction will record number of shares bought.
  • Return of Capital Distribution. Some symbols (usually ETFs or Trusts) might classify some of dividends they pay as “Return of Capital”. Usually during the year you will receive regular dividends and then at the start of next year you will receive statement that will say that out of x amounts of dividends paid, y amount is “Return of Capital”. This type of dividend reduces symbol Cost Basis, so when you sell your symbol, capital gain amount will be larger. Portfolio Slicer uses transaction type “ReturnOfCapital” to record these dividends. More info about this distribution here.
  • Notional Distribution also called “Reinvested Distributions”. Some symbols (usually ETFs or Trust) might classify some of dividends they pay as “Notional Distribution” or “Reinvested Distribution”. For this type of distribution you will not receive any money into your account, but you might have to pay income tax (depending on account type) on that distribution amount. This type of distribution increases symbol Cost Basis, so when you sell your symbol capital gain amount will be smaller. Portfolio Slicer uses transaction type “NotionalDistrib” to record these dividends. More information about this distribution here

Note: If you are using Portfolio Slicer to track Cost Basis, then you must always manually enter amounts for “Return of Capital” and “Notional” distributions. Dividends generated from Dividends.csv file does not provide information about different distribution types.

 

Generated Dividends

Portfolio Slicer can generate dividend information for your investment portfolio based on data provided in Dividends.csv file. This file contains dividend payment date, dividend per share and symbol for which dividend is paid. Portfolio slicer can calculate how many shares for specific symbol you had at the date of dividend payment and this way calculate dividend amount received. Here are limitations of such approach:

  • For results to be accurate, dividends file must have accurate information. Date in the file should be Ex-Dividend date.
  • Portfolio Slicer currently does not have “Return of Capital” or “Notional Distribution” dividend amounts. These distributions might or might not be included in generated dividends.
  • Portfolio Slicer currently does not support dividend reinvestment plans (DRIPs)

But if you mostly buy and hold investments for longer periods, then generated dividends can give you fuller picture on how your investments are doing. This feature also works great when you using Portfolio Slicer to analyze how hypothetical portfolios would do over time.

To configure Portfolio Slicer to generate dividends you should do following:

  • Make sure that external file Dividends.csv has information about dividends paid for symbols you require.
  • In Portfolio Slicer Excel workbook go to worksheet “src” and in the table “Account” review column “Calc WHT”. For accounts that you want withholding tax to be deducted, you should set this column value to “Yes”.

To see dividend amounts generated by Portfolio Slicer you should check report in “Div Gen” tab.

Generated Dividend Report

Entering Dividend transactions manually

Entering dividend transactions manually is time consuming process, but it assures that you transaction list matches exactly transactions in your brokerage/bank institution.

You will use mostly 2 transaction types when entering dividend information “Div” and “DivTA”. When you use transaction type “Div”, you will enter number of share you are holding in Qty column and dividend amount per share in Price column. Column “Total Amount” will show you total dividends amount received. You can also enter same information by using transaction type “DivTA, but in such case you can enter into Qty column anything and then enter into Price column you enter total amount received. Again, always check column “Total Amount” that will show you total impact from this transaction.

Dividend Transactions

Please note that dividend amounts are usually entered in currency of account, but you could also use ExchRate column if there is a need to convert value in Price column to the currency of account.

Normally you would create one transaction for each symbol dividends received. So if specific symbol pays dividends monthly, then for that symbol you would have 12 dividend transactions per year. Having accurate dividend transaction data lets Portfolio Slicer calculate more accurate rate of return. But when you setting up Portfolio Slicer and just start to enter historical information, then you should consider just enter one dividend transaction per symbol per year. So if you received 100$ dividend each month, you could just enter one transaction in the middle of the year with amount of 1200$.

Return of Capital Distribution

Some symbols (usually ETFs or Trusts) might classify some of dividends they pay as “Return of Capital”. Usually during the year you will receive regular dividends and then at the start of next year you will receive statement that will say that out of x amounts of dividends paid, y amount is “Return of Capital”. This type of dividend reduces symbol Cost Basis, so when you sell your symbol, capital gain amount will be larger. Portfolio Slicer uses transaction type “ReturnOfCapital” to record these dividends. Because Return of Capital distribution is reported after years end, it is recommended that you do adjustment transactions for the last day of the year when this type of dividend was paid. So let say that symbol XEI.TO in 2014 paid 0.36$ distribution as “Return of Capital”. Then for 2014-12-31 you would create 2 transactions:

  • With first transaction you want to reduce symbols dividend amount received by 0.36$. So this record Trans Type would be “DivTA”, Qty would be 0 (this column is ignored for DivTA) and Price would be “-0.36”  
  • Second transaction would record “Return of Capital” payment. So this record TransType would be “ReturnOfCapital”, Qty would be 0 (this column is ignored) and Price would be “0.36”

With these two transactions you made sure that total amount of dividends received stays the same as before adjustment, but then some of the dividends received were re-classified as “Return of Capital”.

In some cases you would also want to override reporting exchange rate and usually this will be ExchRateRtp1Override (Exchange Rate for your Primary Currency). By default Portfolio Slicer uses transaction days Exchange rate – in our example that would be 2014-12-31 day. But really for tax purposes you would want to use 2014 year average exchange rate. Example: You are Canadian resident and you primary currency is CAD. You have a USD symbol in open account that made “Return of Capital” distribution in 2014. When calculation Cost Basis, you want to make sure that exchange rate for that transaction is 2014 years average exchange rate, so in column ExchRateRtp1Override you will specify average exchange rate from USD to CAD.

Usually your broker/bank will send you investment statement that will specify what symbols paid “Return of Capital” distribution. You can also use internet to look up if specific symbol made such payment. For example for symbol XEI.TO you can visit webpage: http://www.blackrock.com/ca/individual/en/products/239846/ishares-sptsx-equity-income-index-etf where in “Calendar Year” table view you will see “Return of Capital” distribution of 0.0015 per share for year 2014.

Return of Capital Sample

 

Notional Distributions

Notional Distributions are also called “Reinvested Distributions”. Some symbols (usually ETFs or Trust) might classify some of dividends they pay as “Notional Distribution” or “Reinvested Distribution”. For this type of distribution you will not receive any money into your account, but you might have to pay income tax (depending on account type) on that distribution amount. This type of distribution increases symbol Cost Basis, so when you sell your symbol capital gain amount will be smaller. Portfolio Slicer uses transaction type “NotionalDistrib” to record these dividends. It is recommended that if symbol pays Notional Distribution, you enter one transaction at the last day of the year with total amount per symbol received. For example, symbol XEI.TO in 2014 paid 0.74306 per share as notional distribution. So you would enter transaction with Date 2014-12-31, Qty as 0 and Price  as 743.06 if at the end of the year you were holding 1000 shares.

In some cases you would also want to override reporting exchange rate and usually this will be ExchRateRtp1Override (Exchange Rate for your Primary Currency). By default Portfolio Slicer uses transaction days Exchange rate – in our example that would be 2014-12-31 day. But really for tax purposes you would want to use 2014 year average exchange rate. Example: You are Canadian resident and you primary currency is CAD. You have a USD symbol in open account that made Notional Distribution in 2014. When calculation Cost Basis, you want to make sure that exchange rate for that transaction is 2014 years average exchange rate, so in column ExchRateRtp1Override you will specify average exchange rate from USD to CAD.

It is your responsibility to track Notional Distributions. Same bank report these distributions buy depositing money into your account and then right away withdrawing them. Other banks gives you no indication that Notional Distribution was paid. You will have to use web tools to figure out if symbols you hold paid this type of distribution. For example, you can see XEI.TO Notional distribution (Reinvested Distribution) for 2014 on their website http://www.blackrock.com/ca/individual/en/products/239846/ishares-sptsx-equity-income-index-etf. But to see it you would have to go to Distribution view, change View as to “Table” and then choose “Recent” tab.

Notional Distribution Sample

 

Below is a list of transactions that demonstrates how distributions were tracked for XEI.TO symbol in Portfolio Slicer:

 

Dividend Transactions all