如何用Excel的规划求解功能实现一个投资组合在均值-方差法下的最优化？

如何⽤Excel的规划求解功能实现⼀个投资组合在均值-⽅差法下的最优化？⽂中的计算⽅法参考了Samir Khan的“Mean-Variance Optimization with Transaction Costs”。

⼀般来讲，⼀个投资组合中各项资产的价格变动特征是不⼀样的，⽐如有的资产价格波动率很
⾼，但有可能带来更⾼的回报；⽽其他的资产会在⼤盘下跌之时反⽽上涨。

在构建投资组合时
通过精⼼挑选具有不同价格波动特点的资产，就可以在确保收益最⼤化的同时实现投资组合的
风险最⼩化，⽽均值-⽅差法就可以实现这⼀点。

均值-⽅差法是⼀种⽐较传统的优化投资组合的做法，来源于美国经济学家、1990年诺贝尔经济
学奖获得者Harry Markowitz于1950年代创⽴的基于均值-⽅差模型现代组合投资理论。

均值-⽅差模型的理论是解决投资者如何从所有可能的证券组合中选择⼀个最优组合的问题。

投
资者的决策⽬标通常有两个：尽可能⾼的收益率和尽可能低的不确定性风险。

即先确⽴⼀个⽬
标收益率，然后确定各项资产在投资组合中的权重，使整个投资组合的风险值即整个组合的价
格波动的⽅差值最低，最终使这两个相互制约的⽬标达到最佳平衡。

本⽂的主题就是探讨如何⽤Excel的规划求解功能实现⼀个由四只股票构成的投资组合在均值-⽅
差法下的最优化，即价格波动风险最低，回报率最⾼。

由于在对现有投资组合中各项资产的⽐
率进⾏调整时交易成本会成为⼀个很⼤的影响组合回报率的因素，因此为贴近实际操作，⽂中
的案例考虑到了交易成本，并将资产权重每变动1%的交易成本设定为0.1%；四只股票的初始权
重均为25%，投资组合的⽉度预期回报率为1%。

1、按以下格式设置Excel表格
2、通过雅虎财经⽹站下载美孚⽯油公司XOM、卡特彼勒公司CAT、可⼝可乐公司KO和波⾳公
司BA在2018年2⽉1⽇⾄2019年2⽉1⽇这1年间的⽉度收盘价。

3、⽤LN()函数计算4只股票的⽉度回报率，（）内为⽉度收盘价所在的单元格
4、⽤AVERAGE()函数计算这四只股票⽉度回报率的均值
5、形成协⽅差矩阵。

调⽤EXCEL的“数据分析”功能，点击“协⽅差”后，按“确认”键。

在“输⼊区域”选择四只股票⽉度回报率数据所在单元格B4:E16后，点击“确认”键。

6、协⽅差矩阵计算结果如下：
7、计算四只股票的⽅差值
8、将四只股票的⽅差值求和得出整个组合的⽅差值
9、⽤公式“=I4*B17+I5*C17+I6*D17+I7*E17-I1*(ABS(J4)+ABS(J5)+ABS(J6)+ABS(J7))”得出按
照当前资产权重计算的投资组合回报率0.59%
10、为了使整个组合的回报率达到1%的⽬标值，需要对这四只股票在投资组合中的权重进⾏调
整，为此需要使⽤EXCEL的规划求解功能。

11、在规划求解功能界⾯的“设置⽬标单元格”项⽤光标选定“投资组合优化回报率”所在的数据单
元格B1，“等于”项求的是“最⼩值”，“可变单元格”选定为四只股票权重变化值所在单元格J4:J7，
约束条件为：1）四只股票的新权重均不得低于0；2)整个组合的新权重之和等于100%；3)投资组合优化回报率等于投资组合的预期回报率。

注意此时整个投资组合回报率的⽅差等于0.002752，等⼀下看这个值在各只股票的权重调整过后会发⽣何种变化？
12、在点击规划求解参数功能界⾯的“确认”键后，可以看到美孚⽯油公司的权重增加了5.32%，调整后的权重为30.32%；可⼝可乐公司的权重降低了0.14%，调整后的权重为24.86%；卡特彼勒公司的权重降低了19.59%，调整后的权重为5.41%；波⾳公司的权重增加了14.41%，调整后的权重为39.41%；整个组合的权重仍保持在100%；
投资组合回报率的⽅差从权重调整前0.002752变成0.002703，实现了最⼩化
在Harry Markowitz的均值-⽅差模型诞⽣之前，投资管理更多地是跟着感觉⾛，缺乏科学的理论作指导。

⽽均值-⽅差优化理论诞⽣后，投资组合可以在获得预期回报率的同时实现整个组合回报率的波动率最⼩化。

但是均值-⽅差优化的做法也有⼀些不⾜：
该做法⽤标准差作为衡量价格波动风险的指标，但这只适⽤于股票和债券等投资回报率呈现正态分布的资产类别，⽽对冲基⾦和衍⽣品的回报分布情况有很明显的偏度和峰值，因此并不适⽤于 Markowitz的均值-⽅差理论；
均值-⽅差理论应⽤的前提是假设投资组合⼀经优化完毕，资产配置⽐率就不会再发⽣变化；
投资组合的管理者在优化投资组合的时候并不⼀定⾮得将投资回报率的标准差即波动率降⾄最低，⽽是可以通过削弱投资组合回报率与业绩基准⾛势之间的关联度的⽅式实现。