Canadian Money Forum banner
41 - 60 of 82 Posts

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #41 · (Edited)
Eclectic12 - you're right, I can find all the information (both ROC and reinvested distributions) on the BMO web pages for each ETF. And that's fine ... that source will be OK for my purpose. We very rarely buy or sell units, so the single ROC numbers for the year are sufficient and will not cause a problem; monthly breakdown of ROC is not an issue.

It works for me on 2 computers, both Win10, one Excel 2010, the other Excel 2013.
Here are the steps I'm trying for the CDS Innovations sheet. I started from the BMO ETF dashboard, and follows this CDS Innovations link. From here I downloaded "BMO DISCOUNT BOND INDEX ETF" by clicking on the Excel icon. I saved the XLS file.

I'm trying this with Excel 2011. When I open the XLS file, I see a warning (like yours) and have choices: Enable Macros, Do Not Open, or Disable Macros. I chose Enable Macros.

At this point, I see an error indicating that the scripts contained within the spreadsheet have a bug. I've attached a screen shot of this warning (Compile error). After this error, I see an empty spreadsheet.

Text Font Line
 

·
Registered
Joined
·
1,236 Posts
I'll have to search the thread to be sure of my memory that the CDS Innovations sheets use %.

When I checked 2015 ZLB distribution charge on BMO's web site against yahoo's historical dividends, the dates & $ matched. It seems clear BMO's web site is reporting $$, just as iShares and from what I recall, Vanguard does.


Cheers

PS

The OP in post # 5 says the CDS Innovations is by %.
Yes, to clarify my previous comment in post #37, all the ETF provider tax documents I have seen publish distribution details in $, and all the CDS spreadsheets I have seen except for BMO publish distribution details in $. It is only the BMO spreadsheets on CDS that publish distribution details in %. In the CDS spreadsheet this is indicated in cell G15 that the calculation method is either rate or %.

To get distribution details in $ I think you would have to multiply the % values by the total distribution $ per unit in line 19.
 

·
Registered
Joined
·
4,164 Posts
I'm trying this with Excel 2011. When I open the XLS file, I see a warning (like yours) and have choices: Enable Macros, Do Not Open, or Disable Macros. I chose Enable Macros.

At this point, I see an error indicating that the scripts contained within the spreadsheet have a bug. I've attached a screen shot of this warning (Compile error). After this error, I see an empty spreadsheet.
I tried with Excel 2013 and it worked fine. Maybe it's not compatible with Excel 2011? Seems crazy.

Text Green Blue Line Font
 

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #44 ·
I wonder if it's because I'm running a Mac version. Either way, this doesn't give me confidence in their spreadsheet format. It shouldn't have any macros to begin with ... the CDS Innovations documents do not bode well for longevity of data storage. Simple spreadsheets like CSVs or platform agnostic formats like PDF must be used for long term data retention, and for estate planning, we should all keep that in mind.
 

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #46 · (Edited)
Check your eServices, folks. I now have a T3 for my BMO ETFs - potentially of interest to @GreatLaker @Eclectic12 @AltaRed

This also confused me about something. The T3 details document shows $5.42 ROC on my ZSP position. Therefore, in my own position tracking spreadsheet, I have subtracted 5.42 from my ACB. If there was a reinvested distribution, I would fix that up too, but ZSP had none last year.

However, within TDDI's Holdings, I don't see this adjustment (and they usually do show the correct ACB). Instead I see the original book cost before any distributions happened. Does this mean that TDDI will apply these adjustments after some time delay?
 

·
Registered
Joined
·
2,998 Posts
Check your eServices, folks. I now have a T3 for my BMO ETFs - potentially of interest to @GreatLaker @Eclectic12 @AltaRed

This also confused me about something. The T3 details document shows $5.42 ROC on my ZSP position. Therefore, in my own position tracking spreadsheet, I have subtracted 5.42 from my ACB. If there was a reinvested distribution, I would fix that up too, but ZSP had none last year.

However, within TDDI's Holdings, I don't see this adjustment (and they usually do show the correct ACB). Instead I see the original book cost before any distributions happened. Does this mean that TDDI will apply these adjustments after some time delay?
I do recall TDDI having a significant delay on a Vanguard distribution. Check out the trade date and settlement date.
Font Art Circle Brand Logo
 

·
Registered
Joined
·
1,236 Posts
I do recall TDDI having a significant delay on a Vanguard distribution. Check out the trade date and settlement date.
View attachment 21487
That's the way ETF reinvested distributions always appear in TDDI. The ETF provider waits until the end of the year to determine the overall capital gains distribution for the year, rather than pay them out throughout the year. That way if there are capital gains early in the year and capital losses later in the year they can use the later capital losses against the earlier capital gains rather than paying them out. They need all the ETF's holdings to report their financials by the Feb 28 deadline, which they then factor into the ETF's financials. That's why the deadline for issuing T3s is Mar 31, not Feb 28.
 

·
Registered
Joined
·
1,236 Posts
Check your eServices, folks. I now have a T3 for my BMO ETFs - potentially of interest to @GreatLaker @Eclectic12 @AltaRed

This also confused me about something. The T3 details document shows $5.42 ROC on my ZSP position. Therefore, in my own position tracking spreadsheet, I have subtracted 5.42 from my ACB. If there was a reinvested distribution, I would fix that up too, but ZSP had none last year.

However, within TDDI's Holdings, I don't see this adjustment (and they usually do show the correct ACB). Instead I see the original book cost before any distributions happened. Does this mean that TDDI will apply these adjustments after some time delay?
Yes, I have noticed that in the past. The cost base adjustment for the RoC will appear later. It should true up in a month.
 

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #51 ·
I've struggled a bit with this ACB tracking, so I'd like to double check to see if this is how other people are doing it these days. There are two adjustments required to a position's cost base: the reinvested distribution (+) and the ROC (-).

It seems that using the iShares and BMO web sites, the reinvested distribution is straightforward. It shows up in the distribution history on the web. And the ROC can be found on the T3 and its detail summary (as sent by TD), which automatically has calculated how much ROC applies to my share holdings throughout the year.

So that's all that's needed to keep my ACB up to date, right?
 

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #52 ·
I do recall TDDI having a significant delay on a Vanguard distribution. Check out the trade date and settlement date.
Something to be careful about is that a "capital gain distribution" is not the same as the "reinvested distribution". You would not want to update your ACB with that figure you showed. To keep your ACB up to date, make sure you check the ETF's reinvested distribution.

To see how they're different, look at iShares XSP. The web site shows the 2019 "Capital Gains" distribution was 0.32882. However, in the iShares tax distribution document, you will see that the "Reinvested distribution per unit" was actually 0.30432 which is somewhat different.

This is why I find this stuff complicated. You have to make sure that you find figures for the Reinvested Distribution. For BMO ETFs, this can be found through the distribution info on the web site. For iShares however, the distributions part of the web site doesn't show it. Instead you have to download the special PDF file they publish on tax characteristics each year.
 

·
Registered
Joined
·
666 Posts
So that's all that's needed to keep my ACB up to date, right?
Yes, reinvested distributions and return of capital are all you need. But indeed, finding the information is a major PITA each year. I wish there was an equivalent to box 42 for reinvested distributions. Of course, the government only cares about your ACB going down so that's not likely to happen any time soon.
 

·
Registered
Joined
·
1,236 Posts
I'm certain the amount in the example in post #47 is Reinvested Capital Gains. Regular distributions will be a one-sided transaction... cash deposited into the account. Even with a broker (synthetic) drip the dripped amount will be full shares, so most likely some cash left over. Reinvested distributions always have two transactions with the exact same dollar value, one positive for the distribution, and the other negative for the reinvestment. Reinvested distributions are posted to TDDI accounts some time in March but back-dated to a payment date of early January (which would actually have a record date of December).

VBAL had a reinvested capital gains distribution of $0.114319 per unit, multiplied by 3280 units = $374.97, the same as the total shown in the example.

I use TDDI. For RoC I use the actual values from box 42 on the Summary of Trust Income, which is the detailed breakdown of all the boxes on the T3. For Reinvested Distributions I use the amounts from the Reinvested Distribution transactions on my March statement. I double check against either the ETF provider's website or against the CDS tax breakdown spreadsheets, but have never found a discrepancy.
 

·
Registered
Joined
·
685 Posts
We also use the "Summary of Trust Income" which we receive for each fixed income ETF in each account. It shows the breakdown of each monthly distribution into the three buckets - ie an amount for each of box 25,26,42 for every distribution.
 

·
Registered
Joined
·
24,065 Posts
Discussion Starter · #57 ·
When I have sold MFs, I have used the book value that TD records for it. With all the constant drips of numerous random types of distributions going on, I do not feel I have any reasonable means to calculate anything more accurate.
I flip flop between thinking I should calculate it all (as I wrote above) and just using TD's book value. So far, the book values I've seen appear to be perfectly good.

Until now I have gotten away with keeping all my ETFs inside registered accounts, but now that I'm finally holding some non reg, I'm still confused about the whole thing. Capital gains distributions are not the same as reinvested distributions; the reinvested distributions are often hard to find; and ROC is paid along with each quarterly distribution so if you've placed trades in the year, everything gets more complicated. The T3 helps, but overall, I find the whole process very confusing.
 

·
Registered
Joined
·
11,215 Posts
I've struggled a bit with this ACB tracking, so I'd like to double check to see if this is how other people are doing it these days. There are two adjustments required to a position's cost base: the reinvested distribution (+) and the ROC (-). .... So that's all that's needed to keep my ACB up to date, right?
Assuming you didn't buy or sell anything during the year ... sure reinvested distribution and RoC is all you need. :)

Cheers
 

·
Registered
Joined
·
2,169 Posts
And god help you if you DRIP :)
Yes. Somewhere in this thread I diverged out into Mutual Fund territory. I hold some fractional unit mutual fund from Scotia (BNS385) in my TDDI taxable account. It's a sizable amount, so the distribution amounts are significant. I've held it a long time, so the effects of ROC and other exotic distributions have had a long time to build up to relevant magnitudes. These things only drip, and result in a constant stream of new fractional units. Without Scotia and/or TDDI keeping track, I don't think I have any chance on my own to work out the true ACB.
 
41 - 60 of 82 Posts
Top