Reference no: EM131365440
Assignment -
In this assignment you will use a Monte Carlo simulation to investigate the effect of randomness in returns on an individual's future retirement wealth.
Saving for Retirement
Assume an investor begins saving for retirement at age 25 and retires at age 65. Each year, she contributes $5, 000 to her retirement account and her employer matches this contribution for a total of $10, 000 in annual savings. To keep things simple, assume that each of the 40 annual payments is added to the account at the end the calendar year.1 Savings are invested as follows: 50% in a broad stock market index and 50% in T-Bills.
Your task is to compute the accumulated real retirement savings (at age 65) for different return realizations. As explained below, you will generate returns using a Monte Carlo simulation. On Blackboard, you will find an Excel file containing historical net returns on the S&P 500, 3-month T-bills and the CPI from 1928 to 2013. The return on the CPI serves as a measure of inflation.
STEPS:
1. Compute the annual real return on the 50/50 portfolio for each year in the sample. The resulting set of 86 portfolio returns represents the empirical distribution. These are the returns investors historically realized when investing in a 50/50 mix of stocks and T-bills over this time period.
2. We will use the historical data to access what may happen in the future via a Monte Carlo simulation. To generate a possible path of future returns, draw 40 times with replacement from the empirical distribution. Assuming the historical returns are located in the cell range H11:H96, a random draw can be generated with = INDEX(H11 : H96, RANDBETWEEN(1, 86))
The set of 40 draws you generated can be viewed as one scenario of what may happen in the next 40 years.
3. Using the simulated return path, compute the investor's wealth at age 65.
4. Repeat steps two and three 1, 000 times. The most efficient way of doing so in Excel is to use a data table. An example is contained in the Excel file "Monte Carlo Simulation Example" on Blackboard.
QUESTIONS:
A. Report the mean and standard deviation of the portfolio returns computed in step one.
B. Report the mean, standard deviation, 25th and 75th percentiles, minimum, maximum as well as a histogram of the 1, 000 values you generated for the wealth at age 65. Interpret each of these statistics, i.e. explain in words what they tell you in the context of the example.
C. Assuming a 50/50 mix of both assets, what amount would the investor need to save annually such that her real retirement wealth at at least $1m with a probability of 75%? Assume that the employer matches the investor's contribution. [Hint: (1) To find this number, create an input cell for the annual savings (sum of the investor's and her employer's contributions) and use trial-and-error to determine the required amount. (2) The number you find will only be approximate because of simulation noise - that is ok!]
D. Assuming annual savings of $10,000 (including the employer's contribution), what mix of the two assets ensures that the investor's savings amount to $1.5m on average? How do the standard deviation and the minimum savings change in this case relative to the baseline scenario of a 50/50 mix? [Hint: (1) To find the necessary mix, create an input cell for the asset mix and use trial-and-error to determine the required amount. (2) The number you find will only be approximate because of simulation noise - that is ok!]
Attachment:- Assignment Examples.rar
|
How can the marketer of a nicotine patch a device
: How can the marketer of a nicotine patch a device that assists individuals to stop smoking) use the theory of trying to segment its market?
|
|
Identify elements of a contract in the fact pattern
: A contract has three key elements: offer, acceptance, and consideration. If these three elements are not present, a contract may not exist. Sometimes, the presence of these elements can be elusive. For example, the mental intent of the parties may..
|
|
Consumer attitudes towards the companys brand
: Explain how the product manager of a breakfast cereal might change consumer attitudes towards the company's brand by changing beliefs about the brand, changing beliefs about competing brands.
|
|
Please view the film ethnic notions
: Please view the film Ethnic Notions. After viewing the film, I would like for us to think about how African Americans are portrayed and perceived in American culture today.
|
|
Compute the accumulated real retirement savings
: FIN 421 - SPRING 2017 Assignment. Your task is to compute the accumulated real retirement savings (at age 65) for different return realizations. As explained below, you will generate returns using a Monte Carlo simulation
|
|
How organisation can use given strategies to change
: Assume that Greenpeace is planning an advertising campaign that encourages people to switch from private cars to public transport. Give examples of how the organisation can use the given strategies to change commuters' attitudes:
|
|
What are the pharmacists responsibilities to a patient
: Patients receive care from many professions. These include x-ray technicians, laboratory technicians, physical therapists, nutritionists, and paramedics. Even though these professionals are not doctors they are still held to a higher standard beca..
|
|
What is its effect on w depand ids
: Can any gate voltage of the opposite sign to (b) be applied to the gate without producing expression gate current? What is its effect on W depand Ids?
|
|
Would a regulatory quota be a good solution
: Why would it be advantageous for a company to have equal representation on their corporate board - Would a regulatory quota be a good solution? Why or why not and would a regulatory quota be a good solution?
|