90.2K

Verified Solution

Link Copied!

This is all I was given, but I think it has to be a Monte Carlo Simulation. I just don't know how to proceed. The image provided is the excel document they gave us to work from: In this part of the course project, you will create and use a simulation to analyze a cash flow situation with two random variables. You will then extend your use of the simulation to examine outcomes based on manipulating two decision variables. This project part has two required sections and a third optional section.

Scenario:

You are doing some retirement planning to make sure you have sufficient funds for retirement. You plan to retire in $15$ years. You have already saved $$250,000$ toward retirement. Presently, you are making $$100,000$ per year and contributing $10\%$ of your salary to retirement. You estimate that you will receive annual salary adjustments of $2\mathrm{.}5\%$ over the next $15$ years and will be able to generate an $8\%$ annual return on your investments. You feel relieved because according to your estimates, you will have over $$1,100,000$ at the time you retire.

But you are wondering about the impact of uncertainty on your retirement plans. While on average your raises have been $2\mathrm{.}5\%,$ they have been as low as $0\%$ some years and as high as $5\%$ other years. While average returns on your investments have been $8\%,$ they have a lot of uncertainty with a standard deviation of $4\%.$

Using the spreadsheet model as a template, build a simulation model to help investigate the impact of uncertainty on your retirement. Assume your annual salary adjustments are uniformly distributed between $0\%$ and $5\%,$ and that returns are normally distributed with a mean of $8\%$ and standard deviation of $4\%.$

To satisfy this part of the project, you will:

$$ Create an Excel workbook with a simulation of your retirement income

$$ Answer questions related to the baseline assumptions in the scenario

$$ Modify your Excel workbook to incorporate decision variables and then submit that modified workbook to your instructor

$$ Answer additional questions based on manipulating the decision variables you have included in your workbook

Section A

Working with the assumptions in the baseline scenario, create a simulation that projects the balance in your retirement account after $15$ years. An Excel template is provided on the course project page to help you get started. Be sure to include sufficient iterations in your simulation to generate a reliable sample.

Assuming you retire in $15$ years, use your simulation to answer the following questions:

$1.$ What is the average simulated value of your retirement account after $15$ years?

$2.$ What is the probability that you have more than $$1,000,000$ at retirement?

$3.$ What is the probability that you have less than $$750,000$ at retirement?

Section B

After consulting with your financial advisor, you decide that you are not comfortable with your existing retirement savings plan, and you resolve to improve your prospects. You consider two options:

$$ Increasing your retirement savings contribution.

$$ Seeking out higher$-$risk investments with a higher average rate of return.

$($For the purposes of this scenario, assume that the standard deviation for the rate of return is equal to the mean.$)$

You make it your goal to have a total portfolio value of at least $$1,000,000$ at retirement. Use a two$-$way table to identify options to achieve this outcome. Your two$-$way table should look at the impacts of increasing your annual contribution and using higher return $($and riskier$)$ investment alternatives. Select two possible options from your two$-$way data table and describe them below. Both options must have a total portfolio value greater than $$1,000,000.$ It will also be interesting for you to note the probability of achieving the goal; take some time to consider options with higher odds of achieving your retirement goal.

Option $1$

Inputs $($decisions$)$

Annual contribution $(\%)$:

Expected return $(\%)$:

Outcomes

Total portfolio value

Option $2$

Inputs $($decisions$)$

Annual contribution $(\%)$:

Expected return $(\%)$:

Outcomes

Total portfolio value

Section C $($optional$)$

You further consider your desire for a $$1,000,000$ retirement nest egg. Given your cash flow needs, investments with a higher return seem to be by far the more attractive option, but you are also concerned about the downside risk.

Create a second two$-$way table to assess the risk that you will have less than $$750,000$ at retirement, again assuming that the standard deviation of return for the investments equals the mean return. Now that you are directly measuring the downside risk, you are less concerned about the probability of having $$1,000,000.$ List one option for which you have an $80\%$ or better probability of having at least $$1,000,000$ and at most a $1\%$ chance of having less than $$750,000.$ List the parameters here:

Inputs $($decisions$)$

Annual contribution $(\%)$:

Expected return $(\%)$:

Outcomes

Probability of at least $$1,000,000$ in retirement fund:

Probability of less than $$750,000$ in retirement fund:

Answer & Explanation Solved by verified expert

See Answer