REMEMBER: Include screen prints of your Excel models. PLEASE INCLUDE PHOTOS IN A COMPATIBLE FORM

Scenario

Your friend, a recent college graduate, has just landed her first job earning $60,000 per year. She is 25 years old, plans to work for 35 years, and wants to accumulate a portfolio valued at $2,000,000 for retirement. Currently, she plans to invest 6% of her annual salary to accomplish her portfolio goals. She is also making the following assumptions:

1. Her salary will increase at a rate of 5% annually.
2. The return on her investment portfolio will grow at a rate of 10% per year.

Questions

1. Create an investment planning model for your friend based on the information and assumptions given above. How much will her portfolio be valued at the end of year 20? And at the end of the 35 years? Based on the investment model, how many years does your friend need to invest at the given rate to reach at least the $2,000,000 goal for her portfolio?
2. Based on your answer to question 1, use Goal Seek (Review Appendix A if needed) to answer this question: What should the annual investment rate be for your friend to reach her desired portfolio value at the end of year 35?
3. Your friend is pleased with the results that you presented based on her basic information and assumptions. However, you explain to her that neither salaries nor investment returns increase at a constant rate annually and suggest the following revisions:

• Salary increase varies from 2% to 5% annually.
• Annual portfolio growth rate of 10% with a standard deviation of 5%

Your friend wants to know the results on her portfolio given the above, more realistic assumptions.

Create and run a simulation model based on the revised assumptions. For the Annual investment rate, use the rate you calculated in question 2 above. Run the simulation ONE (1) time only. How much will her portfolio be valued at the end of year 20? And at the end of the 35 years? Based on the simulation model, how many years does your friend need to invest at the given rate to reach at least the $2,000,000 goal for her portfolio?

1. Based on your answers to question 3, your friend wants to revise her portfolio value to approximately $2,500,000 at the end of year 35. Use Goal Seek to answer this question: What should be your friends Annual Investment Rate to potentially achieve her revised portfolio goal?

1. Comment on the expected returns in the early years (year 20) versus the expected returns in the later years.
2. Discuss how the investment planning model you developed can be used as a template to develop other investment models for businesses and individuals alike.

REMEMBER: Include screen prints of your Excel models in support of your analysis.