I
Ian Jemmett
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,D27
36,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.
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,D27
36,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.