1 Assignment 10: retirement Suppose you retire with $1million at age of 65. 2 Current wealth ($) 1,000,000 You plan to invest your retirement into a mixture of a risky fund and a fixed income fund, where the return of the risky fund follows a normal distribution. 3 Risk-free rate 6% You plans to withdraw $150,000 at the end of every year from this accout. 4 Parameters of risky investment 5 Expected annual return 8% To prevent overwithdrawal at the end, you put a safety cushion of 3 If balance of the retirement fund at the end of year, before annual withdrawal is less than 6 Standard deviation of return 20% 3 times the planned annual withdrawal amount, you withdraw one-third of the remaining balance. (Hint: use IF function) 7 Proportion invested in risky 70% 8 Safety cushion 3 You want to find out how much you are left at the age of 75. 1. Simulate one time to find out the remaining balance at the age of 75 ( 10 years later) 2. Simulate 100 times, find out average, standard deviation, along with probabilty of the remaining fund is positive. 9 Annual withdrawal 10 150,000 Output: Average Sigma Negative bequest Below: Data table Balance Year Balance beginning of year Invested in Invested in risky bonds 11 bf withdrawal Ret. on risky fund end of year withdraw Left at end al of 10 years Simulation 12 1 700,000 300,000 NORMSINV() 150,000 1 13 2 2 14 15 16 17 18 19 20 21 34567822 3 4 5 6 7 8 9 9 10 10 22 0 11 23 12 24 13 25 14 26 15 27 16 28 17 29 18