questions - I) Using the New Markowitz Frontier MS-Excel file

profileCaiden
sample-copy-of-markowitz.xlsx
Home>Business & Finance homework help>questions - I) Using the New Markowitz Frontier MS-Excel file

Sheet1

Markowitz Efficient Portfolio
Security Characteristics
A B
Expected Return -0.45 1.23
Expected Volatility 9.02 16.75
Correlation 0.01
Monthly
Weight (A) Weight (B) Expected Volatility Expected Return Return/Risk Ratio Annual Return
0.00 1.00 16.75 1.23 0.07 15.85
0.10 0.90 15.12 1.07 0.07 13.56
0.20 0.80 13.55 0.90 0.07 11.31
0.30 0.70 12.07 0.73 0.06 9.11
0.40 0.60 10.73 0.56 0.05 6.94
0.50 0.50 9.57 0.39 0.04 4.82
0.60 0.40 8.67 0.22 0.03 2.73
0.70 0.30 8.12 0.06 0.01 0.68
0.80 0.20 8.00 -0.11 -0.01 -1.33
0.90 0.10 8.31 -0.28 -0.03 -3.30
1.00 0.00 9.02 -0.45 -0.05 -5.24
Solver Row 0.30 0.70 12.07 0.73 0.06 9.10822
Date Citigroup (C) Harmony (HMY) C - Return (1+) HMY - Return (1+)
1/2/08 28.93 11.58 0.98 1.12
12/3/07 29.44 10.31 0.88 0.99
11/1/07 33.30 10.44 0.79 0.93
10/1/07 41.90 11.20 0.90 0.94
9/4/07 46.67 11.91 1.00 1.33
8/1/07 46.88 8.95 1.01 0.65
7/2/07 46.57 13.84 0.91 0.97
6/1/07 51.29 14.27 0.94 0.96
5/1/07 54.49 14.93 1.02 0.94
4/2/07 53.62 15.88 1.04 1.14
3/1/07 51.34 13.90 1.02 1.00
2/1/07 50.37 13.84 0.91 1.03
1/3/07 55.13 13.39 0.99 0.85
12/1/06 55.70 15.75 1.12 0.93
11/1/06 49.59 16.96 0.99 1.09
10/2/06 50.16 15.54 1.01 1.20
9/1/06 49.67 12.93 1.01 0.94
8/1/06 49.35 13.75 1.02 0.96
7/3/06 48.31 14.30 1.00 0.88
6/1/06 48.25 16.29 0.98 1.14
5/1/06 49.30 14.34 0.99 0.85
4/3/06 49.95 16.80 1.06 1.06
3/1/06 47.23 15.88 1.02 1.14
2/1/06 46.37 13.99 1.00 0.75
1/3/06 46.58 18.63 0.96 1.43
12/1/05 48.53 13.05 1.00 1.08
11/1/05 48.55 12.07 1.06 1.16
10/3/05 45.78 10.45 1.01 0.96
9/1/05 45.52 10.94 1.04 1.45
8/1/05 43.77 7.57 1.01 0.92
7/1/05 43.50 8.20 0.94 0.96
6/1/05 46.23 8.56 0.98 1.12
5/2/05 47.11 7.65 1.00 1.22
4/1/05 46.96 6.27 1.04 0.80
3/1/05 44.94 7.80 0.94 0.91
2/1/05 47.72 8.54 0.97 1.05
1/3/05 49.05 8.13 1.02 0.88
12/1/04 48.18 9.27 1.08 0.88
11/1/04 44.75 10.48 1.01 0.89
10/1/04 44.37 11.80 1.01 0.87
9/1/04 44.12 13.62 0.95 1.10
8/2/04 46.58 12.43 1.06 1.14
7/1/04 44.09 10.92 0.95 1.04
6/1/04 46.50 10.55 1.00 0.88
5/3/04 46.43 11.94 0.97 1.09
4/1/04 48.09 11.00 0.93 0.72
3/1/04 51.70 15.37 1.03 1.03
2/2/04 50.26 14.94 1.02 0.99
1/2/04 49.48 15.16 1.02 0.94
12/1/03 48.54 16.11 1.03 1.03
11/3/03 47.03 15.71 0.99 1.05
10/1/03 47.40 15.01 1.04 1.04
9/2/03 45.51 14.38 1.05 1.03
8/1/03 43.35 14.00 0.97 1.14
7/1/03 44.80 12.24 1.05 0.93
6/2/03 42.80 13.18 1.04 1.03
5/1/03 41.02 12.82 1.05 1.25
4/1/03 39.25 10.28 1.14 0.86
3/3/03 34.45 11.97 1.03 0.86
2/3/03 33.34 13.91 0.97 0.92
1/2/03 34.35 15.08 0.98 0.93
12/2/02 35.19 16.29 0.91 1.31
11/1/02 38.88 12.44 1.05 0.98
10/1/02 36.95 12.67 1.25 0.84
9/3/02 29.65 15.16 0.91 1.10
8/1/02 32.75 13.81 0.98 1.32
7/1/02 33.54 10.44 0.87 0.82
6/3/02 38.75 12.72 0.90 0.84
5/1/02 43.18 15.23 1.00 1.24
4/1/02 43.30 12.27 0.87 1.15
3/1/02 49.52 10.63 1.09 1.07
2/1/02 45.25 9.93 0.95 1.33
1/2/02 47.40 7.46 0.94 1.23
12/3/01 50.48 6.08 1.05 1.18
11/1/01 47.90 5.15 1.05 0.94
10/1/01 45.52 5.47 1.12 1.10
9/4/01 40.50 4.96 0.88 1.13
8/1/01 45.80 4.39 0.91 0.94
7/2/01 50.21 4.67 0.95 0.89
6/1/01 52.84 5.23 1.03 1.17
5/1/01 51.25 4.46 1.04 0.99
4/2/01 49.15 4.49 1.09 1.02
3/1/01 44.98 4.42 0.91 0.93
2/1/01 49.18 4.77 0.88 1.12
1/2/01 55.97 4.25 1.10 1.00
12/1/00 51.06 4.25 1.03 1.23
11/1/00 49.81 3.45 0.95 1.00
10/2/00 52.63 3.45 0.97 0.74
9/1/00 54.06 4.64 0.93 0.98
8/1/00 58.39 4.75 0.83 1.07
7/3/00 70.50 4.44 1.17 0.90
6/1/00 60.25 4.93 0.97 1.13
5/1/00 62.19 4.36 1.05 0.98
4/3/00 59.00 4.44 0.99 0.82
3/1/00 59.88 5.44 1.16 0.96
2/1/00 51.75 5.66 0.91 1.19
1/3/00 57.00 4.74 1.02 0.86
12/1/99 55.69 5.54 1.03 0.95
11/1/99 53.88 5.81 0.99 0.98
10/1/99 54.25 5.90 1.23 1.12
9/1/99 44.00 5.29 0.99 1.39
8/2/99 44.44 3.80 1.00 1.07
7/1/99 44.56 3.55 0.94 0.84
6/1/99 47.50 4.23 0.72 0.97
5/3/99 66.25 4.37 0.88 0.90
4/1/99 74.87 4.86 1.17 1.19
3/1/99 63.88 4.10 1.09 1.07
2/1/99 58.75 3.82 1.05 0.96
1/4/99 56.06 3.98 1.13 0.96
12/1/98 49.69 4.15 0.99 0.97
11/2/98 50.25 4.26 1.07 0.94
10/1/98 47.00 4.53 1.25 1.06
9/1/98 37.50 4.26 0.84 1.56
8/3/98 44.38 2.73 0.66 0.75
7/1/98 67.25 3.66 1.11 1.03
6/1/98 60.63 3.55 0.99 0.94
5/1/98 61.25 3.76 1.00 0.77
4/1/98 61.19 4.88 1.02 1.64
3/2/98 60.00 2.98 1.08 1.00
2/2/98 55.63 2.99 1.12 1.12
1/5/98 49.56 2.66
Monthly Expected Return: -0.45 1.23
Monthly Volatility: 9.02 16.75
Correlation: 0.01
Expected Return 16.752876766578854 15.117345975454761 13.548538613075142 12.072494671000987 10.727573650908935 9.5692228061098756 8.6725252154859902 8.1245825699833087 7.9974039589533108 8.3103295994717836 9.0176585566321439 1.2333381097676011 1.0652460190450463 0.89715392832249163 0.72906183759993692 0.56096974687738221 0.3928776561548275 0.22478556543227279 5.6693474709718139E-2 -0.11139861601283649 -0.27949070673539117 -0.44758279745794605 Citigroup (C) 39449 39419 39387 39356 39329 39295 39265 39234 39203 39174 39142 39114 39085 39052 39022 38992 38961 38930 38901 38869 38838 38810 38777 38749 38720 38687 38657 38628 38596 38565 38534 38504 38474 38443 38412 38384 38355 38322 38292 38261 38231 38201 38169 38139 38110 38078 38047 38019 37988 37956 37928 37895 37866 37834 37803 37774 37742 37712 37683 37655 37623 37592 37561 37530 37502 37469 37438 37410 37377 37347 37316 37288 37258 37228 37196 37165 37138 37104 37074 37043 37012 36983 36951 36923 36893 36861 36831 36801 36770 36739 36710 36678 36647 36619 36586 36557 36528 36495 36465 36434 36404 36374 36342 36312 36283 36251 36220 36192 36164 36130 36101 36069 36039 36010 35977 35947 35916 35886 35856 35828 35800 28.93 29.44 33.299999999999997 41.9 46.67 46.88 46.57 51.29 54.49 53.62 51.34 50.37 55.13 55.7 49.59 50.16 49.67 49.35 48.31 48.25 49.3 49.95 47.23 46.37 46.58 48.53 48.55 45.78 45.52 43.77 43.5 46.23 47.11 46.96 44.94 47.72 49.05 48.18 44.75 44.37 44.12 46.58 44.09 46.5 46.43 48.09 51.7 50.26 49.48 48.54 47.03 47.4 45.51 43.35 44.8 42.8 41.02 39.25 34.450000000000003 33.340000000000003 34.35 35.19 38.880000000000003 36.950000000000003 29.65 32.75 33.54 38.75 43.18 43.3 49.52 45.25 47.4 50.48 47.9 45.52 40.5 45.8 50.21 52.84 51.25 49.15 44.98 49.18 55.97 51.06 49.81 52.63 54.06 58.39 70.5 60.25 62.19 59 59.88 51.75 57 55.69 53.88 54.25 44 44.44 44.56 47.5 66.25 74.87 63.88 58.75 56.06 49.69 50.25 47 37.5 44.38 67.25 60.63 61.25 61.19 60 55.63 49.56 Harmony (HMY) 39449 39419 39387 39356 39329 39295 39265 39234 39203 39174 39142 39114 39085 39052 39022 38992 38961 38930 38901 38869 38838 38810 38777 38749 38720 38687 38657 38628 38596 38565 38534 38504 38474 38443 38412 38384 38355 38322 38292 38261 38231 38201 38169 38139 38110 38078 38047 38019 37988 37956 37928 37895 37866 37834 37803 37774 37742 37712 37683 37655 37623 37592 37561 37530 37502 37469 37438 37410 37377 37347 37316 37288 37258 37228 37196 37165 37138 37104 37074 37043 37012 36983 36951 36923 36893 36861 36831 36801 36770 36739 36710 36678 36647 36619 36586 36557 36528 36495 36465 36434 36404 36374 36342 36312 36283 36251 36220 36192 36164 36130 36101 36069 36039 36010 35977 35947 35916 35886 35856 35828 35800 11.58 10.31 10.44 11.2 11.91 8.9499999999999993 13.84 14.27 14.93 15.88 13.9 13.84 13.39 15.75 16.96 15.54 12.93 13.75 14.3 16.29 14.34 16.8 15.88 13.99 18.63 13.05 12.07 10.45 10.94 7.57 8.1999999999999993 8.56 7.65 6.27 7.8 8.5399999999999991 8.1300000000000008 9.27 10.48 11.8 13.62 12.43 10.92 10.55 11.94 11 15.37 14.94 15.16 16.11 15.71 15.01 14.38 14 12.24 13.18 12.82 10.28 11.97 13.91 15.08 16.29 12.44 12.67 15.16 13.81 10.44 12.72 15.23 12.27 10.63 9.93 7.46 6.08 5.15 5.47 4.96 4.3899999999999997 4.67 5.23 4.46 4.49 4.42 4.7699999999999996 4.25 4.25 3.45 3.45 4.6399999999999997 4.75 4.4400000000000004 4.93 4.3600000000000003 4.4400000000000004 5.44 5.66 4.74 5.54 5.81 5.9 5.29 3.8 3.55 4.2300000000000004 4.37 4.8600000000000003 4.0999999999999996 3.82 3.98 4.1500000000000004 4.26 4.53 4.26 2.73 3.66 3.55 3.76 4.88 2.98 2.99 2.66

Sheet2

Sheet3