Exercise 2: Retirement account (30 Points) 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 $225,000 in his retirement account (401(k) plan), and has invested another $85,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% (Note: Stock market returns need to be calculated on all the contributions for both the 401(k) account and the personal account). 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 $10,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.) Activity 1 (20 PTS): Go to worksheet Sensitivity analysis and complete the calculations in the Excel spreadsheet to calculate how Mr. Clark's total retirement and individual account will grow each year. You MUST use absolute cell references for the growth rates, market returns, etc. If you do not, the second step will not work correctly. Fill out the entire table from age 40 - 65 Activity 2 (10 PTS): Mr. Clark wants to know how his retirement 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%. That is, he wants an output of his final retirement like this: (The first row represents % increase in his earnings, and the first column represents the various market return scenarios.). The cell in pink (denoted First Cell below) represents the model that you will build in Activity 1 - Please write how his retirement account would look if earnings grow at 3% and the market returns 8% (fill in the yellow cell). Increase in earnings First Cell 2% 3% 4% 5% 6% 7% 8% 4% 6% 8% 10% 12% 15% Average Market Return 3 A1 X fx A D E G H K L M 1 Retirement Savings Assumptions 2 3 4 4 5 Current Earnings 6 Earnings Increase 7 Stock MKT return $175,000 3% Mr. Clark's Contribution To 401(k) Employer Contribution to 401(k) Additional savings Increase in add. Savings 6% 5% $10,000 8% 8 4% 9 10 Previous Year's Balance Mr. Clark's Contribution 401(k) Plan Employer Contribution Stock Market Earnings Previous Year Balance 101K Plan Tota Personal Account Additional Stock Market Savings Earnings Salary 401K Plan Total Acct Total Yearly total Mr. Clark's Age 40 41 42 43 44 45 46 47 48 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 39 Sensitivity Analysis 40 Increase in earnings 3% 4% 5% 6% 6% 7% 8% 41 42 43 44 45 6% 8% 8 10% 12% 15% 46 47 48 49 Exercise 2: Retirement account (30 Points) 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 $225,000 in his retirement account (401(k) plan), and has invested another $85,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% (Note: Stock market returns need to be calculated on all the contributions for both the 401(k) account and the personal account). 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 $10,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.) Activity 1 (20 PTS): Go to worksheet Sensitivity analysis and complete the calculations in the Excel spreadsheet to calculate how Mr. Clark's total retirement and individual account will grow each year. You MUST use absolute cell references for the growth rates, market returns, etc. If you do not, the second step will not work correctly. Fill out the entire table from age 40 - 65 Activity 2 (10 PTS): Mr. Clark wants to know how his retirement 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%. That is, he wants an output of his final retirement like this: (The first row represents % increase in his earnings, and the first column represents the various market return scenarios.). The cell in pink (denoted First Cell below) represents the model that you will build in Activity 1 - Please write how his retirement account would look if earnings grow at 3% and the market returns 8% (fill in the yellow cell). Increase in earnings First Cell 2% 3% 4% 5% 6% 7% 8% 4% 6% 8% 10% 12% 15% Average Market Return 3 A1 X fx A D E G H K L M 1 Retirement Savings Assumptions 2 3 4 4 5 Current Earnings 6 Earnings Increase 7 Stock MKT return $175,000 3% Mr. Clark's Contribution To 401(k) Employer Contribution to 401(k) Additional savings Increase in add. Savings 6% 5% $10,000 8% 8 4% 9 10 Previous Year's Balance Mr. Clark's Contribution 401(k) Plan Employer Contribution Stock Market Earnings Previous Year Balance 101K Plan Tota Personal Account Additional Stock Market Savings Earnings Salary 401K Plan Total Acct Total Yearly total Mr. Clark's Age 40 41 42 43 44 45 46 47 48 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 39 Sensitivity Analysis 40 Increase in earnings 3% 4% 5% 6% 6% 7% 8% 41 42 43 44 45 6% 8% 8 10% 12% 15% 46 47 48 49