Please use Excell and show screenshots of functions and spreadsheets.

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 a desired standard of living throughout the clients 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 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 withdrawal is determined by limiting the probability of running out of funds to some specified level, such a 2%. The sustainable retirement withdrawal amount typically is 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 that 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 will soon turn 60. She has $1,500,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 that he expects will each be normally distributed with a mean of 7.0% and a standard deviation of 2.5%. Withdrawals will be made at the beginning of each year on the clients birthday. Roy assumes that the inflation rate follows the uniform distribution with a minimum of 2% and a maximum of 5% based on long term historic data. For example, if her withdrawal at the beginning of the first year is $100,000 and the inflation rate is 3%, her inflation-adjusted withdrawal at the beginning of the second year will be $103,000.

a. For his initial analysis, Roy wants to assume that 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 2%. What is the maximum amount that Roy should advise his client to withdraw on her 60th birthday? How much should the client expect to leave to her heirs? Construct a histogram displaying the distribution of expected inheritance for the recommended withdrawal amount.

b. Roy is now concerned with his analysis on the assumption that his client will live until age 90. After all, she is healthy and might live many years beyond 90, or she could be in a car accident and die at age 62. To account for this uncertainty in the clients age at death, Roy would like to model his clients remaining life expectancy as a random variable between 0 and 45 years that follows the lognormal distribution with a mean of 20 and a standard deviation of 10 (rounded to the nearest integer). Under this assumption, what is the maximum amount that Roy should advise his client to withdraw on her 60th birthday that limits the chance that she will run out of money before her death to a maximum of 2%? How much should the client expect to leave to her heirs?