Excel Finance Project

profilealfredoefp
Project-data.xlsx
Home>Business & Finance homework help>Financial markets homework help>Excel Finance Project

Returns

date SPY LQD FEZ IYR TRVCI Hedged FEZ SPY Call-Option price returns
20120131 0.046375 0.021449 0.059980 0.066010 0.1130273793
20120229 0.043405 0.016277 0.052749 -0.006935 0.0755674264
20120330 0.032140 -0.014163 -0.011619 0.044782 0.0619602452
20120430 -0.006676 0.010788 -0.067180 0.025843 -0.0194431685
20120531 -0.060056 0.007547 -0.126528 -0.042090 -0.0926066891
20120629 0.040490 0.008683 0.099479 0.053341 0.0467787542
20120731 0.011792 0.034718 -0.007087 0.023383 -0.0047355915
20120831 0.025053 -0.000538 0.073519 0.003057 0.078213423
20120928 0.025428 0.010963 0.036044 -0.011072 0.0152077752
20121031 -0.018198 0.013417 0.028682 -0.005902 -0.0814329734
20121130 0.005695 -0.004159 0.034461 -0.005781 0.0242771459
20121231 0.008982 -0.003295 0.055158 0.028764 -0.0057299697
20130131 0.051190 -0.012976 0.036930 0.040204 0.0329502337
20130228 0.012759 0.010723 -0.062883 0.012636 -0.0078629912
20130328 0.037951 -0.000463 -0.016912 0.028544 0.0355646178
20130430 0.019212 0.021670 0.068099 0.057283 0.0130678315
20130531 0.023579 -0.032079 0.014168 -0.064933 0.0422832008
20130628 -0.013374 -0.032575 -0.052027 -0.024076 -0.0424718572
20130731 0.051677 0.011028 0.088741 0.002408 0.1084575687
20130830 -0.029992 -0.009954 -0.022179 -0.065325 0.0268759536
20130930 0.031763 0.007394 0.091671 0.034611 0.0740107067
20131031 0.046307 0.018020 0.062874 0.037310 0.0337929329
20131129 0.029638 -0.001389 0.008451 -0.046396 0.0470961306
20131231 0.025803 0.002123 0.029589 0.009016 0.0481070112
20140131 -0.035248 0.018846 -0.057820 0.034401 0.019165823
20140228 0.045516 0.011414 0.065392 0.047510 0.0710051663
20140331 0.008291 -0.000293 0.009360 0.001309 -0.0859923496
20140430 0.006952 0.013252 0.023910 0.030294 -0.0497579692
20140530 0.023206 0.015098 0.013278 0.027969 0.0722838034
20140630 0.020639 0.000063 -0.001612 0.010084 0.0637980794
20140731 -0.013438 -0.003186 -0.058008 -0.001114 0.0357604128
20140829 0.039464 0.019984 0.002453 0.034723 0.067537343
20140930 -0.013705 -0.016769 -0.026335 -0.058895 -0.0082568779
20141031 0.023551 0.012307 -0.036020 0.083526 0.0368639328
20141128 0.027472 0.009092 0.035276 0.027207 0.0615083181
20141231 -0.002534 -0.000189 -0.062225 0.008186 -0.0413887599
20150130 -0.029629 0.037518 0.000814 0.057132 -0.0261889574
20150227 0.056205 -0.014164 0.064245 -0.025976 0.1009376857
20150331 -0.015661 0.001949 -0.010074 0.010869 -0.0312121246
20150430 0.009834 -0.011839 0.020608 -0.048159 0.0408870034
20150529 0.012856 -0.011171 -0.014891 -0.003311 0.0223338551
20150630 -0.020176 -0.018782 -0.020933 -0.043750 -0.0348557275
20150731 0.022589 0.008299 0.030724 0.049509 0.081182242
20150831 -0.060950 -0.008255 -0.072836 -0.058132 -0.0798773993
20150930 -0.025328 0.011862 -0.051770 0.016118 -0.0236488461
20151030 0.085060 0.005721 0.081832 0.062157 0.1625912445
20151130 0.003655 -0.001540 -0.013654 -0.001990 0.0250731207
20151231 -0.017291 -0.010778 -0.042709 0.011038 -0.0023031996
20160129 -0.049783 0.001228 -0.060993 -0.041023 -0.0753923639
20160229 -0.000830 0.010488 -0.042994 -0.007500 -0.0570130749
20160331 0.067212 0.036040 0.076083 0.102224 0.1022735084
20160429 0.003945 0.015504 0.026181 -0.016697 -0.1115165786
20160531 0.017008 -0.005114 -0.006158 0.022074 0.0908503907
20160630 0.003400 0.030816 -0.056058 0.062050 -0.0638585348
20160729 0.036496 0.012926 0.044755 0.036534 0.1363192751
20160831 0.001198 0.001796 0.008752 -0.033489 0.0186797634
20160930 0.000010 -0.003150 0.001345 -0.014593 0.0286375209
20161031 -0.017337 -0.015248 -0.005800 -0.049603 -0.0019417521
20161130 0.036838 -0.031589 -0.036537 -0.023356 -0.0210491981
20161230 0.020324 0.008340 0.069602 0.042300 -0.0106032034
20170131 0.017895 0.001451 0.016736 0.001300 0.0818678392
20170228 0.039292 0.013173 0.004997 0.043744 0.0555474607
20170331 0.001282 -0.002945 0.065242 -0.014611 0.0242844444
20170428 0.009926 0.011858 0.040715 0.005606 0.0509029355
20170531 0.014113 0.013292 0.045202 -0.001140 0.0678810488
20170630 0.006391 0.004793 -0.010449 0.020940 -0.0236517037
20170731 0.020554 0.007636 0.032493 0.011157 0.0771745167
20170831 0.002918 0.006753 0.001259 0.006695 0.0399644299
20170929 0.020100 -0.000492 0.040727 -0.007853 -0.0130574234
20171031 0.023564 0.001920 0.009697 0.000501 0.1108610823
20171130 0.030566 -0.001229 -0.006243 0.025651 -0.0027980305
20171229 0.012080 0.012152 -0.013410 -0.001095 -0.0072475125

&A

Page &P

Currencies-Prices-Interest-Rates

TRVCI Index EUR Curncy EUSWEA Curncy USSOA Curncy
Last Price Last Price Last Price Last Price
Dates PX_LAST Dates PX_LAST Dates PX_LAST Dates PX_LAST Date SPY Price/Pseudo Price
12/30/11 1506.98 12/30/11 1.2939 12/30/11 0.398 12/30/11 0.077 12/30/11 125.5
1/31/12 1677.31 1/31/12 1.3202 1/31/12 0.3795 1/31/12 0.0813 1/31/12
2/29/12 1804.06 2/29/12 1.3443 2/29/12 0.351 2/29/12 0.1144 2/29/12
3/30/12 1915.84 3/30/12 1.3333 3/30/12 0.3625 3/30/12 0.1285 3/30/12
4/30/12 1878.59 4/30/12 1.3219 4/30/12 0.334 4/30/12 0.137 4/30/12
5/31/12 1704.62 5/31/12 1.2403 5/31/12 0.312 5/31/12 0.158 5/31/12
6/29/12 1784.36 6/29/12 1.258 6/29/12 0.2655 6/29/12 0.17 6/29/12
7/31/12 1775.91 7/31/12 1.2281 7/31/12 0.0935 7/31/12 0.142 7/31/12
8/31/12 1914.81 8/31/12 1.2582 8/31/12 0.098 8/31/12 0.1338 8/31/12
9/28/12 1943.93 9/28/12 1.2922 9/28/12 0.095 9/28/12 0.142 9/28/12
10/31/12 1785.63 10/31/12 1.3005 10/31/12 0.091 10/31/12 0.1565 10/31/12
11/30/12 1828.98 11/30/12 1.3 11/30/12 0.0768 11/30/12 0.154 11/30/12
12/31/12 1818.5 12/31/12 1.3191 12/31/12 0.072 12/31/12 0.152 12/31/12
1/31/13 1878.42 1/31/13 1.3554 1/31/13 0.104 1/31/13 0.13 1/31/13
2/28/13 1863.65 2/28/13 1.3107 2/28/13 0.0775 2/28/13 0.145 2/28/13
3/29/13 1929.93 3/29/13 1.2818 3/29/13 0.0755 3/29/13 0.145 3/29/13
4/30/13 1955.15 4/30/13 1.3077 4/30/13 0.074 4/30/13 0.122 4/30/13
5/31/13 2037.82 5/31/13 1.299 5/31/13 0.0765 5/31/13 0.103 5/31/13
6/28/13 1951.27 6/28/13 1.3067 6/28/13 0.096 6/28/13 0.113 6/28/13
7/31/13 2162.9 7/31/13 1.3253 7/31/13 0.101 7/31/13 0.098 7/31/13
8/30/13 2221.03 8/30/13 1.324 8/30/13 0.093 8/30/13 0.0875 8/30/13
9/30/13 2385.41 9/30/13 1.35 9/30/13 0.0935 9/30/13 0.092 9/30/13
10/31/13 2466.02 10/31/13 1.3659 10/31/13 0.109 10/31/13 0.086 10/31/13
11/29/13 2582.16 11/29/13 1.3607 11/29/13 0.149 11/29/13 0.09 11/29/13
12/31/13 2706.38 12/31/13 1.3764 12/31/13 0.156 12/31/13 0.079 12/31/13
1/31/14 2758.25 1/31/14 1.3533 1/31/14 0.1825 1/31/14 0.079 1/31/14
2/28/14 2954.1 2/28/14 1.3792 2/28/14 0.148 2/28/14 0.075 2/28/14
3/31/14 2700.07 3/31/14 1.3798 3/31/14 0.1795 3/31/14 0.08 3/31/14
4/30/14 2565.72 4/30/14 1.383 4/30/14 0.1958 4/30/14 0.089 4/30/14
5/30/14 2751.18 5/30/14 1.3611 5/30/14 0.1405 5/30/14 0.09 5/30/14
6/30/14 2926.7 6/30/14 1.3651 6/30/14 0.036 6/30/14 0.1 6/30/14
7/31/14 3031.36 7/31/14 1.3387 7/31/14 0.068 7/31/14 0.0935 7/31/14
8/29/14 3236.09 8/29/14 1.3188 8/29/14 0.011 8/29/14 0.0885 8/29/14
9/30/14 3209.37 9/30/14 1.2593 9/30/14 -0.047 9/30/14 0.0855 9/30/14
10/31/14 3327.68 10/31/14 1.2577 10/31/14 0.008 10/31/14 0.0945 10/31/14
11/28/14 3532.36 11/28/14 1.2448 11/28/14 0.011 11/28/14 0.1065 11/28/14
12/31/14 3386.16 12/31/14 1.2154 12/31/14 -0.045 12/31/14 0.1215 12/31/14
1/30/15 3297.48 1/30/15 1.1353 1/30/15 -0.03 1/30/15 0.1255 1/30/15
2/27/15 3630.32 2/27/15 1.1237 2/27/15 -0.0523 2/27/15 0.1205 2/27/15
3/31/15 3517.01 3/31/15 1.0739 3/31/15 -0.077 3/31/15 0.1205 3/31/15
4/30/15 3660.81 4/30/15 1.1192 4/30/15 -0.0878 4/30/15 0.1245 4/30/15
5/29/15 3742.57 5/29/15 1.0959 5/29/15 -0.1168 5/29/15 0.1295 5/29/15
6/30/15 3612.12 6/30/15 1.1185 6/30/15 -0.1125 6/30/15 0.1325 6/30/15
7/31/15 3905.36 7/31/15 1.098 7/31/15 -0.115 7/31/15 0.1355 7/31/15
8/31/15 3593.41 8/31/15 1.121 8/31/15 -0.1243 8/31/15 0.181 8/31/15
9/30/15 3508.43 9/30/15 1.1215 9/30/15 -0.1395 9/30/15 0.1393 9/30/15
10/30/15 4078.87 10/30/15 1.1004 10/30/15 -0.143 10/30/15 0.1343 10/30/15
11/30/15 4181.14 11/30/15 1.0576 11/30/15 -0.2423 11/30/15 0.223 11/30/15
12/31/15 4171.51 12/31/15 1.091 12/31/15 -0.2235 12/31/15 0.341 12/31/15
1/29/16 3857.01 1/29/16 1.0913 1/29/16 -0.2383 1/29/16 0.3645 1/29/16
2/29/16 3637.11 2/29/16 1.0904 2/29/16 -0.3115 2/29/16 0.3865 2/29/16
3/31/16 4009.09 3/31/16 1.1378 3/31/16 -0.3423 3/31/16 0.368 3/31/16
4/29/16 3562.01 4/29/16 1.1396 4/29/16 -0.341 4/29/16 0.366 4/29/16
5/31/16 3885.62 5/31/16 1.1152 5/31/16 -0.3383 5/31/16 0.443 5/31/16
6/30/16 3637.49 6/30/16 1.1145 6/30/16 -0.339 6/30/16 0.4274 6/30/16
7/29/16 4133.35 7/29/16 1.1112 7/29/16 -0.334 7/29/16 0.398 7/29/16
8/31/16 4210.56 8/31/16 1.1141 8/31/16 -0.344 8/31/16 0.4167 8/31/16
9/30/16 4331.14 9/30/16 1.1165 9/30/16 -0.34 9/30/16 0.4058 9/30/16
10/31/16 4322.73 10/31/16 1.0955 10/31/16 -0.349 10/31/16 0.414 10/31/16
11/30/16 4231.74 11/30/16 1.0652 11/30/16 -0.346 11/30/16 0.5275 11/30/16
12/30/16 4186.87 12/30/16 1.0547 12/30/16 -0.3468 12/30/16 0.655 12/30/16
1/31/17 4529.64 1/31/17 1.0701 1/31/17 -0.3508 1/31/17 0.6575 1/31/17
2/28/17 4781.25 2/28/17 1.0595 2/28/17 -0.3535 2/28/17 0.7106 2/28/17
3/31/17 4897.36 3/31/17 1.0681 3/31/17 -0.3536 3/31/17 0.8985 3/31/17
4/28/17 5146.65 4/28/17 1.0939 4/28/17 -0.3593 4/28/17 0.8997 4/28/17
5/31/17 5496.01 5/31/17 1.1213 5/31/17 -0.3607 5/31/17 1.0284 5/31/17
6/30/17 5366.02 6/30/17 1.1403 6/30/17 -0.3583 6/30/17 1.1565 6/30/17
7/31/17 5780.14 7/31/17 1.1731 7/31/17 -0.3545 7/31/17 1.158 7/31/17
8/31/17 6011.14 8/31/17 1.1861 8/31/17 -0.3568 8/31/17 1.1505 8/31/17
9/29/17 5932.65 9/29/17 1.1813 9/29/17 -0.359 9/29/17 1.1585 9/29/17
10/31/17 6590.35 10/31/17 1.163 10/31/17 -0.3579 10/31/17 1.158 10/31/17
11/30/17 6571.91 11/30/17 1.1831 11/30/17 -0.3436 11/30/17 1.308 11/30/17
12/29/17 6524.28 12/29/17 1.198 12/29/17 -0.3468 12/29/17 1.407 12/29/17

Black-Scholes Calculations

Date T d1 d2 N(d1) N(d2) C K sigma r
12/30/11
1/31/12
2/29/12
3/30/12
4/30/12
5/31/12
6/29/12
7/31/12
8/31/12
9/28/12
10/31/12
11/30/12
12/31/12
1/31/13
2/28/13
3/29/13
4/30/13
5/31/13
6/28/13
7/31/13
8/30/13
9/30/13
10/31/13
11/29/13
12/31/13
1/31/14
2/28/14
3/31/14
4/30/14
5/30/14
6/30/14
7/31/14
8/29/14
9/30/14
10/31/14
11/28/14
12/31/14
1/30/15
2/27/15
3/31/15
4/30/15
5/29/15
6/30/15
7/31/15
8/31/15
9/30/15
10/30/15
11/30/15
12/31/15
1/29/16
2/29/16
3/31/16
4/29/16
5/31/16
6/30/16
7/29/16
8/31/16
9/30/16
10/31/16
11/30/16
12/30/16
1/31/17
2/28/17
3/31/17
4/28/17
5/31/17
6/30/17
7/31/17
8/31/17
9/29/17
10/31/17
11/30/17
12/29/17

&A

Page &P

Markowitz