三个资产的组合模型
资产A 资产B 资产C 16% 0 1
资产A 0 24% 23% 21% 19% 18% 17% 16% 16% 16% 16% 17%
资产B 15 24% 21% 19% 17% 16% 14% 13% 12% 12% 13%
资产C 6 23% 21% 19% 16% 14% 12% 11% 10% 10%
s m 24% 21% 19% 16% 14% 12% 10% 8%
资产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 15 33% 31% 28%
投资机会Z中资产B的系数 w2 12 36% 34%
投资机会Z中资产C的系数 w3 1 39%
投资机会Z的方差
投资机会Z的标准差 % 11% 0 1
投资机会Z的期望回报率 % 0 19% 18% 17% 15% 14% 13% 12% 11% 9% 8% 7%
18% 17% 16% 15% 14% 12% 11% 10% 9% 8%
18% 17% 15% 14% 13% 12% 11% 9% 8%
17% 16% 15% 14% 12% 11% 10% 9%
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 % % %
资产A -20% % % %
资产B 0% % % %
资产C 15 1% % % %
1 u 6 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 16
0 16% 0
% 16%
% 0 0
协方差矩阵
联合可调图形
联合可调图形
0
0
s
m
P点沿最小方差曲线的运动
A
B
C
MVP
P
等期望回报率直线计算
in E6: =-(E3-E5)/(E4-E5)
资产A期望回报率m1 7% -16% in D9: =(D8-$E$5)/($E$4-$E$5)
资产B期望回报率m2 13% -12%
资产C期望回报率m3 19% -8% in D12: =$D9+$E$6*D11
等期望回报率直线族斜率 k -2 -4% in D13: =$E9+$E$6*D11
经过当前动点 经过任意点 0%
期望回报率常数值 m0 16% 8% 4% in D20: =D18/D17
等期望回报率直线截距 b 8% in E20: =(D17*E18-D18*E17)/D17
左边界点 右边界点 12%
等期望回报率直线边界点横坐标 16% in D23: =$E$20+$D$20*D22
与动点对应的等值线边界点纵坐标 20%
等期望回报率直线边界点纵坐标 24%
p q
p1 / q1
p2 / q2
关键线斜率/截距
关键线左右端点横坐标
关键线左右端点纵坐标
s22 =
s32 =
m =
16%
w1
w2
Q点沿关键线的运动
B
A
C
MVP
Q
0
0
1
0
0
1
0
0
等标准差椭圆计算公式
的根
对于
等标准差椭圆计算
资产A 资产B 资产C 19% 10% 19% 28% 37% 19% 10% 19% 28% 37% 19% 10% 19% 28% 37% in D12: =E4+F5-2*F4
资产A 0 0 0 in E12: =F5+E3-F3-F4
资产B in F12: =F4-F5
资产C 1 1 1 in D13: =D3+F5-2*F3
s m 2 2 2 in E13: =2*(F3-F5)
资产A统计特征 % 7% 3 3 3 in F13: =F5-F11^2
资产B统计特征 % 13% 4 4 4 in D14: =E12^2-D12*D13
资产C统计特征 % 19% 5 5 5 in E14: =2*E12*F12-D12*E13
6 6 6 in F14: =F12^2-D12*F13
标准差常数值 s0 16% 35 7 7 7
A / B1 / B0 176 8 8 8 in E16: =(-E14+SQRT(E14^2-4*D14*F14))/(2*D14)
C2 / C1 / C0 9 9 9 in F16: =(-E14-SQRT(E14^2-4*D14*F14))/(2*D14)
E / F / G 10 10 10 in F17: =(F16-E16)/60
11 11 11 in F18: =E16+E18*F17
w11 / w12 12 12 12 in E19: =E12*F18+F12
步长 13 13 13 in F19: =D13*F18^2+E13*F18+F13
形式自变量 / w1 3 14 14 14 in F20: =E19^2-D12*F19
B / C 15 15 15 in E21: =(-E19-SQRT(F20))/D12
D 16 16 16 in F21: =(-E19+SQRT(F20))/D12
w21 / w22 17 17 17
18 18 18
19 19 19
20 20 20
21 21 21
22 22 22
23 23 23
24 24 24
25 25 25
26 26 26
27 27 27
28 28 28
29 29 29
30 30 30
31 31 31
32 32 32
33 33 33
34 34 34
35 35 35
36 36 36
37 37 37
38 38 38
39 39 39
40 40 40
41 41 41
42 42 42
43 43 43
44 44 44
45 45 45
46 46 46
47 47 47
48 48 48
49 49 49
50 50 50
51 51 51
52 52 52
53 53 53
54 54 54
55 55 55
56 56 56
57 57 57
58 58 58
59 59 59
60
协方差矩阵
最小方差曲线计算模型2
资产A 资产B 资产C 8% 4% in D11:D13: {=MMULT(MINVERSE($E$3:$G$5),F7:F9)}
资产A -10 48% 32%
资产B -9 44% 30% in F11: =D11/SUM(D$11:D$13)
资产C -8 40% 28%
s m 1 -7 36% 25% in G17: =$G$16*F12+(1-$G$16)*G12
资产A统计特征 / 1 % 7% 1 -6 31% 23%
资产B统计特征 / 2 % 13% 1 -5 27% 20% in F15: =$D$15*G11+(1-$D$15)*F11
资产C统计特征 / 3 % 19% 1 -4 23% 18% in G15: =G11
v u w2 w1 -3 19% 16%
175 35 -2 15% 13% in F19: {=SQRT(MMULT(TRANSPOSE(F15:F17),MMULT(E3:G5,F15:F17)))}
75 176 -1 11% 11% in F20: {=SUM(F15:F17*F7:F9)}
-50 10% 10% in G19: {=SQRT(MMULT(TRANSPOSE(G11:G13),MMULT(E3:G5,G11:G13)))}
MVP 0 8% 9% in G20: {=SUM(G11:G13*F7:F9)}
a 2 w1 7% 7%
w2 1 7% 6% in M30: =F23
w3 7% 5% in M29: =INDEX(M3:M26,MATCH($M$30,$M$3:$M$26,-1))
MVP 2 8% 4% in M31: =INDEX(M3:M26,MATCH($M$30,$M$3:$M$26,-1)+1)
最小方差曲线上点的横坐标 s 8% % 10% 3% in L30: =L29+(L31-L29)*($M$30-$M$29)/($M$31-$M$29)
最小方差曲线上点的纵坐标 m 4% % 3 11% 2%
s / m w1 / w2 13% 0% in F22: =L30
最小方差曲线上动点横坐标 / w2 % 4 15% -1% in G22: =N30
最小方差曲线上动点纵坐标 / w1 16% 17 7 27% -8% in G23: =O30
8 31% -10%
9 36% -13%
0 % 12 48% -20%
% %
% 0
% 18%
% 16%
% 16%
协方差矩阵
两组特定组合系数的计算
