读书笔记：EXCEL计算资产收益与风险（二）
5.4.2 EXCEL计算资产收益与风险02
上节Excel操作课我们讲了如何计算单个资产期望收益率和标准差。这次给大家讲授如何计算两个以及两个以上资产组合的收益率和标准差。两种及以上资产的情况，除了各自本身的期望收益率和标准差，还需要知道两两之间的相关系数或者协方差。
(一)相关系数的获取
还是上次课的4个资产，除了算出它们的期望收益率和标准差之外，还要知道他们之间的相关系数，怎么实现呢?我们需要用到Excel的一个专门的工具模块[数据分析]，如果你用的是2007版以后的Excel,在[数据]选项卡下面的右边有一个叫[数据分析]的模块。如果没有的话，可以点击[文件]，最下面的[选项] ，然后点击[加载项]，选择[Excel 加载项]，点击[转到]，看到有[分析工具库]，打勾，然后点确定，这时候你[数据]选项卡的右边，就会出现一个[数据分析]模块了。接下来我们点击[数据]选项卡下面的[数据分析]。有很多的选项，我们需要的是相关系数，点击相关系数。我们会看到相关系数面板，第1个输入区域，是你想计算哪个区域中数据之间的相关系数，接下来我们选择前复权收益率，首先选择股票名称这一行，选择完了以后按住CTRL+SHIFT，然后按向下键。这时候整个区域都被选择了。
第2个，分组方式，意思就是选择的那个区域，它的样本是每一行一个样本,还是一列一个样本，显然这里面是一列一个资产样本，所以选择列。下面有一个叫[标志位于第1行]，因为选择的数据第一行包括资产名称标签,所以打勾。最后一个[输出区域]，选择准备输出的具体区域,输出结果是一个区域，包含很多个单元格，选择输出区域最左上角那个单元格开始的位置就可以，选择A7,点确定，结果就出现了。
(二)两种资产收益和风险的计算
不同于两种以上资产组合，两种资产的组合，只要权重确定了，对应的组合它的风险和收益都是确定，接下来以恒瑞医药和贵州茅台为例，来展示两种资产组合在一起，它的收益率和标准差如何计算。我们让恒瑞医药的权重从- 150%提高到150%，对应的茅台的权重,从250%降低到-50%,我们来看一下，两种资产在不同权重下组合后的收益率和风险情况。在单元格]3上，输入期望收益率的公式，恒瑞医药和贵州茅台的加权平均数,这时候要注意，期望收益率的行是需要用$固定的。同时13单元格我们输入两种资产组合的标准差公式:等于每-种资产各自的权重的平方乘以方差，加上两两的权重乘以他们的协方差。同时别忘了标准差的行，用$固定。选中第一行的标准差和期望收益率，同样的，我们选择右下角双击,就可以获得所有不同权重组合下期望收益率和标准差的对应数字了。接下来，把标准差和期望收益率之间的关系，画在一张图表上，看一下会怎么样。我们选择插入，选择散点图。正如我们所学的，在相关系数,介于-1~1之间，他们之间的关系是一根双曲线。
我们强制改变恒瑞医药和贵州茅台之间的相关性，让它变成之前学过的几种特殊情况，分别是p等于1和-1。选择C10，把0.42改成1。图片中两种资产之间的组合就变成了一根直线了，再把它改成-1。可以看到这时候他们组合的轨迹就是一条过纵轴的折线了，与我们所学过的结论是一致的。此外，恒瑞医药的期望收益率和风险都是比茅台低的，而格力电器它的期望收益率比贵州茅台要低，但是风险——标准差， 却比贵州茅台要高。如果格力电器和贵州茅台组合是不是应该总是选择贵州茅台呢?同学们可以思考一下，并自己计算一下。
(三)两种以上资产收益和风险的计算
有了上面的基础，再来看下两个以上的资产组合其收益和风险之间的关系。刚刚讲过两种资产组合在一起，只要权重确定了,它的风险和期望收益都是同时唯一确定的。但是，两种以上资产组合的话，同一期望收益率可能可以通过很多种权重组合来实现，它们的风险是不一样的。资产组合选择的目标就是选择一个期望收益率一定的情况下，风险最小的组合。接下来还是以之前的4个股票为例，来描绘如何通过Excel来获得有效前沿。两个以上资产组合风险的计算需要考虑相关性。可以用相关系数矩阵来计算，但实际上使用协方差矩阵可以更方便，因此我们再使用数据分析，与相关系数矩阵类似，获得这些股票之间的协方差矩阵。
接下来我们固定一系列的期望收益率,每一期望收益率都通过最小化组合的方差来确定每个资产的权重。我们生成一列期望收益率，从2%开始，步长为0.1%，一直增加至5%。同时随机给每种资产赋一个初始权重，例如，格力电器、恒瑞医药、贵州茅台，权重都为0,伊利股份权重为100%。初始权重可以任意确定，只要所有资产的权重加起来等于100%就可以。有了每个资产权重和期望收益率，就可以得到每一个组合的期望收益率，也可以计算每种组合的标准差。线形代数的知识告诉我们:组合的方差等于1xn维的权重横向量，乘以nxn维的协方差矩阵，再乘以nx1维的权重列向量。具体在Excel操作中，我们需要使用到两个函数: MMULT和TRANSPOSE, MMULT用来计算矩阵的乘积，TRANSPOSE 用来获得矩阵的转置。具体的公式如单元格K4所示。
此时需要注意的是，不能直接按Enter确定，不然会得到计算错误的结论。因为是矩阵运算，我们需要同时按住CTRL+SHIFT键，然后再按Enter键确定。类似的，我们可以双击右下角的点，将公式运用于整个一列。显然这时候的组合并不是最优的，因为权重都是随机确定的。为了得到最优的结果，我们需要使用Excel的[规划求解]工具。在[数据]选项卡下面的[规划求解]，如果你看不到这样一个规划求解的工具，需要选择[文件]，然后选择[选项]，然后选择[加载项] ,然后选择[Excel 加载项]，点击转到有一个规划求解加载项，前面打勾，然后点击确定，这时候数据选项卡下面就会有[规划求解]工具了。
接下来使用这样一个工具箱，点击进入以后，第1个是设置目标，目标是选择一个单元格,因为希望在同一收益率情况下最小风险，我们选择标准差即单元格K4,接下来有一个选项:将通过更改可变单元格，这个其实就是自变量x，优化过程是改变权重来实现。所以接下来，我们选择格力电器，恒瑞医药和贵州茅台这三个股票权重对应的单元格，这三个单元格都是可以变的。要注意伊利股份是不可以变,因为只要前面三个单元格确定了，伊利股份的权重就等于100%减掉这三个权重之和。接下来有一个选项叫遵守约束，也就是可以给优化加上约束条件，其中一个约束条件就是,期望回报率是给定的,这意味着要添加一个约束条件:期望回报率,单元格L4,它应该等于我们想要的必要回报率，单元格M4，点击确定。下面还有一个是无约束变量为非负数,因为权重可以是任意取值,这个选项不需要,把前面的勾去掉。因为整个优化是非线性的，所以求解方式里面，选择非线性的求解方式，然后点击求解,经过很快的运算,就得到2%期望收益率下最优的权重了，其中伊利股份要持有200%多，其他资产权重都是负的，意味着是卖空其他的资产，去买伊利股份，此时组合标准差是19.06%。因此，每一行都可以这样去确定最优权重。如果不太懂编程的话，这时候只能手动一个一个操作。如果有一定编程技巧的话，可以使用Excel的VBA来实现。
这里我编了一个简单的VBA，可以直接实现，大家如果下载了本次课的Excel, 选择允许宏运行，然后按下CTRL+SHIFT+H键。你就可以看到Excel会一行一行的执行最优化过程。最后我们得到了一系列期望收益率下最优的组合权重以及组合对应的标准差。接下来把它们画出来，可以发现它就是我们所学过的一根双曲线。本章的Excel 教学到此结束，大家可以多多尝试一下。 同时感兴趣的话，建议去学一些编程的知识， 可以大大提高效率。