VBA code

Example:

Investor would like to invest 30% in Large stocks, 30% in Small stocks, 10% in International stocks, 20% in bonds and 10% in cash. Their investment horizon is long term, more than 10 years. Based on the data provided, the expected portfolio return is 9.96% and the portfolio standard deviation would be 13.1%. However, that is an expected return. You should also give a range….what could be the lowest return versus the best return. Finally, if they invest $10,000 today, how much would they have at the end of their investing period. A run of 0 simulations, for example, with an investment horizon of 5 years provided a range between $8,730 and $63,138. (100 simulations)

Points can be earned as follows:

1. Input data using single input boxes 6 points

a. Userform +3 points

2. Calculating the portfolio return 6 points

a. Using look-up function +1

3. Calculating the portfolio risk 5 points

a. Calculating the portfolio risk within the subroutine +3 points

4. Providing a range of low to high returns 5 points

a. Using a simulation, reading results into an array +3

5. Providing the ending investment value, single estimate. Use a MsgBox to display results. 10 points

a. Providing high and low range based on simulation +3

1970-2017 Long

Term

2008 - 2017

Intermediate

2013 - 2107 Short

term

Large Stocks10.50%8.50%15.80%

Small Stocks12.60%9.40%15.00%

Bonds8.30%6.10%3.20%

International Stocks 8.90%1.90%7.90%

Cash4.80%0.30%0.20%

Source: Morningstar

Historical Returns by Asset Class and Investment Period