Question 1 (To be completed in Word and Excel document). Please screenshot Excel Answers into Word document) You are a financial planner advising your clients on their retirement plans. John, one of your clients, has come to you on how much money he needs to set aside for his retirement, with the following set of information.

John currently has $50,000 in savings and has an after-tax annual income of $60,000. He expects that his annual income will grow at an average annual rate of 2% and plans to work for another 35 years before he retires at age 65.

You also have the following information that is needed to provide John with a reasonable estimate for his retirement. A life expectancy of 85 should apply to John according to the Australian Institute of Health and Welfare. Due to the foreseeable low-interest-rate environment, the average return of retirement funds on stocks and bonds is expected to be 5% per annum. As a rule of thumb, John needs 70% of the income in his final year of work to maintain a comfortable standard of living for each year after he retires.

a) Work out how much John needs to set aside the same amount at the end of each working year so that he has saved enough to fund his retirement, starting from the beginning of the first year in retirement. Briefly explain your working steps and show your formulas and calculations.

b) To maintain the same standard of living throughout his retirement, John should increase his annual retirement income by 2% per year, the average annual inflation rate. Re-calculate how much John needs to set aside at the end of each working year. Briefly explain your working steps and show your formulas and calculations.

c) John thinks that he can comfortably skip a couple of coffees that cost him $10 a week and contributes to his retirement fund. By how much will his annual retirement income increase from his coffee sacrifice? Briefly explain your working steps and show your formulas and calculations.