Question 1 If your work involves with Excel spreadsheet, Please screenshot (or paste) Excel Answers into...

 

Transcribed Image Text:

Question 1 If your work involves with Excel spreadsheet, Please screenshot (or paste) Excel Answers into Word document for your group submission (40 Marks) After learning the Topic, The Time Value of Money, in this unit, you are interested in applying its principles and methods to the retirement plan of your friend, Nicole. Below is her personal set of information and macroeconomic conditions over her lifetime. Nicole is currently 40 years old and is planning to retire at age 65. She has $100,000 invested in a superannuation fund that currently yields 5% per annum, on average. It is expected that the long term inflation rate will be 3% per annum during Nicole's retirement. a) A well-established strategy for funding a retirement is spending 4% of the retirement savings in the first year of retirement over a 30-year period and adjusting that amount annually to keep pace with the expected inflation rate. Applying this strategy to Nicole's retirement and assume that she will continue to earn 5% annual return throughout her retirement, what is the actual proportion of the retirement savings at the beginning of the first year she can have access to according to the strategy? If Nicole would like to start with a $100,000 for her retirement, how much does she need to save by the time she retires? Briefly explain your working steps and show your formulas and calculations. b) As a rule of thumb, Nicole needs 70% of the income in her final year of work to maintain a comfortable standard of living for each year during her retirement. It should also grow annually to keep up with the expected inflation rate. Assume that she currently receives an after-tax annual salary of $72,000. She expects her salary will increase by 2% annually. If her annual superannuation return remains at 5% per annum, how much does she need to set aside at the end of each month from her salary before she retires? Briefly explain your working steps and show your formulas and calculations. Question 1 If your work involves with Excel spreadsheet, Please screenshot (or paste) Excel Answers into Word document for your group submission (40 Marks) After learning the Topic, The Time Value of Money, in this unit, you are interested in applying its principles and methods to the retirement plan of your friend, Nicole. Below is her personal set of information and macroeconomic conditions over her lifetime. Nicole is currently 40 years old and is planning to retire at age 65. She has $100,000 invested in a superannuation fund that currently yields 5% per annum, on average. It is expected that the long term inflation rate will be 3% per annum during Nicole's retirement. a) A well-established strategy for funding a retirement is spending 4% of the retirement savings in the first year of retirement over a 30-year period and adjusting that amount annually to keep pace with the expected inflation rate. Applying this strategy to Nicole's retirement and assume that she will continue to earn 5% annual return throughout her retirement, what is the actual proportion of the retirement savings at the beginning of the first year she can have access to according to the strategy? If Nicole would like to start with a $100,000 for her retirement, how much does she need to save by the time she retires? Briefly explain your working steps and show your formulas and calculations. b) As a rule of thumb, Nicole needs 70% of the income in her final year of work to maintain a comfortable standard of living for each year during her retirement. It should also grow annually to keep up with the expected inflation rate. Assume that she currently receives an after-tax annual salary of $72,000. She expects her salary will increase by 2% annually. If her annual superannuation return remains at 5% per annum, how much does she need to set aside at the end of each month from her salary before she retires? Briefly explain your working steps and show your formulas and calculations.