资产A 资产B 资产C 16% 0
资产A 0 24% 23% 21% 19% 18% 17% 16%
资产B 24% 21% 19% 17% 16% 14% 13%
资产C 23% 21% 19% 16% 14% 12% 11%
s m 24% 21% 19% 16% 14% 12% 10%
资产A统计特征 % 7% 24% 22% 19% 17% 15% 12% 10%
资产B统计特征 % 13% 26% 23% 21% 19% 16% 14%
资产C统计特征 % 19% 28% 26% 23% 21% 19%
30% 28% 26% 23%
投资机会Z中资产A的系数 w1 33% 31% 28%
投资机会Z中资产B的系数 w2 36% 34%
投资机会Z中资产C的系数 w3 1 39%
投资机会Z的方差
投资机会Z的标准差 % 11% 0
投资机会Z的期望回报率 % 0 19% 18% 17% 15% 14% 13% 12%
18% 17% 16% 15% 14% 12% 11%
18% 17% 15% 14% 13% 12% 11%
17% 16% 15% 14% 12% 11% 10%
17% 15% 14% 13% 12% 11% 9%
16% 15% 14% 12% 11% 10%
15% 14% 13% 12% 11%
15% 14% 12% 11%
14% 13% 12%
14% 12%
1 13%
协方差矩阵
A B C D E F G H I J K L M N O P Q
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
1
16% 16% 16% 17%
12% 12% 13%
10% 10%
8%
1
11% 9% 8% 7%
10% 9% 8%
9% 8%
9%
R S T U V
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
资产A 资产B 资产C % % %
资产A -20% % % %
资产B 0% % % %
资产C 1% % % %
1 u 2% % % %
资产A期望回报率 7% 1 175 3% % % %
资产B期望回报率 13% 1 75 4% % % %
资产C期望回报率 19% 1 -50 5% % % %
S-1m S -1e p q 6% % % %
中间向量
175 7% % % %
75 8% % % %
-50 9% % % %
10% % % %
A / B / C / D 200 12% % % %
a / b / m0 14% % % %
s m 16% % % %
最小方差曲线上点的坐标 % 16% 18% % % %
MVP的坐标 % % 20% % % %
动点 MVP 25% % % %
w1 30% % % %
w2
0 16% 0
% 16%
% 0 0
协方差矩阵
AB C D E F G H I J K L M N OP
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
)(
1 11 eBmC
D
p
rrr -- S-S= )(
1 11 mBeA
D
q
rrr -- S-S=
qpmw
rrr
+=
mmA T
rr 1-S= emB T
rr 1-S= eeC T
rr 1-S= 2BACD -=
Q R S T U V W
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
A
B
C
MVP
16%
%
P
P点沿最小方差曲线的运动
50%40%30%20%10%0%
0%
5%
10%
15%
20%
25%
s
m
s 2 2
=
s 3 2
=
m = 16%
B
AC
MVP
Q
Q点沿关键线的运动
-1
0
1
2
w1
w2
B
AC
MVP
Q
Q点沿关键线的运动
-1
0
1
2
w1
w2
in E6:
资产A期望回报率m1 7% -16% in D9:
资产B期望回报率m2 13% -12%
资产C期望回报率m3 19% -8% in D12:
等期望回报率直线族斜率 k -2 -4% in D13:
经过当前动点 经过任意点 0%
期望回报率常数值 m0 16% 8% 4% in D20:
等期望回报率直线截距 b 8% in E20:
左边界点 右边界点 12%
等期望回报率直线边界点横坐标 16% in D23:
与动点对应的等值线边界点纵坐标 20%
等期望回报率直线边界点纵坐标 24%
p q
p1 / q1
p2 / q2
关键线斜率/截距
关键线左右端点横坐标
关键线左右端点纵坐标
32
30
mm
mm
b
-
-
=
32
31
mm
mm
k
-
-
-=
12 kwbw +=
A B C D E F GH I J K L M 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
=-(E3-E5)/(E4-E5)
=(D8-$E$5)/($E$4-$E$5)
=$D9+$E$6*D11
=$E9+$E$6*D11
=D18/D17
=(D17*E18-D18*E17)/D17
=$E$20+$D$20*D22
32
30
mm
mm
b
-
-
=
32
31
mm
mm
k
-
-
-=
12 kwbw +=
O P Q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
2
1 ACBE -=
1012 ACBBF -=
0
2
0 ACBG -=
GFwEwACB ++=-=D 1
2
1
2
011 BwBB +=
011
2
12 CwCwCC ++=
)(
1
21 D--= BA
w
)(
1
22 D+-= BA
w
)4(
2
1 2
11 EGFFE
w -+-=
)4(
2
1 2
12 EGFFE
w ---=
23
2
3
2
2 2 sss -+=A
î
í
ì
-=
--+=
2
3230
231312
2
31
ss
ssss
B
B
ï
î
ï
í
ì
-=
-=
-+=
2
0
2
30
2
3131
13
2
3
2
12
)(2
2
ss
ss
sss
C
C
C
02 2
2
2 =++ CBwAw
2
0
2 ss =
0=D 的根
12111 www ££对于
2321213211
1221
2
3
2
21
2
2
2
2
2
1
2
1
2
)1(2)1(2
2)1(
ss
sssss
wwwwww
wwwwww
--+--+
+--++= 2
02121
2
2
2
1 s=+++++ fewdwwcwbwaw
2
2
1 ACBE -=
1012 ACBBF -=
0
2
0 ACBG -=
GFwEwACB ++=-=D 1
2
1
2
011 BwBB +=
011
2
12 CwCwCC ++=
)(
1
21 D--= BA
w
)(
1
22 D+-= BA
w
)4(
2
1 2
11 EGFFE
w -+-=
)4(
2
1 2
12 EGFFE
w ---=
23
2
3
2
2 2 sss -+=A
î
í
ì
-=
--+=
2
3230
231312
2
31
ss
ssss
B
B
ï
î
ï
í
ì
-=
-=
-+=
2
0
2
30
2
3131
13
2
3
2
12
)(2
2
ss
ss
sss
C
C
C
02 2
2
2 =++ CBwAw
2
0
2 ss =
0=D 的根
12111 www ££对于
2321213211
1221
2
3
2
21
2
2
2
2
2
1
2
1
2
)1(2)1(2
2)1(
ss
sssss
wwwwww
wwwwww
--+--+
+--++= 2
02121
2
2
2
1 s=+++++ fewdwwcwbwaw
资产A 资产B 资产C 19% 10% 19% 28% 37% 19%
资产A 0 0
资产B
资产C 1 1
s m 2 2
资产A统计特征 % 7% 3 3
资产B统计特征 % 13% 4 4
资产C统计特征 % 19% 5 -0 5 0
6 6 -0
标准差常数值 s0 16% 7 7 -0
A / B1 / B0 8 8 -0
C2 / C1 / C0 9 9
E / F / G -0 10 10
11 -0 11
w11 / w12 12 12
步长 13 13
形式自变量 / w1 3 14 14
B / C 15 15
D 6E-04 16 16
w21 / w22 17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
31 31
32 32
33 33
34 34
35 35
36 36 -0
37 37 -0
38 38 -0
39 39 -0
40 40 -0
41 41 -0
42 42
43 1 43
44 44
45 45
46 46
47 47
48 48
协方差矩阵
AB C D E F GH I J K L M N O PQR S T
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
49 49
50 50
51 51
52 52
53 53
54 54
55 55
56 56
57 57
58 58
59 59
60 60
AB C D E F GH I J K L M N O PQR S T
53
54
55
56
57
58
59
60
61
62
63
64
65
66
10% 19% 28% 37% 19% 10% 19% 28% 37% in D12: =E4+F5-2*F4
0 in E12: =F5+E3-F3-F4
in F12: =F4-F5
-0 1 in D13: =D3+F5-2*F3
2 in E13: =2*(F3-F5)
3 in F13: =F5-F11^2
4 in D14: =E12^2-D12*D13
5 in E14: =2*E12*F12-D12*E13
-0 6 in F14: =F12^2-D12*F13
-0 7
-0 8 in E16: =(-E14+SQRT(E14^2-4*D14*F14))/(2*D14)
-0 9 in F16: =(-E14-SQRT(E14^2-4*D14*F14))/(2*D14)
10 in F17: =(F16-E16)/60
11 in F18: =E16+E18*F17
12 in E19: =E12*F18+F12
13 in F19: =D13*F18^2+E13*F18+F13
14 in F20: =E19^2-D12*F19
15 in E21: =(-E19-SQRT(F20))/D12
16 in F21: =(-E19+SQRT(F20))/D12
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-0 36
-0 37
-0 38
-0 39
-0 40
0 41
42
43
44
45
46
47
48
U V W X YZAA AB AC AD AE AF AG AHAI AJ AK AL
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
-0 49
-0 50
51
52
-0 53
54
55
56
57
58
59
60
U V W X YZAA AB AC AD AE AF AG AHAI AJ AK AL
53
54
55
56
57
58
59
60
61
62
63
64
65
66
=E12^2-D12*D13
=2*E12*F12-D12*E13
=F12^2-D12*F13
=(-E14+SQRT(E14^2-4*D14*F14))/(2*D14)
=(-E14-SQRT(E14^2-4*D14*F14))/(2*D14)
=E16+E18*F17
=E12*F18+F12
=D13*F18^2+E13*F18+F13
=E19^2-D12*F19
=(-E19-SQRT(F20))/D12
=(-E19+SQRT(F20))/D12
AM AN AO
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
资产A 资产B 资产C 8% 4%
资产A -10 48% 32%
资产B -9 44% 30%
资产C -8 40% 28%
s m 1 -7 36% 25%
资产A统计特征 / 1 % 7% 1 -6 31% 23%
资产B统计特征 / 2 % 13% 1 -5 27% 20%
资产C统计特征 / 3 % 19% 1 -4 23% 18%
v u w2 w1 -3 19% 16%
175 -2 15% 13%
75 -1 11% 11%
-50 10% 10%
MVP 0 8% 9%
a 2 w1 7% 7%
w2 1 7% 6%
w3 7% 5%
MVP 2 8% 4%
最小方差曲线上点的横坐标 s 8% % 10% 3%
最小方差曲线上点的纵坐标 m 4% % 3 11% 2%
s / m w1 / w2 13% 0%
最小方差曲线上动点横坐标 / w2 % 4 15% -1%
最小方差曲线上动点纵坐标 / w1 16% 7 27% -8%
8 31% -10%
9 36% -13%
0 % 12 48% -20%
% %
% 0
% 18%
% 16%
% 16%
协方差矩阵
两组特定
组合系数
的计算
AB C D E F G H I J K L M N O PQ
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
in D11:D13: {=MMULT(MINVERSE($E$3:$G$5),F7:F9)}
in F11: =D11/SUM(D$11:D$13)
in G17: =$G$16*F12+(1-$G$16)*G12
in F15: =$D$15*G11+(1-$D$15)*F11
in G15: =G11
in F19: {=SQRT(MMULT(TRANSPOSE(F15:F17),MMULT(E3:G5,F15:F17)))}
in F20: {=SUM(F15:F17*F7:F9)}
in G19: {=SQRT(MMULT(TRANSPOSE(G11:G13),MMULT(E3:G5,G11:G13)))}
in G20: {=SUM(G11:G13*F7:F9)}
in M30: =F23
in M29: =INDEX(M3:M26,MATCH($M$30,$M$3:$M$26,-1))
in M31: =INDEX(M3:M26,MATCH($M$30,$M$3:$M$26,-1)+1)
in L30: =L29+(L31-L29)*($M$30-$M$29)/($M$31-$M$29)
in F22: =L30
in G22: =N30
in G23: =O30
R S
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
