rate of return calculation

coxwell

Hi please help.
When calculating rate of return over a 10 year period using the excel xirr command, is a loyalty bonus from a provider treated as a receipt (positive value) or a payment/withdrawal (negative value)?

regards
dc

calypso_rhapsody

sounds to me like it should be positive as it increases your return? Not familiar with that Excel function though ...

Totton

I would treat it as a receipt similar to a dividend. Technically it has been withdrawn from the fund to pay the broker who has then paid it to you so I can see the problem but best to keep things simple.

At one time I added all the loyalty bonuses to my accounts as a return from the related fund, it was a pain in the butt adding those each month so I switched to Investment Trusts and ETF's and now only have to worry about counting dividends :-)

coxwell

Thanks for that.
How should one treat a discount given as extra units - I'm trying to measure the performance over 10 years.

bowlhead99

I presume you're trying to use XIRR to track an annualised IRR performance, (i.e. cash in, cash out, on various dates, with an assumption that you sell the remaining units and cash out the final balance at their market value on the last day?

E.g. for a string of cashflows like
1/1/2010 (£1000) investment
1/7/2010 (£1000) investment
15/9/2010 £50 divi rec'd
15/9/2011 £55 divi rec'd
15/9/2012 £60 divi rec'd
10/10/2012 £2500 value if sold today
, XIRR will give you the annualised rate of return for the above (I don't have the full version of excel on this PC so am not going to do the calc...)

The fact that they gave you some free units will mean the divis received in 2010,2011,2012 are bigger than they would otherwise have been if you'd had fewer units. Also the sale proceeds today are bigger than they would otherwise have been. So the effect of the discount was that it gave you nice big cash inflows. Maybe this is the same as if you'd had to pay 1020 on day one instead of 1000 to buy that same number of units.

Clearly the fact of them giving you free units or selling the same units for lower price, however you dress it up, gives you a better return, and XIRR tells you what your actual return was for a given set of cashflows.

But you don't have to 'treat' a discount or do anything with it at all - it is not a cash inflow or outflow, it's just a number of units. XIRR is just going to tell you what return you made on your investment based on the in and out cashflows you experienced, it doesn't care how many units you hold.

If you like, you could change the cashflows to see what your return would have been if you had spent the same cash on day one and got lower cashflows coming back, or if you had spent more cash on day one and got the same cashflow coming back. You could do a whole bunch of what-if scenarios. But you might not find them particularly useful.

Presumably the discount was not because you're a clever negotiator and you're trying to see how much you benefitted from those skills in this instance. You bought them as a retail investor using a standard platform open to everybody, so in a sense you did just pay the going rate for the shares. The marketing docs might say they should have charged you more and then they instantly gave it back, but it's an irrelevance and a distraction from your main goal, which is to see what return you got on your actual investment.

If you feel you must show the discount into your calc somehow, just put the initial transaction on as -1020 or -1030 or whatever, as if you had paid full price, instead of the -1000 you actually paid. And then put a 20 or 30 positive cashflow on the same day to reflect you didn't actually pay it. Excel will add up the cash for the day and see it's -1000 and so will give you the same result as if you just used the proper actual cashflows.

coxwell

thanks for reply - this is a bit complicated as I negotiated the discount at start in terms of extra units and the charges have been paid each year by selling these units. I have also had bonus units for loyalty so as an example:-

£1000 paid in and 1000 units bought at £1 each. With discount balance starts at 1075 units therefore initial investment becomes worth £1075

establishment charges paid each year for first two years by selling units eg 60 units at market rate
annual charges say 50 units at market rate for 10 years
withdrawals taken by selling units at market rate
bonus loyalty units given every 2 years worth market rate
after 10 years what is the rate of return. I presume charges and withdrawals are negative amounts using xirr command in excel and bonus units and discount are positive.
Does this give me the correct rate of return that an IFA would use.

regards
dc

Aegis

Just ignore all unit transactions, as those will automatically factor into the final valuation. Only put in purchases (as positives) and withdrawals/non-reinvested dividends (as negatives), together with the value at the date you want the calculation to run to, with the final value treated as a withdrawal. That should give you the most accurate rate of return.

bowlhead99

coxwell wrote: »

I presume charges and withdrawals are negative amounts using xirr command in excel and bonus units and discount are positive.

You seem to be mixing methodologies. Aegis is right.

A withdrawal is cash coming back into your hands, a Good Thing (imagine buying for £1000 and withdrawing £2000). A charge is assets being taken away from the fund so that the total amount that eventually comes back to you out of the assets of your fund might only be £1990. So this is a Bad Thing and it can't possibly be right to put it in the formula with the same sign as a withdrawal which we said was a Good Thing.

The charges are not a cashflow between you and the fund. They are a change in the level of assets you own in the fund. Other changes in the level of assets you own in the fund happen on a daily basis, and include them going up (e.g. the fund owns Sainsbury shares whose value in the market increases, or Sainsbury pays them a dividend, or they give you a free unit), or going down (the fund owns Tesco shares whose value goes down, or the fund pays cash to its auditors for signing off the annual reports, or the fund pays a management fee or legal expense).

These changes in the value of what you own will all ultimately affect your final return but you don't try and capture them in XIRR - it just uses cash you paid into the fund and cash you got out from the fund.

When you pay money, this is a negative cashflow from your bank account. When you get money back, this is a positive cashflow to your bank account.

When the charges are paid out of the funds assets without you dipping into your own bank account to do it, there is no cashflow. When they give you some free units to say thanks for being a great customer and letting me take all these charges, there is no cashflow. Neither of these things figure in your calculation of the actual total return that you get from the investment.

Imagine you buy an asset for £1000 on day one.
On day 182, some of the units are taken off you and given to the manager as his fee. Those units were worth £17.85
On day 183, they give you some more units which are worth £11.53 as a loyalty bonus.
On day 365, you sell some units and get £200. The remaining investment is worth £900.

The only cashflows between your bank account and the fund were the £1000 in at the start and the £1100 out at the end (i.e. you assume the remaining £900-worth of units was also sold at their value on the last day). Feed them into XIRR and the return you achieved for the year was 10%.

Does this give me the correct rate of return that an IFA would use.

It depends what the IFA is trying to calculate. The figure is directly comparable with a bank account paying AER of 10%. It is also exactly the same percentage annual return as putting 100k into a property investment and getting back 5k at the end of year one when you sell a piece of land at the back and 115.5k at the end of year two when you sell the rest of the house. Both the bank account and the house deal give you an annualised return of 10%.

XIRR is a function which gives you the annualised rate of return for any string of cashflows which are not necessarily fixed periods apart or equal in size. So you can compare the IRRs you got from a dividend paying fund or a growth fund or an individual share or a rental property or whatever, over your specified date range.

Stochasticity

For the purposes of XIRR:

- Only payments by you to the provider should be counted as a positive value.

- Anything that is reflected within the end value of the policy should not be included.

- Anything that the provider pays out to you should be a negative value. The end value should also be a negative value.

Therefore, how you treat dividends, loyalty bonuses and the like depends on where they go. If they are retained within the policy, e.g. reinvested, you do not need to account for them (they are already accounted for within the end value). If they are paid out to your bank account, then you need to account for them separately using a negative value.

There is no circumstance under which a loyalty bonus should be accounted for using a positive value.

coxwell

thanks to all for info, I need just a little bit more.

I now understand that to measure my investment over a period of years it would seem I only include actual payments made by me and any actual withdrawals made to me and the value of the investment at the end of the period when using the xirr command in xcel. That gives me the annualised rate of return.

What I don't understand is that my IFA insists on bringing up charges and commission as if it should be included in the calculation. I believe he his trying to make the performance look better than it is or am I missing the point.

regards
dc

Aegis

coxwell wrote: »

thanks to all for info, I need just a little bit more.

I now understand that to measure my investment over a period of years it would seem I only include actual payments made by me and any actual withdrawals made to me and the value of the investment at the end of the period when using the xirr command in xcel. That gives me the annualised rate of return.

What I don't understand is that my IFA insists on bringing up charges and commission as if it should be included in the calculation. I believe he his trying to make the performance look better than it is or am I missing the point.

regards
dc

Charges are an integral part of the return. You could argue that commission should be included as cash payments out of the investment to show the pre-commission performance, but it wouldn't accurately reflect your overall rate of return including the advice costs.