今天这篇文章是为回应读者的要求，对如何应用EXCEL的规划求解功能实现投资组合在均值方差方法下的配置优化再次进行实例说明。
假设本投资组合由五只交易所交易基金构成，分别为：
XLK
XLF
XLC
XLI
XLY
设定的前提条件为这五只基金的原始配置比率均为20%，即等权重配置。为实现优化，必然会对基金的配置权重进行调整，某些基金的权重要增加，有的要减少，但不能净做空，也不加杠杆，还要保持整个投资组合全仓配置，此外，买卖基金的单位交易成本假定为市值的0.05%。
数据使用的是2020年4月至2021年4月期间共13个月份的月度收盘价，数据下载来源为finance.yahoo.com，注意，原始数据下载后，应使用adjusted close，即经调整后的数据，该数据对分红金额进行了除权处理，能更准确地反映回报率的变化。
数据下载整理完成后，结果如下：
第二步是计算月度回报率，公式为本月回报率=LN（本月月度收盘价/上月月度收盘价），也可用“本月月度收盘价/上月月度收盘价）-1”的方法计算，结果应相差不大，同时计算单项资产月度回报率的均值。本例中收盘价数据共有13个，因此月度回报率数据应为12个，如下：
下一步是计算投资组合的方差值，由于整个投资组合的方差值=∑（单项资产回报率的方差），因此需要使用“协方差”功能形成单项资产回报率的协方差矩阵：
打开“协方差”功能后，“输入区域”为五只基金各月度回报率的数值，光标点选红框圈定的区域即可，其它选项参照图示，“输入区域”选EXCEL表中任一较大空白区域即可。
点击确认键后，形成单项资产回报率的协方差矩阵如下：
空白区域需要用相应的协方差数据填满，也就是将左侧红圈区域的纵列数据依次转换放在右侧红圈区域的横排单元格中。以纵列一为例，光标选定红色圈定区域，在第一个单元格内输入公式TRANSPOSE（），（）内为粉色圈定区域的单元格。
然后同时按键“shift+ctrl+enter”，左侧红圈区域的纵列数据依次转换放在右侧红圈区域的横排单元格中，依次填满全部空白区域。
左纵列右横排数据的对应关系如下，确保同颜色的左右区域数值相同。
下一步是分别计算单项资产回报率的方差，最后再将单项资产回报率的方差加总得出整个投资组合回报率的方差。
前面提到，这五只基金的原始配置比率均为20%，最终要实现的目的是：在投资组合优化后的回报率达到预期目标的前提下，通过对各基金的配置权重进行调整，实现投资组合回报率的方差值最小化。
因此，红圈区域的变化值就是基金权重调整的百分比数值，也是即将使用的规划求解功能的“变动项”。
为此需要通过该公式确定逻辑关系：各资产配置权重调整后的新比率=当前的配置比率+变化值，比如下图中XLK基金初始的配置比率为手工设定的20%，在尚未进行调整的时候，变化值为0%，因此新的配置比率=20% +0%=20%，在EXCEL表中单元格数据之间的逻辑关系为L44=K44+M44。
其它四个基金的配置权重调整的逻辑均照此办理，L44单元格中的公式设定完毕后，光标按住该单元格下拉至L48，然后在单元格L49加总计算整个投资组合的配置比率，整个投资组合的配置比率=∑（单项资产的配置比率）。
下一步是计算投资组合优化后的回报率，但是在未调整权重的情况下该值肯定为初始回报率，整个投资组合优化后的回报率=∑（单项资产新的配置比率*相应的配置权重）-单位交易成本*∑（单项资产配置比率调整值的绝对值）。计算结果显示，在这五只交易所交易基金在初始配置权重相等的情况下，整个投资组合的初始回报率为3.71%。
我的目标是想让投资组合优化后的回报率达到3.82%，同时使目前为0.0018113的投资组合回报率的方差值尽可能保持最低，从而达到优化投资组合的目标。为此需使用规划求解功能，规划求解的目标单元格为投资组合回报率的方差所在单元格N40，数值设定为“最小值=0”，可变单元格为单向资产配置比率变化值所在单元格M44:M48，约束条件主要为三大项：1，投资组合优化后的回报率=投资组合预期回报率，注意该预期回报率的数值如果设定过高，有可能规划求解无法得出结果，一般应在当前的回报率与投资组合中单项资产回报率之间寻求一个妥协；2，整个投资组合的配置比率=100%，即单项资产不能净做空，也不加杠杆，还要保持整个投资组合全仓配置；3，单项资产的配置比率≧0%，否则规划求解的计算结果可能会出现负的配置比率，从而违背条件2。
设定好以上各项条件，下一步就是实际计算，规划求解的结果显示，投资组合优化后的回报率达到了3.82%，各基金的配置权重调整的情况见下表中红圈部分，可见在单项资产的配置比率均≧0%，也就是没有出现净做空，加杠杆和整个组合全仓配置的情况下，这个投资组合回报率的方差值为0.0023782。
