Your next door neighbor, Scott Jansen, has a 12 year old daughter and he wants to pay the tuition for her rst year of college 6 years from now. The tuition for the rst year will be $17,500. Scott has gone through his budget and nds that he can invest $200 per month for the next 6 years. Scott has opened accounts at two mutual funds. The rst fund follows an investment strategy designed to match the return of the S&P 500. The second fund invests in short term Treasury bills. Both funds have very low fees. Scott has decided to follow a strategy in which he contributes a xed, fraction of the $200 to each fund. An adviser from the rst fund suggested that each month he invest 80% of the $200 in the S&P 500 fund and the other 20% in the T-bill fund. The adviser explained that the S&P 500 has averaged much larger returns than the T-bill fund. Even though stock returns are risky investments in the short run, the risk would be fairly minimal over the longer 6 year period. An adviser from the second fund recommended just the opposite: invest 20% in the S&P 500 fund and 80% in T-bills, he said. Treasury bills are backed by the U.S. government. If you follow this allocation, he said, your average return will be lower, but at least you will have enough to reach your $17,500 target in 6 years.

1. The spreadsheet contains 261 monthly returns of the S&P 500 and Treasury

bills from January 1970 through September 1991. (If you can find more recent data on the Web, feel free to use

it.) Suppose that in each of the next 72 months (6 years), it is equally likely that any of the historical returns will

occur. Set up a spreadsheet to simulate the two suggested investment strategies over the 6-year period. Make your

excel spreadsheet like a professional work with the documentation on it. Use the absolute reference and

relative reference and excel functions properly such that you use different formulas as little as possible.

Put the first 10 months simulation work with headings in your word file.

2. Plot the value of each strategy over time for one simulation trial.

3. Simulate 5,000 trials of the two strategies over the 6 year period. Create a histogram of the original fund values. Based

on your simulation results, which of the two strategies would you recommend (to reach your $17,500 target in 6

years)? Why?

4. Suppose that Scott needs to have $19,500 to pay for the first year's tuition. Based on the same simulation results,

which of the two strategies would you recommend now? Why?

5. Conduct a more simulation that he will invest X% of the $200 in the S&P 500 fund and the other (100 - X)% in

the T-bill fund in each month, for X = 0; 10; 20; _ _ _ 90; 100. What are your comments on this simulation results.

6. What other real world factors might be important to consider in designing the simulation and making a recommendation?

https://drive.google.com/file/d/0B3L56cHLotTeeE1ETzd4S21sN2s/view