Question

Mary and David are planning for their wedding ceremony one and a half year from now. They were advised by the wedding planner that the wedding cost should be around USD 28,000.This couple has gone through their budget and found that they can invest USD 300 per weekfrom the freelance income starting from this week. This couple has opened accounts at The Tracker Fund of Hong Kong and China Government Bond. The first fund follows an investment strategy designed to match the return of the Blue Chip in Hong Kong. The second fund invests in long-term Bond in China.  The fees for both funds are very low.

The couple has decided to follow a strategy in which they contribute a fixed fraction of the USD 300 to each investment. A financial consultant from an investment bank suggested them to invest 80% of the USD 300 each week in The Tracker Fund of Hong Kong and the remaining 20% in the China Government Bond.The consultant explained that The Tracker Fund of Hong Kong has averaged larger returns than the China Government Bond. Even though stock returns are risky investments in the short run, the risk would be fairly minimal in the historical record. However, another financial consultant recommended just the opposite: invest 20% in The Tracker Fund of Hong Kong and the remaining 80% in the China Government Bond,he said, China Government Bond is backed by the China government. If you follow this allocation, he said, your average return will be lower, but at least you can reach your USD 28,000 target in one and a half year from now.

Not knowing which consultant to believe, the couple has come to you for help.

Questions:

The spreadsheet Marriage.xlsx contains 261 weekly returns of The Tracker Fund of  

Hong Kong and the China Government Bond from the first week of January 2012 to the first week of January 2017. In each of the next one and a half year from now, it is . Set up a spreadsheet to simulate the two suggested investment strategies over the above investment period. Plot the value of each strategy over time for one simulation trial. What was the total value of each strategy one and a half year from now?         

Did either of the strategies reach the target? (non-random number)

Simulate 200 trials of the two suggested investment strategies over the investment period. Create a histogram of the final investment values. Based on your simulation results, which of the two suggested investment strategies would you recommend? Why?   (Random number)

Suppose the couple needs to have USD 35,000 for the expense for their marriage. Based on the same simulation results, which of the two strategies would you recommend now? Why?

Week	Return (Blue Chip)	Return (Bond)
1	-0.00637362	0.006863729
2	0.035806558	0.006046148
3	-0.010874714	0.006647246
4	-0.008032959	0.007129663
5	0.046948305	0.00668351
6	-0.007513179	0.007147246
7	0.001550548	0.006142851
8	0.027091182	0.005819774
9	-0.022585692	0.006542851
10	-0.001004395	0.005723071
11	0.059304336	0.005421973
12	-0.053779067	0.005764829
13	-0.016607676	0.00571428
14	-0.013056031	0.00505494
15	0.001507691	0.00494505
16	-0.016454929	0.005104391
17	0.05217797	0.004290106
18	-0.016601082	0.005356039
19	-0.014571414	0.004561534
20	1.75824E-05	0.004740655
21	-0.045645009	0.005171423
22	0.040652707	0.004880215
23	0.005272522	0.004131864
24	-0.065540594	0.005295599
25	-0.017742839	0.00502527
26	0.030338431	0.005176918
27	0.095613091	0.004968127
28	0.014980205	0.00656373
29	-0.016748335	0.005787906
30	0.061484554	0.00610439
31	0.037361501	0.004503292
32	-0.005297797	0.005328566
33	-0.097865836	0.005143951
34	0.028560411	0.00370659
35	0.018888992	0.004414281
36	0.046307646	0.004608787
37	-0.031197771	0.004114282
38	0.063197739	0.004778017
39	-0.006103291	0.005116478
40	-0.044362593	0.004624171
41	-0.081701017	0.004799995
42	0.064366969	0.004487908
43	0.003315381	0.004354941
44	-0.097715287	0.004442853
45	-0.060120819	0.003949447
46	-0.052971376	0.003861535
47	0.082659258	0.00413626
48	0.055895549	0.00412637
49	0.038148314	0.004081315
50	-0.010701088	0.004380215
51	0.058872469	0.004582413
52	0.064144991	0.004851644
53	0.046030723	0.004761534
54	0.015472512	0.00541428
55	0.042025233	0.005495599
56	0.041409849	0.005348346
57	-0.040382377	0.005378017
58	0.002316481	0.005052742
59	-0.043868088	0.005810983
60	0.045298856	0.005886807
61	-0.006103291	0.005591203
62	-0.044362593	0.005899994
63	0.002915382	0.00614395
64	0.096339464	0.006098895
65	0.021313166	0.006801092
66	0.032880187	0.007479113
67	0.007939553	0.007691201
68	0.006252741	0.008597794
69	0.024086789	0.008479112
70	-0.022573604	0.008063728
71	0.003999996	0.008936255
72	0.042977979	0.008745046
73	-0.003957139	0.008967024
74	0.011729659	0.008905486
75	0.055447197	0.008415376
76	0.014410975	0.008429662
77	-0.017499983	0.014070316
78	-0.036557106	0.013671415
79	-0.000196703	0.013246141
80	-0.0433571	0.011731856
81	-0.097865836	0.009602188
82	0.028560411	0.008745046
83	0.018888992	0.010151638
84	0.046307646	0.010771418
85	-0.031197771	0.012408779
86	0.063197739	0.011632955
87	0.003967029	0.01051977
88	-0.018483498	0.011546142
89	0.045043911	0.008376915
90	0.012032955	0.00562417
91	0.067098834	0.006484609
92	0.002780217	0.006969224
93	-0.072054873	0.007404388
94	0.056494449	0.00754285
95	0.021109869	0.008359332
96	0.067065867	0.008363728
97	0.003373623	0.007730762
98	-0.108417474	0.007987904
99	0.047175777	0.008339552
100	0.061769169	0.007842849
101	0.032494473	0.007999992
102	0.07428564	0.008943947
103	0.014406579	0.008612079
104	0.030835134	0.008279113
105	0.020494485	0.008994497
106	0.120362517	0.009127463
107	-0.034626339	0.009438452
108	-0.04813182	0.010941747
109	0.02285712	0.008059333
110	-0.097865836	0.007061531
111	0.028560411	0.007114279
112	0.018888992	0.006352741
113	0.046307646	0.006769224
114	-0.031197771	0.007870322
115	0.063197739	0.007290103
116	0.003967029	0.006094499
117	-0.018483498	0.006863729
118	0.045043911	0.006046148
119	0.012032955	0.006647246
120	0.067098834	0.007129663
121	0.002780217	0.00668351
122	-0.072054873	0.007147246
123	0.056494449	0.006142851
124	0.021109869	0.005819774
125	0.067065867	0.00505494
126	0.003373623	0.00494505
127	-0.108417474	0.005098352
128	0.047175777	0.00428503
129	0.061769169	0.005349702
130	0.032494473	0.004556138
131	0.07428564	0.004735046
132	0.014406579	0.005165306
133	0.030835134	0.004874442
134	0.020494485	0.004126976
135	0.120362517	0.005289334
136	-0.034626339	0.005019325
137	-0.04813182	0.005170794
138	0.02285712	0.00496225
139	0.0417582	0.006555965
140	-0.02340657	0.003788915
141	0.00681318	0.004296006
142	-0.0087912	0.003229139
143	0.00076923	0.005000666
144	-0.06087906	0.004837123
145	-0.05516478	0.005066522
146	0.05802192	0.005546173
147	0.04846149	0.005326653
148	-0.02912085	0.005567027
149	-0.01791207	0.006517549
150	-0.05626368	0.006768899
151	-0.0065934	0.006052166
152	0.04549446	0.006729386
153	-0.03164832	0.007360506
154	-0.01912086	0.007406605
155	-0.02362635	0.008641405
156	0.13923063	0.007785277
157	0.0120879	0.007633808
158	0.12373614	0.008113459
159	0.04813182	0.007183792
160	0.01901097	0.007425264
161	0.03824172	0.007536122
162	0.0285714	0.006661334
163	0.04010985	0.006223392
164	0.08329662	0.00623327
165	-0.00571428	0.007069642
166	0.04197798	0.007543805
167	-0.03439557	0.00743185
168	0.0186813	0.006861098
169	0.12362625	0.00743185
170	0.00021978	0.007213427
171	0.00285714	0.006568038
172	-0.01109889	0.007483437
173	0.02780217	0.006202538
174	0.08439552	0.006573526
175	0.01505493	0.005682275
176	0.00197802	0.005232259
177	-0.00351648	0.004819562
178	0.06758235	0.005289334
179	0.01747251	0.005019325
180	-0.00285714	0.005170794
181	-0.00670329	0.00496225
182	-0.03527469	0.006555965
183	0.04912083	0.003788915
184	0.07868124	0.004296006
185	0.05131863	0.003229139
186	0.00483516	0.005000666
187	0.08352736	0.004837123
188	0.06080704	0.005066522
189	-0.01361024	0.005546173
190	0.06025824	0.005326653
191	0.01822016	0.005567027
192	-0.06245344	0.006517549
193	0.02809856	0.006768899
194	-0.02897664	0.006696458
195	0.14740768	0.006214611
196	0.04533088	0.006959882
197	0.02985472	0.006052166
198	-0.00965888	0.006729386
199	0.01130528	0.007360506
200	0.05477024	0.007406605
201	0.05466048	0.008641405
202	0.0422576	0.007785277
203	-0.0241472	0.007633808
204	-0.23620352	0.008113459
205	-0.08989344	0.007183792
206	0.08100288	0.007425264
207	0.04686752	0.007536122
208	0.0515872	0.006661334
209	-0.03314752	0.006223392
210	0.01185408	0.00623327
211	0.00856128	0.007069642
212	0.05092864	0.007543805
213	-0.0043904	0.00743185
214	-0.03633056	0.006861098
215	0.04653824	0.00743185
216	0.02996448	0.007213427
217	-0.01558592	0.006568038
218	0.01986656	0.007483437
219	0.07935648	0.006202538
220	-0.02733024	0.006573526
221	0.02590336	0.005682275
222	0.05663616	0.005232259
223	0.04412352	0.004819562
224	-0.00592704	0.005855696
225	0.09856448	0.00518177
226	0.02118368	0.00457809
227	-0.00428064	0.005360678
228	-0.02557408	0.005059936
229	0.02283008	0.005002861
230	0.02590336	0.006345226
231	-0.07364896	0.006761216
232	0.01415904	0.007861011
233	0.02886688	0.007281478
234	-0.02711072	0.00608729
235	0.107016	0.00685561
236	-0.0076832	0.006038995
237	-0.00351232	0.006639382
238	-0.09911328	0.007121229
239	-0.05400192	0.006675603
240	-0.00406112	0.00713879
241	0.07068544	0.006135584
242	0.03007424	0.00581289
243	0.04851392	0.00653511
244	0.05092864	0.005716301
245	-0.0043904	0.005415558
246	-0.03633056	0.00575801
247	0.04653824	0.00570752
248	0.02996448	0.00504896
249	-0.01558592	0.0049392
250	0.01986656	0.005098352
251	0.07935648	0.00428503
252	-0.02733024	0.005349702
253	0.02590336	0.004556138
254	0.05663616	0.004735046
255	0.04412352	0.005165306
256	-0.00592704	0.004874442
257	0.09856448	0.004126976
258	0.02118368	0.005289334
259	-0.00428064	0.005019325
260	-0.02557408	0.005170794
261	0.02283008	0.00496225

Answer 1

STRATEGY-1
	STRATEGY-1						Note: Plant & Equipment would be in His torical cost so changes not happen
	A	B	C	D	E		A	B	C	D	E
	Week	AMT invested each week (80% of 300)	Return% (Blue Chip)	Return Amt	Return Amt		Week	AMT invested each week (20% of 300)	Return % (Bond)	Return Amt	Amt at end of week
				(BxC)	(B+D)					(BxC)	(B+D)
	1	240	-0.00637362	-1.5296688	238.4703312		1	60	0.006863729	0.41182374	60.41182374
( Previous week amount invested ( B ) + Previous week Total return Amt( E )	2	478.4703312	0.035806558	17.13237567	495.6027069		2	120.4118237	0.006046148	0.72802771	121.1398514
	3	735.6027069	-0.010874714	-7.999469055	727.6032378		3	181.1398514	0.006647246	1.20408115	182.3439326
	4	967.6032378	-0.008032959	-7.772717138	959.8305207		4	242.3439326	0.007129663	1.72783057	244.0717632
	5	1199.830521	0.046948305	56.33000923	1256.16053		5	304.0717632	0.00668351	2.03226667	306.1040298
	6	1496.16053	-0.007513179	-11.24092187	1484.919608		6	366.1040298	0.007147246	2.61663556	368.7206654
	7	1724.919608	0.001550548	2.674570648	1727.594179		7	428.7206654	0.006142851	2.63356717	431.3542326
	8	1967.594179	0.027091182	53.304452	2020.898631		8	491.3542326	0.005819774	2.85957059	494.2138032
	9	2260.898631	-0.022585692	-51.06396012	2209.834671		9	554.2138032	0.006542851	3.62613834	557.8399415
	10	2449.834671	-0.001004395	-2.460601694	2447.374069		10	617.8399415	0.005723071	3.53594185	621.3758833
	11	2687.374069	0.059304336	159.3729347	2846.747004		11	681.3758833	0.005421973	3.69440164	685.070285
	12	3086.747004	-0.053779067	-166.0023739	2920.74463		12	745.070285	0.005764829	4.29520279	749.3654878
	13	3160.74463	-0.016607676	-52.49262273	3108.252007		13	809.3654878	0.00571428	4.62494102	813.9904288
	14	3348.252007	-0.013056031	-43.714882	3304.537125		14	873.9904288	0.00505494	4.41796918	878.408398
	15	3544.537125	0.001507691	5.344066722	3549.881192		15	938.408398	0.00494505	4.64047645	943.0488744
	16	3789.881192	-0.016454929	-62.36222593	3727.518966		16	1003.048874	0.005104391	5.11995365	1008.168828
	17	3967.518966	0.05217797	207.0170856	4174.536051		17	1068.168828	0.004290106	4.5825575	1072.751386
	18	4414.536051	-0.016601082	-73.28607498	4341.249976		18	1132.751386	0.005356039	6.0670606	1138.818446
	19	4581.249976	-0.014571414	-66.75529004	4514.494686		19	1198.818446	0.004561534	5.4684511	1204.286897
	20	4754.494686	1.76E-05	0.083595427	4754.578282		20	1264.286897	0.004740655	5.993548	1270.280445
	21	4994.578282	-0.045645009	-227.9775706	4766.600711		21	1330.280445	0.005171423	6.87944289	1337.159888
	22	5006.600711	0.040652707	203.5318718	5210.132583		22	1397.159888	0.004880215	6.81844064	1403.978329
	23	5450.132583	0.005272522	28.73594395	5478.868527		23	1463.978329	0.004131864	6.04895935	1470.027288
	24	5718.868527	-0.065540594	-374.8180403	5344.050487		24	1530.027288	0.005295599	8.10241098	1538.129699
	25	5584.050487	-0.017742839	-99.07690875	5484.973578		25	1598.129699	0.00502527	8.03103323	1606.160732
	26	5724.973578	0.030338431	173.6867159	5898.660294		26	1666.160732	0.005176918	8.62557749	1674.78631
	27	6138.660294	0.095613091	586.9362853	6725.596579		27	1734.78631	0.004968127	8.61863871	1743.404949
	28	6965.596579	0.014980205	104.3460647	7069.942644		28	1803.404949	0.00656373	11.8370632	1815.242012
	29	7309.942644	-0.016748335	-122.4293682	7187.513275		29	1875.242012	0.005787906	10.8537245	1886.095736
	30	7427.513275	0.061484554	456.6773411	7884.190617		30	1946.095736	0.00610439	11.8797274	1957.975464
	31	8124.190617	0.037361501	303.5319558	8427.722572		31	2017.975464	0.004503292	9.08753276	2027.062996
	32	8667.722572	-0.005297797	-45.91983464	8621.802738		32	2087.062996	0.005328566	11.1210529	2098.184049
	33	8861.802738	-0.097865836	-867.2677334	7994.535004		33	2158.184049	0.005143951	11.101593	2169.285642
	34	8234.535004	0.028560411	235.1817041	8469.716708		34	2229.285642	0.00370659	8.26304787	2237.54869
	35	8709.716708	0.018888992	164.5177692	8874.234478		35	2297.54869	0.004414281	10.1420255	2307.690716
	36	9114.234478	0.046307646	422.0587438	9536.293221		36	2367.690716	0.004608787	10.9121822	2378.602898
	37	9776.293221	-0.031197771	-304.9985572	9471.294664		37	2438.602898	0.004114282	10.0331	2448.635998
	38	9711.294664	0.063197739	613.7318655	10325.02653		38	2508.635998	0.004778017	11.9863054	2520.622303
	39	10565.02653	-0.006103291	-64.48143133	10500.5451		39	2580.622303	0.005116478	13.2036972	2593.826001
	40	10740.5451	-0.044362593	-476.4784308	10264.06667		40	2653.826001	0.004624171	12.2717452	2666.097746
	41	10504.06667	-0.081701017	-858.1929294	9645.873738		41	2726.097746	0.004799995	13.0852555	2739.183001
	42	9885.873738	0.064366969	636.3237285	10522.19747		42	2799.183001	0.004487908	12.5624758	2811.745477
	43	10762.19747	0.003315381	35.680785	10797.87825		43	2871.745477	0.004354941	12.5062821	2884.251759
	44	11037.87825	-0.097715287	-1078.569441	9959.30881		44	2944.251759	0.004442853	13.0808778	2957.332637
	45	10199.30881	-0.060120819	-613.1907989	9586.118012		45	3017.332637	0.003949447	11.9167953	3029.249432
	46	9826.118012	-0.052971376	-520.5029918	9305.61502		46	3089.249432	0.003861535	11.9292448	3101.178677
	47	9545.61502	0.082659258	789.0334547	10334.64847		47	3161.178677	0.00413626	13.0754569	3174.254134
	48	10574.64847	0.055895549	591.075782	11165.72426		48	3234.254134	0.00412637	13.3457292	3247.599863
	49	11405.72426	0.038148314	435.1091503	11840.83341		49	3307.599863	0.004081315	13.4993569	3321.09922
	50	12080.83341	-0.010701088	-129.2780614	11951.55535		50	3381.09922	0.004380215	14.8099415	3395.909162