用EXCEL实现多个资产的投资组合优化

用EXCEL实现多个资产的投资组合优化【摘要】我们可以用excel来构建多个资产的投资组合，实现收益最大化或者风险最小化，并计算达到目标收益的概率。

【关键词】投资组合；最优

一、风险资产数据

假设我们要构建含五个风险资产的投资组合。根据统计以往10年的五个资产的历史数据，我们得到以下数据

相关系数（correlation）风险资产1 风险资产2 风险资产3 风险资产4 风险资产5

风险资产1 1 0.51 0.49 0.27 0.47

风险资产2 0.51 1 0.98 0.5 0.94

风险资产3 0.49 0.98 1 0.48 0.9

风险资产4 0.27 0.5 0.48 1 0.46

风险资产5 0.47 0.94 0.9 0.46 1

预期收益（e（r））0.085 0.13 0.135 0.13 0.11

收益标准差（）0.091 0.206 0.212 0.19 0.12

占组合最大百分比（%）100 40 80 30 10

占组合最小百分比（%）0 10 0 0 0

二、假设

为了简化计算过程，我们做了一下假设：

1.根据中心极限理论，我们假设五个资产的收益分布为正态分布。

2.我们假设资产的相关系数，预期收益，收益的标准差在短期内保持不变。后面我们会通过压力测试来检验构建的投资组合对这些条件变动的敏感程度。

三、数学模型

首先，我们计算投资组合的期望收益，

是每个资产的期望收益，是将要构建的投资组合中每个资产的比重。

然后计算投资组合的收益的标准差，

是两个资产间的协方差。

如果用矩阵的方式来计算，会有以下等式

是五个资产的收益期望值的矩阵：

是单位矩阵：

只要确定了五个资产的比重，我们就可以计算出投资组合的收益期望值，标准差和达到目标收益的可能性（因为收益为正态分布，可以通过norm.dis公式，输入目标收益、投资组合期望、方差，得到概率值）。相反地，我们也可以用excel的规划求解功能，通过设定目标收益期望，标准差或者达到目标收益的概率，算出各资产的比例。

四、投资组合配置

如，我们希望构建的投资组合，有60%以上的可能收益达到6.3%，有40%以上的可能收益达到8%，并且小于2%的可能有超过10%的损失。我们在规划求解功能中设定以上的条件，将得出以下资产比重

配置。

比重

风险资产1 0.704

风险资产2 0.1

风险资产3 0

风险资产4 0.096

风险资产5 0.1

总计 1

五、压力测试

使用excel的模拟分析-方案管理功能，我们可以改变各个风险资产的数据，对构建的投资组合进行压力测试。

压力测试1：风险资产1的收益期望值增加2%、1%和减少2%、1%。

通过结果（附表1），我们可以看到，在最坏的情况下，构建的投资组合略微地低于了我们之前设定的目标（有60%以上的可能收益达到6.3%，有40%以上的可能收益达到8%，并且小于2%的可能有超过10%的损失）。

压力测试2：风险资产3的收益期望值减少2%、1%。

由于我们构建的投资组合中没有配置风险资产3，它的收益变化对投资组合没有影响。

压力测试3：风险资产1和其他资产的相关系数提高20%、降低20%。

通过结果（附表2），我们可以看到，即使在最坏的情况下（风险资产1和其他资产的相关系数提高20%），构建的投资组合仍能达到目标。

六、结论

只要能获得或者合理的估算风险资产的期望收益，方差以及风险资产间的相关系数，无需借助特殊的分析工具，只需要用excel 就可以进行简单计算，快速地优化投资组合并估算其风险。这种方法适用于任意数量的风险资产的投资组合。

附录

附表1

风险资产1

收益+2% 风险资产1

收益+1% 风险资产1 无变化风险资产1

收益-1% 风险资产1

收益-2%

变动单元格??

风险资产1 收益期望0.105 0.095 0.085 0.075 0.065

结果

收益期望 11.0% 10.3% 9.6% 8.9% 8.2%

收益高于6.3% 69.4% 66.7% 63.9% 61.1% 58.1%

收益高于8% 62.7% 59.9% 56.9% 53.9% 51.0%

损失大于10% 1.2% 1.5% 1.8% 2.1% 2.6%

附表2

相关系数 +20% 相关系数不变相关系数 -20% 变动单元格

风险资产1/风险资产2 0.612 0.51 0.408

风险资产1/风险资产3 0.588 0.49 0.392

风险资产1/风险资产4 0.324 0.27 0.216

风险资产1/风险资产5 0.564 0.47 0.376

结果

收益期望 9.63% 9.63% 9.63%

收益高于6.3% 63.60% 63.92% 64.44%

收益高于8% 56.76% 56.92% 57.20%

损失大于10% 2.02% 1.79% 1.45%