excel在投资组合理论教学中的应用

EXCEL 在投资组合理论教学中的应用 李吉栋 （河北经贸大学金融学院，石家庄，050061） 摘要：投资组合理论是金融学科的一个重要理论，内容比较抽象，数学模型多，学生理解 起来很困难。在投资组合理论的教学过程中，利用 EXCEL 的数据运算和图表功能，将抽 象的理论知识直观地演示出来，不但使教学内容深入浅出，易于理解，也可以帮助学生将 理论知识与投资实践结合起来，激发学生们的学习兴趣。 关键词：EXCEL；投资组合理论；规划求解 投资组合理论是金融学科的一个重要理论。该理论认为，在由若干证券构成的所有可 能的投资组合中，只有部分组合是有效的，理性投资者在这些有效组合中选择最适合自己 的组合。基于对证券期望收益率、标准差以及协方差的估计，我们可以找到这些有效组合， 即投资组合前沿。在以往的教学实践中，学生们普遍反映这部分内容非常抽象，数学模型 多，难以把这些理论和模型与投资实践联系起来，理解起来很困难。如果在教学过程中能 够利用中国证券市场的真实数据，将这些数学模型演算一下，再借助于必要的图形分析， 使这些抽象的理论和模型能够直观地演示出来，对学生理解教学内容会大有帮助。EXCEL 是一款功能强大的电子表格数据处理软件，而且具备丰富的图表演示功能，非常适合在投 资组合理论教学中使用。下面笔者将结合自己的教学实践，介绍在投资组合理论教学中如 何应用 EXCEL。 一、运用 EXCEL 演示证券的相关性对组合风险的影响 投资组合理论的核心思想就是风险的分散化。投资者之所以要持有多个证券，其根本 原因就在于部分风险会随着持有证券个数的增加而有所降低。影响证券组合风险分散化效 果的一个重要因素就是证券之间的相关性。我们一般是通过分析两个风险证券的组合来帮 助学生理解证券相关性与风险分散化之间的关系。 首先，假设市场上有两个风险证券，知道这两个证券的期望收益率、标准差和相关系 数，计算出这两个证券的不同权重组合的期望收益率和标准差，画出投资组合曲线；然后 改变两个证券的相关系数，比较投资组合曲线的变动情况。市场上两个风险证券的相关数 据如图 1 所示。按照期望收益率和标准差的计算公式，计算出一系列不同权重组合的期望 收益率和标准差，用 EXCEL 绘图功能中的散点图，画出这一系列不同权重组合期望收益 率和标准差的相关关系图，如图 1 所示。 再利用 EXCEL 的微调按钮，调整证券的相关系数。添加微调按钮的方法是：点击“视 图”按钮下面的“工具栏” ，选择“窗体” ，点击微调项，在 B4 单元格处添加微调按钮 （如图 1） ，在微调按钮上点击右键，在对话框中设置当前值、步长和单元格链接（$B$4） ， 再设定相关系数单元格与微调按钮链接单元格的关联，C4=B4/10-1 ，如图 1 所示。然后用 鼠标点击微调按钮的向上箭头或向下箭头，调整相关系数大小，证券组合曲线也随之变动， 如图 2 所示。通过 EXCEL 的微调按钮，教师在 PPT 上可以连续地调整相关系数，图中的 曲线也随之连续地移动，证券相关系数与证券组合标准差之间的关系就直观的演示出来了。 二、运用 ECEL 模拟证券组合的可行集和有效集 证券组合的有效集是可行集的子集，它们满足的条件是：在所有期望收益率相同的组 合中，它们的标准差最小；在所有标准差相同的组合中，它们的期望收益率最大。如果能 图 1 相关系数为 0.5 时的两证券组合 图 2 利用微调按钮调整相关系数后的两证券组合 利用中国证券市场的真实数据，模拟出一组证券组合的可行集区域，学生们就可以非常直 观地理解有效集和投资组合前沿的概念了。 下面通过一个例题来说明模拟证券组合可行集的过程。首先找到 4 个证券，西山煤电、 浦发银行、中国卫星和新兴铸管，选取 2006 年 8 月到 2011 年 8 月的月度收盘价格数据 （本例题的数据来自于大智慧，为复权后数据） ，计算它们每一期的收益率，以及平均收益 率、收益率标准差和协方差数据，如图 3 所示（第 5 行到第 61 行数据隐藏） 。收益率均值 用 AVERAGE( )函数计算，标准差用 STDEV()函数计算，协方差矩阵用数据分析工具库中 的协方差工具产生，具体步骤是：点击“工具”中的“数据分析 ”按钮（如果没有安装， 需要点击“加载宏” ，选择“分析工具库”安装） ，选择“协方差 ”，在对话框的输入区域选 择 F1:I62，在输出区域选择 A67，选中“标志位于第一行”后点击确定，得到左下半部分 的协方差矩阵，再根据协方差矩阵的对称关系补齐右上半部分即可。 有了基础数据后就可以进行数据模拟了，具体步骤是：首先用 RAND()在 A74:D74 区 域产生 4 个均匀分布的随机数，按四个随机数的相同比例折算成 4 个证券的权重，如图 4 所示。然后利用随机产生的权重数据计算该组合的期望收益率和标准差，期望收益率的计 算公式为 B80=MMULT(F64:I64,TRANSPOSE(A77:D77)，标准差的计算公式为 C80=SQRT(MMULT(MMULT(A77:D77,B68:E71),TRANSPOSE(A77:D77)。再利用 EXCEL 的模拟运算表功能随机产生包含上述四个证券的 1000 个组合的期望收益率和标准 差，具体步骤为：在 A81 单元格填入 1，点击“编辑”中的 “填充”按钮，选择“序列” ， 步长设定为 1，终止值设定为 1000，令序列数据为列数据，在 A 列 81 行到 1080 行就产生 从 1 到 1000 的序列数据，选中区域 A80:C1080，点击“ 数据 ”中的“模拟运算表” ，在 “输入引用列的单元格”位置输入一个空白单元格，如 D78，再点击确定就模拟出了 1000 个证券组合，如图 4 所示。最后利用 EXCEL 的绘图功能，将 1000 个期望收益率和标准差 数据绘制散点图，如图 5 所示。从真实数据模拟出的散点图上，学生们可以很快地找到投 资组合前沿的位置，有效集和投资组合前沿的概念也就很容易理解了。 图 3 证券平均收益率、标准差和协方差矩阵计算 图 4 任意权重证券组合数据模拟 1.7% 1.9% 2.1% 2.3% 2.5% 2.7% 2.9% 3.1% 3.3% 12.0% 13.0% 14.0% 15.0% 16.0%标 准 差 期望 收益 率 三、运用规划求解功能求投资组合前沿曲线和切点组合 构建证券的前沿组合是一个规划求解问题，金融学专业的学生一般不开设运筹学课程， 因此不了解规划求解的原理，对这部分内容理解起来很吃力。如果在教学过程中，利用 EXCEL 的规划求解功能将求解证券组合前沿的过程直观地演示一下，使教学内容深入浅出， 学生们就可以很容易地理解投资组合前沿曲线的涵义和求解方法。 利用 EXCEl 的规划求解功能寻找前沿组合的具体步骤为：首先随机给定一个证券组合， 如图 6 所示，为了使四个证券权重之和为 1，令单元格 D13=1-C13-B13-A13。在单元格 E13 和 F13 中分别输入标准差和期望收益率的计算公式， E13=SQRT(MMULT(MMULT(A13:D13,B6:E9),TRANSPOSE(A13:D13)， F13=MMULT(B2:E2,TRANSPOSE(A13:D13)。然后点击“工具”中的“规划求解” （如果 没有安装，点击“工具”中的“加载宏”安装） ，在“规划求解参数 ”对话框中，设定目标 单元格 E13 等于最小值，可变单元格为 A13:C13，约束条件为： A130，A131，B130，B131，C130，C13 1，D13 0， D131，F13=0.019（忽略证券 的融资融券交易，假设证券的权重都介于 0 到 1 之间） ，如图 7 所示，点击确定就可以求解 出期望收益率为 1.9%的前沿组合了。 图 5 任意权重证券组合的期望收益率标准差散点图 图 6 特定证券组合的相关指标运算关系 重复上述步骤，可以分别求出不同目标期望收益率的前沿组合。最后用 EXCEL 的绘图 功能绘制散点图，就得到由这四个证券构成的投资组合前沿曲线，如图 8 所示。这样，利 用 EXCEL 的规划求解功能，就可以非常直观地演示前沿组合的求解过程。 根据投资组合理论，当投资者可以在无风险资产和风险资产之间配置资产时，其风险 资产组合为切点组合，即过无风险资产向投资组合前沿所做的切线的切点，如图 9 所示。 切点位置是所有风险资产组合中夏普比率最大的点，用 EXCEL 的规划求解功能也可以找 到切点组合。求解步骤为：在单元格 G3 中输入夏普比率的计算公式 G13=(F13-2.5%/12) /E13（设无风险利率为 2.5%） ，在 A13、B13 和 C13 单元格随便输入三个权重数据，点击 “规划求解” ，设置目标单元格 G13 等于最大值，可变单元格为 A13:C13，约束条件为： A130，A131，B130，B131，C130，C13 1，D13 0， D131，点击确定就可以求出 切点组合了，如图 9 所示。 图 7 规划求解的参数设定 图 8 有效组合求解结果与证券组合前沿曲线 四、结论 利用 EXCEL 的数据运算和图表功能，可以将抽象的理论和模型直观地演示出来，如果 再结合中国证券市场的真实数据，就可以使学生们比较容易地将课堂上的理论知识与投资 实践结合起来，也可以更好地激发学生的学习兴趣。在投资组合理论教学过程中，如果能 够同步安排 EXCEL 建模实验课，让学生们自己再