Question

(c) You have commenced work as a certified Financial Planner. Your supervisor has provided the following financial data for a new client Brant Jerome. The client turned 34 years old today and plans to retire when she turns 67. The client owns a diversified share portfolio which is valued today at $47,000. It is expected that this portfolio will earn (on average) 7% per annum indefinitely. Brant also has a superannuation account with a balance of $78,000 to which he currently contributes $1,000 per month. The superannuation account is expected to continue to earn 8% per annum. At his retirement your client plans to consolidate his financial holdings and purchase a monthly annuity as a pension to fund his planned lifestyle. Brant believes he will need to self-fund his retirement until he reaches the age of 85 at which time he would like to have $120,000 remaining to fund any costs not covered by the age pension. During the pension phase of his retirement Brant will adopt a Balanced investment strategy which will return 5% pa (compounded monthly) on his annuity investment. (i) What will be the value of Brant’s financial assets when he retires at age 67? Present all calculations to support your answer. (ii) What will be the monthly pension amount that Brant will receive on his retirement? Present all calculations to support your answer.

Answer 1

Part 1 - Diversified Share Portfolio will be worth 47000*(1.07)^33 = 438291 at year 67

Super Annuation account calculation is shown in the image. For example . In yr 35 , there will be 2 components of return. One will be on the amount at the beginning of year 35 + the amount invested during the year i.e. 12000. Both of these will earn 8% as per the following formula

=+(+78000+12000/2)*8% = 6720

The amount at the end of the yr 35 will be beginning amount + Amount invested + Return earned ( I am assuming whatever return is earned is reinvested at the same rate)

i.e Amount at the end of year 35 is 78000 + 12000 + 6720 = 96720

Same is repeated till yr 67 to get the value of super annuation.

Hence the total value of holdings comes out to be 3248486.58

Part 2 - For calculating the monthly installment, use the pmt function as follows

=PMT(5%/12,12*18,-3248486.58,120000,0)