Simulation-Based Decision

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.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.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 decide

that you want a 95% or better probability that you will have at least

$1,000,000 at retirement. Use a two-way table to identify options for securing

that 95% likelihood. 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 (that satisfy the 95%

criteria) from your two-way data table and describe them below.


Option 1

Inputs (decisions)

Annual

contribution (%):

Expected

return (%):

Outcomes

Probability

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

 

Option 2

Inputs (decisions)

Annual

contribution (%):

Expected

return (%):

Outcomes

Probability

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

 

Section C (optional)

You

further consider your desire for a near-certain $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:

Get Help With a similar task to - Simulation-Based Decision

Login to view and/or buy answers.. or post an answer

Related Questions

Similar orders to Simulation-Based Decision
41
Views
1
Answers
STATS HW
#49 & #52...
52
Views
1
Answers
Stats HW
Problem 25 and 29, then the third file goes with 29 as it is a reference...
37
Views
1
Answers
MTH 154 Quantitative Reasoning - Buying New and Used Cars with APR rates
Use the rates that is associated with my name, Brian Maximo Cendejas that is under file "MTH154_Fall2020_CarInterest02.pdf". All information need to complete the project is under the "MTH 154_Project02_CarBuying.pdf "...
65
Views
1
Answers
Math 144 Assignment 2
Just to get all sections done correctly....