计算方差-协方差矩阵
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
12.64 18.49 43.37 48.51 30.26 31.58 23.52 28.16
JNJ 6.78 7.20
10.91 19.43 24.44 29.15 38.04 39.36 43.80 55.19 52.15 51.49
10.56 336.44 36.70%
10.86 305.82 33.36%
58
Βιβλιοθήκη BaiduJNJ
0.0222 0.0412 0.0360 0.0181 0.0101 -0.0039
59
K
-0.0043 -0.0052 0.0181 0.0570 -0.0076 -0.0046
60
BA
0.0857 0.0379 0.0101 -0.0076 0.0896 0.0248
61
IBM
0.0123 -0.0022 -0.0039 -0.0046 0.0248 0.0184
K -9.79% 7.46% 37.76% -5.09% 32.04% -10.74% -48.93% 11.67% 19.90% 10.88% 15.49%
BA 58.73% -0.24% 65.55% 54.34% 37.11% 15.05% 43.53% 28.29% -15.09% -23.23% 39.82%
5.51% 23.86% 0.0570
27.63% 29.93% 0.0896
17.63% <-- =AVERAGE(G23:G33) 13.56% <-- =STDEV(G23:G33) 0.0184 <-- =VAR(G23:G33)
使用数组函数 {<-- {=MMULT(TRANSPOSE(B23:G33-B35:G35),B23:G33-B35:G35)/10}} 计
K -9.79% 7.46% 37.76% -5.09% 32.04% -10.74% -48.93% 11.67% 19.90% 10.88% 15.49%
BA 58.73% -0.24% 65.55% 54.34% 37.11% 15.05% 43.53% 28.29% -15.09% -23.23% 39.82%
29
3-Jan-00
30
2-Jan-01
31
2-Jan-02
32
2-Jan-03
33
2-Jan-04
34
35
均值
36
标准差
37
方差
IBM
GE 2.36 4.15 4.98 8.80
13.51 21.64 30.57 40.51 42.42 34.82 22.25 31.86
MSFT 2.68 2.64 3.68 5.73
-3.38%
JNJ -12.42% 23.13% 39.28%
4.51% -0.81% 8.19% -15.02% -7.74% 4.68% -24.10% -19.70%
K -15.31%
1.94% 32.25% -10.60% 26.53% -16.25% -54.44%
6.15% 14.39%
69 算样本方差-协方差矩阵
70
GE
MSFT
JNJ
K
BA
IBM
71
GE
0.1035 0.0758 0.0222 -0.0043 0.0857 0.0123
72
MSFT
0.0758 0.1657 0.0412 -0.0052 0.0379 -0.0022
73
JNJ
0.0222 0.0412 0.0360 0.0181 0.0101 -0.0039
50
2-Jan-02
51
2-Jan-03
52
2-Jan-04
53
B
C
D
E
F
G
H
GE 32.78% -5.43% 33.27% 19.21% 23.45% 10.89%
4.49% -19.05% -43.40% -68.45% 12.24%
MSFT -22.89% 11.83% 22.90% 57.73% 16.65% 63.87% -10.18% -68.58% -17.11% -50.85%
4.61% -19.74% -44.78% 35.90%
MSFT -1.50% 33.21% 44.28% 79.12% 38.04% 85.25% 11.20%
-47.19% 4.27%
-29.47% 18.01%
JNJ 6.01% 41.56% 57.71% 22.94% 17.62% 26.62% 3.41% 10.69% 23.11% -5.67% -1.27%
2.97 152.93 16.68%
K 20.37 18.47 19.90 29.03 27.59 38.01 34.14 20.93 23.52 28.70 32.00 37.36
0.41 15.44 1.68%
BA 2.34 4.21 4.20 8.09
13.93 20.19 23.47 36.27 48.13 41.39 32.81 48.86
1.2
1.3
A
B
C
D
E
F
G
H
6只股票的年度股票价格及收益数据
通用电气公司 (GE), 微软公司 (MSFT), 强生公司 (JNJ), 家乐氏公司 (K), 波音公司 (BA),
1
2
价格数据
3
日期
4
4-Jan-93
5
3-Jan-94
6
3-Jan-95
7
2-Jan-96
8
2-Jan-97
9
2-Jan-98
在前面的计算中我们除以M-1而非M，以此得到无偏的方差和协方差的估 计。不过这个选择看起来几乎没有多大影响。我们引用主流的教科书：“ 对于为什么要用M-1取代M这儿有一段很长的历史。如果你从来没有听说 过，你可以参考任何一本好的统计教材。这里我们主要想提醒你，如果你 在计算一个分布的方差时，这个分布存在已知的先验的均值，而不需要从 历史数据估计的时候，那么M-1应该变回M。（我们同样想说关于在分母 上用M-1替代M上，我们认为对你是已知的，但这却是对你不负责任的— —例如，试图用图例说明去证明一个充满疑问的假设）” Excel本身某程度上在除以M还是M-1这个问题上也有些混乱。在下面的电 子表中我们给出几种计算均值，方差，标准差和协方差的方法。
1.5
10.2.1一个稍微更有效率的替代方法 正如你所期望那样，的确存在其他计算方差-协方差矩阵可选方法。这里所 介绍的方法跳过了超额收益的计算，并且直接使用单元格B71：G76中的公 式进行计算。它通过使用数组函数=MMULT(TRANSPOSE(B23：G33B35：G35)，B23：G33-B35：G35)/10。通过写入B23：G33-B35我们直 接将每项收益减去均值得到超额收益向量：
lecture
10 FINANCIAL MODELING 金融建模
1.1
第10章 计算方差-协方差矩阵
10.1 引言
要计算有效投资组合，我们就必须计算股票收益数据的方差-协方差矩阵 。本章中，我们将讨论在Excel中怎样实现这个计算。其中最显而易见 的计算为样本方差-协方差矩阵：这是直接由历史收益计算而得的矩阵。 我们介绍几种计算方差-协方差的方法，包括在电子表中用超额收益矩阵 直接计算、VBA实现该方法计算。 即使样本方差-协方差矩阵看起来像一个很明显的选择，但我们将用大量 的文字说明它也许不是方差与协方差最好的估计。样本方差-协方差矩阵 有两个不尽人意的缺陷：一是它常使用不现实的参数，二是它难以用于 预测。这些将主要在10.5和10.6节中讨论。作为样本矩阵的替换，10.9 和10.10节将讨论用于优化方差-协方差矩阵估计的“压缩”方法。 在开始本章之前，你应先阅读第34章数组函数的内容。里面有一些 Excel函数，其参数是向量和矩阵；它们的实施与标准Excel函数略有不 同。本章重点讨论这些数组函数Transpose()和MMult()，还有“自制” 的数组函数的使用。
过减去资产各自的平均收益，得到超额收益矩阵（接下来的电子表中的
42-52行）。在55-61行中我们计算样本方差-协方差矩阵。
A
40
超额收益
41
日期
42
3-Jan-94
43
3-Jan-95
44
2-Jan-96
45
2-Jan-97
46
2-Jan-98
47
4-Jan-99
48
3-Jan-00
49
2-Jan-01
5.37% 9.97%
BA 31.11% -27.86% 37.93% 26.72%
9.49% -12.57% 15.90%
0.67% -42.71% -50.86% 12.20%
IBM 3.89% <-- =G23-G$35 -11.59% <-- =G24-G$35 9.70% 23.46% -15.00% -19.74% 6.14% 4.14% -13.08% -2.09% 14.17%
5.51% 23.86% 0.0570
27.63% 29.93% 0.0896
17.63% <-- =AVERAGE(G23:G33) 13.56% <-- =STDEV(G23:G33) 0.0184 <-- =VAR(G23:G33)
1.4
我们用我们的数字例子来说明计算方差-协方差矩阵的矩阵方法。我们通
IBM 21.51% <-- =LN(G5/G4)
6.04% <-- =LN(G6/G5) 27.33% 41.08%
2.63% -2.11% 23.76% 21.76% 4.55% 15.54% 31.80%
23.66% 32.17% 0.1035
21.38% 40.71% 0.1657
18.43% 18.97% 0.0360
1.6
A
21
收益数据
22
日期
23
3-Jan-94
24
3-Jan-95
25
2-Jan-96
26
2-Jan-97
27
2-Jan-98
28
4-Jan-99
29
3-Jan-00
30
2-Jan-01
31
2-Jan-02
32
2-Jan-03
33
2-Jan-04
34
35
均值
36
标准差
37
方差
38
B
C
D
E
F
G
H
GE 56.44% 18.23% 56.93% 42.87% 47.11% 34.55% 28.15%
IBM 11.79 14.62 15.53 20.41 30.78 31.60 30.94 39.24 48.78 51.05 59.63 81.95
0.84 41.01 4.47%
0.79 65.13 <-- =G15*G17 7.10% <-- =G18/SUM($B$18:$K$18)
GE 56.44% 18.23% 56.93% 42.87% 47.11% 34.55% 28.15%
4.61% -19.74% -44.78% 35.90%
MSFT -1.50% 33.21% 44.28% 79.12% 38.04% 85.25% 11.20%
-47.19% 4.27%
-29.47% 18.01%
JNJ 6.01% 41.56% 57.71% 22.94% 17.62% 26.62% 3.41% 10.69% 23.11% -5.67% -1.27%
10
4-Jan-99
11
3-Jan-00
12
2-Jan-01
13
2-Jan-02
14
2-Jan-03
15
2-Jan-04
16
17
已发行股票
18
市场价值
19
占投资组合比例
20
21
收益数据
22
日期
23
3-Jan-94
24
3-Jan-95
25
2-Jan-96
26
2-Jan-97
27
2-Jan-98
28
4-Jan-99
74
K
-0.0043 -0.0052 0.0181 0.0570 -0.0076 -0.0046
75
BA
0.0857 0.0379 0.0101 -0.0076 0.0896 0.0248
76
IBM
0.0123 -0.0022 -0.0039 -0.0046 0.0248 0.0184
1.7
10.3 我们应该除以M还是M-1？Excel与统计量？
使用数组函数 {<-- {=MMULT(TRANSPOSE(B42:G52),B42:G52)/10}} 计算样本方差-协方差矩
阵 54
55
GE
MSFT
JNJ
K
BA
IBM
56
GE
0.1035 0.0758 0.0222 -0.0043 0.0857 0.0123
57
MSFT
0.0758 0.1657 0.0412 -0.0052 0.0379 -0.0022
62
63 注意: 将数组函数放进单元格 B56:G61:
64 1. 标记整个区域 B56:G61
65 2. 键入 <-- {=MMULT(TRANSPOSE(B42:G52),B42:G52)/10} 到其中一个单元格.
66 3. 完成输入后, 按 [Ctrl]+[Shift]+[Enter] 作为数组函数输入公式.
IBM 21.51% <-- =LN(G5/G4)
6.04% <-- =LN(G6/G5) 27.33% 41.08%
2.63% -2.11% 23.76% 21.76% 4.55% 15.54% 31.80%
23.66% 32.17% 0.1035
21.38% 40.71% 0.1657
18.43% 18.97% 0.0360
JNJ 6.78 7.20
10.91 19.43 24.44 29.15 38.04 39.36 43.80 55.19 52.15 51.49
10.56 336.44 36.70%
10.86 305.82 33.36%
58
Βιβλιοθήκη BaiduJNJ
0.0222 0.0412 0.0360 0.0181 0.0101 -0.0039
59
K
-0.0043 -0.0052 0.0181 0.0570 -0.0076 -0.0046
60
BA
0.0857 0.0379 0.0101 -0.0076 0.0896 0.0248
61
IBM
0.0123 -0.0022 -0.0039 -0.0046 0.0248 0.0184
K -9.79% 7.46% 37.76% -5.09% 32.04% -10.74% -48.93% 11.67% 19.90% 10.88% 15.49%
BA 58.73% -0.24% 65.55% 54.34% 37.11% 15.05% 43.53% 28.29% -15.09% -23.23% 39.82%
5.51% 23.86% 0.0570
27.63% 29.93% 0.0896
17.63% <-- =AVERAGE(G23:G33) 13.56% <-- =STDEV(G23:G33) 0.0184 <-- =VAR(G23:G33)
使用数组函数 {<-- {=MMULT(TRANSPOSE(B23:G33-B35:G35),B23:G33-B35:G35)/10}} 计
K -9.79% 7.46% 37.76% -5.09% 32.04% -10.74% -48.93% 11.67% 19.90% 10.88% 15.49%
BA 58.73% -0.24% 65.55% 54.34% 37.11% 15.05% 43.53% 28.29% -15.09% -23.23% 39.82%
29
3-Jan-00
30
2-Jan-01
31
2-Jan-02
32
2-Jan-03
33
2-Jan-04
34
35
均值
36
标准差
37
方差
IBM
GE 2.36 4.15 4.98 8.80
13.51 21.64 30.57 40.51 42.42 34.82 22.25 31.86
MSFT 2.68 2.64 3.68 5.73
-3.38%
JNJ -12.42% 23.13% 39.28%
4.51% -0.81% 8.19% -15.02% -7.74% 4.68% -24.10% -19.70%
K -15.31%
1.94% 32.25% -10.60% 26.53% -16.25% -54.44%
6.15% 14.39%
69 算样本方差-协方差矩阵
70
GE
MSFT
JNJ
K
BA
IBM
71
GE
0.1035 0.0758 0.0222 -0.0043 0.0857 0.0123
72
MSFT
0.0758 0.1657 0.0412 -0.0052 0.0379 -0.0022
73
JNJ
0.0222 0.0412 0.0360 0.0181 0.0101 -0.0039
50
2-Jan-02
51
2-Jan-03
52
2-Jan-04
53
B
C
D
E
F
G
H
GE 32.78% -5.43% 33.27% 19.21% 23.45% 10.89%
4.49% -19.05% -43.40% -68.45% 12.24%
MSFT -22.89% 11.83% 22.90% 57.73% 16.65% 63.87% -10.18% -68.58% -17.11% -50.85%
4.61% -19.74% -44.78% 35.90%
MSFT -1.50% 33.21% 44.28% 79.12% 38.04% 85.25% 11.20%
-47.19% 4.27%
-29.47% 18.01%
JNJ 6.01% 41.56% 57.71% 22.94% 17.62% 26.62% 3.41% 10.69% 23.11% -5.67% -1.27%
2.97 152.93 16.68%
K 20.37 18.47 19.90 29.03 27.59 38.01 34.14 20.93 23.52 28.70 32.00 37.36
0.41 15.44 1.68%
BA 2.34 4.21 4.20 8.09
13.93 20.19 23.47 36.27 48.13 41.39 32.81 48.86
1.2
1.3
A
B
C
D
E
F
G
H
6只股票的年度股票价格及收益数据
通用电气公司 (GE), 微软公司 (MSFT), 强生公司 (JNJ), 家乐氏公司 (K), 波音公司 (BA),
1
2
价格数据
3
日期
4
4-Jan-93
5
3-Jan-94
6
3-Jan-95
7
2-Jan-96
8
2-Jan-97
9
2-Jan-98
在前面的计算中我们除以M-1而非M，以此得到无偏的方差和协方差的估 计。不过这个选择看起来几乎没有多大影响。我们引用主流的教科书：“ 对于为什么要用M-1取代M这儿有一段很长的历史。如果你从来没有听说 过，你可以参考任何一本好的统计教材。这里我们主要想提醒你，如果你 在计算一个分布的方差时，这个分布存在已知的先验的均值，而不需要从 历史数据估计的时候，那么M-1应该变回M。（我们同样想说关于在分母 上用M-1替代M上，我们认为对你是已知的，但这却是对你不负责任的— —例如，试图用图例说明去证明一个充满疑问的假设）” Excel本身某程度上在除以M还是M-1这个问题上也有些混乱。在下面的电 子表中我们给出几种计算均值，方差，标准差和协方差的方法。
1.5
10.2.1一个稍微更有效率的替代方法 正如你所期望那样，的确存在其他计算方差-协方差矩阵可选方法。这里所 介绍的方法跳过了超额收益的计算，并且直接使用单元格B71：G76中的公 式进行计算。它通过使用数组函数=MMULT(TRANSPOSE(B23：G33B35：G35)，B23：G33-B35：G35)/10。通过写入B23：G33-B35我们直 接将每项收益减去均值得到超额收益向量：
lecture
10 FINANCIAL MODELING 金融建模
1.1
第10章 计算方差-协方差矩阵
10.1 引言
要计算有效投资组合，我们就必须计算股票收益数据的方差-协方差矩阵 。本章中，我们将讨论在Excel中怎样实现这个计算。其中最显而易见 的计算为样本方差-协方差矩阵：这是直接由历史收益计算而得的矩阵。 我们介绍几种计算方差-协方差的方法，包括在电子表中用超额收益矩阵 直接计算、VBA实现该方法计算。 即使样本方差-协方差矩阵看起来像一个很明显的选择，但我们将用大量 的文字说明它也许不是方差与协方差最好的估计。样本方差-协方差矩阵 有两个不尽人意的缺陷：一是它常使用不现实的参数，二是它难以用于 预测。这些将主要在10.5和10.6节中讨论。作为样本矩阵的替换，10.9 和10.10节将讨论用于优化方差-协方差矩阵估计的“压缩”方法。 在开始本章之前，你应先阅读第34章数组函数的内容。里面有一些 Excel函数，其参数是向量和矩阵；它们的实施与标准Excel函数略有不 同。本章重点讨论这些数组函数Transpose()和MMult()，还有“自制” 的数组函数的使用。
过减去资产各自的平均收益，得到超额收益矩阵（接下来的电子表中的
42-52行）。在55-61行中我们计算样本方差-协方差矩阵。
A
40
超额收益
41
日期
42
3-Jan-94
43
3-Jan-95
44
2-Jan-96
45
2-Jan-97
46
2-Jan-98
47
4-Jan-99
48
3-Jan-00
49
2-Jan-01
5.37% 9.97%
BA 31.11% -27.86% 37.93% 26.72%
9.49% -12.57% 15.90%
0.67% -42.71% -50.86% 12.20%
IBM 3.89% <-- =G23-G$35 -11.59% <-- =G24-G$35 9.70% 23.46% -15.00% -19.74% 6.14% 4.14% -13.08% -2.09% 14.17%
5.51% 23.86% 0.0570
27.63% 29.93% 0.0896
17.63% <-- =AVERAGE(G23:G33) 13.56% <-- =STDEV(G23:G33) 0.0184 <-- =VAR(G23:G33)
1.4
我们用我们的数字例子来说明计算方差-协方差矩阵的矩阵方法。我们通
IBM 21.51% <-- =LN(G5/G4)
6.04% <-- =LN(G6/G5) 27.33% 41.08%
2.63% -2.11% 23.76% 21.76% 4.55% 15.54% 31.80%
23.66% 32.17% 0.1035
21.38% 40.71% 0.1657
18.43% 18.97% 0.0360
1.6
A
21
收益数据
22
日期
23
3-Jan-94
24
3-Jan-95
25
2-Jan-96
26
2-Jan-97
27
2-Jan-98
28
4-Jan-99
29
3-Jan-00
30
2-Jan-01
31
2-Jan-02
32
2-Jan-03
33
2-Jan-04
34
35
均值
36
标准差
37
方差
38
B
C
D
E
F
G
H
GE 56.44% 18.23% 56.93% 42.87% 47.11% 34.55% 28.15%
IBM 11.79 14.62 15.53 20.41 30.78 31.60 30.94 39.24 48.78 51.05 59.63 81.95
0.84 41.01 4.47%
0.79 65.13 <-- =G15*G17 7.10% <-- =G18/SUM($B$18:$K$18)
GE 56.44% 18.23% 56.93% 42.87% 47.11% 34.55% 28.15%
4.61% -19.74% -44.78% 35.90%
MSFT -1.50% 33.21% 44.28% 79.12% 38.04% 85.25% 11.20%
-47.19% 4.27%
-29.47% 18.01%
JNJ 6.01% 41.56% 57.71% 22.94% 17.62% 26.62% 3.41% 10.69% 23.11% -5.67% -1.27%
10
4-Jan-99
11
3-Jan-00
12
2-Jan-01
13
2-Jan-02
14
2-Jan-03
15
2-Jan-04
16
17
已发行股票
18
市场价值
19
占投资组合比例
20
21
收益数据
22
日期
23
3-Jan-94
24
3-Jan-95
25
2-Jan-96
26
2-Jan-97
27
2-Jan-98
28
4-Jan-99
74
K
-0.0043 -0.0052 0.0181 0.0570 -0.0076 -0.0046
75
BA
0.0857 0.0379 0.0101 -0.0076 0.0896 0.0248
76
IBM
0.0123 -0.0022 -0.0039 -0.0046 0.0248 0.0184
1.7
10.3 我们应该除以M还是M-1？Excel与统计量？
使用数组函数 {<-- {=MMULT(TRANSPOSE(B42:G52),B42:G52)/10}} 计算样本方差-协方差矩
阵 54
55
GE
MSFT
JNJ
K
BA
IBM
56
GE
0.1035 0.0758 0.0222 -0.0043 0.0857 0.0123
57
MSFT
0.0758 0.1657 0.0412 -0.0052 0.0379 -0.0022
62
63 注意: 将数组函数放进单元格 B56:G61:
64 1. 标记整个区域 B56:G61
65 2. 键入 <-- {=MMULT(TRANSPOSE(B42:G52),B42:G52)/10} 到其中一个单元格.
66 3. 完成输入后, 按 [Ctrl]+[Shift]+[Enter] 作为数组函数输入公式.
IBM 21.51% <-- =LN(G5/G4)
6.04% <-- =LN(G6/G5) 27.33% 41.08%
2.63% -2.11% 23.76% 21.76% 4.55% 15.54% 31.80%
23.66% 32.17% 0.1035
21.38% 40.71% 0.1657
18.43% 18.97% 0.0360