EXCEL制作马柯维茨有效前沿，并用来资产配置

前言：有效前沿（efficient portfolio）亦称“有效边界”。理性的投资者一般是厌恶风险而偏好收益的，对于相同的风险水平，会选择能提供最大收益率的组合；对于相同的预期收益率，会选择风险最小的组合。能同时满足这些条件的投资组合就是有效前沿。
马柯维茨将风险用资产价格的波动率（即标准差）来量化，将资产收益的均值作为投资者期望回报。相关理论这里不再赘述。
有效前沿可以用来呈现资产组合中风险与收益的分布情况，为资产配置提供参考。
下面以雅诗兰黛、JP、麦当劳、耐克四支股票在2019年1月4日至2019年5月1日期间的股份来讲解，将每日收盘价作为股票当日价格，在获取股票价格时可以用EXCEL自带的stockhistory(&#34;股票代码&#34;,起始日,结束日)进行获取，或者通过各证券平台获取，以上四公司股票价格如下图所示：

图一

在获获取股票价格后，开始计算股票的日收益率（daily return）,股票的收益率有以下两种算法，

图二

第①种是一般复利算法，第②种是连续复利算法，以下以第②种算法展开，计算结果如下图：

图三

接下来计算样本参数（sample parameter）,主要有日收益率均值(Expection Daily return）即日收益率的算术平均数；样本容量（n）即每支股票的日收益率个数；日收益率标准差（stddev Daily return）即日每支股票的日收益率的波动率；日收益率方差（variance_Daily_return）即每支股票日收益率离差平方和的均值。需要用到的函数详见下图：

图四

在计算组合的标准差时，先需要计算每两支股票日收益率之间的协方差，四支股票就形成了一个4*4的矩阵，相关函数如下图：

图五

以上计算的各数值均为计算资产组合的参数作铺垫的，组合中每支股票的权重会影响组合的风险与收益。我们要获得一系列的权重组合，可以先利用randombetween函数，可以随机生成四个数，由于每一组权重的总和必须为100%，那么对应的权重（weight）就可以通过对应的随机数除以四个随机数的和，当作每支股票的权重，用此方法制作出来的权重在每点击EXCEL时，都会随机调整一次，这样就可以自动获得一系列的权重组合了。解决了权重，就要计算组合的期望值，在这里需要将日收益率期望值*252（一年一般只有252个交易日）转化为年化收益率，这里需要用到sumproduct函数，详见下图；最后计算风险指标，即组合的标准差，由于需要考虑至权重，因此，这里组合的方差需要用到矩阵的乘法函数，详见下图公式：

图六

需要将资产组合的一系列的标准差、期望值呈现在图中，就先要获得一系列的数值，这里先利用EXCEL的埴充>序列功能生成一列数据，最好多一些，这里我生成了200000个，详见下图中的Trail列。然后在此列的右侧分别设为组合的标准差、组合的期望，序列1对应的数值只接引用图六中的对应值。

图七

在此之后选中上图七红色方框所有单元格，执行数据>模拟分析，在图八中方框中，选中任意空的单元格，点击确定，此时系统会将一系列权重对应的资产组合的收益与风险呈现出来，详见图九。

图八

图九

最后，插入图表>散点图，就可以得到资产组合的有效前沿图像：

图十

期权匿名回答 · 2022-6-17 07:26:11

请问模拟运算表那里引用的行和列是什么呀，点空白地方根本做不出来

期权匿名回答 · 2022-6-17 07:26:54

首先，你必须将图中红色框框选中，然后选择模拟运算，只用填写列单元格就好

EXCEL制作马柯维茨有效前沿，并用来资产配置

2 个回复