Unitisation : How to Use Benchmark Indices

[JohnnyCyclops]

Back for one of my periodic lessons in unitisation. Previously done over on TMF. All help welcome.

We unitise the HYP on both an Accumulation and Income basis. ACC units are created when new monies are added to the HYP (and units would be cancelled if we ever pulled money out of the HYP). INC units are created when stocks are bought in the HYP (either from new monies or from dividend reinvestment). INC units are cancelled if a stock is sold.

We've been HYP building for six years, adding broadly the same amount of new monies each year, trying to space purchases during each year, but sometimes it's been a little 'lumpy'. Hence unitisation.

As a benchmark we've used the FTSE100 Total Returns Index for the ACC Units and used the FTSE100 Index for the INC Units. So far so good. When I say "used", I mean we captured those index values at HYP start back on 3rd and 6th April 2011 (3rd for FTSE100TR, for when we added cash to the ISA, 6th for FTSE100 for when the first HYP stock purchase was made with it). We baselined both indices to 100.00 in April 2011 and have measured from there. The FTSE100TR was at 150.67 on 22 Jan when we last added new monies. Our HYP ACC Unit value (also baselined 100.00 on 3rd April 2011), is now at 124.11. The gap is 26.56 or 17.6%.

Initially the gap wasn't that wide, and in some years narrowed, but after six years the gap just keeps widening and I don't think the HYP is underperforming the broader market by that much (although I might be wrong!). In the INC Units the gap is even more pronounced (FTSE100 121.83, INC Units 97.40, gap 24.42, 20.00%).

QUESTION
Is it valid to compare the two (HYP and Index) like this? I think I should be doing an additional step of unitising both indices as well, i.e. as new monies are added to ACC Units I should be adding that value to the theoretical index measure to show the comparable effect in the indices of purchases through time. Otherwise, on the indices I'm just doing a startpoint/endpoint measure, which would be fine for something like Pyad's HYP1 "buy in one go".

[monabri]

Maybe other Lemons will chip in here but is there any point in comparing the performance of your portfolio with an index such as the FTSE100 which is dominated by the likes of HSBC/GSK/BP?

For example, if you were to take each stock in your portfolio and then compare it with the FTSE100 is there any correlation (use HYPTUSS to plot them - it only takes a few minutes to do for ALL the shares in your 30-ish stock portfolio (I've seen your previous posts)).

Why not use the XIRR function in Excel. Simply list out all the dates when you've added new money (not reinvested dividend which have bought new shares) in column A. In column B, enter the sums of money I've invested as negative numbers and finally, todays date and portfolio value.

Here's one I've just made up. The dividends that buy new shares are not treated as new money and merely increase the "current value today" figure which is entered as a positive number.

You can then take "snapshots" of your portfolio as often as you require. Just type in the current date and the current portfolio value.


ALL VALUES ABOVE ARE MADE UP!

[JohnnyCyclops]

Maybe other Lemons will chip in here but is there any point in comparing the performance of your portfolio with an index such as the FTSE100 which is dominated by the likes of HSBC/GSK/BP?

I'm sorry, but that's a total side-issue and I'd prefer my thread not be hijacked please. My HYP, my choice of index. The point being it doesn't matter what index I'm benchmarking it with (The Timbuktu Cocoa Futures Market Index!). It's the principle of comparing a HYP to an Index that's important.

I do thank you for the time taken to write the rest of your post, but it's OT for me. I know how to XIRR, thank you. That wasn't my question (but has been in the past).

[monabri]

sorry - I genuinely didn;t mean to hijack your post nor teach you "how to suck lemons"

[JohnnyCyclops]

sorry - I genuinely didn;t mean to hijack your post nor teach you "how to suck lemons"

Lemons always welcome here, and others may benefit from the clear XIRR guide you provided. Many thanks.

[tjh290633]

Back for one of my periodic lessons in unitisation. Previously done over on TMF. All help welcome.

We unitise the HYP on both an Accumulation and Income basis. ACC units are created when new monies are added to the HYP (and units would be cancelled if we ever pulled money out of the HYP). INC units are created when stocks are bought in the HYP (either from new monies or from dividend reinvestment). INC units are cancelled if a stock is sold.

We've been HYP building for six years, adding broadly the same amount of new monies each year, trying to space purchases during each year, but sometimes it's been a little 'lumpy'. Hence unitisation.

As a benchmark we've used the FTSE100 Total Returns Index for the ACC Units and used the FTSE100 Index for the INC Units. So far so good. When I say "used", I mean we captured those index values at HYP start back on 3rd and 6th April 2011 (3rd for FTSE100TR, for when we added cash to the ISA, 6th for FTSE100 for when the first HYP stock purchase was made with it). We baselined both indices to 100.00 in April 2011 and have measured from there. The FTSE100TR was at 150.67 on 22 Jan when we last added new monies. Our HYP ACC Unit value (also baselined 100.00 on 3rd April 2011), is now at 124.11. The gap is 26.56 or 17.6%.

Initially the gap wasn't that wide, and in some years narrowed, but after six years the gap just keeps widening and I don't think the HYP is underperforming the broader market by that much (although I might be wrong!). In the INC Units the gap is even more pronounced (FTSE100 121.83, INC Units 97.40, gap 24.42, 20.00%).

QUESTION
Is it valid to compare the two (HYP and Index) like this? I think I should be doing an additional step of unitising both indices as well, i.e. as new monies are added to ACC Units I should be adding that value to the theoretical index measure to show the comparable effect in the indices of purchases through time. Otherwise, on the indices I'm just doing a startpoint/endpoint measure, which would be fine for something like Pyad's HYP1 "buy in one go".

Yes, your approach is valid. No you shouldn't be unitising the index, as it already unitised.

I've compared your results with mine, using 31 Mar 2011 as the start point.

For the income units I get:

.                FTSE      Inc Unit
31-Mar-11    -5,908.76      -4.61
18-Feb-17     7,299.96       6.54
IRR              3.65%      6.13%
Change          23.54%     41.96%

and for my accumulation units using the FTSE100TR index (your figures) I get:

.            FT100TR   Acc Unit
31-Mar-11    -100.00     -12.76
18-Feb-17     150.67      24.88
IRR            7.20%     12.00%
Change        50.67%     95.05%

which leads me to the conclusion that either you are doing something wrong or your portfolio is not doing very well. There are times when the index will get ahead of you and vice versa, but not to that extent.

I cannot see you being that far out of kilter, so I suspect that your unitisation method is the problem. Do you use the current unit price when you add more units?

TJH

[Breelander]

...after six years the gap just keeps widening ... In the INC Units the gap is even more pronounced (FTSE100 121.83, INC Units 97.40, gap 24.42, 20.00%).
QUESTION
Is it valid to compare the two (HYP and Index) like this?

ANSWER
Yes, it is a valid comparison, both for Inc. vs. Ftse and Acc. vs. Ftse-TR.

My baseline was in 2011 as well, 23rd December in my case. At the baseline both Income units and ftse-100 were normalised to 100, today they are Inc. 137.3, ftse-100 132.4. (I don't do Acc units.)

I've checked your normalisation for the ftse-100 with your dates, you seem to be calculating it correctly - I get the same figure. Normalising my Income Units to be 100 on 8 Apr 2011 (closest date I have to your 6th in my records) makes mine 127.6 today, more or less equal to your normalised ftse-100 figure.

Yes, the comparison is valid, but is it useful? particularly the Inc. vs ftse one? ie. does Capital matter to you? Judging by your Acc. units your income is doing better than your capital, at least that's a hopeful sign.

[JohnnyCyclops]

Yes, your approach is valid. No you shouldn't be unitising the index, as it already unitised.

I've compared your results with mine, using 31 Mar 2011 as the start point.

For the income units I get:

.                FTSE      Inc Unit
31-Mar-11    -5,908.76      -4.61
18-Feb-17     7,299.96       6.54
IRR              3.65%      6.13%
Change          23.54%     41.96%

and for my accumulation units using the FTSE100TR index (your figures) I get:

.            FT100TR   Acc Unit
31-Mar-11    -100.00     -12.76
18-Feb-17     150.67      24.88
IRR            7.20%     12.00%
Change        50.67%     95.05%

which leads me to the conclusion that either you are doing something wrong or your portfolio is not doing very well. There are times when the index will get ahead of you and vice versa, but not to that extent.

I cannot see you being that far out of kilter, so I suspect that your unitisation method is the problem. Do you use the current unit price when you add more units?

TJH

ANSWER
Yes, it is a valid comparison, both for Inc. vs. Ftse and Acc. vs. Ftse-TR.

My baseline was in 2011 as well, 23rd December in my case. At the baseline both Income units and ftse-100 were normalised to 100, today they are Inc. 137.3, ftse-100 132.4. (I don't do Acc units.)

I've checked your normalisation for the ftse-100 with your dates, you seem to be calculating it correctly - I get the same figure. Normalising my Income Units to be 100 on 8 Apr 2011 (closest date I have to your 6th in my records) makes mine 127.6 today, more or less equal to your normalised ftse-100 figure.

Yes, the comparison is valid, but is it useful? particularly the Inc. vs ftse one? ie. does Capital matter to you? Judging by your Acc. units your income is doing better than your capital, at least that's a hopeful sign.

Many thanks TJH and Bree. Also thank you to the Fool who took the time to private message me as well.

First, the question. How to treat the benchmark index. It still seems counterintuitive to me, when reguarly adding to (enlarging) the HYP each year, to just measure the start/end point of the index, but as TJH said, indices are already an index in themselves, i.e. at somepoint the FTSE100 did equal 100 (or 1000, or whatever).

Second, insights. Thank you Bree. I had a lightbulb moment in that Income Units don't describe the income (i.e. in classic managed funds (OEICs, etc) the income is 'paid away' by the fund manager). Your comment that 'income is doing better than your capital' made me go back and redo some XIRRs, which has been insightful.

I already run a pivot table for the HYP, one column per stock, of all its transactions, so I can obtain each stock's XIRR, but also in the final column the HYP XIRR. I've copied that table and only left in the buys/sells (i.e. removed from the XIRR the dividends, special dividends, return of capital, etc). That was insightful.

Next, I did go and pore over my worksheet for ACC Units. Although not my original question, I was getting sufficient doubt that I had made some basic error of structure, logic or formulae. I could not find any errors. In doing so I smartened up the XIRR extractions on the same table, that I started a few years ago and never quite finished, trying to obtain 'interval' annual XIRR for both the HYP and the FTSE100TR. This time completed, with success.

I'm satisfied that the calcs are right. There are two reasons for the gap between HYP and Index. The first may be instructive for other Fools when initially HYP building.

Our first HYP pick, in April 2011 was Halfords. Bought pre-HYP, and later 'retrofitted' once we discovered the HYP approach. Bought at 368p. We created the ACC Unit value at 100.00, off this purchase. The second purchase wasn't until 18 August 2011 when we added an equal purchase weight of Aviva (again, not technically done for HYP reasons, but later became HYP buy #2). At that point HFD had dropped materially to around 300p. The unit value was therefore around 84 when AV. was added. And effectively, this lower unit valuation became 'locked in' very early on, bottoming at 69 at the end of 2011.

As can be seen (I hope!) from XIRR below, the HYP isn't actually doing too badly, but not as good at TJH's 12% over the same period (but he's had a bit more practice than me ). I've come to believe that the ACC Unit value's 'true' baseline is nearer the ~80 that it initally fell to, and the increase to ~126 now better reflects performance, rather than forever being hamstrung by the initial HFD tumble, and compares better to the FTSE100TR gain of 100 to 153. I don't think I'm fudging or wishful thinking.

XIRR Lifetime (TR):
HYP          7.63%
FTSE100TR    7.55% (close tally to TJH's calc)

XIRR Intervals (Annually):
Year to:        HYP    FTSE100TR   Difference
31 Mar 2012    -6.24%    1.25%      -7.49%
31 Mar 2013    19.23%   15.54%       3.69%
31 Mar 2014    13.34%    6.60%       6.74%
31 Mar 2015     5.66%    6.34%      -0.68%
31 Mar 2016    -4.42%   -5.25%       0.82%
17 Feb 2017    15.18%   25.37%     -10.20%

I said there were two reasons for the widening gap. The second should be obvious from the above, but the current year (since April 2016) has just been awful. True our HYP is up 15%, but the index is up 25% in the same period. After two years of near parity (2015 & 16) and two years of 'beating the market (2013 & 14), we're not so clever this year.

The pivot table with the company data and XIRR are as follows. There's a very slight difference in overall XIRR. Above it's 7.63% from looking at "Money In" to the portfolio, aligned to the ACC Unit method. Below, it's 7.73% from analysing the stock transactions themselves. I'm comfortable with the differences.

The XIRR for 'Growth only' is 2.92%, and therefore inferred is the remainder (4.81%) is from dividends, special dividends and returns of capital. The 'Initial Date' below, is the point of first purchase, although many stocks were bought in two tranches over time, and some have been topped up in recent years. I've presented in date order, which makes clear the XIRR on those held only a few months provide no real guide. The XIRR on the first eight purchases to March 2012 are instructive, as they have barely made any growth XIRR (a collective mean average of just 0.47%) again another reason why the ACC Units were initially hampered, I feel. The final column, "XIRR Attr. Div." is just the previous two columns subtracted, to show returns from non-growth sources (attributable to dividends, etc.).

[tjh290633]

Johnny, I think I can see where your apparent underperformance came from. The first year is always anomalous, because you usually only get some of the dividends in that year. Looking at what you bought in the first 12 months, you had four shares which have suffered since then, HFD, BBY, BLT and TSCO.

I would be inclined to move the baseline forward a year, in fact look at the performance since successive year ends up to the present day. I have done that with my own portfolio. It can be instructive.

TJH

[JohnnyCyclops]

Johnny, I think I can see where your apparent underperformance came from. The first year is always anomalous, because you usually only get some of the dividends in that year. Looking at what you bought in the first 12 months, you had four shares which have suffered since then, HFD, BBY, BLT and TSCO.

I would be inclined to move the baseline forward a year, in fact look at the performance since successive year ends up to the present day. I have done that with my own portfolio. It can be instructive.

TJH

TJH

I take comfort from your wisdom. I'm all done with XIRR and XLS for the day, but will return to the fray another day. As I hope was seen from the internal (annual) XIRR data above, beyond the first year (not good), the second and third year the XIRR outperformed the index, and in the fourth and fifth came level pegging. This sixth year is turning into a dud (too many 'lemons' in the basket, and a few turned sour at the same time).

I should be able to do what you describe, and calc XIRR from baselines other than the April 2011 HYP start. One for another day.

I'd not previously XIRR'd the index, and having done so, and comparing almost equally to it, I feel better. And admiring of your 12% return in the same six years, know I can do a bit better as well.

[77ss]

QUESTION
Is it valid to compare the two (HYP and Index) like this?

Pose a question like that on a bulletin board and you should expect a variety of answers. Not all of which will agree with you.

At the risk of being accused of 'hijacking' 'your' thread, I would say not.

Perhaps I'm missing something, but are you comparing like with like?

This is the HYP board. The HYP approach is an equal-weighted one. The FT100 index is a market capitalisation based index. Oranges and lemons?

Have you thought about comparing your results with an equal weighted version of the FT100?

Russell do one, and there may well be others.

http://www.ftse.com/analytics/factsheets/Home/Search

[Breelander]

Have you thought about comparing your results with an equal weighted version of the FT100?
Russell do one, and there may well be others.

Readily available too, including historical prices back to Jan 2012. Don't seem to do a TR version though.
https://markets.ft.com/data/indices/tea ... TUKXEQ:FSI

[Arborbridge]

What is the point of the comparison?
I'd suggest it could be to compare you HYP with whatever else you might have done with the capital. So think about that, and compare - it might be a FTSE tracker, or some particular IT - or indeed anything else which takes your fancy!

Arb.

[tjh290633]

I should be able to do what you describe, and calc XIRR from baselines other than the April 2011 HYP start. One for another day.

I'd not previously XIRR'd the index, and having done so, and comparing almost equally to it, I feel better. And admiring of your 12% return in the same six years, know I can do a bit better as well.

It is simple to do with the Accumulation units. Here is mine for calendar years compared with the normal FTSE (not TR):

Since        Acc Unit   IRR      FTSE       IRR   
31-Dec-10       12.32   12.13%   5,899.94    3.53%
31-Dec-11       13.45   12.70%   5,572.28    5.39%
31-Dec-12       15.80   11.60%   5,897.81    5.29%
31-Dec-13       19.56    7.97%   6,749.09    2.53%
31-Dec-14       20.34    9.88%   6,566.09    5.08%
31-Dec-15       21.42   14.03%   6,242.32   14.72%
31-Dec-16       24.88   15.80%   7,142.83   16.85%

As you can see, the unit has lagged the plain FTSE since 31 Dec 2015. I don't have the TR version immediately to hand.

TJH

[JohnnyCyclops]

What is the point of the comparison?
I'd suggest it could be to compare you HYP with whatever else you might have done with the capital. So think about that, and compare - it might be a FTSE tracker, or some particular IT - or indeed anything else which takes your fancy!

Arb.

Yes. Very much. It's "look at what you coulda won!", really.

I do take the point being made re the FTSE100TR. I could just as easily do FTAS or FT350HY (as Geng has mentioned a few times).

[77ss]

Have you thought about comparing your results with an equal weighted version of the FT100?
Russell do one, and there may well be others.

Readily available too, including historical prices back to Jan 2012. Don't seem to do a TR version though.
https://markets.ft.com/data/indices/tea ... TUKXEQ:FSI

TR versions; two different methodologies:

http://www.ftse.com/Analytics/FactSheet ... 3a6fab.pdf
http://www.ftse.com/Analytics/FactSheet ... 6a7e25.pdf

Note the better performance of the equal weighted index over the market capitalisation one.

For those interested in benchmarking against what you can actually buy - Deutsche AWM have an equal weighted ETF. Heading a bit off-topic now, so I'll stop.

https://www.etfstrategy.co.uk/deutsche- ... etf-77738/

[OZYU]

I should be able to do what you describe, and calc XIRR from baselines other than the April 2011 HYP start. One for another day.

I'd not previously XIRR'd the index, and having done so, and comparing almost equally to it, I feel better. And admiring of your 12% return in the same six years, know I can do a bit better as well.

It is simple to do with the Accumulation units. Here is mine for calendar years compared with the normal FTSE (not TR):

Since        Acc Unit   IRR      FTSE       IRR   
31-Dec-10       12.32   12.13%   5,899.94    3.53%
31-Dec-11       13.45   12.70%   5,572.28    5.39%
31-Dec-12       15.80   11.60%   5,897.81    5.29%
31-Dec-13       19.56    7.97%   6,749.09    2.53%
31-Dec-14       20.34    9.88%   6,566.09    5.08%
31-Dec-15       21.42   14.03%   6,242.32   14.72%
31-Dec-16       24.88   15.80%   7,142.83   16.85%

As you can see, the unit has lagged the plain FTSE since 31 Dec 2015. I don't have the TR version immediately to hand.

TJH

TJ, I have played with your data a bit, hope you don't mind.

I think it would be useful to present the data, by adding, maybe between the Acc units and the IRR, the rate at which the acc units have compounded by from those same dates.

Code: Select all

Years | From      | Acc U. | IRR    | have multiplied by | Compounded by
    6 | 31-Dec-10 |  12.32 | 12.13% |        2.019480519 |         12.4%
    5 | 31-Dec-11 |  13.45 | 12.70% |        1.849814126 |         13.1%
    4 | 31-Dec-12 |   15.8 | 11.60% |        1.574683544 |         12.0%
    3 | 31-Dec-13 |  19.56 |  7.97% |         1.27198364 |          8.3%
    2 | 31-Dec-14 |  20.34 |  9.88% |        1.223205506 |         10.6%
    1 | 31-Dec-15 |  21.42 | 14.03% |        1.161531279 |         16.2%
    0 | 31-Dec-16 |  24.88 | 15.80% |                    |             
     


As you can see, there is strong similarity, as expected, between the two since both give you a view your of TR.

In fact this is for recent years, from more distant dates, the differences will become very small.

On my own data, from 1987 to say 2006, basically negligible difference, maximum +-0.12% in the ratio., often much less.

Now for newish portfolios, where the new money will still be a fair proportion of portfolio value, differences will be greater at times, as expected.
Large injections of cash of course will create very large differences, and it is pointless to even worry about the differences over short periods.


Ozyu

[JohnnyCyclops]

Ok, I've been poking at it again this evening, and think I'm getting somewhere. Below the values and XIRR for:

1. Lifetime value - i.e. from April 2011 up to last Friday
2. Discrete years - i.e. in 12 month slices
3. From a historic date, up to last Friday, over a range of timescales

Columns across are:

Money In/Out XIRR - a classic XIRR looking at all the dates and values money was added to the HYP (nothing's been taken out)
ACC Unit Values - baseline 100
ACC Unit Values XIRR - the XIRR of these accumulation unit values
FTSE100TR - the value of the index over time
FTSE100TR XIRR - the XIRR of the index
FTSE100TR Indexed - baseline the index at 100
FTSE100TR Indexed XIRR - the XIRR of the above (not surprisingly as I've indexed an index, it's the same XIRR as the TR Index)

Given that last point, I've not further used the Indexed (base 100) of the FTSE100TR.

The impact of the year one "pull down" on Unit values is seen in the discrete year to 31 Mar 2012, with the Money In/Out XIRR at -6.24% but the ACC Unit Value XIRR at -16.11%. It's also there in the historic period view, i.e. the lifetime view from Apr 2011 to date has 7.63% and 4.01% respectively, however the view since Mar 2012 to date has them much closer at 8.01% and 8.65%, and they remain close across all other time periods.

My final question (and might come down to "it's up to you!", which is fine) is which of my two XIRR should I focus on. Is it the "Money In/Out" XIRR, wherein at 7.63% I'm just a nod ahead of the FTSE100TR XIRR over the same period at 7.55%. Or is it the ACC Unit Value XIRR at 4.01%?

If I moved the 'start' point forwards a year to 31 Mar 2012, those two numbers become very close (8.01% and 8.65%) and it's more of a moot point.

I sense given the way unitisation values fell significantly on purchase number 1 (Halfords) back in 2011, that I'll never be able to overcome the handicap in the historic unit value. Money In/Out XIRR is a useful measure anyway. Yes?

Thank you again, for those who've replied, both here and privately.

                 | Money  | ACC Unit | ACC Unit | FTSE100 | FTSE100 | FTSE100    | FTSE100   
                 | In/Out | Value    | Value    | TR      | TR      | TR Indexed | TR Indexed
                 | XIRR   |          | XIRR     |         | XIRR    |            | XIRR      
Lifetime         |        |          |          |         |         |            |           
at 03 Apr 2011   |        |   100.00 |          | 3897.04 |         |     100.00 |           
to 17 Feb 2017   |  7.63% |   126.03 |    4.01% | 5978.70 |   7.55% |     153.42 |      7.55%
                 |        |          |          |         |         |            |           
                 |        |          |          |         |         |            |           
Discrete Years   |        |          |          |         |         |            |           
at 03 Apr 2011   |        |   100.00 |          | 3897.04 |         |            |           
to 31 Mar 2012   | -6.24% |    84.01 |  -16.11% | 3945.41 |   1.25% |            |           
to 31 Mar 2013   | 19.23% |    97.50 |   16.16% | 4554.78 |  15.54% |            |           
to 31 Mar 2014   | 13.34% |   110.83 |   13.55% | 4858.11 |   6.60% |            |           
to 31 Mar 2015   |  5.66% |   117.06 |    5.62% | 5166.30 |   6.34% |            |           
to 31 Mar 2016   | -4.42% |   111.32 |   -4.89% | 4894.50 |  -5.25% |            |           
to 17 Feb 2017   | 15.18% |   126.03 |   15.06% | 5978.70 |  25.37% |            |           
                 |        |          |          |         |         |            |           
                 |        |          |          |         |         |            |           
From… to current |        |          |          |         |         |            |           
from 3 Apr 2011  |  7.63% |   100.00 |    4.01% | 3897.04 |   7.55% |            |           
from 30 Mar 2012 |  8.01% |    84.01 |    8.65% | 3945.41 |   8.87% |            |           
from 28 Mar 2013 |  6.77% |    97.50 |    6.81% | 4554.78 |   7.23% |            |           
from 31 Mar 2014 |  5.41% |   110.83 |    4.55% | 4858.11 |   7.45% |            |           
from 31 Mar 2015 |  5.31% |   117.06 |    3.99% | 5166.30 |   8.04% |            |           
from 31 Mar 2016 | 15.18% |   111.32 |   15.06% | 4894.50 |  25.37% |            |           
at 17 Feb 2017   |        |   126.03 |          | 5978.70 |         |            |

[Breelander]

TR versions; two different methodologies...

True, but not published on any free-to-access site that I can find. The FT, for example, publish the ft100 equal weighted index, but not its TR version.

[OZYU]

JC

What you have called 'Acc unit value Xirr' is in fact a calculation on the compound rates of return of the acc units.

So to answer your question both are different way to evaluate your tr, useful to have both at the very least in case some corrupt data has slipped into the spreadsheet.

Using a little xirr table to do it is fine, I have given you off board other ways of doing that calculation. These other ways have the advantage that you can do the calculation 'in line' without the need to set up a little xirr table for these calcs. I did it over two columns in my post above playing with TJ's data as an example, but for clarity, in practice it gets built into your spreadsheet as one easy calc.

Oxyu

PS I typed in a very detailed response to all this, in particular on my preferred method to show all this graphically, previewed it a few times as I corrected things, then posted, VANISHED! Not the first time, I must be too slow, need to prepare long posts elsewhere in future.