Financial Modeling VBA Project:

Overall objective: make a VBA subroutine that will collect input data from user on asset allocation, and with then calculate an expected return for the specified investment horizon. User will also be informed of return variation based on historical risk measures.

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, this 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 10 0 simulations, for example, with an investment horizon of 5 years provided a range between $8,730 and $63,138. (100 simulations)

Historical Returns by Asset Class and Investment Period 1970-2017 Long 2008 - 2017 2013 - 2107 Short Term Intermediate term Large Stocks 10.50% 8.50% 15.80% Small Stocks 12.60% 9.40% 15.00% Bonds 8.30% 6.10% 3.20% International Stocks 8.90% 1.90% 7.90% Cash 4.80% 0.30% 0.20% Source: Morningstar