1. Context

William is an actuarial analyst at one of the Big Four consulting firms in Sydney 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

2. Cashflow assumptions

Currently William 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, William's income after living costs) will be deposited in a bank account earning deterministic interest of 2% p.a. effective.

3. Task

The goal of your analysis is to evaluate the option. To simplify the tasks, you don't have to consider tax in your calculations. You are required to complete the analysis (with the investment rate for his superannuation account is deterministic at 3.9% per annum). For the submission, you are required to submit your spreadsheet in Microsoft Excel. Your spreadsheet should include the following information:

• the development of William'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 William's wealth upon his retirement in the dollar value. Furthermore, you should submit a report with the following information
• summary of key information and findings;
• explanation of the limitations of your calculations;

Make sure that your spreadsheet should

a. include all the steps of calculations, including assumptions, inputs, intermediate calculations and outputs;

b. include a report. Be well structured and documented. Your report should assume that the audience is William and your task is to explain to him your calculation as a financial adviser;

c. be of no more than 1 page; (be either Microsoft word or PDF)