CALCULATING THE RISK OF A TWO-ASSET PORTFOLIO
An investor puts 60% of his investment into SPY, an S&P500 index ETF, and the remaining 40% into AGG, a U.S. Core Bond ind
creating a balanced portfolio. The standard deviation of SPY is 10.7%, while the standard deviation on AGG is 2.98%. Find the
standard deviation of this portfolio if the correlation between the two instruments is -0.10.
Asset
SPY
AGG
Correlation
Portfolio variance
Portfolio standard deviation
Expected portfolio return
Weight of CSCO
0%
5%
10%
15%
20%
25%
30%
35%
40%
45%
50%
55%
60%
65%
70%
75%
80%
85%
90%
95%
100%
Weight
Expected return
8.60%
3.82%
Portfolio standard deviation
Portfolio expected return
FOLIO
g 40% into AGG, a U.S. Core Bond index ETF,
d deviation on AGG is 2.98%. Find the
10.
Standard deviation
Portfolio Expected Return and Standard Deviation
120.00%
Portfolio expected return
100.00%
80.00%
60.00%
40.00%
20.00%
0.00%
0.00%
20.00%
40.00%
60.00%
80.00%
Portfolio standard deviation
100.00%
120.00%
120.00%
THE MINIMUM VARIANCE PORTFOLIO
Using the information from Problem 1, help the investor determine the minimum variance portfolio by utilzing the
Solve tool in Excel.
Asset
SPY
AGG
Correlation
Portfolio variance
Portfolio standard deviation
Expected portfolio return
Weight
Expected return
8.60%
3.82%
-0.1
0
0.00%
0.00%
variance portfolio by utilzing the
Standard deviation
10.70%
2.98%
THE THREE ASSET PO
In this problem, we will find the portfolio return and risk for a three-asset portfolio using IBM, JNJ, and KO. We have collected
accounts for stock splits and dividends. Our goal here is to find the portfolio's expected return and variance if we invest 25% i
historical pattern.) Next, we find the monthly returns for each stock in our portfolio.
Date
4/1/2019
3/1/2019
2/1/2019
1/1/2019
12/1/2018
11/1/2018
10/1/2018
9/1/2018
8/1/2018
7/1/2018
6/1/2018
5/1/2018
4/1/2018
3/1/2018
2/1/2018
1/1/2018
12/1/2017
11/1/2017
10/1/2017
9/1/2017
8/1/2017
7/1/2017
6/1/2017
5/1/2017
4/1/2017
3/1/2017
2/1/2017
1/1/2017
12/1/2016
11/1/2016
10/1/2016
9/1/2016
8/1/2016
7/1/2016
6/1/2016
5/1/2016
4/1/2016
3/1/2016
2/1/2016
1/1/2016
IBM
140,44
141,10
136,54
132,87
112,36
121,29
112,67
147,59
141,44
139,95
134,90
134,95
138,44
146,53
147,37
154,81
145,09
139,63
144,25
135,85
132,53
134,05
142,53
140,05
147,08
159,79
163,71
158,88
151,12
146,36
138,66
143,32
142,12
143,67
135,76
134,91
128,06
132,90
113,82
108,40
JNJ
139,90
139,79
135,74
132,20
128,20
145,01
138,19
136,39
132,08
129,95
118,98
116,43
123,12
124,74
125,62
133,65
135,13
133,94
134,01
124,98
126,44
126,78
126,37
121,70
117,16
118,19
115,21
106,76
108,61
104,20
108,59
110,60
110,99
116,47
112,81
104,06
103,50
99,91
96,45
95,75
KO
48,21
46,45
44,95
47,71
46,94
49,57
47,09
45,05
43,47
45,48
42,40
41,57
41,77
41,61
41,41
45,60
43,96
43,50
43,70
42,44
42,95
43,22
41,94
42,52
40,35
39,34
38,90
38,54
38,94
37,09
38,98
38,59
39,60
39,78
41,02
40,36
40,54
41,65
38,73
38,54
Return IBM
12/1/2015
11/1/2015
10/1/2015
9/1/2015
8/1/2015
7/1/2015
6/1/2015
5/1/2015
119,54
119,98
120,55
124,76
126,22
138,26
138,83
142,61
94,17
92,13
91,94
84,95
84,88
90,51
88,02
89,79
38,57
37,98
37,74
35,44
34,73
36,29
34,37
35,89
THE THREE ASSET PORTFOLIO
g IBM, JNJ, and KO. We have collected monthly stock prices for the past four years. As with our prior chapter, we are using the adjusted p
return and variance if we invest 25% in IBM, 50% in JNJ, and the remaining 25% in KO. (NOTE: We are assuming that the future returns w
Return JNJ
Return KO
IBM
Expected return
Variance
Standard deviation
Weight
Portfolio return
Covariance IBM, JNJ
Covariance IBM, KO
Covariance JNJ, KO
Portfolio variance
Portfolio standard devation
Portfolio vairance (using MMULT)
Portfolio standard deviation
r, we are using the adjusted price, which
ming that the future returns will follow the
JNJ
KO
Note: use Ctrl+Shift+Enter
MINIMUM VARIANCE WITH RETURN TARGET PORTFOLIO
Using the provided data solve for the portfolio weights which would produce the minimum variance protfolio with a portfolio
raturn of at least 1.3%. Allow for short selling.
Return
Variance
Standard deviation
Weight
Sum of weights
Return GE
Return MSFT
Return AMZN
Return PFE
Return WMT
Return NOK
Portfolio return
Portfolio variance
Portfolio standard deviation
GE
1,26%
0,43%
6,54%
0,1
1
MSFT
1,25%
0,34%
5,87%
0,1
AMZN
1,85%
0,63%
7,94%
0,1
Return GE
Return MSFT Return AMZN
0,004273648 0,001747885
0,002238034
0,001747885 0,003448741
0,001236066
0,002238034 0,001236066
0,006304789
0,001473122 0,000495836
0,001119676
0,001136553 0,000512768
0,000810632
0,002913437 0,001493252
0,003704317
0,01208
0,01097
0,10473
PFE
1,29%
0,20%
4,47%
0,1
WMT
1,11%
0,19%
4,41%
0,1
Return PFE Return WMT
0,001473122 0,001136553
0,000495836 0,000512768
0,001119676 0,000810632
0,002001953 0,000894571
0,000894571 0,001946485
0,001854707 0,002105754
protfolio with a portfolio
NOK
1,06%
3,74%
19,34%
0,5
Return NOK
0,002913437
0,001493252
0,003704317
0,001854707
0,002105754
0,037396426
MAX RETURN WITH RISK CONSTRAINT AND LEVERAGE
Using the provided data, solve for the max return possible while allowing for a max standard deviation of 6.25% and portfolio
leverage of 150%
Return
Variance
Standard deviation
Weight
Sum of weights
Return GE
Return MSFT
Return AMZN
Return PFE
Return WMT
Return NOK
Portfolio return
Portfolio variance
Portfolio standard deviation
GE
1,26%
0,43%
6,54%
0,1
1
MSFT
1,25%
0,34%
5,87%
0,1
AMZN
1,85%
0,63%
7,94%
0,1
Return GE
Return MSFT Return AMZN
0,004273648 0,001747885
0,002238034
0,001747885 0,003448741
0,001236066
0,002238034 0,001236066
0,006304789
0,001473122 0,000495836
0,001119676
0,001136553 0,000512768
0,000810632
0,002913437 0,001493252
0,003704317
0,01208
0,01097
0,10473
PFE
1,29%
0,20%
4,47%
0,1
WMT
1,11%
0,19%
4,41%
0,1
Return PFE Return WMT
0,001473122 0,001136553
0,000495836 0,000512768
0,001119676 0,000810632
0,002001953 0,000894571
0,000894571 0,001946485
0,001854707 0,002105754
on of 6.25% and portfolio
NOK
1,06%
3,74%
19,34%
0,5
Return NOK
0,002913437
0,001493252
0,003704317
0,001854707
0,002105754
0,037396426
PRICES FOR 13 STOCKS AND THE
Problem 1: Use the data from the "Price Data for Problem 1" sheet to calculate the returns for each of the assets.
Problem 2: Using the return data from Problem 1, calculate the assets characteristics below.
Problem 2
Average
Standard deviation
WMT
YHOO
MSFT
F
K
Walmart
WMT
Yahoo
YHOO
Microsoft
MSFT
Ford
F
Kellogg
K
Covariance between asset and SPY
Variance of SPY
Beta
Using Excel SLOPE function
Intercept
R-Squared
Problem 1
Date
2/Jun/14
1/May/14
1/Apr/14
3/Mar/14
3/Feb/14
2/Jan/14
2/Dec/13
1/Nov/13
1/Oct/13
3/Sep/13
1/Aug/13
1/Jul/13
3/Jun/13
1/May/13
1/Apr/13
1/Mar/13
1/Feb/13
2/Jan/13
3/Dec/12
1/Nov/12
1/Oct/12
4/Sep/12
1/Aug/12
2/Jul/12
1/Jun/12
1/May/12
2/Apr/12
1/Mar/12
1/Feb/12
3/Jan/12
1/Dec/11
1/Nov/11
3/Oct/11
1/Sep/11
1/Aug/11
1/Jul/11
1/Jun/11
2/May/11
1/Apr/11
1/Mar/11
1/Feb/11
3/Jan/11
1/Dec/10
1/Nov/10
1/Oct/10
1/Sep/10
2/Aug/10
1/Jul/10
1/Jun/10
3/May/10
1/Apr/10
1/Mar/10
1/Feb/10
4/Jan/10
1/Dec/09
2/Nov/09
1/Oct/09
1/Sep/09
3/Aug/09
1/Jul/09
1/Jun/09
OR 13 STOCKS AND THE SP500
returns for each of the assets.
cs below.
GS
Goldman
Sachs
GS
XOM
Exxon
XOM
C
Citicorp
C
Teva
HPQ
Teva
Teva
Hewlett
Packard
HPQ
IBM
GE
NBL
SP500
IBM
IBM
General
Electric
GE
Nobel
Energy
NBL
SP500
PRICES FOR 13 STOCKS AND THE SP500
Date
2/Jun/14
1/May/14
1/Apr/14
3/Mar/14
3/Feb/14
2/Jan/14
2/Dec/13
1/Nov/13
1/Oct/13
3/Sep/13
1/Aug/13
1/Jul/13
3/Jun/13
1/May/13
1/Apr/13
1/Mar/13
1/Feb/13
2/Jan/13
3/Dec/12
1/Nov/12
1/Oct/12
4/Sep/12
1/Aug/12
2/Jul/12
1/Jun/12
1/May/12
2/Apr/12
1/Mar/12
1/Feb/12
3/Jan/12
1/Dec/11
1/Nov/11
3/Oct/11
1/Sep/11
1/Aug/11
1/Jul/11
1/Jun/11
2/May/11
1/Apr/11
1/Mar/11
1/Feb/11
3/Jan/11
1/Dec/10
1/Nov/10
1/Oct/10
Walmart
WMT
75.07
76.77
79.22
75.96
73.76
73.74
77.70
79.53
75.35
72.61
71.65
76.06
72.69
73.03
75.39
72.59
68.22
67.42
65.76
69.03
71.91
70.74
69.59
70.96
66.47
62.75
55.78
57.95
55.57
57.72
56.21
55.06
53.02
48.51
49.72
48.92
49.32
51.25
50.69
47.99
47.59
51.33
49.37
49.25
49.32
Yahoo Microsoft
YHOO
MSFT
35.13
41.70
34.65
40.94
35.95
40.12
35.90
40.70
38.67
38.04
36.01
37.30
40.44
36.87
36.98
37.58
32.94
34.64
33.17
32.55
27.12
32.67
28.09
30.93
25.13
33.55
26.30
33.90
24.73
31.93
23.53
27.60
21.31
26.82
19.63
26.26
19.90
25.55
18.77
25.47
16.84
27.08
15.98
28.24
14.65
29.24
15.84
27.78
15.83
28.83
15.24
27.51
15.54
29.99
15.22
30.21
14.83
29.72
15.47
27.47
16.13
24.15
15.71
23.80
15.64
24.59
13.17
22.98
13.61
24.56
13.10
25.14
15.04
23.86
16.55
22.95
17.70
23.63
16.68
23.15
16.40
24.23
16.12
25.13
16.63
25.29
15.82
22.89
16.49
24.02
Ford
Kellogg
F
K
17.24
65.70
16.44
68.98
16.15
66.38
15.48
62.29
15.27
59.82
14.84
57.15
15.19
60.20
16.81
59.77
16.84
61.88
16.51
57.46
15.85
59.40
16.52
64.32
15.05
62.37
15.26
60.25
13.24
62.72
12.70
62.13
12.18
58.34
12.51
56.00
12.42
53.46
10.98
53.09
10.70
49.69
9.41
49.06
8.91
48.10
8.77
44.91
9.10
46.45
10.02
45.93
10.71
47.21
11.79
50.07
11.70
48.87
11.74
45.85
10.13
46.83
9.98
45.52
10.99
49.75
9.10
48.82
10.47
49.85
11.49
50.79
12.98
50.37
14.04
51.89
14.56
51.77
14.03
48.80
14.17
48.42
15.01
45.12
15.80
45.82
15.00
44.16
13.30
44.72
Goldman
Sachs
GS
167.44
159.81
159.28
163.29
165.88
163.01
176.06
167.80
159.25
156.63
150.61
161.86
149.25
159.94
143.68
144.75
147.31
144.95
125.05
115.47
119.48
110.97
103.20
98.07
93.17
93.01
111.38
120.30
111.37
107.50
87.21
92.45
105.24
90.83
111.65
129.27
127.47
134.79
144.26
151.51
156.46
155.98
160.30
148.84
153.26
Exxon
XOM
100.68
100.53
101.72
97.02
95.62
90.89
99.81
92.20
87.79
84.29
85.38
91.21
87.90
88.02
85.98
87.06
86.52
86.38
83.09
84.62
86.98
87.25
83.29
82.33
81.11
74.54
81.28
81.65
81.43
78.41
79.36
75.32
72.68
67.60
68.90
73.78
75.25
77.18
80.89
77.35
78.64
73.79
66.88
63.62
60.43
1/Sep/10
2/Aug/10
1/Jul/10
1/Jun/10
3/May/10
1/Apr/10
1/Mar/10
1/Feb/10
4/Jan/10
1/Dec/09
2/Nov/09
1/Oct/09
1/Sep/09
3/Aug/09
1/Jul/09
1/Jun/09
4/May/09
48.73
45.65
46.34
43.51
45.77
48.28
50.04
48.39
47.82
47.83
48.57
44.24
43.71
45.30
44.17
42.90
44.05
14.17
13.11
13.88
13.84
15.34
16.53
16.53
15.31
15.01
16.78
14.97
15.90
17.81
14.61
14.32
15.66
15.84
22.06
21.14
23.12
20.62
23.12
27.24
26.12
25.57
25.02
27.06
26.11
24.51
22.73
21.79
20.67
20.89
18.36
11.52
10.63
12.02
9.49
11.04
12.26
11.83
11.05
10.20
9.41
8.37
6.59
6.79
7.15
7.53
5.71
5.41
44.94
44.20
44.18
44.40
47.16
48.15
46.82
45.70
47.35
46.29
45.75
44.53
42.53
40.68
40.71
39.91
37.07
137.52
130.25
143.10
124.55
136.87
137.42
161.49
147.98
140.44
159.44
160.21
160.35
173.71
155.91
153.55
138.63
135.94
56.16
53.73
53.86
51.50
54.56
60.75
60.04
58.26
57.38
60.73
66.85
63.46
60.75
61.23
61.95
61.52
61.03
OCKS AND THE SP500
Citicorp
C
47.10
47.57
47.90
47.59
48.62
47.42
52.09
52.90
48.76
48.48
48.30
52.10
47.93
51.95
46.61
44.19
41.93
42.12
39.51
34.53
37.33
32.67
29.66
27.08
27.36
26.46
32.97
36.47
33.25
30.64
26.24
27.41
31.50
25.55
30.96
38.23
41.51
41.02
45.75
44.05
46.64
48.04
47.14
41.86
41.56
Teva
Teva
52.42
50.49
48.52
52.47
49.54
43.99
39.51
40.18
36.25
36.92
37.35
38.49
38.01
37.04
36.83
38.16
35.97
36.24
35.62
38.49
38.31
39.25
37.52
38.53
37.16
36.92
42.84
42.18
41.94
42.00
37.55
36.85
37.81
34.45
38.28
42.93
44.38
46.85
41.89
45.96
45.90
49.86
47.56
45.65
47.15
Hewlett
Packard
HPQ
33.68
33.34
32.90
32.21
29.60
28.72
27.71
26.95
24.01
20.68
21.87
25.14
24.28
23.77
20.05
23.20
19.48
15.97
13.78
12.44
13.27
16.34
16.05
17.34
19.12
21.43
23.40
22.52
23.80
26.31
24.22
26.17
24.92
21.02
24.24
32.76
33.90
34.70
37.47
38.03
40.42
42.33
39.00
38.77
38.87
IBM
IBM
181.27
184.36
195.33
191.38
184.10
174.70
185.47
177.67
176.25
182.13
179.26
190.87
187.02
203.57
197.28
207.77
195.62
196.97
185.80
184.36
187.87
200.34
188.17
188.46
188.08
185.50
198.30
199.81
188.39
183.72
175.40
179.34
175.42
166.14
163.33
172.02
162.28
159.80
160.65
153.57
152.45
151.96
137.67
132.70
134.11
General
Electric
GE
26.28
26.57
26.67
25.68
25.26
24.71
27.56
26.00
25.50
23.30
22.40
23.59
22.45
22.39
21.40
22.20
22.30
21.22
19.99
19.94
19.88
21.43
19.40
19.44
19.52
17.68
18.13
18.59
17.64
17.18
16.44
14.47
15.19
13.84
14.69
16.13
16.98
17.54
18.27
17.91
18.69
17.87
16.22
13.93
14.10
Nobel
Energy
NBL
77.46
72.07
71.60
70.86
68.59
62.03
67.78
69.90
74.57
66.57
61.02
61.94
59.51
57.14
56.00
57.17
54.79
53.17
50.18
48.21
46.74
45.61
43.24
42.90
41.62
41.45
48.63
47.88
47.81
49.18
46.12
48.07
43.54
34.50
43.06
48.47
43.58
45.32
46.71
46.90
44.96
44.12
41.69
39.35
39.37
SP500
180.83
177.94
173.88
172.63
171.22
163.75
169.64
165.47
160.61
153.57
148.92
153.39
145.98
147.98
144.62
141.91
136.80
134.99
128.35
127.21
126.50
128.90
125.66
122.91
121.25
116.46
123.91
124.71
120.75
115.76
110.81
109.69
109.95
99.13
106.66
112.80
115.16
117.12
118.48
115.08
115.05
111.25
108.69
101.89
101.89
38.97
36.98
40.86
37.47
39.47
43.55
40.36
33.89
33.09
32.99
40.96
40.76
48.24
49.83
31.59
29.60
37.07
47.94
46.00
44.23
47.07
49.64
53.00
56.93
54.16
51.02
50.54
47.49
45.27
45.34
46.18
47.70
44.12
41.45
38.90
35.48
42.48
39.94
42.38
47.87
48.96
46.72
43.29
47.38
45.05
43.58
43.35
41.15
39.69
35.43
31.42
125.27
114.99
119.32
114.75
116.40
119.27
118.57
117.57
112.65
120.48
116.30
110.52
109.60
108.17
107.56
95.24
96.93
14.30
12.65
14.08
12.60
14.19
16.37
15.80
13.94
13.87
13.05
13.73
12.22
14.08
11.85
11.42
9.99
11.39
36.29
33.72
32.32
29.08
28.67
36.73
35.10
34.93
35.47
34.16
31.30
31.40
31.55
28.92
29.15
28.13
28.37
98.17
90.14
94.41
88.24
93.13
101.22
99.65
94.00
91.18
94.58
92.78
87.54
89.21
86.00
83.02
77.17
77.00
Purchase answer to see full
attachment