I have done all the values but I dont know how to calculate for the monthly withdraw if the individual earns 4.5% return during retirement, and wants to have $350,000 remaining at the end of his retirement.

. 300 months IZ Ich . a years Worksheet Three -- Consider the impact of volatility on retirement planning using a simulation analysis to see 100 possible "outcomes for an individual saving for retirement where the monthly return is randomly distributed (instead of constant). Consider an individual that is currently 28-years old, has $60,000 to start a retirement account, and wants to retire at his 64 birthday (assume that the individual has just turned 28 and will retire at the end of year 63 (right before his 64 birthday). He is going to save (all savings are at the end of the month - except for the $60,000 which will be contributed initially) $175 per month for the first 5 years 60 menth 312 $225 per month for the next 6 years la $375 per month for the next 5 years - $700 per month for the next 10 years ago $1100 per month for the next 5 years 305 $1400 per month for the final 5 years 44-33 Assume all retirement savings are going into a Roth IRA, so we can ignore tax issues. The rate of return is going to be random 0.78% per month with a standard deviation of 4.45% per month during the first 25 years 39 0.61% per month with a standard deviation of 3.55% per month during the last 11 years. 132 432 In order to generate the random returns, set up 100 trials (1 per column) with 432 months (1 per row). For the first month, you will use the following formula to generate a return; -Lognorm.inv(rando, return, st. deviation) - 1 Where the return is 0.0078 (0.78%) and the st. deviation is 0.0445 (4.45%). Note that this is for the first 25 years (300 months) and you will need to change the return and standard deviation for the final 11 years (132 months). Once you have all of you random returns generated, you should "lock" them by using a copy and paste special values. =B11*(1+B32 fit 25 your After you have the returns, you can calculate how much the individual will have at retirement under cach of the 100 different random scenarios. Also, for each of the 100 ending retirement values, calculate the monthly withdraw if the individual earns a 4.5% return during retirement, wants to have $350,000 remaining at the end of his retirement, and withdraws from the Roth IRA at the BEGINNING of each month over 25 years. Create a summary table to show the following: A) The maximum total wealth accumulation and monthly retirement income from the 100 scenarios. B) The minimum total wealth accumulation and monthly retirement income from the 100 scenarios. C) The average (mean) total wealth accumulation and monthly retirement income from the 100 scenarios. D) The average (median) total wealth accumulation and the monthly retirement income from the 100 scenarios. E) The standard deviation of your total wealth accumulation and monthly retirement income from the 100 scenarios. (NOTE -A through E will all be dollar values) F) The number of times you reached retirement with less than $750,000. The number of times you reached retirement with $750,000-$1,250,000. H) The number of times you reached retirement with $1,250,000-$1,750,000. 1) The number of times you reached retirement with $1,750,000-$2,500,000 J) The number of times you reached retirement with $2,500,000-$3,000,000. K) The number of times you reached retirement with more than $3,000,000. Finally, create a pie graph to illustrate the results of F through K. The chart should be placed on a separate sheet within the spreadsheet and labeled (use the "Move Chart" command to move to its own sheet). Hint: You can use the COUNTIF formula to help you with F through K