Remove ads

Introducing the LemonFools Personal Finance Calculators

Portfolio Unitization - Results Interpretation

Stocks and Shares ISA , Choosing funds for ISA's, risk factors for funds etc
Investment strategy discussions not dealt with elsewhere.
tjh290633
Lemon Quarter
Posts: 3055
Joined: November 4th, 2016, 11:20 am
Has thanked: 203 times
Been thanked: 978 times

Re: Portfolio Unitization - Results Interpretation

#201258

Postby tjh290633 » February 14th, 2019, 9:43 am

Pref, I can see what your problem is. You are using a relational database, not a spreadsheet. Presumably you have to generate a report to get the complete picture, which does not lend itself to using XIRR. Export the correct form of report to Excel, then apply the function to the cash flow column.

TJH

Hariseldon58
Lemon Slice
Posts: 307
Joined: November 4th, 2016, 9:42 pm
Has thanked: 12 times
Been thanked: 124 times

Re: Portfolio Unitization - Results Interpretation

#201380

Postby Hariseldon58 » February 14th, 2019, 4:14 pm

@prefinvestor

If you wish to extract the data from various tables to create your date ordered table to simplify your workings it can be done fairly easily in Excel.

Use an array function, a single cell function it creates multiple columns and rows of data as output, if you are unfamiliar they are very, very powerful functions and they can turn Excel into a pretty good Relational Database, they take a little while to get your head around but there is an excellent web site on Excel, there are enough examples that allow you 'adapt' to work out anything.


I have put a link below which talks about various ways of tackling a particular problem, using arrays is covered about a third of the way down the page, the site is brilliant and Oscar will respond to help, the weekly newsletter is very informative on Excel.

https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#undiar

PrefInvestor
Lemon Pip
Posts: 89
Joined: February 9th, 2019, 8:24 am
Has thanked: 4 times
Been thanked: 24 times

Re: Portfolio Unitization - Results Interpretation

#201391

Postby PrefInvestor » February 14th, 2019, 5:03 pm

Hi TJH/All,

Thanks for offers of help and encouragement. No I am not using a database just an Excel spreadsheet of my own invention in which the data is held in about 10 different Excel tables organised on relational database principles.

To date for the whole of my investing life I have worked on the very simple principles outlined below:-

1. I know my “average” (or break even point) for each investment calculated from all buys and sells to date. Using this I can calculate my gain/loss for each investment daily using today’s price.
2. I know the capital value at the start of the year, I know what it is today, I know how much money I have deposited or withdrawn and how much cash I have, so I know how much I’ve made or lost YTD. And I know all those figures right back to 2012 so I know exactly how much I have made in that time.
3. I consider my YTD percentage gain to be capital (now) – capital (@ start of year) – less deposits + withdrawals / capital (@ start of year). This may not be how anyone else does it but I am happy with that.
4. In income terms I have a record of all my dividend payments and in fact of all payments expected for the rest of the calendar year. Using this I calculate total dividends received to date for each investment. I display both these on my investments summary page and total them to give a hypothetical total gain figure for each investment.

I fully understand all these calculations and am confident that they are correct, but almost certainly are not comparable with what anyone else does. Periodically I compare my spreadsheet with my brokers numbers and to date they have always agreed exactly.

My main problem I think is that I am not an accountant nor am I up to speed with what are doubtless commonly accepted financial principles and metrics. The portfolio unitizing scheme is a classic example, I have now implemented the steps described in the monevator article – but the results are meaningless to me. Of course I could easily have done something wrong, but if so I’m never going to find out what.

Were I to go on to implement an XIRR calculation per investment (which I’m sure I could do) I fear it will be just the same, it might as well say “42” (the meaning of life the universe and everything) as far as I’m concerned. So I am dis-inclined to continue down this path.

If anyone ever asks me what my XIRR is or for my unitised portfolio performance I shall confidently say that I have no idea !. I shall just continue doing what I have always done.

Thanks for your help anyway.

ATB

Pref

tjh290633
Lemon Quarter
Posts: 3055
Joined: November 4th, 2016, 11:20 am
Has thanked: 203 times
Been thanked: 978 times

Re: Portfolio Unitization - Results Interpretation

#201397

Postby tjh290633 » February 14th, 2019, 6:07 pm

PrefInvestor wrote:Were I to go on to implement an XIRR calculation per investment (which I’m sure I could do) I fear it will be just the same, it might as well say “42” (the meaning of life the universe and everything) as far as I’m concerned. So I am dis-inclined to continue down this path.

If anyone ever asks me what my XIRR is or for my unitised portfolio performance I shall confidently say that I have no idea !. I shall just continue doing what I have always done.

Thanks for your help anyway.

ATB

Pref

My concern is that you may be deluding yourself about the true rate of return of your portfolio, because you have money in and money out from time to time.

Really if you ignore dividends and just have a cash flow for the portfolio as a whole, then you should be able to get a realistic figure. Unitisation helps you to compare your progress with an index or other yardstick, but with your method you have to compare with the Total Return versions of the index concerned.

TJH

BusyBumbleBee
Lemon Slice
Posts: 399
Joined: November 4th, 2016, 7:55 am
Has thanked: 147 times
Been thanked: 95 times

Re: Portfolio Unitization - Results Interpretation

#201636

Postby BusyBumbleBee » February 15th, 2019, 7:09 pm

tjh290633 wrote:That is correct for Accumulation units where the dividends roll up inside the units. It is not correct for income units, where the dividends may be withdrawn as cash or reinvested into whichever share the owner wishes.TJH

in response to my earlier quote from the monevator article
The number of units you own doesn’t change because you were paid a dividend – no more than if one of your shares went up by 20p
I am afraid that I must disagree with you here. This is about Portfolio Unitization not about accumulation or distribution units in a fund.

Take two identical portfolios held in two separate nominee accounts which not only holds the shares but also receives the dividends.

Account one (accumulation) : Investments £100,000 : dividends received in the month : £1,500 : at end of month the total is £101,500 and the unit price (if there are 100,000 units) is 1.015.

Account two (distribution) : Investments £100,000 : dividends received in the month : £1,500 : at end of month the total is £101,500 and the unit price (if there are 100,000 units) is 1.015.

I assume you are happy with just investing the £1,500 in Portfolio one and taking the new unit value as £1.015 (ignoring costs of course)

But with portfolio two you seem to be saying that you are going to distribute the money and then issue new units when you invest it : Surely this is in fact the sale of some units and the purchase of more when you buy something else? Which I strongly suspect will give us the same answer or, are you simply going to issue £1,500 units at £1 - thereby giving both portfolios the same value?

The purpose of Unitisation - for me anyway - it to capture total return in the unit price and dividends are a very important component of that.

kind regards - BBB

tjh290633
Lemon Quarter
Posts: 3055
Joined: November 4th, 2016, 11:20 am
Has thanked: 203 times
Been thanked: 978 times

Re: Portfolio Unitization - Results Interpretation

#201643

Postby tjh290633 » February 15th, 2019, 8:11 pm

Your "distribution" model is wrong. It is only correct if you assume that you are holding cash for later distribution or reinvestment. In the latter case you buy more units with the cash, and you have 101,500 units. With the former you have £1,500 cash and 100,000 units of £1.00 each. Effectively your units go ex-dividend.

Your two models are both accumulation units
.
TJH

BusyBumbleBee
Lemon Slice
Posts: 399
Joined: November 4th, 2016, 7:55 am
Has thanked: 147 times
Been thanked: 95 times

Re: Portfolio Unitization - Results Interpretation

#201755

Postby BusyBumbleBee » February 16th, 2019, 11:56 am

tjh290633 wrote:Your "distribution" model is wrong. It is only correct if you assume that you are holding cash for later distribution or reinvestment. In the latter case you buy more units with the cash, and you have 101,500 units. With the former you have £1,500 cash and 100,000 units of £1.00 each. Effectively your units go ex-dividend.

Your two models are both accumulation units.TJH

I am afraid your logic totally escapes me.

You seem happy with your method - and I am certainly happy with mine so let's leave it there. regards - BBB

mc2fool
Lemon Quarter
Posts: 1217
Joined: November 4th, 2016, 11:24 am
Has thanked: 4 times
Been thanked: 258 times

Re: Portfolio Unitization - Results Interpretation

#201783

Postby mc2fool » February 16th, 2019, 1:59 pm

BusyBumbleBee wrote:This is about Portfolio Unitization not about accumulation or distribution units in a fund.

Yes, but you have to decide which basis you are going to do the unitisation on, accumulation or income (distribution).

Account two (distribution) : Investments £100,000 : dividends received in the month : £1,500 : at end of month the total is £101,500 and the unit price (if there are 100,000 units) is 1.015.

But you haven't distributed anything so the situation you're describing above is the pre ex-div date one. For the distribution case once the income is distributed the situation is, Investments £100,000 and £1,500 of dividends received and paid out (distributed) to the holders bank account. So, account two is 100,000 units worth £1 each for a total of £100,000.

"The income that accrues to the accumulation share class is added to its capital value. On the date of the next income distribution the price of that share class will remain the same, all other things being equal, whereas the price of the income shares will drop as the income is stripped out of its net asset value for distribution to the shareholders. This will affect the relative performance of the income and accumulation units in the same way that it does if you re-invest the dividends from your shareholdings. https://www.youinvest.co.uk/articles/sh ... tion-units

If you want to reinvest the distributed £1,500 (or count as doing so for total return calculation purposes) you do so by buying more income units, so you'd end up with 101,500 units at £1 each, for a total of £101,500.

That's the way total return works for accumulation and income units. With accumulation units the number of units stays the same but their value increases, whereas with income units the number of units increases but their value stays the same. Units * unit value comes to the same in both cases, of course (at least in the no costs theoretical case).

BusyBumbleBee
Lemon Slice
Posts: 399
Joined: November 4th, 2016, 7:55 am
Has thanked: 147 times
Been thanked: 95 times

Re: Portfolio Unitization - Results Interpretation

#201802

Postby BusyBumbleBee » February 16th, 2019, 4:25 pm

mc2fool wrote:
BusyBumbleBee wrote:This is about Portfolio Unitization not about accumulation or distribution units in a fund.

Yes, but you have to decide which basis you are going to do the unitisation on, accumulation or income (distribution).


WHY?

I am not a customer of a fund manager and the calculations you give are from a customer perspective

I am effectively my own fund manager here and only have units which are neither accumulation nor distribution units and want to know:

a) how much each unit is worth at any given time
b) how many units are disposed of when some money is taken out : that money comes from the cash reserve within the pot which in turn is built up from dividends received and/or the sale of shares.
c) how many units are bought when a contribution is made to the pot.

moorfield
Lemon Quarter
Posts: 1416
Joined: November 7th, 2016, 1:56 pm
Has thanked: 286 times
Been thanked: 317 times

Re: Portfolio Unitization - Results Interpretation

#201827

Postby moorfield » February 16th, 2019, 8:26 pm

mc2fool wrote:
BusyBumbleBee wrote:Account two (distribution) : Investments £100,000 : dividends received in the month : £1,500 : at end of month the total is £101,500 and the unit price (if there are 100,000 units) is 1.015.

But you haven't distributed anything so the situation you're describing above is the pre ex-div date one. For the distribution case once the income is distributed the situation is, Investments £100,000 and £1,500 of dividends received and paid out (distributed) to the holders bank account. So, account two is 100,000 units worth £1 each for a total of £100,000.



Concur. And just to add BBB if you then bought more distribution units with your £1500 you would have 101,500 distribution units priced at £1. Which as TJH has pointed out is the same as 100,000 accumulation units prices at £1.015.

nmdhqbc
2 Lemon pips
Posts: 235
Joined: March 22nd, 2017, 10:17 am
Has thanked: 27 times
Been thanked: 58 times

Re: Portfolio Unitization - Results Interpretation

#201833

Postby nmdhqbc » February 16th, 2019, 9:21 pm

BusyBumbleBee wrote:b) how many units are disposed of when some money is taken out : that money comes from the cash reserve within the pot which in turn is built up from dividends received and/or the sale of shares.


If you want to distribute cash from sale proceeds in your inc units then I don't see any point in calculating inc units at all. You have all the info you need in the acc units. The dis units are supposed to give a read on how well your investments are doing on a capital only basis and a separate figure for the income per unit they are paying naturally. If you include any sale proceeds you feel like in the inc units distributions then there's no point in calculating it. You are choosing yourself falsely how much of the return is from income vs capital appreciation. As such you may as well keep it simple and do acc only. If the reality is that you sell some capital for income then acc seems appropriate anyway.

Alaric
Lemon Quarter
Posts: 2670
Joined: November 5th, 2016, 9:05 am
Has thanked: 5 times
Been thanked: 540 times

Re: Portfolio Unitization - Results Interpretation

#201842

Postby Alaric » February 16th, 2019, 10:22 pm

BusyBumbleBee wrote:I am effectively my own fund manager here and only have units which are neither accumulation nor distribution units and want to know:


You could perhaps regard yourself and your assets as being the shareholder of an investment trust. If as the "shareholder" you put more money in, then you need a price for a "rights" issue. Otherwise it's just distributions when money is taken out.


Return to “Investment Strategies”

Who is online

Users browsing this forum: No registered users and 4 guests