How do you solve this, problem 10-50 in Managerial Decision Modeling, in Excel?

Question

How do you solve this, problem 10-50 in Managerial Decision Modeling, in Excel?

Lynn Rogers (who just turned 30) currently earns $60,000 per year. At teh end of each calendar year, she plans to invest 10% of her annual income in her tax-deferred retirement account. Lynn expects her salary to grow between )% and 8% each year, following a discrete uniform distribution between these two rates. Based on historical market returns, she expects the tax-deffered account to return -5% and 20% in any given year, following a continuous uniform distribution between theses two rates. Use N replications of a simulation model to answer each of the following questions.

a) 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)?

b) If Lynn wants this probability to be over 95%, what should be her savings rate each year?

Explanation / Answer

(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% appoximate 16%

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.