Question 1

All calculations are to be rounded to 2 decimal places in the question.

You are an intern performing financial planning for a client named John. John is currently 30 years old. He plans to retire at the age of 65. In order to maintain his current lifestyle, he estimates that the current cost of living of S$2,500 per month will be sustained in the future. In order to save for his retirement, he is looking at an annuity plan which has an expected return of 4%. However, he is not sure how much he needs to deposit into the plan annually, so that by the time he retires, there will be sufficient cashflow from the annuity plan for his remaining life.

In order to create the cashflow model for John, you check the past inflation rate in Singapore and decide that the advisor can assume an inflation rate of 2%. You will also assume that John will live until the age of 82 years - the average life expectancy for Singaporeans - in the model.

Based on the inflation rate, what is the future annual spending when John retires at 65 years old?

(a) Demonstrate your understanding of Time Value of Money, by calculating his future annual spending when John retires at the age of 65, with an appropriate mathematical formula and verify your answer with an appropriate Excel formula.

(b) Develop a cashflow model on Excel, and assess how much financial assets he needs at 65 years old to support living until 82 years old? Assume, in the model, that there are 17 years of living cost to account for and that the model reflects the lumpsum the annuity plan gives John when he turns 65. Once the lumpsum is withdrawn from the annuity plan, the return on the lumpsum will be 0%. A cashflow model illustrates the cashflow in each period.

(c) In order to support the retirement plan, how much constant annual saving is required to be deposited into the annuity plan from now until John is 65 years old? You can assume that there are 35 years in all.

(d) If John desires to retire at 55, how much annual saving is required from now until the age of 55? Assume that the annuity provides a lumpsum withdrawal at the age of 55 and that he will start to spend from the age of 56 till the age of 82 (a total of 27 years). Once the lumpsum is withdrawn from the annuity plan, the return on the lumpsum will be 0%.

The annuity plan provides an option: John may remain invested after the age of 65, but at a lower return rate of 3.0%. He will be able to make a drawdown annually according to the inflation-adjusted living expenses. The balance in his annuity plan will become 0 when John is at age 82.

(e) Develop a cashflow model on Excel and calculate how much he needs to have in his annuity plan at the age of 65, and what constant amount he needs to deposit annually from the present when he is aged 30 until when he is aged 65. (Hint: You may use Excel's Goal Seek to seek the amount that is needed at year 65. The goal is that he will have balance of 0 in annuity plan at the age of 82.)