What is the total value of strategy

Question

Discussion:

Your next door neighbor, Scott Jansen, has a 12-year old daughter, and he wants to pay the tuition for her first year of college 6 years from now. The tuition for the first year will be $17,500. Scott has gone through his budget and finds that he can invest $200 per month for the next 6 years. Scott has opened accounts at two mutual funds. The first 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 fixed fraction of the $200 to each fund. An advisor from the first 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. The advisor 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 advisor from the second fund recommended just the opposite: invest 20% in the S&P 500 fund and 80% in T-bills. Treasury bills are backed by the United States 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.

Not knowing which advisor to believe, Scott has come to you for help.

a. It contains 261 monthly returns of the S&P 500 and Treasury bills. Suppose that in each of the next 72 months (6 years) it is equally likely that any of the historical returns will occur. Develop a spreadsheet model to simulate the two suggested investment strategies over the 6-year period. Plot the value of each strategy over time for a single iteration of the simulation.

b. What is the total value of each strategy after 6 years?

College data
Historical Returns
		S&P 500	T-Bill
Month	Yr/Mo	Total Return	Total Return
1	7001	-7.43%	0.60%
2	7002	5.86%	0.62%
3	7003	0.30%	0.57%
4	7004	-8.89%	0.50%
5	7005	-5.47%	0.53%
6	7006	-4.82%	0.58%
7	7007	7.52%	0.52%
8	7008	5.09%	0.53%
9	7009	3.47%	0.54%
10	7010	-0.97%	0.46%
11	7011	5.36%	0.46%
12	7012	5.84%	0.42%
13	7101	4.19%	0.38%
14	7102	1.41%	0.33%
15	7103	3.82%	0.30%
16	7104	3.77%	0.28%
17	7105	-3.67%	0.29%
18	7106	0.21%	0.37%
19	7107	-3.99%	0.40%
20	7108	4.12%	0.47%
21	7109	-0.56%	0.37%
22	7110	-4.04%	0.37%
23	7111	0.27%	0.37%
24	7112	8.77%	0.37%
25	7201	1.94%	0.29%
26	7202	2.99%	0.25%
27	7203	0.72%	0.27%
28	7204	0.57%	0.29%
29	7205	2.19%	0.30%
30	7206	-2.05%	0.29%
31	7207	0.36%	0.31%
32	7208	3.91%	0.29%
33	7209	-0.36%	0.34%
34	7210	1.07%	0.40%
35	7211	5.05%	0.37%
36	7212	1.31%	0.37%
37	7301	-1.59%	0.44%
38	7302	-3.33%	0.42%
39	7303	-0.02%	0.46%
40	7304	-3.95%	0.52%
41	7305	-1.39%	0.51%
42	7306	-0.51%	0.51%
43	7307	3.94%	0.64%
44	7308	-3.18%	0.70%
45	7309	4.15%	0.68%
46	7310	0.03%	0.65%
47	7311	-10.82%	0.56%
48	7312	1.83%	0.64%
49	7401	-0.85%	0.63%
50	7402	0.19%	0.58%
51	7403	-2.17%	0.56%
52	7404	-3.73%	0.75%
53	7405	-2.72%	0.75%
54	7406	-1.29%	0.60%
55	7407	-7.59%	0.70%
56	7408	-8.28%	0.60%
57	7409	-11.70%	0.81%
58	7410	16.57%	0.51%
59	7411	-4.48%	0.54%
60	7412	-1.77%	0.70%
61	7501	12.51%	0.58%
62	7502	6.74%	0.43%
63	7503	2.37%	0.41%
64	7504	4.93%	0.44%
65	7505	5.09%	0.44%
66	7506	4.62%	0.41%
67	7507	-6.59%	0.48%
68	7508	-1.44%	0.48%
69	7509	-3.28%	0.53%
70	7510	6.37%	0.56%
71	7511	3.13%	0.41%
72	7512	-0.96%	0.48%
73	7601	11.99%	0.47%
74	7602	-0.58%	0.34%
75	7603	3.26%	0.40%
76	7604	-0.99%	0.42%
77	7605	-0.73%	0.37%
78	7606	4.27%	0.43%
79	7607	-0.68%	0.47%
80	7608	0.14%	0.42%
81	7609	2.47%	0.44%
82	7610	-2.06%	0.41%
83	7611	-0.09%	0.40%
84	7612	5.40%	0.40%
85	7701	-4.89%	0.36%
86	7702	-1.51%	0.35%
87	7703	-1.19%	0.38%
88	7704	0.14%	0.38%
89	7705	-1.50%	0.37%
90	7706	4.75%	0.40%
91	7707	-1.51%	0.42%
92	7708	-1.33%	0.44%
93	7709	0.00%	0.43%
94	7710	-4.15%	0.49%
95	7711	3.70%	0.50%
96	7712	0.48%	0.49%
97	7801	-5.96%	0.49%
98	7802	-1.61%	0.46%
99	7803	2.76%	0.53%
100	7804	8.70%	0.54%
101	7805	1.36%	0.51%
102	7806	-1.52%	0.54%
103	7807	5.60%	0.56%
104	7808	3.40%	0.56%
105	7809	-0.48%	0.62%
106	7810	-8.91%	0.68%
107	7811	2.60%	0.70%
108	7812	1.72%	0.78%
109	7901	4.21%	0.77%
110	7902	-2.84%	0.73%
111	7903	5.75%	0.81%
112	7904	0.36%	0.80%
113	7905	-1.68%	0.82%
114	7906	4.10%	0.81%
115	7907	1.10%	0.77%
116	7908	6.11%	0.77%
117	7909	0.25%	0.83%
118	7910	-6.56%	0.87%
119	7911	5.14%	0.99%
120	7912	1.92%	0.95%
121	8001	6.10%	0.80%
122	8002	0.31%	0.89%
123	8003	-9.87%	1.21%
124	8004	4.29%	1.26%
125	8005	5.62%	0.81%
126	8006	2.96%	0.61%
127	8007	6.76%	0.53%
128	8008	1.31%	0.64%
129	8009	2.81%	0.75%
130	8010	1.87%	0.95%
131	8011	10.95%	0.96%
132	8012	-3.15%	1.31%
133	8101	-4.38%	1.04%
134	8102	2.08%	1.07%
135	8103	3.80%	1.21%
136	8104	-2.13%	1.08%
137	8105	0.62%	1.15%
138	8106	-0.80%	1.35%
139	8107	0.07%	1.24%
140	8108	-5.54%	1.28%
141	8109	-5.02%	1.24%
142	8110	5.28%	1.21%
143	8111	4.41%	1.07%
144	8112	-2.65%	0.87%
145	8201	-1.63%	0.80%
146	8202	-5.12%	0.92%
147	8203	-0.60%	0.98%
148	8204	4.14%	1.13%
149	8205	-2.88%	1.06%
150	8206	-1.74%	0.96%
151	8207	-2.15%	1.05%
152	8208	12.67%	0.76%
153	8209	1.10%	0.51%
154	8210	11.26%	0.59%
155	8211	4.38%	0.63%
156	8212	1.73%	0.67%
157	8301	3.48%	0.69%
158	8302	2.60%	0.62%
159	8303	3.65%	0.63%
160	8304	7.58%	0.71%
161	8305	-0.52%	0.69%
162	8306	3.82%	0.67%
163	8307	-3.13%	0.74%
164	8308	1.70%	0.76%
165	8309	1.36%	0.76%
166	8310	-1.34%	0.76%
167	8311	2.33%	0.70%
168	8312	-0.61%	0.73%
169	8401	-0.65%	0.76%
170	8402	-3.28%	0.71%
171	8403	1.71%	0.73%
172	8404	0.69%	0.81%
173	8405	-5.34%	0.78%
174	8406	2.21%	0.75%
175	8407	-1.43%	0.82%
176	8408	11.25%	0.83%
177	8409	0.02%	0.86%
178	8410	0.26%	1.00%
179	8411	-1.01%	0.73%
180	8412	2.53%	0.64%
181	8501	7.68%	0.65%
182	8502	1.37%	0.58%
183	8503	0.18%	0.62%
184	8504	-0.32%	0.72%
185	8505	6.15%	0.66%
186	8506	1.59%	0.55%
187	8507	-0.26%	0.62%
188	8508	-0.61%	0.55%
189	8509	-3.21%	0.60%
190	8510	4.47%	0.65%
191	8511	7.16%	0.61%
192	8512	4.67%	0.65%
193	8601	0.44%	0.56%
194	8602	7.61%	0.53%
195	8603	5.54%	0.60%
196	8604	-1.24%	0.52%
197	8605	5.49%	0.49%
198	8606	1.66%	0.52%
199	8607	-5.69%	0.52%
200	8608	7.48%	0.46%
201	8609	-8.22%	0.45%
202	8610	5.56%	0.46%
203	8611	2.56%	0.39%
204	8612	-2.64%	0.49%
205	8701	13.43%	0.42%
206	8702	4.13%	0.43%
207	8703	2.72%	0.47%
208	8704	-0.88%	0.44%
209	8705	1.03%	0.38%
210	8706	4.99%	0.48%
211	8707	4.98%	0.46%
212	8708	3.85%	0.47%
213	8709	-2.20%	0.45%
214	8710	-21.52%	0.60%
215	8711	-8.19%	0.35%
216	8712	7.38%	0.39%
217	8801	4.27%	0.29%
218	8802	4.70%	0.46%
219	8803	-3.02%	0.44%
220	8804	1.08%	0.46%
221	8805	0.78%	0.51%
222	8806	4.64%	0.49%
223	8807	-0.40%	0.51%
224	8808	-3.31%	0.59%
225	8809	4.24%	0.62%
226	8810	2.73%	0.61%
227	8811	-1.42%	0.57%
228	8812	1.81%	0.63%
229	8901	7.23%	0.55%
230	8902	-2.49%	0.61%
231	8903	2.36%	0.67%
232	8904	5.16%	0.67%
233	8905	4.02%	0.79%
234	8906	-0.54%	0.71%
235	8907	8.98%	0.70%
236	8908	1.93%	0.74%
237	8909	-0.39%	0.65%
238	8910	-2.33%	0.68%
239	8911	2.08%	0.69%
240	8912	2.36%	0.61%
241	9001	-6.71%	0.57%
242	9002	1.29%	0.57%
243	9003	2.63%	0.64%
244	9004	-2.47%	0.69%
245	9005	9.75%	0.68%
246	9006	-0.70%	0.63%
247	9007	-0.32%	0.68%
248	9008	-9.03%	0.66%
249	9009	-4.92%	0.60%
250	9010	-0.37%	0.68%
251	9011	6.44%	0.57%
252	9012	2.74%	0.60%
253	9101	4.42%	0.52%
254	9102	7.16%	0.48%
255	9103	2.38%	0.44%
256	9104	0.28%	0.53%
257	9105	4.28%	0.47%
258	9106	-4.57%	0.42%
259	9107	4.68%	0.49%
260	9108	2.35%	0.46%
261	9109	-1.64%	0.46%