Hi, For Q1 of SA7 Sept 2022 exam, the Examiner's report says "see Excel" for the solutions or breakdown of the projection calculations. Are these Excel calcs available anywhere? Also, what would we be expected to show in the exam for a question like this? I am assuming we would need to show more than the Examiners report currently gives Thank you
Very good point. I have the spreadsheet here but I am not sure I can post it as it is property of the IFoA. If you email them they will probably add it to the zip file. To help create the spreadsheet, create a column of age from 30, add the contributions in the next column, put the interest rate 0.0895 as a cell (say cell C2) and then roll up each contribution to vesting date. Assuming mid year it might be (1+C2)^(70-C2-0.5).
Thanks Colin! My assumptions were slightly different - it was the 0.5 throwing me off, but everything is tying back now. I have emailed the IFoA to get the Excel spreadsheet added to the zip. Would still be great to know what they expect students to show in the word document as an answer for calc questions. Thanks for the help
The Excels now seem to be available on downloading the Examiner's Report. I'm able to match my answers with the Excel which show that Member A will have £1,569,995 and £1,092,679 in their fund at age 70 for forestry investment and passive equity respectively. However, I'm not sure why the answer script says these values are £1,492,376 and £1,043,962 respectively - is there something I'm missing?
Hmm. Good point. The excel spreadsheet that has been uploaded has only one version. the one I have (which I cannot attach) has a couple of tabs which deal with contributions at the end of the year and another contributions in the middle of the year. The mid year tab seems to give the 1,492 versions. Both were awarded marks by the marking team.
Hi Colin, could we have used the annuity formula to answer this question instead of spreadsheet? I didn't get the exact answer as the marks scheme but got a similar answer: Part (i)(a) Time horizon, n = 40 i = 10.25% p.a. Annuity in advance = (1+i)*annuity in arrears = (1+i)*([1-v^n]/i) Present value of investing $1 every year annually in advance for a term of 40 years (at 10.25% return) = (1+10.25%)*([1-(1+10.25%)^-40]/10.25%) = 10.5391 Accumulated value of the fund in 40 years = $3,000*10.5391*(1+10.25%)^40 = $1,567,000 Assumptions: Assume contributions paid at the start of the year Assuming return of 10.25% p.a. for the forestry fund Fund charges/expenses and tax is ignored Part (ii) (a) Time horizon, n = 40 i = 10.25% p.a. g = 3% i* = 10.25% - 3% = 7.25% Annuity in advance = (1+i)*annuity in arrears = (1+i)*([1-v^n]/i) Present value of investing $1 every year annually in advance for a term of 40 years = (1+7.25%)*([1-(1+7.25%)^-40]/7.25%) = 13.8933 Accumulated value of the fund in 40 years = $2,000*13.8933*(1+10.25%)^40 = $1,377,100
I am sure these would have scored full marks - the examiner was not wanting the result to the nth decimal place. My spreadsheet has conts at start, mid and at end of the year, and my spreadsheet for the 10.25% conts gives 1,567k which is exactly what you have above. Interestingly, my spreadsheet for the 2,000 rising at 3% is 1,408k which is slightly different from your annuity. It may be due to the assumption that we can allow for the 3% conts increases by deducting 3% from 10.25 arithmetically to give 7.25%. It may be more accurate to use 1.1025/1.03 = 7.039%. then your annuity is 14.206 and your calc ends up 1,408k which tallies with my spreadsheet. If I were in the exam I would definitely state the conts at start assumption - my preference would be conts in the middle, or at end of the year as it seems more realistic.
Thanks Colin, this is really helpful. If we used excel to calculate this, can we submit the spreadsheet or do we have to explain in words what we did and put in screenshots in the word document?
It says that you should use Excel as a calculator. Your key answer should be in Word. But if you paste numbers in a spreadsheet into word, those numbers will be considered. But the examiner will not look at the formulae in the cells, so if you have made a mistake or used a different assumption from the examiners, the numbers will not match the examiners numbers and be marked wrong. So I would still stick down in Word the formulae and assumptions you have used in the excel spreadsheet. And as I always emphasise in my tutorials, NEVER paste a spreadsheet in as a picture or a pdf. The IFoA software will simply grey it out, and markers are unlikely to even be able to see the picture. I would also be sensible about the size of what you are pasting in. If the annuity runs for 50 years, you will be pasting over 3 sheets of A4 paper, so I would simply do it as an annuity in Word.
