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行数据隐藏）。收益率均值用A VERAGE( )函数计算，标准差用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证券平均收益率、标准差和协方差矩阵计算

1.7%1.9%

2.1%2.3%2.5%2.7%2.9%

3.1%3.3%12.0%13.0%1

4.0%1

5.0%1

6.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，约束条件为：A13≥0，A13≤1，B13≥0，B13≤1，C13≥0，C13≤1，D13≥0，D13≤1，F13=0.019（忽略证券的融资融券交易，假设证券的权重都介于0到1之间），如图7所示，点击确定就可以求解出期望收益率为1.9%的前沿组合了。

图5任意权重证券组合的期望收益率——标准差散点图