读书笔记：EXCEL计算资产收益与风险（一）

5.4.1 EXCEL计算资产收益与风险01

之前我们学习了收益与风险之间的关系。我们讲了单个资产的收益率和风险怎么测量，两个资产组合在一起，它的收益和风险有什么样的关系。还把风险和收益的关系扩展到n种资产，在这种情况下，如何去寻求最优化的投资组合。最后得到了非常有名的马科维茨的资产组合理论。但同时也强调，这个理论在当初刚提出的时候运用起来是非常麻烦的，因为随着资产数的增加，所需要估计的参数是以平方级的比例增加的。

不过目前好在技术的发展，这种运算量已经不能算得上是限制。这节课,我们就来讲一讲怎么用Excel来实现单个资产收益率和方差的计算。

这里需要额外说明的是，Excel 可能是一种比较简便和直观展示的软件，但它运用起来其实还不太方便,尤其是当你需要批量处理的时候。所以如果大家感兴趣, 一方面，可以去学一学Excel的编程VBA，或者去学习一些其他金融机构用的比较多的软件，如Matlab、Python、 R等等。这门课的操作中我尽量使用手动操作，尽可能不涉及过于复杂的编程技巧。在开始本次课之前，同学们需要做的一个准备,就是去下载相关的资产的收益率数据。尽管大家可以直接下载本次课所用的数据，但我还是建议大家自己手动去下载数据。鉴于时间关系，我这里就不具体讲如何下载数据了。稍微提一下，可以考虑的数据来源渠道有WIND、CSMAR、各类炒股软件、雅虎财经等。

本次课下载了被市场上普遍认为是白马股的几个公司，它们分别是格力电器、恒瑞医药、贵州茅台、伊利股份这4只股票。数据区间是从2005年1月至2019年8月的月度数据。下面来讲如何计算单个资产的期望收益和标准。

(一)期望收益率的计算

首先计算4个股票的期望收益率。之前讲过，资产的期望收益率等于未来不同状态下的加权平均。这意味着得去预测未来不同经济状态资产收益率，以及发生的概率。显然这是比较困难的。实际上还有一种方式可以选择，那就是可以通过某种资产历史收益率来估计未来的期望收益率。简单的讲,我们可以用一只资产过去的历史平均收益率作为这个资产预期收益率的替代指标。当然，这样做其实是有假设的，这个假设就是历史会重演以及资产的基本情况没有发生大幅变动。同时用这种方式计算的时候，[历史] 应该能涵盖一个完整的牛熊市。这样我们估算出来的，期望收益率才比较合理。

因此,为了计算4个股票各自的期望收益率，我们可以用资产历史的收益率算一个平均数，在Excel里面就是期望收益率这一行，怎么实现呢?以格力电器下面对应的单元格B3为例，我们用Excel内置的函数average,选定格力电器历史收益率的区间，就是格力下面的这一列，2005年1月到2019年8月，总共涵盖了176个月。选定完了以后，按回车,就会出来结果，等于3.39, Excel里面有个比较方便的功能，就是可以直接拉单元格，这样的话，类似的公式就会复制到其他单元，同时对应的作用区域也会对应变动。可以看到，当选中B3后，单元格右下角有一个小点，把鼠标放在这里，它会变成一个十字架， 接下来我们点击鼠标左键，然后往右拉就可以了。 你会发现，右边的三个单元格也有数字了。点开的话，我们看到它存储的都是average这样一个函数，同时发现这个函数它的作用区间也对应发生变动了，点开恒瑞医药对应的单元格可以看到，它作用的区域同时变成了恒瑞医药下面的恒瑞医药的历史收益率了。

(二)风险的计算

接下来计算收益率的样本方差和样本标准差。样本方差的公式是，每一个样本减去样本平均数的平方，加起来，再除以样本数减1。为了直观展示，在收益率的旁边计算了每一个收益率，减掉历史平均收益的平方。选择格力电器对应的第1个格子，可以看到，收益率减平均收益率的平方，它的公式等于收益率减掉平均收益率，但是这时候你需要注意的是，我们在格力电器的平均收益率的单元格的标识B3, 3前面加了一个美元符号，这个表示什么意思呢?刚刚讲了Excel 的公式，我们拉动它的时候，函数作用域会自动更新。如果把美元符号放在单元格的前面，就表示它是固定的，不会随着单元格的拉动而变动。因为对于格力电器这一列来说，它的平均数都是一样的，都是B3单元格。所以随着下拉的话,函数中单元格的行数不应该发生变化，所以我把行号，3前面加一个美元符号，表示固定行数，但是列是可以变动的，为什么呢?因为当我要作用于恒瑞医药的话，平均收益率的单元格需要从B3变成C3。还是用刚刚我们讲的拉的方法。首先我们可以横着拉，这样可以算出每一个资产第1个样本差异。之后这4个单元格被选中，看到整个4个单元格的右下角又出现了一个点，把鼠标移到这个点也会变成十字架，我们可以往下拉，但还可以更简单，双击鼠标左键，它会自动把剩下的都用上面4个单元格对应的公式给填充了。

接下来用我们学过的公式来算，每一种股票它的样本方差和标准差。首先，在期望收益下面算了第1种样本方差，样本方差1，这是用概率论里学过的公式来算,它等于收益率减平均收益率的平方之和再除以样本数减1。这里我们用到两个函数，分别是SUM和COUNT, SUM表示把指定的单元格区域数字加和，COUNT表示计算对应的单元格区域总共有多少个,也就是样本数量，同时对上面的样本方差开根号就可以得到标准差，所使用的函数是SQRT (square root)，类似的， 用向右拉的方式，可以得到剩下三种资产的样本方差和样本标准。

当然，实际上Excel本身就内置了计算样本方差和总体方差的公式:

VAR.P:计算总体方差，分母是n, P表示Population

VAR.S:计算样本方差，分母是n-1，S表示Sample

STDEV.P:计算总体标准差

STDEV.S:计算样本标准差

用.p和.s的公式，又分别计算了两组方差,我们用概率论里的公式手动计算出来的,的确跟用.S公式计算出来的是一样的。后续我们也都是用样本方差或样本标准差，也就是.S算出来的结果。这次的Excel操作课讲解就到这里，我们下次再来讲讲两种资产期望收益率和标准差的计算。