How do i set up the simulation model to answer the following questions? Lynn Rogers (who just turned 30) currently earns $60,000 per year. At the end of each calendar year, she plans to invest 10% of her annual income in a tax-deferred retirement account. Lynn expects her salary to grow between 0% and 8% each year, following a discrete uniform distribution between these two rates. Based on historical market returns, she expects the tax-deferred account to return between 5% and 20% in any given year, following a continuous uniform distribution between these two rates. Use N replications of a simulation model to answer each of the following questions. What is the probability that Lynn will have in excess of $1 million in this account when she turns 60 (i.e., in 30 years)? If Lynn wants this probability to be over 95%, what should be her savings rate each year?

How do I set this up in excel? what is the simulation model? Thank you!

Simluated using R (code below) for N = 10,000 replications:

N <- 10000 savings <- rep(0, N) savings_rate <- 10 for (j in 1:N) { income <- 60000 income_growth <- runif(30, 0, 8) investment_return <- runif(30, -5, 20) for (i in 1:30) { investment <- income * savings_rate / 100 savings[j] = (savings[j] + investment) * (1 + investment_return[i]/100) income <- income * ( 1 + income_growth[i]/100) } }

# p = Probability that Lynn will have in excess of $1 million in 30 years p = sum(savings>1000000)/N

(a)

Probability that Lynn will have in excess of $1 million in this account in 30 years = 0.47 or 47%

(b)

To find the savings rate for this probability to be over 95%, the simulation can be run by varying the savings rate manually and finding the minimum savings rate which gives the probability of more than 95% every time.

This savings rate comes to be 15.4%

So, if Lynn wants this probability to be over 95%, her savings rate each year should be 15.4%