Saving for Retirement $($SOLVE ON EXCEL$)$

ohn Morrow is ten years away from retirement. He has accumulated a $$100,000$ nest egg that he would like to invest for his golden years. Furthermore, he is confident that he can invest $$10,000$ more each year until retirement. He is curious about what kind of nest egg he can expect to have accumulated at retirement eleven years from now.

John plans to split his investments evenly among four investments: a Money Market Fund, a Income Fund, a Growth and Income Stock Fund, and an Aggressive Growth Fund. Based on past performance, John expects each of these funds to earn a return in each of the upcoming eleven years according to the distributions shown in the following table.

$\backslash$table$[[$Fund$,$Distribution,$],[$Money Market Fund,Uniform $($Minimum $=2\%,$ Maximum $=10\%),],[$Income Fund,Normal $($Mean $=7\%,$ Standard Deviation $=2\%)],[$Growth and Income Fund,Normal $($Mean $=5\%,$ Standard Deviation $=3\%),],[$Aggressive Growth Fund,Rate of Return,Probability$],[-1\mathrm{.}0\%,0\mathrm{.}15],[0\mathrm{.}0\%,0\mathrm{.}3],[,3\mathrm{.}0\%,0\mathrm{.}3],[,5\mathrm{.}5\%,0\mathrm{.}15],[,0\mathrm{.}1,]]$

Assume that the initial nest egg $($$$100,000)$ is distributed evenly among the four investment options $($$$25,000).$ Initial Investment

Assume the Year $1$ investment $($$$10,000)$ are made beginning in Year $1.$ in each fund$).$ The returns of each fund are allowed to accumulate $($i$.$e$.,$ are re$-$invested$)$ in the same fund and no redistribution will be done before retirement. Furthermore, nine additional investments of $$10,000$ will be made and split evenly among the four funds $($ $$2,500$ each$)$ at year $1,$ year $2,$dots, year $10.$ Annual Investment

A financial advisor has told John that he can retire comfortably if he can accumulate $$300,000$ after year $10$ to supplement his other sources of retirement income. Complete a $1000$ trial simulation for final total after Year $10$ ending. Use Cell E$4$ as your table reference.

The uncertain elements in this problem are the annual return of each investment over the next $10$ years $($Year $0$ through Year $10).$

Formulas of note:

Money Market Fund

Money Market End: money market start$*{?}^{**}$ money market return