Introducing the LemonFools Personal Finance Calculators
Portfolio Unitization  Results Interpretation

 Lemon Quarter
 Posts: 2802
 Joined: November 4th, 2016, 11:20 am
 Has thanked: 183 times
 Been thanked: 892 times
Re: Portfolio Unitization  Results Interpretation
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
TJH

 Lemon Slice
 Posts: 273
 Joined: November 4th, 2016, 9:42 pm
 Has thanked: 11 times
 Been thanked: 117 times
Re: Portfolio Unitization  Results Interpretation
@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.getdigitalhelp.com/2009/03/30/howtoextractauniquelistandtheduplicatesinexcelfromonecolumn/#undiar
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.getdigitalhelp.com/2009/03/30/howtoextractauniquelistandtheduplicatesinexcelfromonecolumn/#undiar

 Posts: 22
 Joined: February 9th, 2019, 8:24 am
 Has thanked: 1 time
 Been thanked: 4 times
Re: Portfolio Unitization  Results Interpretation
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 disinclined 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
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 disinclined 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

 Lemon Quarter
 Posts: 2802
 Joined: November 4th, 2016, 11:20 am
 Has thanked: 183 times
 Been thanked: 892 times
Re: Portfolio Unitization  Results Interpretation
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 disinclined 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

 Lemon Slice
 Posts: 336
 Joined: November 4th, 2016, 7:55 am
 Has thanked: 120 times
 Been thanked: 65 times
Re: Portfolio Unitization  Results Interpretation
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
I am afraid that I must disagree with you here. This is about Portfolio Unitization not about accumulation or distribution units in a fund.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
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

 Lemon Quarter
 Posts: 2802
 Joined: November 4th, 2016, 11:20 am
 Has thanked: 183 times
 Been thanked: 892 times
Re: Portfolio Unitization  Results Interpretation
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 exdividend.
Your two models are both accumulation units
.
TJH
Your two models are both accumulation units
.
TJH

 Lemon Slice
 Posts: 336
 Joined: November 4th, 2016, 7:55 am
 Has thanked: 120 times
 Been thanked: 65 times
Re: Portfolio Unitization  Results Interpretation
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 exdividend.
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

 Lemon Quarter
 Posts: 1159
 Joined: November 4th, 2016, 11:24 am
 Has thanked: 4 times
 Been thanked: 226 times
Re: Portfolio Unitization  Results Interpretation
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 exdiv 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 reinvest the dividends from your shareholdings. https://www.youinvest.co.uk/articles/sh ... tionunits
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).

 Lemon Slice
 Posts: 336
 Joined: November 4th, 2016, 7:55 am
 Has thanked: 120 times
 Been thanked: 65 times
Re: Portfolio Unitization  Results Interpretation
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.

 Lemon Quarter
 Posts: 1336
 Joined: November 7th, 2016, 1:56 pm
 Has thanked: 253 times
 Been thanked: 300 times
Re: Portfolio Unitization  Results Interpretation
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 exdiv 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.

 2 Lemon pips
 Posts: 223
 Joined: March 22nd, 2017, 10:17 am
 Has thanked: 23 times
 Been thanked: 55 times
Re: Portfolio Unitization  Results Interpretation
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.

 Lemon Quarter
 Posts: 2416
 Joined: November 5th, 2016, 9:05 am
 Has thanked: 4 times
 Been thanked: 480 times
Re: Portfolio Unitization  Results Interpretation
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 3 guests