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. 


Thread Tools  Display Modes 
#1




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 stepbystep 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 redo 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 stepbystep calculation nor why the stepbystep calculation seems to produce a figure closer to (or, when I do the stepbystep 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 
#3




Should I trust XIRR or Legal & General?
Thanks. I ran your check formula on the monthly stepbystep 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 overstating 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 365day 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 stepbystep 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 redo 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 stepbystep calculation nor why the stepbystep calculation seems to produce a figure closer to (or, when I do the stepbystep 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




Should I trust XIRR or Legal & General?

#5




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 stepbystep 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 redo 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 stepbystep calculation nor why the stepbystep calculation seems to produce a figure closer to (or, when I do the stepbystep 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




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? 11Sep09 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 stepbystep 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 redo 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 stepbystep calculation nor why the stepbystep calculation seems to produce a figure closer to (or, when I do the stepbystep 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 stepbystep 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 redo 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 stepbystep calculation nor why the stepbystep calculation seems to produce a figure closer to (or, when I do the stepbystep 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 365day 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 stepbystep 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 overstating 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...nepart4.aspx 
Thread Tools  
Display Modes  

