PART B: Retirement account (3 Activities, 10 points each)
Learning objectives: Become familiar with sensitivity analysis, goal seek, and setting up an Excel model.
You have joined one of the top business schools in the nation, so your older relative, Mr. Clark, wants some help to plan his retirement and to figure out how much he will have saved when he is 65. He is currently 40 years old, making $175,000 per year. He has $245,000 in his retirement account (401(k) plan) and has invested another $95,000 on his own in a personal account.
He expects that his earnings will increase at least 3% each year.
Conventional wisdom dictates that in the long run, the stock market returns an average of 8% per year. Therefore, he expects that his 401(k) and his personal stock account will have a net annual gain of 8%.
Mr. Clark contributes 6% of his salary to his 401(k) and his employer contributes another 5% of his salary.
On his personal account, Mr. Clark is confident that he can add $8,000 to his personal account this year and increase his annual contribution by 4% each year after.
He wants to know the total balance (401(k) and personal stock account) at the end of each year from now until he is 65 years old.
KEY ASSUMPTION: For simplicity, assume that the annual contributions to both the 401(k) account and the personal stock account are made at the beginning of the year. This way, the contributions will accrue returns for the entire year. (This isn’t true in the “real world,” but making this assumption significantly simplifies this model.)
(a) Go to the Excel worksheet “Retirement Savings” and complete the calculations on how Mr. Clark’s total retirement and individual account will grow each year.
In the Answers worksheet à Write down the total balance (401(k) and personal stock account) at the end of year when Mr. Clark is 65 years old.
(b) Conduct sensitivity analysis to answer the following question:
Mr. Clark wants to know how his total balance (401(k) and personal stock account) will look if his salary increases on average 2%, 3%, 4%, 5%, 6% or 7% each year until he is 65 and if the market returns 4%, 6%, 8%, 10%, 12% and an outrageous 15%.
(c) Now assume Mr. Clark has reached the age of 66 with the total balance in his bank account as the one you get from activity a.
At the beginning of each year, he puts all his money left in his account in some bond to receive a nominal 3% return.
Meanwhile, Mr. Clark starts to withdraw from the age of 66. Assume he withdraws at the end of each year. He can withdraw any amount but usually people tend to anchor to their annual salary before retirement. You can assume his withdrawal amount at the age of 66 is equal to some percentage of his salary at age 65[1]. However, starting from age 67, please assume the withdrawal increases by 4% from the previous year until age 95.
At the end of each year, Mr. Clark also decides to give his grandchild $3,000 each year. After his own withdrawal and giving to grandchild, he also likes to donate 0.5% of his total amount in his bank account left by the end of that year to charity.
Please use the Excel sheet “Withdrawal” to answer the following question:
How much Mr. Clark should withdraw at the age of 66 so that his money will last until he is 95 years old.
Hint: You can use Goal Seek (under What If analysis) such that the final amount left is $0.
You also need to figure out what numbers to put in the Assumption table.
In the Answers worksheet à Write down your finding (i.e., how much Mr. Clark should withdraw at the age of 66 so that his money will last until when he is 95 years old).
[1] Hint: Please use whatever percentage you’d like of his age-65 salary to begin the calculation, and your goal seek would be to see the best percentage for this in order to have zero at the end of age 95.
Solution
Instant Download on Payment
