View Single Post
  #3  
Old September 11th, 2009, 11:45 AM posted to microsoft.public.excel.worksheet.functions
Ian Jemmett[_2_]
external usenet poster
 
Posts: 3
Default Should I trust XIRR or Legal & General?

Thanks. I ran your check formula on the monthly step-by-step calculation &
got the same result as my XIRR calculation on the same numbers & dates. That
gives me the confidence to go back to L&G and ask them why they are
over-stating their projected maturity value.

Ian

"JoeU2004" wrote:

"Ian Jemmett" Ian wrote:
I don't understand why XIRR gives me a significantly different result
[....]
I don't know whether I should go back to L&G and tell them their
figures are wrong or whether XIRR is unreliable.
[....]
Apologies for the length


And forgive me for not delving into all that myself. It's late.

My suggestion is: you ask L&G to explain the descrepancy.

But I do want to address your question ("should I trust XIRR") in general.

First, it is possible for XIRR to return the wrong answer.

Sometimes, it is because of the model. That is, for some models, there can
be multiple valid solutions. XIRR might find one; other software might find
a different one.

But sometimes, it is due to a flaw in the implementation, where XIRR should
return a #NUM error, but instead it returns a bogus rate of return.

To guard against that, you might consider the following.... If
XIRR(B1:B10,C1:C10) is computed in A1, then put in another cell:

=if(isnumber(A1), if(ROUND(XNPV(A1,B1:B10,C1:C10),2) = 0, A1, NA()), A1)

Of course, this is what XIRR should do. But apparently, it does not under
some circumstances.

Second, beware of potential differences in methods of annualizing periodic
rates of return.

XIRR compounds based on 365-day year.

Other software might compound based on different assumptions, or it might
simply multiply the periodic rate by the the frequency.


----- original message -----

"Ian Jemmett" Ian
wrote in message
...
EHi Everybody, excuse the preamble but if I tell you what I'm trying to
achieve it might help.

I am a pensions adviser and our practice gets involved in a lot of pension
transfer advice situations. Quite rightly, this is an area in which the
reasoning behind advice needs to be particularly robust.

I have built a spreadsheet that compares the past performance we have
achieved with what the existing plan has achieved and, among other things,
projects forward on a 'if we continue to outperform at the same rate, you
will end up with £xxx extra in your pension fund if you transfer into the
plan we recommend...' (Past performance should not be taken as a sole
guide
to the future etc).

As part of this process, I need the spreadsheet to calculate the
'Reduction
in Yield' ie the effect of the existing company's charges on a gross
investment return of x%pa. This is not information that the existing gives
us
specifically but they do give us a projection. In the case I am looking at
at
the moment, L&G tells me that if they achieve a gross return of 7% the
client
will end up, on 19/8/2016 with £77,200.

The client has a fund value, as at 13/7/2009 of £16,654.47 and makes
monthly
contributions of £475.60. So that I don't have too many lines on my
spreadsheet, I have annualised the contribution. I realise this will
distort
the outcome somewhat but have assumed that it should still give a figure
that's good enough for my purposes.

This is how it's laid out:
D27 13/07/2009; E27 -£16,654.47
D28 to D35 13/07/2010 to 13/07/2016; E28 to E35 -£5,707.20
D36 13/08/2016; E36 -£475.60
D37 19/08/2016; £77,200.00

In E39 I have =XIRR(E27:E36,D2736,0.06)

I get the result 7.01% which, as the £77,200 is based on a return before
charges of 7%, cannot be right.

As I am still building this spreadsheet I have an additional worksheet on
which I check the calculation on a step-by-step basis. I put in the
present
fund value as a +ve, build it each year by adding 12x the monthly
contribution & multiplying the total by a %age. In the last year I add 1
monthly contribution to the previous year's total & multiply the result by
the same percentage/12. I then do a Goal Seek on that year's total,
setting
it to £77,200 by changing the cell containing the %age. I get the result
6.14%, giving a reduction in yield of 0.86%.

If I re-do the whole thing using monthly contributions but build it in the
same way, I get a net annualised return of 6.34%/RIY of 0.66%, which is
what
L&G tell us the charges for this plan would be. XIRRing the monthly
columns
gives me 6.58%/RIY of 0.42%.

I don't understand why XIRR gives me a significantly different result to
my
step-by-step calculation nor why the step-by-step calculation seems to
produce a figure closer to (or, when I do the step-by-step monthly, spot
on)
the annual charge L&G states. I don't know whether I should go back to L&G
and tell them their figures are wrong or whether XIRR is unreliable.
Alternatively, do I need to do something different to produce a different
result?

Apologies for the length & thanks for any help anyone can give me.