Unitisation

[Markblox]

Would anyone be able to point me to the top up spreadsheet as I would like to unitise my portfolio.

[Markblox]

Just found it on the financial software part of the site!

[Breelander]

Would anyone be able to point me to the top up spreadsheet as I would like to unitise my portfolio.

TheTop Up Spreadsheet does not include unitisation, but a possible extension was proposedhere (more 'in development' than a finished product). The Lemon Fool Financial Software repository has a number of other notes on unitisation, including links to a number of board posts on the subject.

[Breelander]

The TMF repository links are direct to the boards. For future reference the same posts are more permanently available in the Wayback machine.
https://web.archive.org/web/20161104225 ... sort=whole
https://web.archive.org/web/20161124183 ... sort=whole
https://web.archive.org/web/20161124183 ... sort=whole

[Breelander]

https://web.archive.org/web/20161124183 ... sort=whole

My own unitisation is described here...
https://web.archive.org/web/20120314180 ... 69453.aspx
...including links to posts by Gengulphus, TJH and others that were essential reading for me to get started.

[Markblox]

Would anyone be able to point me to the top up spreadsheet as I would like to unitise my portfolio.

TheTop Up Spreadsheet does not include unitisation, but a possible extension was proposedhere (more 'in development' than a finished product). The Lemon Fool Financial Software repository has a number of other notes on unitisation, including links to a number of board posts on the subject.

Thanks very much Breelander.
Last night I downloaded Open Office, the top up spreadsheet and also copied the unitisation spreadsheet into the page. I input my portfolio into the main page and now all I have to do is work out how to input the data to unitise and get down to the mind numbing task of data input! I also have to make a decision on the start date. I don't want to go back to the beginning but the portfolio started to grow rapidly in size at the end of 06. Just ten years then, but if I read it correctly there is no dividend payments to input which will make it a whole lot easier, although I may be wrong as my eyes were getting a bit heavy by that stage.

[Breelander]

I also have to make a decision on the start date. I don't want to go back to the beginning but the portfolio started to grow rapidly in size at the end of 06. Just ten years then...

Retrospectively unitising can be mind-numbingly tedious. It took me 6 month to work back 10 years - and that's with full records of cash added and purchase dates. Not that it's difficult, just that after a few hours the eyes glaze over and you have to take a break or you'll start making mistakes. That's why most people choose 'Today' as the unitisation start date.

... but if I read it correctly there is no dividend payments to input which will make it a whole lot easier, although I may be wrong as my eyes were getting a bit heavy by that stage.

That is correct for Income Units, but Accumulation Units need to know the dividends received. The effort of tracking back every dividend I ever received was one of the reasons I chose to go for only calculating Income Units. Income Units are directly comparable with the standard ftse indices, while Accumulation Units should be compare with the Total Returns versions of those indices.

[Gengulphus]

The other potential stumbling block with backdating unitisation is knowing the portfolio value on all dates that units were added or removed: without it, you don't know the right unit price to use. No problem beyond a bit of data entry if you valued the portfolio at the time; a nuisance but doable using free historical share price sources if all the shares still exist; a real nuisance and only approximately and/or expensively doable if some of them no longer exist due e.g. to takeovers.

Gengulphus

[Breelander]

The other potential stumbling block with backdating unitisation is knowing the portfolio value on all dates that units were added or removed...

Yes, I should have added that my spreadsheet (by luck) already included a history of my portfolio composition and historic share prices. I already had the historical portfolio valuation.

[Markblox]

I have also come to realise that when I have taken money out of the portfolio to spend has to be taken into account. Life is too short. It will be from today for that reason.

[ADrunkenMarcus]

Good luck with it.

It took me too long to unitise and I am glad I've now done so.

Best wishes


Mark.

[Alaric]

[quote="Markblox"]I have also come to realise that when I have taken money out of the portfolio to spend has to be taken into account.[/quote

If you have full details and dates of all cash flows in and out along with portfolio values at start dates and end dates, you can use Excel's or Open Office's XIRR function to calculate the rate of return on the portfolio between two dates. Unitisation is another method of calculating the same information. It's arguably more complex but necessary if there's more than one investor in the portfolio to give the split of their share of the assets.

[Gengulphus]

If you have full details and dates of all cash flows in and out along with portfolio values at start dates and end dates, you can use Excel's or Open Office's XIRR function to calculate the rate of return on the portfolio between two dates. Unitisation is another method of calculating the same information. It's arguably more complex but necessary if there's more than one investor in the portfolio to give the split of their share of the assets.

No, unitisation does not calculate the same information as XIRR(). Both are arguably rates of return, but the devil's in the detail of what you mean by the "rate of return".

For example, I invest £4k in a share and it increases in value by 25% in the first year, to £5k. Encouraged, I add a further £4k, increasing the value of the holding to £9k. But unfortunately, it drops by 20% in the second year, to £7.2k.

Unitisation says that I bought say 4k units at 100p each initially. During the first year, the unit value rose to £5k/4k = 125p, so my second £4k only bought me 3.2k units, raising my total number of units to 7.2k. During the second year, the unit value fell to £7.2k/7.2k = 100p, so as far as unitisation is concerned, I'm back where I started: a 0% overall rate of return.

XIRR() instead answers the question "What interest rate would a bank account have to pay (if positive) or charge (if negative) to match the performance of the investments? I.e. to have £4k deposited in it, another £4k deposited in it a year later, and to be worth £7.2k at the end of the second year?" The answer comes out as -6.82% (which isn't surprising, as you've put a total of £8k in and have only got £7.2k left).

The point is that unitisation measures the success of your decisions what to be invested in without taking your decisions when to add and remove money into account, while XIRR() measures the overall success of your decisions what to be invested in, when to add money and when to remove it. In this particular scenario, you chose to add more money for the second year: unitisation didn't 'see' that and just saw that 25% up followed by 20% down equals no overall movement on the investments, while XIRR() did 'see' it and so paid attention to the fact that you lost more in the second year than you gained in the first, due to having more invested then.

Which to use depends on whether you're trying to make decisions about going to/from cash, or just adding cash when it's available, removing it when it's needed. The classic case for using unitisation is a unit trust manager, who decides what to invest in - but the owners of the units decide when to buy more units or redeem units. Many HYPers are also either just adding cash when available (in the 'build phase') or removing it when needed (in the 'drawing income phase'), so unitisation is probably best for them as well. Having said that, unitisation needs more data and usually the two aren't that far from each other (especially in the long term), so XIRR() may well be the not-ideal-but-it-will-have-to-do solution.

Unitisation is needed when splitting the assets between investors, yes - e.g. in the above example, if the two £4ks were contributed by different investors, the first has had the 25% increase and the 20% decrease, while the second has only had the 20% decrease, and that's correctly reflected by the first having 4k units, the second 3.2k units and the unit value being 100p. But it's not the only reason for using unitisation!

Gengulphus

[Markblox]

If you have full details and dates of all cash flows in and out along with portfolio values at start dates and end dates, you can use Excel's or Open Office's XIRR function to calculate the rate of return on the portfolio between two dates. Unitisation is another method of calculating the same information. It's arguably more complex but necessary if there's more than one investor in the portfolio to give the split of their share of the assets.

No, unitisation does not calculate the same information as XIRR(). Both are arguably rates of return, but the devil's in the detail of what you mean by the "rate of return".

Gengulphus

I use a fantastic IPhone App called : All My Shares and it is very easy to use and a very powerful tool and can't recomend it highly enough. In the last six months they have added a Internal rate of return function for the whole portfolio and also individual shares. The only thing missing is a way of comparing portfolio performance with the FTSE 100 or any other indice. I now have that with the addition I have added to the excellent top up spreadsheet. Very useful, thanks to all concerned.