Question

Bobby Brown has just turned 48 years of age and has come to you seeking retirement advice. During your discussion the following information was communicated: Bobby wants to retire on his 65th birthday; he currently has $198,000 in his superannuation fund; he contributes $800 per fortnight into his super account; and the investment returns are 8% p.a. In addition to the super fund, Bobby has also invested $100,000 today (his 48th birthday). The investment is in a direct share portfolio which produces average returns of 6% p.a. after tax. The investment returns are paid every 6 months. When Bobby retires at the age of 65, he will use his super and non-super investments to purchase an ordinary annuity which will provide him with a regular monthly income stream until he reaches life expectancy at 83 years of age. The rate of return for the annuity will be 4% p.a. Bobby is concerned that he may live beyond life expectancy and wishes to ensure he has a residual value of $150,000 remaining in his annuity when he turns 83. He will use the money to supplement the aged pension. Task What will be the value of Bobby’s financial assets when he retires at age 65? Present all calculations to support your answer. [5 marks] What will be the annual pension amount that Bobby will receive until age 83? Returns are compounded annually at year end. Present all calculations to support your answer. [5 marks]

Answer 1

Value of super investments at age 65

Value of super investments at age 65 is calculated using FV function in Excel :

rate = 8%/24 (converting annual rate into fortnightly interest rate. There are 24 fortnightly periods per year)

nper = 17*24 (total number fortnightly contributions until age 63 = number of years until age 63 * 24)

pmt = -800 (Fortnightly contribution. This is entered with a negative sign because it is a cash outflow into the fund)

pv = -198000 (Amount currently in fund. This is entered with a negative sign because it is like a cash outflow into the fund)

FV is calculated to be $1,462,677.46

Value of non super investments at age 65

Value of super investments at age 65 is calculated using FV function in Excel :

rate = 6%/2 (converting annual rate into semiannual interest rate. There are 2 semiannual periods per year)

nper = 17*2 (total number semiannual contributions until age 63 = number of years until age 63 * 24)

pmt = 0 (semiannual contribution is zero)

pv = -100000 (Amount currently in fund. This is entered with a negative sign because it is like a cash outflow into the fund)

FV is calculated to be $273,190.53

Value of Bobby’s financial assets when he retires at age 65 = $1,462,677.46 + $273,190.53

Value of Bobby’s financial assets when he retires at age 65 = $1,735,867.99

Monthly regular income stream until age 83 is calculated using PMT function in Excel :

rate = 4%/12 (converting annual rate into monthly rate)

nper = 18*12 (Total number of monthly payments = number of years until age 83 * 12)

pv = -1735867.99 (Value of Bobby’s financial assets when he retires at age 65)

fv = 150000 (Residual value required at age 83)

PMT is calculated to be $10,811.28

Annual income until age 83 = monthly income * 12

Annual income until age 83 = $10,811.28 * 12

Annual income until age 83 = $129,735.33