PLEASE ANSWER USING EXCEL DATA ANALYSIS TOOLPAX. please no psi

Problem 1C Chapter: Problem: CH12 1C Live Well, Die Broke For investment advisors, a major consideration in planning for a client in retirement is the determination of a withdrawal amount that will provide the client with the funds necessary to maintain his or her desired standard of living throughout the client's remaining lifetime. If a client withdraws too much or if investment returns fall below expectations, there is a danger of either running out of funds or reducing the desired standard of living A sustainable retirement withdrawal is the inflation-adjusted monetary amount a client can withdraw periodically from his or her retirement funds for an assumed planning horizon This amount cannot be determined with complete certainty because of the random nature of investment returns. Usually, the sustainable retirement with drawal is determined by limiting the probability of running out of funds to some specified level, such as 5% The sustainable retirement withdrawal amount is typically expressed as a percentage of the initial value of the assets in the retirement portfolio but is actually the inflation-adjusted monetary amount that the client would like each year for living expenses Assume an investment advisor, Roy Dodson, is assisting a widowed client in determining a sustainable retirement withdrawal. The client is a 59-year-old woman who turns 60 in two months. She has $1,000,000 in a tax-deferred retirement account that will be the primary source of her retirement income. Roy has designed a portfolio for his client with returns he expects to be normally distributed with a mean of 8% and standard deviation of 2% Withdrawals will be made at the beginning of each year on the client's birthday Roy assumes that the inflation rate will be 3%, based on long-term historic data So if her withdrawal at the beginning of the first year is $40,000, her inflation-adjusted withdrawal at the beginning of the second year will be $41,200 her third year's withdrawal will be $42,436, and so on. For his initial analysis, Roy wants to assume his client will live until age 90. In consultation with his client, he also wants to limit the chance that she will run out of money before her death to a maximum of 5% a. What is the maximum amount Roy should advise his client to withdraw on her 60th birthday? If she lives until age 90, how much should the client expect to leave to her heirs? b. Roy is now concerned about basing his analysis on the assumption that his client will live to age 90. After all, she is healthy and might live to be 110, or she could be in a car accident and die at age 62. To account for this uncertainty in the client's age at death, Roy would like to model the client's remaining life expectancy as a random variable between 0 and 50 years that follows a lognormal distribution with a mean of 20 and standard deviation of 10 (rounded to the nearest integer). Under this assumption, what is the maximum amount Roy should advise his client to withdraw on her 60th birthday and how much should the client expect to leave to her heirs? (Hint Modify your spreadsheet to accommodate ages up to 110, and use a VLOOKUP) function to return the client's ending balance in her randomly determined year of death) c. Roy is pleased to now be modeling the uncertainty in his client's life expectancy But he is now curious about limiting to 59% the chance that his client will run out of money before her death. In particular, he is wondering how sensitive the sustainable withdrawal amount is to changes in this 5 % assumption. To answer this question, create an efficient frontier showing the maximum sustainable withdrawal amount as the chance of running out of money is varied from 196to 10 % How should Roy explain the meaning of this chart to his client? d. Suppose Roy's client has three children and wants there to be a 95% chance that they will each inherit at least $250,000 when she dies. Under this assumption, what is the maximum amount Roy should advise his client to withdraw on her 60th birthday, and how much should the client expect to leave to her heirs