快讯
主页 > 投资组合管理 > Excel中构建方差协方差矩阵(Variacne-Covariance Matrix)

Excel中构建方差协方差矩阵(Variacne-Covariance Matrix)

方差-协方差矩阵(Variance-Covariance Matrix)在各个领域都有广泛的应用,多数人在利用Excel计算VCM的时候习惯直接利用数据分析工具–协方差–进行分析(Excel2007)。

1

输出后可以得到如下的结果:

4-1

通过对右方结果的复制和转置粘贴就可以得到所谓的方差协方差矩阵了:

7-1

但是仔细看就会发现,这种通过数据分析—协方差 输出计算书的方差协方差矩阵,默认使用的是总体方差,而非样本方差,也就是默认使用的公式分母是N. 但是在金融计算中,多数利用的是样本数据,也就是分母是N-1的公式。所以,在“样本”的假设下,上面的VCM并不准确(不影响最后相关系数的计算)。这就需要我们队以上的矩阵进行调节,一种方法是在结果的基础上直接对分母进行调节。比如上图中的每个数都乘以3/2就可以了。另一种方法是利用矩阵的乘积直接输出我们需要的样本的VCM。

首先,我们需要先计算出数据的超额收益 Excess Return(每个数据减去对应组的平均数),转置之后相乘然后除以N-1(3-1)就得到了需要的VCM

 

选中一个2X2的区域,然后利用函数 =MMULT ( 矩阵② , 矩阵①)/2 然后按住Ctrl+Shift+Enter 就可以得到“样本”的VCM了。