PYAD HYP 2019_04 REINVEST – Year Two - Commentary

[IanTHughes]

[moorfield]

Ian, here's a different view of the diversification (edit: using your Nov 2020 figures) which digs a little deeper than the pie charts and analyses relative weights of each sector to the average. This is more interesting to me and, at a glance, highlights where I might top slice to ratchet up overall income - could some of that overweight PNN holding, for example, find a home yielding > 4.5% ?





(Incidentally, this was done on Google Sheets which imho is much superior to Excel, for many reasons which I won't labour as that's probably O/T here. I'd recommend you try it.)

[Gengulphus]

A curiosity about Ian's 'doughnut' charts above: they indicate that Land Securities and British Land are in two different sectors, confusingly both called "Real Estate IT"... But definitely good-looking charts!

Gengulphus

[Gengulphus]

It is still Lockdown and remains bitterly cold so I have now been reading up on “Doughnut” Pie Charts! Imagine, three concentric rings with the outer ring displaying Holding percentages, the middle ring Business Sector percentages and the inner ring Industry Percentages! Now you cannot do that with a boring Column Chart!

So don't make your column charts boring! ;-)

Out of interest, and also to learn a bit more about what I could do with charts in Excel, I had a go at something roughly along those lines using your November 10th data. I didn't bother about business sectors, since this HYP only has three sector duplicates (so there would be little difference between the 'holding' and 'sector' parts of the chart) and I picked up the "FTSE industry" names from the London Stock Exchange pages on the shares:

Not saying that I know how to produce all aspects of that chart automatically - some bits are handcrafted - but it shows the sort of thing that is possible.

Very good indeed. Was that based on a Stacked Column chart?

Yes, though a lot of tweaking was required from that base!

Gengulphus

[IanTHughes]

A curiosity about Ian's 'doughnut' charts above: they indicate that Land Securities and British Land are in two different sectors, confusingly both called "Real Estate IT"... But definitely good-looking charts!

No, not a curiosity, an error, now corrected!

The problem is that certain elements of these "doughnut" charts have to be maintained manually, or at least I cannot think of a simple way to dynamically create them.

Incidentally, I do think your chart was excellent. It clearly showed what I was attempting to demonstrate with my pie charts, which is a breakdown of 100% Income or Value by Industry and Holding. I cannot immediately see how to create that, would you be able to give me a pointer or two? I do agree with you that for this portfolio Business Sector is basically redundant due to the very few Business Sectors containing more than one holding.

Mind you, what my Income pie chart did show quite clearly was the heavy reliance on income from "Financials", just the kind of information I was trying to tease out.

Thanks for your interest and assistance.


Ian

[GrahamPlatt]

Oh that’s very good Ian, a pie on the table. That is your tablecloth in the background? viewtopic.php?f=15&t=24257&p=380024#p379979

[IanTHughes]

Ian, here's a different view of the diversification (edit: using your Nov 2020 figures) which digs a little deeper than the pie charts and analyses relative weights of each sector to the average. This is more interesting to me and, at a glance, highlights where I might top slice to ratchet up overall income - could some of that overweight PNN holding, for example, find a home yielding > 4.5% ?

(Incidentally, this was done on Google Sheets which imho is much superior to Excel, for many reasons which I won't labour as that's probably O/T here. I'd recommend you try it.)

Thanks for the effort which is appreciated.

However I have to tell you that I am not interested in "switching funds from overweight to underweight", whether at Business Sector level or Holding level. First of all, this is a Reinvestment portfolio. Dividend monies are naturally invested away from low yield, by simply choosing the highest available yield deemed sustainable, and such monies are also steered clear of Sectors with too high a concentration by value - limit is currently set at 9.00%.

Personally, I am not yet convinced that such switching will work better than leaving alone, but in any case this portfolio is being managed as a standard HYP as set out by pyad. To do otherwise - reinvestment a la tjh290633 for example - would be to determine the returns achieved by that strategy, not standard HYP. Not least because that re-investment strategy is already being reported on - by tjh290633!

Anyway, thanks for your interest and thanks for tip about Google Sheets, I shall try it out.


ian

[IanTHughes]

Oh that’s very good Ian, a pie on the table. That is your tablecloth in the background? viewtopic.php?f=15&t=24257&p=380024#p379979

Ha ha!". Such a shame that I could not find the icons for a knife and fork!


Ian

[Gengulphus]

Incidentally, I do think your chart was excellent. It clearly showed what I was attempting to demonstrate with my pie charts, which is a breakdown of 100% Income or Value by Industry and Holding. I cannot immediately see how to create that, would you be able to give me a pointer or two? ...

Basically, I saved the relevant part of the first table in your November 10th post as a text file, then imported that text file into my spreadsheet. After adding Sector, Industry, Sect.Val(%) and Ind.Val(%) columns and doing some sorting to get the companies into the desired order (primarily by industry weighting, secondarily by sector weighting, finally by holding weighting), I then selected the entire table and inserted a "2-D Column" chart of the "100% Stacked Column" type into the spreadsheet. The resulting chart is rather a mess, but select it and click on the 'filter' button to its right, then unselect all but Value(%) in the categories, switch to the Names tab and select the column containing the EPICs in the categories, and click on "Apply". Then click on "Select Data...", followed by "Switch Row/Column" and you get a "breakdown of 100% by holding" chart - which then needs a lot of tweaking to get the chart I came up with!

Gengulphus

[IanTHughes]

I found them most entertaining but a little blurry.

For example this one looks fine here https://i.imgur.com/ZUz8kUp.png but not so great on your post here viewtopic.php?p=379862#p379862.

Being very very slightly picky, a pity the RDSB narrative overlaps BP, as it were, but that's the problem with having two holdings in one sector.
Although BLND/LAND appear fine, probably due to them being at 12 o'clock.

A great innovation all the same.

I am not sure that there is anything I can do about "blurriness".

The JPG file on my PC is perfectly sharp, as is the copy kept on IMGUR, as you yourself have demonstrated. If the post does contain a blurry image, although I cannot see it myself, I guess it must be down to the TLF software.

Of course it could simply be that I was pissed when I loaded the posts - maybe my hands were shaking when working the keys

With regards to the RDSB narrative slightly obscuring that from BP, I have to tell you that is down to the Excel Chart Functionality! You see I can originally set it up perfectly, only for the next time I open it for one or other of the labels to slip a few millimetres. Indeed some labels have slipped to the centre of the Pie Chart! Sometimes data contained within the label simply disappears! I have no idea why this happens but I can assure you that it does If I happen to save as a picture without noticing a label slippage, voila ... one spoiled picture!


Ian

[IanTHughes]

Incidentally, I do think your chart was excellent. It clearly showed what I was attempting to demonstrate with my pie charts, which is a breakdown of 100% Income or Value by Industry and Holding. I cannot immediately see how to create that, would you be able to give me a pointer or two? ...

Basically, I saved the relevant part of the first table in your November 10th post as a text file, then imported that text file into my spreadsheet. After adding Sector, Industry, Sect.Val(%) and Ind.Val(%) columns and doing some sorting to get the companies into the desired order (primarily by industry weighting, secondarily by sector weighting, finally by holding weighting), I then selected the entire table and inserted a "2-D Column" chart of the "100% Stacked Column" type into the spreadsheet. The resulting chart is rather a mess, but select it and click on the 'filter' button to its right, then unselect all but Value(%) in the categories, switch to the Names tab and select the column containing the EPICs in the categories, and click on "Apply". Then click on "Select Data...", followed by "Switch Row/Column" and you get a "breakdown of 100% by holding" chart - which then needs a lot of tweaking to get the chart I came up with!

My goodness, that does sound a bit involved, how did you come up with it?

However, a copy of the above instructions has now been saved to my "To Do" file on my laptop, I shall give it a go in the next couple of days or weeks.

Thank you so much, not only for taking the trouble to generate the report, but also for now explaining in detail how you did it! Much appreciated


Ian

[Gersemi]

I found them most entertaining but a little blurry.

For example this one looks fine here https://i.imgur.com/ZUz8kUp.png but not so great on your post here viewtopic.php?p=379862#p379862.

Being very very slightly picky, a pity the RDSB narrative overlaps BP, as it were, but that's the problem with having two holdings in one sector.
Although BLND/LAND appear fine, probably due to them being at 12 o'clock.

A great innovation all the same.

I am not sure that there is anything I can do about "blurriness".

The JPG file on my PC is perfectly sharp, as is the copy kept on IMGUR, as you yourself have demonstrated. If the post does contain a blurry image, although I cannot see it myself, I guess it must be down to the TLF software.


Ian

Ian's pie charts & text are perfectly clear on my laptop, although the RDSB & BP narratives do overlap.

[PinkDalek]

Ian's pie charts & text are perfectly clear on my laptop, although the RDSB & BP narratives do overlap.

That’s interesting, although it was only the text that looked blurry to me.

I checked now on an iPhone & perfectly clear scrolling in. May look at the PC wide screen again later, it may have been the wine or whatever I use this (that) end.

[Gengulphus]

Incidentally, I do think your chart was excellent. It clearly showed what I was attempting to demonstrate with my pie charts, which is a breakdown of 100% Income or Value by Industry and Holding. I cannot immediately see how to create that, would you be able to give me a pointer or two? ...

Basically, I saved the relevant part of the first table in your November 10th post as a text file, then imported that text file into my spreadsheet. After adding Sector, Industry, Sect.Val(%) and Ind.Val(%) columns and doing some sorting to get the companies into the desired order (primarily by industry weighting, secondarily by sector weighting, finally by holding weighting), I then selected the entire table and inserted a "2-D Column" chart of the "100% Stacked Column" type into the spreadsheet. The resulting chart is rather a mess, but select it and click on the 'filter' button to its right, then unselect all but Value(%) in the categories, switch to the Names tab and select the column containing the EPICs in the categories, and click on "Apply". Then click on "Select Data...", followed by "Switch Row/Column" and you get a "breakdown of 100% by holding" chart - which then needs a lot of tweaking to get the chart I came up with!

My goodness, that does sound a bit involved, how did you come up with it?

Persistent exploration of what I could do - i.e. basically trial and error! And I very nearly missed the final "Switch Row/Column" step because the chart doesn't look very promising until that step 'magically' made it into something that was basically the chart I wanted.

By the way, that's only an account of how I happened to get to the required chart settings. There are probably more efficient ways of getting there - but I haven't done the further exploration that's needed to find them. So if you do find a better way, I'd be interested to hear about it!

Gengulphus

[IanTHughes]

This is a response to the following message, posted on this thread: viewtopic.php?f=15&t=22901

Accumulation Units: Cash Movements

There are two types of Cash Movement event:

- Cash Transferred In - Purchasing Units
- Cash Transferred Out - Selling Units.

For this portfolio there is only one recorded Cash Transferred In event – the initial investment on 18 Mar 2019. Every month, one workday before the 6th of that month, there will be a Cash Transferred Out event the value of which is the Cash Balance recorded for that day.

Dividend Units: Cash Movements

There are three types of Cash Movement event:

- Cash Transferred In - Purchasing Units
- Dividends Received - Purchasing Units
- Cash Transferred Out - Selling Units.

For this portfolio there is only one recorded Cash Transferred In event – the initial investment on 18 Mar 2019. Every month, one workday before the 6th of that month, there will be a Cash Transferred Out event the value of which is the Cash Balance recorded for that day. Dividends Received are recorded as a Cash Movement events on the Payment Date for the Dividend

Hi thanks Ian, this is the best explanation of how to calculate accumulation and dividend units I have seen for a long time.

Can I ask one thing, just to make sure I have understood correctly?

For dividend units, when you receive a dividend payment, you use it to purchase units at the prevailing unit price. However, if you don't actually purchase shares by the 6th of the month, it would still be in cash, and units would be sold at the unit price on the 5th of the month and the cash transferred out. However, if you have bought shares before the 6th, then obviously it would only be any residual cash that is 'sold' and transferred out .

Have I got this right?

PS, I like the graphs and pies!

FD

Thanks

I am pleased that you like my explanation of Accumulation and Dividend Units, although I did rather confuse things when I said:

Every month, one workday before the 6th of that month, there will be a Cash Transferred Out event the value of which is the Cash Balance recorded for that day.

As I stated in my post after yours, this is a portfolio where "All Dividends are Re-Invested". No income is withdrawn from this "virtual" portfolio.

Leaving that aside, and to answer your question directly, yes. One should only create a "Unit Sale" for the amount of cash that is actually transferred out. Any cash re-invested is by definition retained within the portfolio.

However, on further reflection I think I should expand slightly on the unitisation explanation given above. The explanation as to how to treat Dividends Received, both for Accumulation and Dividend (Income) Units, is on the assumption that the account receiving the dividend is "INSIDE" the portfolio. This assumption was implied by my statement regarding the Portfolio Value:

For each day, the Portfolio Value = SUM all Holding Values PLUS the Portfolio Cash Balance PLUS the Ex-Dividend amount.

although I did not explicitly state it.

Personally, I always receive dividends within a Brokerage Cash Account which I always consider to be "INSIDE" the portfolio, so I do sometimes forget that not everyone does the same.

To go further, if one does receive a dividend in an account which is "OUTSIDE" the portfolio, then the treatment of such a dividend event should be as follows:

Dividend Units

No action - the dividend was not received by the portfolio and so there is no cash value with which to "Purchase" additional Units.

Accumulation Units

Cash Withdrawal - the dividend amount was expected to be part of the portfolio but was not received, which is in effect a Cash Withdrawal and a "Sale" of Units.


I am glad that you like the charts! They are now all fully automated, so will be reported from time to time.


Ian

[Alaric]

As I stated in my post after yours, this is a portfolio where "All Dividends are Re-Invested". No income is withdrawn from this "virtual" portfolio.

The performance of Accumulation units is thus marginally influenced by the timing of dividend reinvestment as well as what is chosen.

[funduffer]

Thank you for your response, Ian, and I apologise for putting my question to you on the wrong thread.

FD

[monabri]

Just a comment:

Cost of Investments = £282153

Value of Investments = £266839

Total = £-15313 : ameliorated though by £1912 cash balance and £736 XD accrual.

Reported XIRR is +2.76%

Is this correct that although the cost of investments is £282k and the current value is £266k+"change", we have a positive XIRR value in the reinvestment version ?

[IanTHughes]

Just a comment:

Cost of Investments = £282153

Value of Investments = £266839

Total = £-15313 : ameliorated though by £1912 cash balance and £736 XD accrual.

Reported XIRR is +2.76%

Is this correct that although the cost of investments is £282k and the current value is £266k+"change", we have a positive XIRR value in the reinvestment version ?

At last, we have this comment within the correct thread! Not your fault monabri, I gave the incorrect link. Damn Cut and Paste! Also many, many thanks to csearle for taking the time and trouble to make the correction required as a result of my silly error!

Anyway, to get to your comment about my XIRR calculation ....

XIRR is measuring a return based solely on Cashflows - Cash Going In and Cash Going Out. I think you would agree that "Cost of Investments", "Unrealised Loss", "Cash Balance" and "Ex-Dividend" are not Cashflows - Cash Going In and Cash Going Out.

In fact there is only one actual Cashflow for this "Reinvestment" portfolio, the "Cash Going In" to set it up, which I have logged as £254,904.77 on 18 March 2019. For XIRR purposes we assume a final "Cash Going Out" Cashflow, which is the current value of the portfolio, basically how much would we receive if all investments were sold and the proceeds withdrawn. So we have:

18 Mar 2019 - £254,904.77 - Cash In
05 Apr 2021 - £269,488.45 - Cash Out

You must agree that, whether you use XIRR or another calculation, a profit of £14,583.68 must surely mean a positive Rate of Return over the time period in question


Ian

Moderator Message:
Note: Inaccurate cash out date corrected by moderator.

[dealtn]

05 Apr 2019 - £269,488.45 - Cash Out




Ian

04 Apr 2021 ?

Moderator Message:
This and subsequent posts overtaken by events: original post containing error corrected by moderator.