The HR department at a certain company was asked to develop a financial planning model that would help employees address how much their portfolios would be worth in 10 years, 20 years, and when they retire. John was asked to lead this effort and decided to begin by developing a financial plan for himself. John is making $34,000/year at the age of 25. After 2 years of contribution to the company retirement plan and receiving a small inheritance, John has accumulated a portfolio valued at $14,500. John plans to work for 30 more years and hopes to accumulate a portfolio valued at $1,000,000. Is this possible?
Assume the following:
1. 5% salary growth is reasonable.
2. He plans to contribute 4% of his salary.
3. 10% annual portfolio growth seems reasonable.
4. Contributions occur monthly throughout the year.
Develop an Excel worksheet and calculate the value at the end of 5 years.
John projected he could save around $691,500 after 30 more years of service. What would his alternatives be if he wants to make $1,000,000? John showed this to his boss who made the following observations.
1. Salary growth should not be constant. Should vary from 0 to 10% with a uniform probability distribution.
2. Annual portfolio growth rate should be approximated by a normal probability distribution with a mean of 10% and a standard deviation of 5%
Develop an Excel worksheet with this new information.
John is willing to work 5 more years. What is the result of that decision?