Assignment 5 Fall 2023

.pdf

School

Embry-Riddle Aeronautical University *

*We aren’t endorsed by this school

Course

MGMT 428

Subject

Industrial Engineering

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by studybro82

1 Assignment 5 Please follow the format of the sample report for simulation models. You will need to submit both a report and the accompanying Excel spreadsheet. Please include screenshots of your spreadsheet model and the frequency charts and statistics table of the forecast cells in your report, including the formula to compute/simulate key quantities of interest. Problem 1. Suppose you currently have a portfolio of three stocks, A, B, and C. You own 500 shares of A, 300 of B, and 1000 of C. The current share prices are $42.76, $81.33, and $58.22, respectively. You plan to hold this portfolio for at least a year. During the coming year, economists have predicted that the national economy will be awful, stable, or great with probabilities 0.2, 0.5, and 0.3. Given the state of the economy, the returns (one-year percentage changes) of the three stocks are independent and normally distributed. However, the means and standard deviations of these returns depend on the state of the economy, as indicated in the table below. 1. Simulate the value of the portfolio and the portfolio return in the next year by randomly generating 10000 samples. 2. What is the probability that your will have a return at least 25%? Can you provide a 95% (two-sided) confidence interval of this probability Problem 2. 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 re maining 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 withdrawal is determined by limiting the probability of running out of funds to some State of Economy A B C A B C Awful -30% -25% -15% 17% 10% 12% Stable -3% 4% 8% 10% 8% 6% Great 20% 25% 22% 15% 10% 10% Means Standard deviations

2 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 expense. Assume an investment advisor, Rachel Lee, is assisting a client in determining a sustainable retirement withdrawal. The client is a 59 year-old man who turns 60 in two months. He has $2,000,000 in a tax-deferred retirement account that will be the primary source of his retirement income. Rachel has designed a portfolio for her client with returns she expects to be normally distributed with a mean of 8% and standard deviation of 3%. Withdrawals will be made at the beginning of each year on the client’s birthday. Rachel assumes that the inflation rate will be 3%, based on long-term historical data. So if the client’s withdrawal at the beginning of the first year is $40,000, his inflation-adjusted withdrawal at the beginning of the second year will be $41,200, and third year’s withdrawal will be $42,436, and so on. To account for uncertainty i n the client’s age at death, Rachel woul d like to model the client’s remaining life expectancy as a random variable between 0 and 40 years that follows a lognormal distribution with a mean of 20 and standard deviation of 8 (rounded to the nearest integer). This Lognormal distribution can be generated using the formula =round(CB.lognormal(20,10),0), but you should also make sure the remaining life expectancy is between 0 and 40 years. If Rachel advises her client to withdraw $95,000 on his 60th birthday, what is the probability that he will run out of money before his death? What is the maximum amount her client should withdraw on his 60 th birthday so that the probability he will run of money before his death is no more than 5%?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help