Basic
A B C D E F G H I J K L
1 Two-Security Portfolios: BASIC Worksheet
2 8.0%
3 Parameters for two securities:
4 Er s Corr (r) 7.0% E
5 D 5.00% 8.00% -0.7
6 E 7.00% 16.00% -0.008960 6.0%
7
8 5.0% D
Expected return
9 w(D) w(E) ERP sP
4.0%
10 1 0 5.00% 8.00%
11 0.9 0.1 5.20% 6.19%
3.0%
12 0.8 0.2 5.40% 4.75%
13 0.7 0.3 5.60% 4.09%
2.0%
14 0.6 0.4 5.80% 4.58%
15 0.5 0.5 6.00% 5.93%
1.0%
16 0.4 0.6 6.20% 7.71%
17 0.3 0.7 6.40% 9.67%
18 0.2 0.8 6.60% 11.74% 0.0%
19 0.1 0.9 6.80% 13.85% 0.0% 5.0% 10.0% 15.0% 20.0%
Standard Deviation
20 0 1 7.00% 16.00%
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 Optimal Portfolio
42
43 Desired rate of return: 0.12
44
45 w(D) -2.50000
46 w(E) 3.50000
47 ERC 12.0000%
48 sC 71.4423%
49
50
51 Weight Weight Expected Standard
52 Bonds Stocks Return Deviation
53 0.00 1.00 0.00 7.00% 16.00%
54 0.01 0.99 0.01 6.98% 15.78%
55 0.02 0.98 0.02 6.96% 15.57%
56 0.03 0.97 0.03 6.94% 15.35%
57 0.04 0.96 0.04 6.92% 15.14%
58 0.05 0.95 0.05 6.90% 14.92%
59 0.06 0.94 0.06 6.88% 14.71%
60 0.07 0.93 0.07 6.86% 14.49%
61 0.08 0.92 0.08 6.84% 14.28%
62 0.09 0.91 0.09 6.82% 14.07%
63 0.10 0.90 0.10 6.80% 13.85%
64 0.11 0.89 0.11 6.78% 13.64%
65 0.12 0.88 0.12 6.76% 13.43%
66 0.13 0.87 0.13 6.74% 13.21%
67 0.14 0.86 0.14 6.72% 13.00%
68 0.15 0.85 0.15 6.70% 12.79%
69 0.16 0.84 0.16 6.68% 12.58%
70 0.17 0.83 0.17 6.66% 12.37%
71 0.18 0.82 0.18 6.64% 12.16%
72 0.19 0.81 0.19 6.62% 11.95%
73 0.20 0.80 0.20 6.60% 11.74%
74 0.21 0.79 0.21 6.58% 11.53%
75 0.22 0.78 0.22 6.56% 11.32%
76 0.23 0.77 0.23 6.54% 11.11%
77 0.24 0.76 0.24 6.52% 10.90%
78 0.25 0.75 0.25 6.50% 10.70%
79 0.26 0.74 0.26 6.48% 10.49%
80 0.27 0.73 0.27 6.46% 10.28%
81 0.28 0.72 0.28 6.44% 10.08%
82 0.29 0.71 0.29 6.42% 9.88%
83 0.30 0.70 0.30 6.40% 9.67%
84 0.31 0.69 0.31 6.38% 9.47%
85 0.32 0.68 0.32 6.36% 9.27%
86 0.33 0.67 0.33 6.34% 9.07%
87 0.34 0.66 0.34 6.32% 8.87%
88 0.35 0.65 0.35 6.30% 8.67%
89 0.36 0.64 0.36 6.28% 8.48%
90 0.37 0.63 0.37 6.26% 8.28%
91 0.38 0.62 0.38 6.24% 8.09%
92 0.39 0.61 0.39 6.22% 7.90%
93 0.40 0.60 0.40 6.20% 7.71%
94 0.41 0.59 0.41 6.18% 7.52%
95 0.42 0.58 0.42 6.16% 7.33%
96 0.43 0.57 0.43 6.14% 7.15%
97 0.44 0.56 0.44 6.12% 6.97%
98 0.45 0.55 0.45 6.10% 6.79%
99 0.46 0.54 0.46 6.08% 6.61%
100 0.47 0.53 0.47 6.06% 6.44%
101 0.48 0.52 0.48 6.04% 6.26%
102 0.49 0.51 0.49 6.02% 6.10%
103 0.50 0.50 0.50 6.00% 5.93%
104 0.51 0.49 0.51 5.98% 5.77%
105 0.52 0.48 0.52 5.96% 5.62%
106 0.53 0.47 0.53 5.94% 5.47%
107 0.54 0.46 0.54 5.92% 5.32%
108 0.55 0.45 0.55 5.90% 5.18%
109 0.56 0.44 0.56 5.88% 5.05%
110 0.57 0.43 0.57 5.86% 4.92%
111 0.58 0.42 0.58 5.84% 4.80%
112 0.59 0.41 0.59 5.82% 4.69%
113 0.60 0.40 0.60 5.80% 4.58%
114 0.61 0.39 0.61 5.78% 4.49%
115 0.62 0.38 0.62 5.76% 4.40%
116 0.63 0.37 0.63 5.74% 4.32%
117 0.64 0.36 0.64 5.72% 4.25%
118 0.65 0.35 0.65 5.70% 4.20%
119 0.66 0.34 0.66 5.68% 4.15%
120 0.67 0.33 0.67 5.66% 4.12%
121 0.68 0.32 0.68 5.64% 4.10%
122 0.69 0.31 0.69 5.62% 4.09%
123 0.70 0.30 0.70 5.60% 4.09%
124 0.71 0.29 0.71 5.58% 4.11%
125 0.72 0.28 0.72 5.56% 4.14%
126 0.73 0.27 0.73 5.54% 4.18%
127 0.74 0.26 0.74 5.52% 4.23%
128 0.75 0.25 0.75 5.50% 4.29%
129 0.76 0.24 0.76 5.48% 4.36%
130 0.77 0.23 0.77 5.46% 4.44%
131 0.78 0.22 0.78 5.44% 4.54%
132 0.79 0.21 0.79 5.42% 4.64%
133 0.80 0.20 0.80 5.40% 4.75%
134 0.81 0.19 0.81 5.38% 4.86%
135 0.82 0.18 0.82 5.36% 4.99%
136 0.83 0.17 0.83 5.34% 5.12%
137 0.84 0.16 0.84 5.32% 5.26%
138 0.85 0.15 0.85 5.30% 5.40%
139 0.86 0.14 0.86 5.28% 5.55%
140 0.87 0.13 0.87 5.26% 5.70%
141 0.88 0.12 0.88 5.24% 5.86%
142 0.89 0.11 0.89 5.22% 6.02%
143 0.90 0.10 0.90 5.20% 6.19%
144 0.91 0.09 0.91 5.18% 6.36%
145 0.92 0.08 0.92 5.16% 6.53%
146 0.93 0.07 0.93 5.14% 6.70%
147 0.94 0.06 0.94 5.12% 6.88%
148 0.95 0.05 0.95 5.10% 7.06%
149 0.96 0.04 0.96 5.08% 7.25%
150 0.97 0.03 0.97 5.06% 7.43%
151 0.98 0.02 0.98 5.04% 7.62%
152 0.99 0.01 0.99 5.02% 7.81%
153 1.00 0.00 1.00 5.00% 8.00%
MVP
A B C D E F G H I J K L M N
1 Two-Security Portfolios: MVP Worksheet
2 8.0%
3 Parameters for two securities:
4 Er s Corr (r) 7.0% E
5 D 5.00% 8.00% -0.7
6 E 7.00% 16.00% -0.008960 6.0%
7 MVP
8 5.0% D
Expected return
9
10 4.0%
11 Min Var Portfolio Short Sales No Short
12 Allowed Sales
3.0%
13 w(D) 0.69231 0.69231
14 w(E) 0.30769 0.30769
2.0%
15 ERP 5.6154% 5.6154%
16 sP 4.0913% 4.0913%
1.0%
17
18
0.0%
19
0.0% 5.0% 10.0% 15.0% 20.0%
20 Standard Deviation
21
22
23
24
25
26
27
28 w(D) w(E) ERP sP
29 1 0 5.00% 8.00%
30 0.9 0.1 5.20% 6.19%
31 0.8 0.2 5.40% 4.75%
32 0.7 0.3 5.60% 4.09%
33 0.6 0.4 5.80% 4.58%
34 Optimal Portfolio w/o a Risk Free Asset 0.5 0.5 6.00% 5.93%
35 0.4 0.6 6.20% 7.71%
36 Desired rate of return: 0.12 0.3 0.7 6.40% 9.67%
37 0.2 0.8 6.60% 11.74%
38 w(D) -2.50000 0.1 0.9 6.80% 13.85%
39 w(E) 3.50000 0 1 7.00% 16.00%
40 ERC 12.0000%
41 sC 71.4423%
42
43
44 Weight Weight Expected Standard
45 Bonds Stocks Return Deviation
46 0.00 1.00 0.00 7.00% 16.00%
47 0.01 0.99 0.01 6.98% 15.78%
48 0.02 0.98 0.02 6.96% 15.57%
49 0.03 0.97 0.03 6.94% 15.35%
50 0.04 0.96 0.04 6.92% 15.14%
51 0.05 0.95 0.05 6.90% 14.92%
52 0.06 0.94 0.06 6.88% 14.71%
53 0.07 0.93 0.07 6.86% 14.49%
54 0.08 0.92 0.08 6.84% 14.28%
55 0.09 0.91 0.09 6.82% 14.07%
56 0.10 0.90 0.10 6.80% 13.85%
57 0.11 0.89 0.11 6.78% 13.64%
58 0.12 0.88 0.12 6.76% 13.43%
59 0.13 0.87 0.13 6.74% 13.21%
60 0.14 0.86 0.14 6.72% 13.00%
61 0.15 0.85 0.15 6.70% 12.79%
62 0.16 0.84 0.16 6.68% 12.58%
63 0.17 0.83 0.17 6.66% 12.37%
64 0.18 0.82 0.18 6.64% 12.16%
65 0.19 0.81 0.19 6.62% 11.95%
66 0.20 0.80 0.20 6.60% 11.74%
67 0.21 0.79 0.21 6.58% 11.53%
68 0.22 0.78 0.22 6.56% 11.32%
69 0.23 0.77 0.23 6.54% 11.11%
70 0.24 0.76 0.24 6.52% 10.90%
71 0.25 0.75 0.25 6.50% 10.70%
72 0.26 0.74 0.26 6.48% 10.49%
73 0.27 0.73 0.27 6.46% 10.28%
74 0.28 0.72 0.28 6.44% 10.08%
75 0.29 0.71 0.29 6.42% 9.88%
76 0.30 0.70 0.30 6.40% 9.67%
77 0.31 0.69 0.31 6.38% 9.47%
78 0.32 0.68 0.32 6.36% 9.27%
79 0.33 0.67 0.33 6.34% 9.07%
80 0.34 0.66 0.34 6.32% 8.87%
81 0.35 0.65 0.35 6.30% 8.67%
82 0.36 0.64 0.36 6.28% 8.48%
83 0.37 0.63 0.37 6.26% 8.28%
84 0.38 0.62 0.38 6.24% 8.09%
85 0.39 0.61 0.39 6.22% 7.90%
86 0.40 0.60 0.40 6.20% 7.71%
87 0.41 0.59 0.41 6.18% 7.52%
88 0.42 0.58 0.42 6.16% 7.33%
89 0.43 0.57 0.43 6.14% 7.15%
90 0.44 0.56 0.44 6.12% 6.97%
91 0.45 0.55 0.45 6.10% 6.79%
92 0.46 0.54 0.46 6.08% 6.61%
93 0.47 0.53 0.47 6.06% 6.44%
94 0.48 0.52 0.48 6.04% 6.26%
95 0.49 0.51 0.49 6.02% 6.10%
96 0.50 0.50 0.50 6.00% 5.93%
97 0.51 0.49 0.51 5.98% 5.77%
98 0.52 0.48 0.52 5.96% 5.62%
99 0.53 0.47 0.53 5.94% 5.47%
100 0.54 0.46 0.54 5.92% 5.32%
101 0.55 0.45 0.55 5.90% 5.18%
102 0.56 0.44 0.56 5.88% 5.05%
103 0.57 0.43 0.57 5.86% 4.92%
104 0.58 0.42 0.58 5.84% 4.80%
105 0.59 0.41 0.59 5.82% 4.69%
106 0.60 0.40 0.60 5.80% 4.58%
107 0.61 0.39 0.61 5.78% 4.49%
108 0.62 0.38 0.62 5.76% 4.40%
109 0.63 0.37 0.63 5.74% 4.32%
110 0.64 0.36 0.64 5.72% 4.25%
111 0.65 0.35 0.65 5.70% 4.20%
112 0.66 0.34 0.66 5.68% 4.15%
113 0.67 0.33 0.67 5.66% 4.12%
114 0.68 0.32 0.68 5.64% 4.10%
115 0.69 0.31 0.69 5.62% 4.09%
116 0.70 0.30 0.70 5.60% 4.09%
117 0.71 0.29 0.71 5.58% 4.11%
118 0.72 0.28 0.72 5.56% 4.14%
119 0.73 0.27 0.73 5.54% 4.18%
120 0.74 0.26 0.74 5.52% 4.23%
121 0.75 0.25 0.75 5.50% 4.29%
122 0.76 0.24 0.76 5.48% 4.36%
123 0.77 0.23 0.77 5.46% 4.44%
124 0.78 0.22 0.78 5.44% 4.54%
125 0.79 0.21 0.79 5.42% 4.64%
126 0.80 0.20 0.80 5.40% 4.75%
127 0.81 0.19 0.81 5.38% 4.86%
128 0.82 0.18 0.82 5.36% 4.99%
129 0.83 0.17 0.83 5.34% 5.12%
130 0.84 0.16 0.84 5.32% 5.26%
131 0.85 0.15 0.85 5.30% 5.40%
132 0.86 0.14 0.86 5.28% 5.55%
133 0.87 0.13 0.87 5.26% 5.70%
134 0.88 0.12 0.88 5.24% 5.86%
135 0.89 0.11 0.89 5.22% 6.02%
136 0.90 0.10 0.90 5.20% 6.19%
137 0.91 0.09 0.91 5.18% 6.36%
138 0.92 0.08 0.92 5.16% 6.53%
139 0.93 0.07 0.93 5.14% 6.70%
140 0.94 0.06 0.94 5.12% 6.88%
141 0.95 0.05 0.95 5.10% 7.06%
142 0.96 0.04 0.96 5.08% 7.25%
143 0.97 0.03 0.97 5.06% 7.43%
144 0.98 0.02 0.98 5.04% 7.62%
145 0.99 0.01 0.99 5.02% 7.81%
146 1.00 0.00 1.00 5.00% 8.00%
+RiskFree
A B C D E F G H I J K L
1 Two-Security Portfolios: +RiskFree Worksheet
2 8.0%
3 Asset Allocation Analysis: Risk and Return
4 Er s Corr (r) 7.0% 0.07 E
5 D 5.00% 8.00% -0.7 D E rf
6 E 7.00% 16.00% -0.008960 0.05 5.00%
6.0%
7 rf 5.00% 0
8
5.0% D
Expected return
9
rf
10 Optimal Risky Port Short Sales No Short
4.0%
11 Allowed Sales
12 w(D) 0.58333 0.58333
13 w(E) 0.41667 0.41667 3.0%
14 ERP 5.8333% 5.8333%
15 sP 4.7610% 4.7610% 2.0%
16
17 Reward to variability 0.17504 0.17504 1.0%
18
19 0.0%
20 0.0% 5.0% 10.0% 15.0% 20.0%
21 Standard Deviation
22
23
24
25
26
27
28
29 Optimal Portfolio with a Risk Free Asset
30 Short Sales No Short
31 Allowed Sales
32 Desired rate of return: 0.12
33
34 Weight OP 8.40000 8.40000
35 Weight RF -7.40000 -7.40000
36 ERC 12.0000% 12.0000%
37 sC 39.9920% 39.9920%
38
39
40 w(D) w(E) ERP sP
41 1 0 5.00% 8.00%
42 0.9 0.1 5.20% 6.19%
43 0.8 0.2 5.40% 4.75%
44 0.7 0.3 5.60% 4.09%
45 0.6 0.4 5.80% 4.58%
46 0.5 0.5 6.00% 5.93%
47 0.4 0.6 6.20% 7.71%
48 0.3 0.7 6.40% 9.67%
49 Optimal Portfolio w/o a Risk Free Asset 0.2 0.8 6.60% 11.74%
50 0.1 0.9 6.80% 13.85%
51 Desired rate of return: 0.12 0 1 7.00% 16.00%
52
53 w(D) -2.50000 Min Var Portfolio Short SalesNo Short
54 w(E) 3.50000 Allowed Sales
55 ERC 12.0000% CAL(MV) w(D) 0.69231 0.69231
56 sC 71.4423% w(E) 0.30769 0.30769
57 ERP 5.6154% 5.6154%
58 sP 4.0913% 4.0913%
59 Weight Weight Expected Standard
60 Bonds Stocks Return Deviation
61 0.00 1.00 0.00 7.00% 16.00%
62 0.01 0.99 0.01 6.98% 15.78%
63 0.02 0.98 0.02 6.96% 15.57%
64 0.03 0.97 0.03 6.94% 15.35%
65 0.04 0.96 0.04 6.92% 15.14%
66 0.05 0.95 0.05 6.90% 14.92%
67 0.06 0.94 0.06 6.88% 14.71%
68 0.07 0.93 0.07 6.86% 14.49%
69 0.08 0.92 0.08 6.84% 14.28%
70 0.09 0.91 0.09 6.82% 14.07%
71 0.10 0.90 0.10 6.80% 13.85%
72 0.11 0.89 0.11 6.78% 13.64%
73 0.12 0.88 0.12 6.76% 13.43%
74 0.13 0.87 0.13 6.74% 13.21%
75 0.14 0.86 0.14 6.72% 13.00%
76 0.15 0.85 0.15 6.70% 12.79%
77 0.16 0.84 0.16 6.68% 12.58%
78 0.17 0.83 0.17 6.66% 12.37%
79 0.18 0.82 0.18 6.64% 12.16%
80 0.19 0.81 0.19 6.62% 11.95%
81 0.20 0.80 0.20 6.60% 11.74%
82 0.21 0.79 0.21 6.58% 11.53%
83 0.22 0.78 0.22 6.56% 11.32%
84 0.23 0.77 0.23 6.54% 11.11%
85 0.24 0.76 0.24 6.52% 10.90%
86 0.25 0.75 0.25 6.50% 10.70%
87 0.26 0.74 0.26 6.48% 10.49%
88 0.27 0.73 0.27 6.46% 10.28%
89 0.28 0.72 0.28 6.44% 10.08%
90 0.29 0.71 0.29 6.42% 9.88%
91 0.30 0.70 0.30 6.40% 9.67%
92 0.31 0.69 0.31 6.38% 9.47%
93 0.32 0.68 0.32 6.36% 9.27%
94 0.33 0.67 0.33 6.34% 9.07%
95 0.34 0.66 0.34 6.32% 8.87%
96 0.35 0.65 0.35 6.30% 8.67%
97 0.36 0.64 0.36 6.28% 8.48%
98 0.37 0.63 0.37 6.26% 8.28%
99 0.38 0.62 0.38 6.24% 8.09%
100 0.39 0.61 0.39 6.22% 7.90%
101 0.40 0.60 0.40 6.20% 7.71%
102 0.41 0.59 0.41 6.18% 7.52%
103 0.42 0.58 0.42 6.16% 7.33%
104 0.43 0.57 0.43 6.14% 7.15%
105 0.44 0.56 0.44 6.12% 6.97%
106 0.45 0.55 0.45 6.10% 6.79%
107 0.46 0.54 0.46 6.08% 6.61%
108 0.47 0.53 0.47 6.06% 6.44%
109 0.48 0.52 0.48 6.04% 6.26%
110 0.49 0.51 0.49 6.02% 6.10%
111 0.50 0.50 0.50 6.00% 5.93%
112 0.51 0.49 0.51 5.98% 5.77%
113 0.52 0.48 0.52 5.96% 5.62%
114 0.53 0.47 0.53 5.94% 5.47%
115 0.54 0.46 0.54 5.92% 5.32%
116 0.55 0.45 0.55 5.90% 5.18%
117 0.56 0.44 0.56 5.88% 5.05%
118 0.57 0.43 0.57 5.86% 4.92%
119 0.58 0.42 0.58 5.84% 4.80%
120 0.59 0.41 0.59 5.82% 4.69%
121 0.60 0.40 0.60 5.80% 4.58%
122 0.61 0.39 0.61 5.78% 4.49%
123 0.62 0.38 0.62 5.76% 4.40%
124 0.63 0.37 0.63 5.74% 4.32%
125 0.64 0.36 0.64 5.72% 4.25%
126 0.65 0.35 0.65 5.70% 4.20%
127 0.66 0.34 0.66 5.68% 4.15%
128 0.67 0.33 0.67 5.66% 4.12%
129 0.68 0.32 0.68 5.64% 4.10%
130 0.69 0.31 0.69 5.62% 4.09%
131 0.70 0.30 0.70 5.60% 4.09%
132 0.71 0.29 0.71 5.58% 4.11%
133 0.72 0.28 0.72 5.56% 4.14%
134 0.73 0.27 0.73 5.54% 4.18%
135 0.74 0.26 0.74 5.52% 4.23%
136 0.75 0.25 0.75 5.50% 4.29%
137 0.76 0.24 0.76 5.48% 4.36%
138 0.77 0.23 0.77 5.46% 4.44%
139 0.78 0.22 0.78 5.44% 4.54%
140 0.79 0.21 0.79 5.42% 4.64%
141 0.80 0.20 0.80 5.40% 4.75%
142 0.81 0.19 0.81 5.38% 4.86%
143 0.82 0.18 0.82 5.36% 4.99%
144 0.83 0.17 0.83 5.34% 5.12%
145 0.84 0.16 0.84 5.32% 5.26%
146 0.85 0.15 0.85 5.30% 5.40%
147 0.86 0.14 0.86 5.28% 5.55%
148 0.87 0.13 0.87 5.26% 5.70%
149 0.88 0.12 0.88 5.24% 5.86%
150 0.89 0.11 0.89 5.22% 6.02%
151 0.90 0.10 0.90 5.20% 6.19%
152 0.91 0.09 0.91 5.18% 6.36%
153 0.92 0.08 0.92 5.16% 6.53%
154 0.93 0.07 0.93 5.14% 6.70%
155 0.94 0.06 0.94 5.12% 6.88%
156 0.95 0.05 0.95 5.10% 7.06%
157 0.96 0.04 0.96 5.08% 7.25%
158 0.97 0.03 0.97 5.06% 7.43%
159 0.98 0.02 0.98 5.04% 7.62%
160 0.99 0.01 0.99 5.02% 7.81%
161 1.00 0.00 1.00 5.00% 8.00%