A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Should I trust XIRR or Legal & General?



 
 
Thread Tools Display Modes
  #1  
Old September 11th, 2009, 10:59 AM posted to microsoft.public.excel.worksheet.functions
Ian Jemmett
external usenet poster
 
Posts: 1
Default Should I trust XIRR or Legal & General?

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.

Ads
  #2  
Old September 11th, 2009, 11:20 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default Should I trust XIRR or Legal & General?

"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.


  #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.



  #4  
Old September 11th, 2009, 01:43 PM posted to microsoft.public.excel.worksheet.functions
Niek Otten
external usenet poster
 
Posts: 2,533
Default Should I trust XIRR or Legal & General?

Hi Ian,

I didn't check your whole post, but this doesn't seem right:

D28 to D35 13/07/2010 to 13/07/2016

Should't that read

D28 to D35 13/07/2010 to 13/07/2017

?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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.


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

Thank you Niek and apologies. It should have read
D28 to D34 13/07/2010 to 13/07/2016; E28 to E34 -£5,707.20
D35 13/08/2016; E35 -£475.60
D36 19/08/2016; E36 £77,200

Ian

"Niek Otten" wrote:

Hi Ian,

I didn't check your whole post, but this doesn't seem right:

D28 to D35 13/07/2010 to 13/07/2016

Should't that read

D28 to D35 13/07/2010 to 13/07/2017

?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"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.


  #6  
Old June 3rd, 2010, 09:01 AM posted to microsoft.public.excel.worksheet.functions
Mark Nowell
external usenet poster
 
Posts: 1
Default XIRR vs L&G

Ian,

I'm doing the same process and one thing may throw your calculations off - many plans factor in a maturity bonus. This will affect the RIY & make it look better than your XIRR calculation.

I'd be interested to hear if you got a suitable reply from L&G

Cheers

Mark



Ian Jemmett wrote:

Should I trust XIRR or Legal & General?
11-Sep-09

EHi Everybody, excuse the preamble but if I tell you what I am 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 do not 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 is good enough for my purposes.

This is how it is 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 do not 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 do not 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.

Previous Posts In This Thread:

On Friday, September 11, 2009 5:59 AM
Ian Jemmett wrote:

Should I trust XIRR or Legal & General?
EHi Everybody, excuse the preamble but if I tell you what I am 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 do not 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 is good enough for my purposes.

This is how it is 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 do not 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 do not 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.

On Friday, September 11, 2009 6:20 AM
JoeU2004 wrote:

Should I trust XIRR or Legal & General?
And forgive me for not delving into all that myself. it is 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 -----

On Friday, September 11, 2009 6:45 AM
Ian Jemmett wrote:

Thanks.
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:

On Friday, September 11, 2009 8:43 AM
Niek Otten wrote:

Should I trust XIRR or Legal & General?
Hi Ian,

I did not check your whole post, but this does not seem right:

D28 to D35 13/07/2010 to 13/07/2016

Should't that read

D28 to D35 13/07/2010 to 13/07/2017

?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

On Friday, September 11, 2009 9:21 AM
Ian Jemmett wrote:

Thank you Niek and apologies.
Thank you Niek and apologies. It should have read
D28 to D34 13/07/2010 to 13/07/2016; E28 to E34 -??5,707.20
D35 13/08/2016; E35 -??475.60
D36 19/08/2016; E36 ??77,200

Ian

"Niek Otten" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 4
http://www.eggheadcafe.com/tutorials...ne-part-4.aspx
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.