Question

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 adviser 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
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 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 adviser to believe, Scott has
come to you for help.
Questions
1. The file Investing for College.xlsx 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. Develop a spreadsheet
model to simulate the two suggested investment

The excel information is provided below

The College Fund Simulation

Historical Return Table:

Month Year/Month S&P 500 Total Return T-bill 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%

strategies over the 6-year period. Plot
the value of each strategy over time for a single
iteration of the simulation. What is the
total value of each strategy after 6 years? Do
either of the strategies reach the target?

Answer 1

This Homework Help Question: "Case Study" No answers yet.

Be the First!