The good, bad and the Uggerly

[Arborbridge]

Below, warts and all, is my most recent check on XIRR for my HYP shares. The current XIRR is given in the RH column, and the first three columns show how the ranking in the list has varied from 2015. Two shares, Schroders and Pennon, have not been included because their purchase date within a year ago, gives a misleading XIRR. Note how some companies fall from grace, then return eventually, some have a wobble then come back, others never have much grace! Median XIRR is 9.9%.



PS if anyone knows how to make the columns narrower, I'd be grateful for the advice.

Arb.

[Dod1010]

Well done Arb. Even although we are not supposed to worry about XIRR around here it is a fascinating table; so much so that I must print it off and give it some thought.

No idea how to construct your table never mind make the columns narrower!

Dod

[Arborbridge]

Dod,

One could use this to prune out the obvious big losers, but I just wonder if in ten years' time, I would find the ones near the top became the "new" losers.

If after five years or more, those near the bottom are boringly settled in their ways and stay there. it would be a good thing to prune, perhaps. I'm in no hurry to rush into conclusions.

[Raptor]

Moderator Message:

Well done Arb. Even although we are not supposed to worry about XIRR around here it is a fascinating table; so much so that I must print it off and give it some thought.

No idea how to construct your table never mind make the columns narrower!

Dod

Total returns is a nice to have but as Dod says not the real object of hyp practical. Nice to see the ups and downs though.

Raptor.

[Arborbridge]

Raptor,
As I'm sure you appreciate, I'm a dedicated HYPer and had no desire to hijack any of its principles. XIRR is an interesting way of checking one's shares+plus buying efficiency - it's another layer of the onion but to be looked at within the total context of HYP. Had I written this section about XIRR at the end of a long report on my HYP it would have raised no eyebrows but would have conveyed the same facts (I could always add a few charts of income as well, as a counterbalance!)

Arb.

[tjh290633]

Interesting, Arb. In the intersts of keeping on topic, here is my alphabetical list of holdings, with the original yield at purchase and the subsequent XIRR for both the holding as tinkered and the single original share:

.                                .   Tinkered    1 share                        
EPIC   Share                            IRR         IRR       Since       Start yld
ADM    Admiral Group plc                   16.56%    15.94%   05-Mar-14       6.63%
AV.    Aviva plc                           10.21%     9.22%   26-Oct-10       6.53%
AZN    AstraZeneca plc                     15.97%     9.12%   01-Jun-93       4.37%
BA.    BAe Systems plc                     12.70%     6.17%   26-Nov-99       2.13%
BATS   British American Tobacco plc        18.09%    18.09%   19-Feb-10       4.76%
BLND   British Land plc                     3.53%    10.06%   01-Oct-10       5.50%
BLT    BHP Billiton plc                    -2.13%    -1.69%   19-Feb-10       2.86%
BP.    BP plc                              12.79%    11.00%   09-Nov-79       5.03%
BT.A   BT Group plc                        12.87%    20.07%   28-Nov-84       5.31%
CLLN   Carillion plc                      -18.81%   -23.10%   27-Apr-16       6.25%
CPG    Compass Group plc                   14.48%     9.90%   31-Jan-01       1.18%
DGE    Diageo plc                          17.06%    17.06%   06-May-09       4.37%
GSK    GlaxoSmithKline plc                  0.08%     9.92%   28-Sep-10       5.34%
IMI    IMI plc                             51.27%    16.29%   30-Mar-09       7.82%
IMB    Imperial Brands plc                 22.38%    10.09%   01-Oct-96       5.71%
INDV   Indivior plc                        24.87%    70.71%   23-Dec-14       6.63%
KGF    Kingfisher plc                       8.19%     9.28%   04-Sep-07       3.47%
LGEN   Legal & General Group plc           25.21%    25.98%   14-Jun-16       6.72%
LLOY   Lloyds Banking Group plc            18.68%    10.57%   22-Dec-88       5.50%
MARS   Marstons plc                        12.13%    10.70%   17-Feb-11       5.72%
MKS    Marks & Spencer plc                 11.18%    11.67%   09-Feb-70       2.06%
NG.    National Grid Transco plc           15.41%    11.37%   20-Oct-00       4.73%
PSON   Pearson plc                          1.05%     3.60%   22-Oct-09       4.36%
RB.    Reckitt Benckiser Group plc         18.99%    18.91%   21-Mar-11       4.08%
RDSB   Royal Dutch Shell plc B              7.90%     6.87%   07-Jun-06       3.77%
RIO    Rio Tinto plc                       56.87%    47.93%   27-Apr-16       6.08%
S32    South32 Ltd                         23.97%    15.79%   18-May-15       3.19%
SGRO   Segro plc                            5.52%     1.99%   04-Sep-07       4.23%
SMDS   DS Smith plc                        15.58%     9.76%   08-Feb-07       4.09%
SSE    Scottish & Southern Energy plc      11.79%    11.66%   28-Sep-10       6.69%
TATE   Tate & Lyle plc                     14.02%     3.94%   21-Jul-99       5.46%
TSCO   Tesco plc                            8.84%     6.97%   19-Jun-97       3.08%
TW     Taylor Wimpey plc                    6.31%    -3.21%   30-Aug-05       4.26%
ULVR   Unilever plc                        14.69%    14.69%   19-Feb-10       3.72%
UU.    United Utilities Group plc          11.01%     9.84%   09-Aug-01       7.12%
VOD    Vodafone Group plc                  12.75%    11.68%   16-Aug-06       6.09%
WMH    William Hill plc                    10.40%     0.66%   05-Mar-08       6.20%

I have included the date first bought, which is, of course, highly relevant. Hence the highest value of XIRR is from a recent purchase, RIO, and reflects the change in price over the period and also a profit taking move when trimmed because overweight in November 2016.

In case there are eyebrows raised at the yield when first bought of some shares, I must point out that MKS was inherited from my mother in 1970, BA. resulted from the GEC/Marconi reorganisation in 1999, and BLT was effectively bought at a lower price because I ditched AAL when they stopped paying dividends in 2010 before I had received any, yet rose in price. The effective yield of BLT is therefore 4.5% on the original outlay on AAL, which I thought to be a bargain. Compass arose from a holding in Forte, later taken over by Granada and then merged with Compass to form Granada Compass, only to demerge in 2001 into Compass at 482p and the TV side of Granada, which became ITV. It was in a Single Company PEP, later amalgamated into my main PEP in 2005, by when CPG was yielding about 4.5%.

Fun might be had by comparing the XIRR figures with the original yield and the years held so far, but I leave that to anyone who thinks they can see a point.

TJH

[Arborbridge]

I have included the date first bought, which is, of course, highly relevant.

Yes, that is always an Achilles' heel when it comes to comparisons. I could list my dates bought, which would give the general picture, but the issue is more complicated when there are multiple purchases and even some sales.

XIRR is interesting, but only gives a glimpse into one's own success at picking plus timing.

I ignore shares for this purchase until I can see that the XIRR has "settled down" after purchase - usually after a year or two.
Before that, as we well know, the results are completely misleading.


Arb.

[monabri]

Just to clarify, do the XIRR values include dividends paid or is it simply current price versus purchase price?

[Wasron]

Arb,

The most interesting company on your list for me is IGG. They were clearly performing well until they had a major hiccup in 2016.

Looks like they've recovered a little but still yield over 5%. Do you know if their issues were short-lived, or should they be viewed with extreme caution?

My Sainsbury's holding is back in profit after a dodgy couple of years, so I'm wondering whether now is the time to drop the cutter.

Regards,

WasRon

[Minesadouble]

I hold IGG, the shares took a dip when the FCA made a ruling on spread betting CFDs. From memory the stock price plunged around 40 points.
Shame they felt the need to intervene.

[Arborbridge]

Monabri:
XIRR figures are total return and include the effect of dividends paid and the "time value" of each. As it takes time into account, the result is dependant on when you buy or sell, or receive dividends as well as the market price on the date you work out the return.

Wasron:

Do you know if their issues were short-lived, or should they be viewed with extreme caution?

I don't know about extreme caution, no I'd say just normal caution!
The changes by the FCA had (if hazy memory serves) an on-going and permanent effect, but I would guess IGG has learned to live with it. It has certainly knocked my overall return, but that return is still very acceptable. But then, that can apply to several companies, until it isn't! Equity is a risk game, inevitaby.


Arb.

[Gengulphus]

PS if anyone knows how to make the columns narrower, I'd be grateful for the advice.

Put in more columns! ;-)

As already mentioned, the date first purchased would be useful in evaluating how well the XIRR() value is likely to have settled down. You mention having excluded shares first bought less than a year ago because their XIRR() values are essentially totally unreliable, which I thoroughly agree with. But actually XIRR() values starting from a few years ago are still very unreliable... To get an idea, take a look at the red line in:

[Image held on hosting site at http://i.imgur.com/0cZ3sjf.png and produced/owned by me.]

As can be seen, the XIRR() value varied between about 0% and over 15% in year 2, between about 5% and over 15% in year 3, and so on. It's still varying between about 3% and about 8% in year 8. The average length of time shares have been held in the portfolio is only about half the time the portfolio has been in existence, so the year 8 variation is about what one might expect of a portfolio that had been held for 4 years. Equally, I would expect the XIRR() values of individual shareholdings to vary more than that of a portfolio of the shares - basically, diversification reduces the variability of such measures. So I would expect individual shareholdings held for less than about 5 years to still have pretty unreliable XIRR() values, and even above that to only have gradually increasing reliability and to be capable of changing by quite large amounts (e.g. I'm pretty certain that Lloyds in HYP1 will have exhibited a positive XIRR() value roughly equalling its average dividend yield for its first 7 years or so, then to have turned strongly negative in the next couple of years and remained negative ever since).

A related value that would be interesting is the average holding period of the shares in the holding - the date of the first purchase can be rather misleading, especially if only a small holding was purchased initially and the bulk of the holding was purchased much more recently. That's rather more work and it's not totally clear what it should be if there have been partial sales since the holding of the share concerned was last at zero - it depends on which shares one regards the partial sale as having sold. But a reasonable way of calculating a value is:

1. Find the last date that the holding of the share concerned was zero, and start the calculation with SHARES = 0, SHARE_DAYS = 0, LAST_DATE = the date found.
2. Sort the trades since that date into date order. If by any chance you have multiple trades on the same day, treat them as a single buy of the net increase they produce to the number of shares or as a single sale of the net decrease they produce to the number of shares, depending on what net change they produce.
3. Go through that list in earliest-to-latest date order. As you encounter each trade, add ((date of trade) - LAST_DATE) * SHARES to SHARE_DAYS, then set LAST_DATE to (date of trade), then:
   • If the trade is a buy, add (shares bought) to SHARES.
   • If the trade is a sale, multiply SHARE_DAYS by (SHARES - (shares sold))/SHARES and then subtract (shares sold) from SHARES.
4. After dealing with the last trade, add ((current date) - LAST_DATE) * SHARES to SHARE_DAYS.
5. Calculate the average holding period as SHARE_DAYS/SHARES. That gives it in days - divide further by 365, 365.25 or 365.2425 (depending how pedantic you're feeling!) if you want it in years.

That basically assumes that partial sales sell shares bought on the same day (should be a very rare event for HYPs!) as far as possible and otherwise sells them proportionately from what's left of all previous buys. Note by the way that step 1 is basically just a labour-saving device - you can start the calculation at the very beginning of your history of holding the share and rely on step 3 resetting SHARES and SHARE_DAYS to 0 and LAST_DATE to the relevant date any time it encounters a complete sale. And it may well be easier to do it that way in a spreadsheet - what's saves labour in a calculation done by calculator and in one done by spreadsheet can differ!

One other thing that might be doable to get narrower columns is to insert blank columns to the right. Obviously if that works, it just shifts the blank space around, from being between the columns to being on the right of all of them. But it might help make the table more readable by bringing related values closer together. It will need some trial-and-error to find out how viable an approach it is, including investigating whether the blank columns can be done with ordinary spaces or require non-breaking spaces - I do know that another problem I've dealt with in the past required non-breaking spaces (that was the opposite problem of preventing columns from running into each other and instead break into multiple lines as necessary (and if possible) when you have a lot of columns - solved by inserting an extra column of non-breaking spaces between the two columns that are running into each other).

Gengulphus

[Gengulphus]

Having done the experimenting I mentioned, inserting extra blank columns to the right does squeeze the other columns closer together, but you need to make the extra columns quite wide to have a noticeable effect, and you need to use non-breaking spaces rather than ordinary spaces to give the extra columns any width at all (so far as I can tell). To get non-breaking spaces in Windows, use the "Character Map" accessory - it's the apparent space character 5 positions after lower-case 'z'. (For Macs, Linux, etc, I'm afraid you're on your own!)

Only doing that runs into a problem on narrow screens/windows, that the existing columns run into each other. Solving that involves inserting further blank columns of non-breaking spaces between the existing columns, but making them only a few non-breaking spaces wide.

As an example, here's Arb's table with an extra column added to the right, of width 60 non-breaking spaces, plus one between each pair of existing columns, each 2 non-breaking spaces wide.


I should add that I do not expect anyone to do any of this - this is purely information for people who happen to feel perfectionist about such matters! I'm probably towards the upper end of that 'perfectionist' scale, but even I am only likely to use this in work I'm going to re-use many times and that is liable to run into table display problems, such as my standard spreadsheet-generated GDHYP tables...

Gengulphus

[tjh290633]

Just to clarify, do the XIRR values include dividends paid or is it simply current price versus purchase price?

They are based on the cash flow for the particular holding, including all purchases as negative figures, dividends and disposals as positive figures and the current holding value as a positive figure.

TJH

[Arborbridge]

it's the apparent space character 5 positions after lower-case 'z'.

How did you hit upon this? - or was it just experience from something previously.

Do you copy and paste them into the lemon formatting program, or into the spreadsheet?

[Raptor]

The changes by the FCA had (if hazy memory serves) an on-going and permanent effect, but I would guess IGG has learned to live with it. It has certainly knocked my overall return, but that return is still very acceptable. But then, that can apply to several companies, until it isn't! Equity is a risk game, inevitaby.


Arb.

I had held off topping up IGG after the FCA announcement, but IGG seem to have taken it in their stride. Small top-up today at 567.2.

Raptor.

[Gengulphus]

it's the apparent space character 5 positions after lower-case 'z'.

How did you hit upon this? - or was it just experience from something previously.

Experience from my career in computing, which was very much to do with how computers represent various things as strings of bits. One of those things is characters - there are various standards that say which string of bits represents which character, and of course that includes which characters the standard can represent. Can't say I've still got all the detail in my head, but I do generally remember what's there and where to look it up. That includes non-breaking spaces being there and them generally being just like ordinary spaces except for not being treated specially for purposes such as breaking text up into lines. So when I discovered last December that TLF's table facility was sometimes treating spaces specially in ways I didn't want, just knowing that non-breaking spaces exist at all was 90% of the solution! Can't say I remembered at the time where to find them in 'Character Map', but I knew they were somewhere and could be found if I looked... Took me ten minutes or so to find them that first time, but I've used them enough since for them to be back in my 'working knowledge'.

Do you copy and paste them into the lemon formatting program, or into the spreadsheet?

I've done it two ways.

For that last post of mine, I started by pressing the "quote" button for your OP of this thread, then copy-and-pasted the contents of the TLF editing window into Notepad, did the edits I wanted and copy-and-pasted it back to preview and submit the post. The non-breaking spaces went in during the editing - the key step was globally replacing "||" (the column separator in the table source) by "|| ||" (with the two spaces in the middle being non-breaking ones) to insert the extra columns that stop existing columns running into each other if the window / screen is too narrow. I'm fairly certain the same thing could be done directly in the TLF editing window in principle, but without a 'global replace' facility it would have required 144 separate edits (4 inter-column separators on each of 36 table rows), so using Notepad made a big difference!

For my GDHYP tables, I insert the non-breaking spaces in the spreadsheet, but not as extra spreadsheet columns. Instead, I do it by having an extra worksheet for each type of table I've designed. That basically has a column of the EPICs of the holdings I want in the table, in the order I want them, plus a column for each other column I want in the table obtained by looking up the data item by EPIC in the main part of the spreadsheet and using the TEXT() function on it where appropriate to format it as text in the precise format I want (e.g. date, currency, simple number, etc). Then a final 'gather each row together' column concatenates all the entries for each row together, with suitable constant text strings between them to do things like left/right/centre-aligning them, putting the "L="s at the start of the row, etc - this is where the non-breaking spaces for the extra blank columns come in. Finally, a 'gather it all together' cell concatenates all the entries of the 'gather each row together' column together, with table tags before and after, so that that cell contains a single, very long string that I can simply cut-and-paste into the TLF editing window. The net result is that to get the table in a TLF post, I just have to make certain the main part of the spreadsheet is up-to-date on its raw data (share counts, prices, forecast dividends and the portfolio's cash balance), then cut-and-paste that cell into my post.

It should also be possible by inserting extra spreadsheet columns whose text contents are the desired non-breaking spaces, then using the TLF "export spreadsheet to table" tool, but I haven't tried it that way. That's mainly for historical reasons - my spreadsheet was already set up to produce 'preformatted' text ready for cut-and-pasting into a TMF post (which involved rather more complex formulae using LEN() to work out how many spaces to pad each textual entry by, REPT() to construct a string of that many spaces, and string concatenation to pad it on the left if I wanted a right-aligned column or on the right if I wanted a left-aligned column). As a result, it was simplest and quickest to just go with the flow of the existing spreadsheet, only changing the detailed construction of the text to be cut-and-pasted into the post, rather than changing how it was intended to be used.

Gengulphus

[feinmann]

Of relevance to IGG I would have thought: https://www.fca.org.uk/publications/mul ... ssessments