Ok. I managed to make a spreadsheet that gives the RIY, but it only works for a single lump sum contribution. I think that it should also be possible to give this RIY for decumulation, but I don't think it's working...
I've attached it if anyone's able to take a look.
Data input captures the initial investment, period investing for, returns (calculates based on asset allocation assumptions), all charges (copes with monetary and %) etc.
The (Optional) Additional IN-OUT tab lets you program in contributions/withdrawals for certain periods and turns it into a monthly net cashflow.
This then all pulls together in the Monthly compounding spreadsheet. This does the projection for 'no charges' and 'with charges'. You then have a monthly 'impact of charges' (no charges value minus with charges value) together with a running total of the charges. The net investment is calculated and the return is value/net investment. This is done each month on an 'actual' basis and also annualised. The RIY is the reduction in the annualised return.
Any thoughts re this? I have a suspicion that I might need to change the return calculations as it 'kinda' assumes the net investment amount was there from the beginning.
Really value a second set of eyes on this...
This then got me thinking to the £/p disclosures (Disclosure for report). Should this instead be based on the calculated projections (with the cashflows)?
Think I might have got myself in a knot!
Any thoughts/views would be really helpful... Thanks in advance :-)
I've attached my annotated calculation sheet. If I mirror your inputs I get a different set of outputs for the RIY figure, notably so when I add a withdrawal.
So... I think I have made this do the RIY calculation correctly including cashflows. Borrowed some of the code from @benjaminfabi spreadsheet. I've got a projection based on no charges run alongside one with charges (month to month calc withdrawals day 1 and charges day 31). This gives the cumulative charges and the impact of charges.
There is a bit of flab in the spreadsheet as I experimented (needs cleaning up) so i've got some rolling annual returns in there and rolling RIY.
Then cumulative returns and annualised cumulative returns (which are only write on month 1 as this is where they are 'looked up' from. This lets me look up the RIY and then to make the reporting easier to check i've duplicated the values. This final section is offset slightly to make the VLOOKUP work. So the figures are for the end of the previous month (i.e. if you say the investment runs to 5 years this is the figure from the last day of year 5 not year 6 month 1 that the VLOOKUP heads over to.
Did monthly periods so I could place annual (and hopefully soon quarterly) charges where they appear in real life.
Disclosure tab brings it all together. Needs tidying up, but what do you think?
In Genovo, it asks for this information. I'm working on an Investment SR and wondering where I could get this information from. I used the FE RIY calculator for the RIY figures but can't seem to find an appropriate calculator for the attached which is a Comparison between the recommended investment and the current investment.
@Rcaisley As you are no doubt aware, the required growth % in Genovo is designed to show the estimated annual return that the recommended alternative plan must achieve to provide a fund at least as good as that offered by the existing plan at the end of the assumed investment term.
I know SAP and O&M include this info. in their pension switching modules (and no doubt their investment switching modules too). Sorry I've never used the FE RIY calculator so not sure if this info. is included. CashCalc also have a RIY calculator too.
However, it's also worth bearing in mind that there's no compulsion to complete this info. The tables in Genovo are "intelligent" which means if you don't include data in a particular row / column then that row / column isn't included when the table is rendered in the report.
@edevan5 said: @Rcaisley As you are no doubt aware, the required growth % in Genovo is designed to show the estimated annual return that the recommended alternative plan must achieve to provide a fund at least as good as that offered by the existing plan at the end of the assumed investment term.
I know SAP and O&M include this info. in their pension switching modules (and no doubt their investment switching modules too). Sorry I've never used the FE RIY calculator so not sure if this info. is included. CashCalc also have a RIY calculator too.
However, it's also worth bearing in mind that there's no compulsion to complete this info. The tables in Genovo are "intelligent" which means if you don't include data in a particular row / column then that row / column isn't included when the table is rendered in the report.
Hi Ed,
Thank you for your response. I'm aware of the "intelligent" fields, would have just been nice not to leave any fields blank and was hoping somebody may know where to obtain this information. In the most recent report I have done, I left the attached two fields blank.
At present, we use SAP for pensions but have not paid for the investment side of the system as the advisers felt that they wouldn't use it enough to justify the costs. I was wondering whether there are alternative calculators anywhere online which may help.
Comments
Ok. I managed to make a spreadsheet that gives the RIY, but it only works for a single lump sum contribution. I think that it should also be possible to give this RIY for decumulation, but I don't think it's working...
I've attached it if anyone's able to take a look.
Data input captures the initial investment, period investing for, returns (calculates based on asset allocation assumptions), all charges (copes with monetary and %) etc.
The (Optional) Additional IN-OUT tab lets you program in contributions/withdrawals for certain periods and turns it into a monthly net cashflow.
This then all pulls together in the Monthly compounding spreadsheet. This does the projection for 'no charges' and 'with charges'. You then have a monthly 'impact of charges' (no charges value minus with charges value) together with a running total of the charges. The net investment is calculated and the return is value/net investment. This is done each month on an 'actual' basis and also annualised. The RIY is the reduction in the annualised return.
Any thoughts re this? I have a suspicion that I might need to change the return calculations as it 'kinda' assumes the net investment amount was there from the beginning.
This then got me thinking to the £/p disclosures (Disclosure for report). Should this instead be based on the calculated projections (with the cashflows)?
Any thoughts/views would be really helpful... Thanks in advance :-)
Chartered Financial Planner
Certified Financial Planner
Head of Technical at Paradigm Norton
Twitter: https://twitter.com/danatkinsonuk
Instagram: https://www.instagram.com/danatkinsonuk/
Looks good.
I've been updating ours too and using your assumptions I came up with:
End of year 1
No Charges - £103,988 (i'm not sure we should include initial costs here though?)
After Charges - £102,083
Reduction in Profit - £1,905
My changes have been to look at what most commonly happens.
Daily changes - OCFs and Growth
Monthly changes - Platform
Quarterly - Adviser
I've created a running total based on the above deductions and additions
Hi Dan,
I've attached my annotated calculation sheet. If I mirror your inputs I get a different set of outputs for the RIY figure, notably so when I add a withdrawal.
ratemysetup
I'd like to get my head around whether it's correct to average the 5 years of RIY as an overall RIY for the period?
So... I think I have made this do the RIY calculation correctly including cashflows. Borrowed some of the code from @benjaminfabi spreadsheet. I've got a projection based on no charges run alongside one with charges (month to month calc withdrawals day 1 and charges day 31). This gives the cumulative charges and the impact of charges.
There is a bit of flab in the spreadsheet as I experimented (needs cleaning up) so i've got some rolling annual returns in there and rolling RIY.
Then cumulative returns and annualised cumulative returns (which are only write on month 1 as this is where they are 'looked up' from. This lets me look up the RIY and then to make the reporting easier to check i've duplicated the values. This final section is offset slightly to make the VLOOKUP work. So the figures are for the end of the previous month (i.e. if you say the investment runs to 5 years this is the figure from the last day of year 5 not year 6 month 1 that the VLOOKUP heads over to.
Did monthly periods so I could place annual (and hopefully soon quarterly) charges where they appear in real life.
Disclosure tab brings it all together. Needs tidying up, but what do you think?
Chartered Financial Planner
Certified Financial Planner
Head of Technical at Paradigm Norton
Twitter: https://twitter.com/danatkinsonuk
Instagram: https://www.instagram.com/danatkinsonuk/
In Genovo, it asks for this information. I'm working on an Investment SR and wondering where I could get this information from. I used the FE RIY calculator for the RIY figures but can't seem to find an appropriate calculator for the attached which is a Comparison between the recommended investment and the current investment.
@Rcaisley As you are no doubt aware, the required growth % in Genovo is designed to show the estimated annual return that the recommended alternative plan must achieve to provide a fund at least as good as that offered by the existing plan at the end of the assumed investment term.
I know SAP and O&M include this info. in their pension switching modules (and no doubt their investment switching modules too). Sorry I've never used the FE RIY calculator so not sure if this info. is included. CashCalc also have a RIY calculator too.
However, it's also worth bearing in mind that there's no compulsion to complete this info. The tables in Genovo are "intelligent" which means if you don't include data in a particular row / column then that row / column isn't included when the table is rendered in the report.
Hi Ed,
Thank you for your response. I'm aware of the "intelligent" fields, would have just been nice not to leave any fields blank and was hoping somebody may know where to obtain this information. In the most recent report I have done, I left the attached two fields blank.
At present, we use SAP for pensions but have not paid for the investment side of the system as the advisers felt that they wouldn't use it enough to justify the costs. I was wondering whether there are alternative calculators anywhere online which may help.
Thanks again Ed and speak soon.