Reference no: EM132932969

Context

Robert is an actuarial analyst at one of the Big Five consulting firms in Canada and he has been living with his family to save money. He will turn 35 years of age on the 1st of April 2021 and is considering to purchase his own properties. He is thinking about an option to keep living with his family and decide not to purchase any property. Under this option, he does not have to pay any rent but will also lose the potential rental income and capital gains from properties

Cashflow assumptions

Currently  Robert  has  $400,000  cash  in his  bank  account  and  $100,000  in his  defined contribution  superannuation  account.  He  currently  earns  $150,000  per  annum  payable fortnightly in arrears plus a 10% superannuation contribution which is also paid fortnightly. He spends $500 per week on upkeep. Furthermore,  he expects his  living costs and salary to increase by 2% p.a. effective at the start of each year to match inflation expectations. The residual income (that is, Robert's income after living costs) will be deposited in a bank account earning deterministic interest of 2% p.a. effective.

Question

1. what development of Robert's wealth at the end of each month from the 1st of April 2021 to the 31st of March 2051, that is, assuming that he retires on his 65th birthday, The accumulated value of Robert's wealth upon his retirement in the dollar value?

Note: Please simplify the tasks use spreadsheet in Microsoft Excel, don't have to consider tax and analysis with the investment rate for his superannuation account is deterministic at 3.9% per annum