SHORT A
B
C
D
E
F
G
H
I
J
K
L
NO SALES
c
Means
8%
9%
10%
11%
0.05
0.03
0.2
0.9
Ctrl+A works the VBA program
which calculates efficient
portfolios for no-short sales.
This program iteratively
substitutes a constant ranging
from -3.5% 'till 16% (1/2%
jumps) and calculates the
optimal portfolio.
16.0% <-- This is the constant
Optimal portfolio proportions
x1
0.0000
x2
0.0000
x3
0.0000
x4
1.0000
Total
1
###
Portfolio mean
Portfolio sigma
Theta
11.00% <-- {=MMULT(TRANSPOSE(C12:C15),G4:G7)}
94.87% <-- {=SQRT(MMULT(TRANSPOSE(C12:C15),MMULT(B4:E7,C12:C15)))}
-5.27% <-- =(B18-C9)/B19
Note: Because the formulas in cells B18 and B19 use the TRANSPOSE function, they must be
entered as arrays, with [Ctrl]-[Shift]-[Enter] . The curly brackets are entered automatically
by Excel. See section 9.4 of Chapter 9 for more details.
To compute the values given in pages 201-204: Substitute in the appropriate
value for c, and then bring up the Excel Solver: Tools|Solver.
In most cases this is enough; however, you may have to load the Solver: Tools|Add-ins,
then click the box for the Solver Add-in.
Variance-covariance matrix
0.1
0.03
-0.08
0.03
0.2
0.02
-0.08
0.02
0.3
0.05
0.03
0.2
c
M
N
Sigma
20.24%
20.25%
20.25%
20.25%
20.25%
20.26%
20.26%
20.27%
20.27%
20.28%
20.29%
20.30%
20.31%
20.32%
20.34%
20.37%
20.41%
20.46%
20.54%
20.67%
20.90%
21.36%
23.27%
31.91%
45.25%
60.76%
74.30%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
94.87%
Means
minus c
0.0%
1.0%
2.0%
3.0%
Means
8%
9%
10%
11%
0.05
0.03
0.2
0.9
c
-0.035
-0.03
-0.025
-0.02
-0.015
-0.01
-0.005
0
0.005
0.01
0.015
0.02
0.025
0.03
0.035
0.04
0.045
0.05
0.055
0.06
0.065
0.07
0.075
0.08
0.085
0.09
0.095
0.1
0.105
0.11
0.115
0.12
0.125
0.13
0.135
0.14
0.145
0.15
0.155
0.16
8.0%
z
1.4705
0.1701
0.8110
-0.1454
mean
variance
covariance
O
P
Q
R
S
x1
0.605
0.604
0.603
0.603
0.602
0.601
0.599
0.598
0.597
0.595
0.593
0.591
0.589
0.586
0.582
0.578
0.573
0.566
0.557
0.545
0.528
0.499
0.427
0.200
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
x2
0.089
0.089
0.089
0.089
0.090
0.090
0.091
0.091
0.092
0.093
0.093
0.094
0.095
0.097
0.098
0.100
0.102
0.105
0.108
0.113
0.120
0.132
0.163
0.259
0.251
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
x3
0.307
0.307
0.307
0.308
0.309
0.309
0.310
0.311
0.311
0.312
0.313
0.315
0.316
0.318
0.320
0.322
0.325
0.329
0.334
0.342
0.352
0.368
0.386
0.422
0.488
0.556
0.300
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
x4
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.025
0.119
0.260
0.444
0.700
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
RESULTS
Variance-covariance matrix
0.1
0.03
-0.08
0.03
0.2
0.02
-0.08
0.02
0.3
0.05
0.03
0.2
x
63.76%
7.38%
35.17%
-6.30%
z
0.0287
0.0371
0.0605
0.0171
8.59%
3.72%
y
20.04%
25.87%
42.19%
11.90%
transpose y
20.04%
25.87%
42.19%
11.90%
sigma
23.51%
98.98%
94.77%
90.56%
86.37%
82.18%
78.01%
73.85%
69.71%
65.59%
61.50%
57.43%
53.40%
49.41%
45.49%
41.63%
31.91%
28.60%
25.58%
22.97%
20.93%
19.64%
19.23%
x
63.76%
7.38%
35.17%
-6.30%
transpose19.78%
21.20%
23.34%
26.02%
29.08%
32.43%
35.98%
mean
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
12.07%
9.46%
10.18%
4.10%
prop. x
mean
variance
sigma
0.5
12.07%
5.53%
23.51%
start
step
-3
0.18
Efficient Frontier, Short Sales Allowed
9.5%
9.0%
Mea n
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
8.5%
8.0%
7.5%
18%
19%
20%
21%
Sigma
22%
23%
24%
-3
-2.82
-2.64
-2.46
-2.28
-2.1
-1.92
-1.74
-1.56
-1.38
-1.2
-1.02
-0.84
-0.66
-0.48
0
0.18
0.36
0.54
0.72
0.9
1.08
1.26
1.44
1.62
1.8
1.98
2.16
2.34
Mean
8.70%
8.70%
8.70%
8.71%
8.71%
8.71%
8.71%
8.71%
8.71%
8.72%
8.72%
8.72%
8.73%
8.73%
8.74%
8.74%
8.75%
8.76%
8.78%
8.80%
8.82%
8.87%
9.01%
9.46%
10.01%
10.44%
10.70%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
11.00%
Efficient Frontier
N o Short Sales Allowed
12%
11%
11%
Mean
10%
10%
9%
9%
8%
18%
28%
38%
4 8%
58%
Sigma
68%
78%
88%
98%
108%
Comparing Two Efficient Frontiers
For low sigmas the two frontiers coincide.
For higher sigmas, no restrictions on short sales
gives higher returns.
12.0%
11.0%
Me a n
10.0%
9.0%
8.0%
7.0%
6.0%
19%
29%
39%
49%
59%
69%
Sigma
No short sales
Sh ort sales allowed
79%
89%
99%
Note: To get the two data series (with and without short sales) to chart on the same
set of axes, we copy all the sigmas in one column and then copy the means in
two adjacent columns. The graph is produced with an XY graph format.
down to here
Sigma
No short sales
Short sales allowed
20.24%
8.70%
20.25%
8.70%
20.25%
8.70%
20.25%
8.71%
20.25%
8.71%
20.26%
8.71%
20.26%
8.71%
20.27%
8.71%
20.27%
8.71%
20.28%
8.72%
20.29%
8.72%
20.30%
8.72%
20.31%
8.73%
20.32%
8.73%
20.34%
8.74%
20.37%
8.74%
20.41%
8.75%
20.46%
8.76%
20.54%
8.78%
20.67%
8.80%
20.90%
8.82%
21.36%
8.87%
23.27%
9.01%
31.91%
9.46%
45.25%
10.01%
60.76%
10.44%
74.30%
10.70%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
94.87%
11.00%
98.98%
12.07%
94.77%
12.07%
90.56%
12.07%
86.37%
12.07%
82.18%
12.07%
78.01%
12.07%
73.85%
12.07%
69.71%
12.07%
65.59%
12.07%
61.50%
12.07%
57.43%
12.07%
53.40%
12.07%
49.41%
12.07%
45.49%
12.07%
41.63%
12.07%
31.91%
12.07%
28.60%
12.07%
25.58%
12.07%
22.97%
12.07%
20.93%
12.07%
19.64%
12.07%
19.23%
12.07%
19.78%
12.07%
21.20%
12.07%
23.34%
12.07%
26.02%
12.07%
29.08%
12.07%
32.43%
12.07%
35.98%
12.07%