计算方差-EXCEL在投资组合理论中的应用

EXCEL在投资组合理论中的应用教学内容:一、计算投资组合的数字特征;二、在没有卖空限制下计算有效前沿组合(1)计算有效前沿;(2)绘制资本市场线;(3)绘制证券市场线;三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别四、EGP法计算前沿组合在EXCEL中的实现。一计算期望收益率、标准差、协方差矩阵和相关系数;1.一个简单的两资产组合的例子（表1）假如有两只股票12个月度的价格数据:股票A和股票B,资料如下:1.1.收益率与期望收益1）收益率的计算以股票A为例,计算该股票的月收益率.股票A在第t月的收益率为在第t月月末与第(t-1)月末价格之比的自然对数,计算公式为:1ln()AtAtAtPrP注意:对数收益率是对普通收益率泰勒级数展开得到的，t期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。对数收益率的好处是可以直接相加，比如t期到t+n期的对数收益率可以由Rt+R(t+1)+R(t+2)+...得到。(1)这个公式采用的是连续收益率计算公式,而离散收益率计算公式为,,11AtAtAtPrP(2)如果在第t月末获得股利收入,记为tDiv,则收益率为,,1lnAttAtAtPDivrP.(3)在考虑股利收入下,股票的离散型收益率为,,1,1AttAtAtAtPDivPrP.本例中的收益率的计算采用连续收益率形式,并忽略股利收入.具体步骤是:使用EXCEL中的LN函数计算股票的收益率.调用Ln函数的方法是:单击EXCEL工具栏下的[]xf，或者选择[插入]菜单中的[函数]命令，弹出[粘贴菜单]对话框，在[函数分类]中选择[数学与三角函数]。在[函数名]中选择[LN]函数，单击[确定]按钮即可。2）期望收益的计算期望收益是指持有股票的投资者在下一个时期所能获得的收益预期。单个证券的期望收益可以通过计算历史数据的样本均值来估计。在EXCEL中可以通过[统计]中的[AVERAGE]函数实现对期望收益的计算（见表1）。具体操作步骤如下：（1）股票A每月的收益率：单击C4单元格，在编辑栏输入=LN（B4/B3），应用自动填充单元格命令即可求出各月收益率对应的C4：C15单元格区域的值。同样可求出股票B的月收益率。（2）股票A的月期望收益率：选择C16单元格，在编辑栏中输入=AVERAGE（C4：C15）。股票B的月期望收益率：选择E16单元格，在编辑栏中输入=AVERAGE（E4：E15）。（3）股票A的年期望收益率：选择C17单元格，在编辑栏中输入=12*C16。同样的方法可得股票B的年期望收益率。1.2方差与标准差方差与标准差刻画证券收益率变动，是风险的常用度量指标，在EXCEL中方差，样本方差，标准差，样本标准差分别用VAR（计算基于给定样本的方差），VARP（计算基于给定的样本总体的方差），STEDV，STDEVP来表示，公式如下：221111(),()1nniiiiiiVARrErVARPrErnn（无偏估计）,STDEVVARSTDEVPVARPVAR:计算基于给定样本的方差。函数VAR假设其参数是样本总体中的一个样本。VARA:计算基于给定样本的方差。不仅数字，文本值和逻辑值（如TRUE和FALSE）也将计算在内.函数VARA假设参数为总体的一个样本。如果数据代表的是样本总体，则必须使用函数VARPA来计算方差。VARP:计算基于整个样本总体的方差。函数VARP假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数VAR计算方差。计算基于整个样本总体的方差。函数VARP假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数VAR计算方差。沿用上面求出的12个月的收益率，通过EXCEL中[工具栏]下的[xf]/[统计]中的VARP函数和[xf]/[统计]中的STDEVP函数，计算收益率的方差、标准差。1.3协方差协方差是度量两种风险资产收益之间线性关联程度的统计指针。正的协方差表示资产收益同向变动；负的协方差表示它们反向变动。可以通过EXCEL工具栏中[xf]/[统计]/[COVAR]直接求协方差，具体步骤如下：（1）单击一空白单元格，选择[xf]/[统计]/[COVAR]命令，出现[COVAR]函数对话框。（2）COVAR函数对话框中，[Array1]选择相应单元格区域，[Array2]选择相应单元格区域。（3）完成后单击确定。1.4相关系数相关系数刻画两个随机变量的线性关联程度。有两种计算方法：（1）根据定义式计算。(,)ABABABCovrr（2）可以通过EXCEL工具栏中[xf]/[统计]/[CORREL]直接求得。表12．投资组合期望收益和方差的计算，及标准差—期望收益曲线的绘制（表2）。上一节介绍了单只股票的期望收益，标准差和股票间协方差等数字特征的计算过程，本节介绍两个证券构成投资组合的数字特征的计算。沿用上节的例子，构造一个由股票A和股票B各占50%的投资组合(,)ABp=(50%,50%),式中1AB。投资组合p的期望收益是：()()()pAABBErErEr方差是：22()()()2(,)BpAABABABVarrVarrVarrCovrr根据上面的公式运用EXCEL可以求出该投资组合p的期望收益和方差。任意改变投资权重A，运用EXCEL中的[模拟运算表]功能可以算出两种股票任意投资组合的期望收益和方差。具体操作步骤如下：（1）建立工作表，输入A的一组设定值和模拟运算表的样板，本例中是在A=50%的情况下求得投资组合标准差和期望收益。单击J2单元格，在编辑栏中输入=SQRT（F19），单击K2单元格，在编辑栏中输入=AVERAGE(F5:F16),或者输入=F18。预留空白单元格区域J3:K19以备填写计算结果。（2）选定需计算的单元格区域I2:K19，以反白显示。（3）单击[数据]菜单中的[模拟运算表]选项。由于工作表中的“组合的标准差”和“组合的期望收益”各成一列，故在屏幕弹出的对话框中的[输入引用列的单元格]中输入$C$1，如图。（4）单击[确定]按钮，在空白区域将自动填入不同投资比例下组合的标准差和期望收益。计算结果如图。根据获得的数据，使用EXCEL的图表功能可以绘制标准差—收益曲线。具体操作步骤如下：（1）选定作图需要的数据，在本例中为J3:K19区域。（2）单击[插入]菜单下的[图表]选项，或直接单击工具栏上的[图表向导]按钮。屏幕弹出[图表向导—4步骤之1—图表类型]的对话框，选择[标准类型]/[xy散点图]/[无数据点平滑线散点图]。（3）单击[下一步]按钮，进入[图表向导—4步骤之2—图表源数据]对话框。因为第一步中已经选定了所需数据，因此一般不需要改动步骤二中的任何设置。（4）单击[下一步]按钮，进入[图表向导—4步骤之3—图表选项]对话框。在这步中可以修改所绘图表，如在[标题]标签中可以在绘制的曲线图上添加图表，x轴y轴标题，在[网格线]标签中增加或删除网格线等。设置完毕后单击[下一步]按钮。（5）在[图表向导—4步骤之4—图表位置]中选择图表需要嵌入的位置，单击[完成]按钮结束操作，即可在指定位置插入绘成的图表。（6）可以对图表进行修改。3多个风险资产投资组合的期望收益和方差的计算推广到多个风险资产的投资组合的情形，计算投资组合的数字特征，并得出投资组合标准差和收益之间的关系。3．1运用EXCEL进行矩阵运算需要用到的函数：MDETERM（数组）：返回数组所代表的矩阵行列式的值；MINVERSE（数组）：返回数组所代表的矩阵的逆；MMULT（数组1，数组2）：返回两数组矩阵的乘积；TRANSPOSE（数组）：返回数组矩阵的转置矩阵。1）矩阵的转置计算步骤：（1）输入矩阵A（3*4阶矩阵）中的数值。（2）选择结果矩阵区域为，以反白显示。（3）单击EXCEL工具栏中[xf]/[查找与引用]/[TRANSPOSE]函数，在屏幕弹出的对话框中，选择[Array]参数为B22：K33。（4）用[Ctrl+Shift+Enter]组合键完成键入。2）矩阵的乘积。3）矩阵的逆。3.2计算方差—协方差的几种方法（本部分Excel操作放置在文档中）1)用超额收益矩阵Excel函数：求平均值的函数AVERAGE，求矩阵的转置矩阵的函数TRANSPOSE，求矩阵的乘积的函数MMULT。具体操作步骤为：（1）计算每个资产的超额收益率：根据公式11,1,2,,MiittrriNM，使用AVERAGE函数计算出股票AMR的收益率。选择B14单元格，在编辑栏中输入=AVERAGE(B4：B13）,应用自动填充单元格命令，可求出其它5只股票的期望收益率，计算结果如图所示。（2）计算超额收益矩阵：用每只股票各期的收益率减去该只股票的期望收益率。选择C17单元格，在编辑栏中输入=B4-＄B＄14，回车后得到股票的超额收益，应用自动填充单元格命令可求出股票AMR其它年份的超额收益，同样的方法可求出其它5只股票在各年份的超额收益。计算结果如图所示。（3）使用数组函数TRANSPOSE计算超额收益转置矩阵：选择区域B16：G25，单击EXCEL插入栏下[函数]，出现“插入函数”对话框，选择类别“全部”，从选择函数栏下选择“TRANSPOSE”，按“确定”后出现“函数参数”对话框，在array栏里填入$B$28：$K$23,按确定即可。（4）计算方差—协方差矩阵：使用数组函数MMULT计算TA和A的乘积，再除以期数M（本例中M=10），即可求得方差—协方差矩阵。2)用OFFSET函数计算方差—协方差矩阵OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新引用的函数，返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。OFFSET函数的参数依次为引用区域，下偏移行数，右偏移列数，返回区域行数和返回区域列数。如果省略后面的两个参数，则假设其高度或宽度与应用区域相同。沿用上例中的数据，用OFFSET函数计算方差—协方差矩阵。为方便引用参数，在计算前先将偏移量（0，1，2，3）输入到方差协方差矩阵中。具体操作步骤如下：（1）选择单元格B16.（2）在编辑栏中输=COVAR(OFFSET($B$4:$B$13,0,B$15),OFFSET($B$4:$B$13,0,$A16),在公式单元格中出现股票A的方差值。（3）使用EXCEL自动填充单元格命令求出单元格区域B16：G21的值，即求得方差协方差矩阵。3)用单指数（SIM）模型计算方差—协方差单指数模型建立的前提是假设每种资产的随机收益率与市场随机收益率（市场指数）之间存在着线形回归关系。iiixiRR，其中,ixRR分别是第i个资产和市场的随机收益率，,ii是回归参数，i是残差，()0,(,)0iijECov满足可得任何资产的期望收益和资产间协方差分别为：2()(),(,)iiixijijxERERCovij具体步骤如下：（1）计算市场指数的方差：选择H13单元格，在编辑栏中输入=VARP（H2：H11）（2）股票A的系数：选择B12单元格，在编辑栏输入=SLOPE(B2:B11,$H$2:$H$11)，回车后即出现股票A的系数。应用自动填充功能得到其他股票的系数.（3）将各公司的值列于待求的方差协方差矩阵的左边缘。选择单元格B15，在工具栏下的公式编辑栏中输入=B$12*$A15*$H$13,求出矩阵左上角单元格的结果B16：G20。（4）应用自动填充功能求解方差协方差矩阵，对应单元格区域中的值，结果如图所示。注：上述方法均可使用，前两种方法是根据收益率计算的，SIM方法使用值，算的结果有差异，用SIM模型使输入数据的估计量减少到3n+2,简化了计算过程，减小了估计误差.实践证明：使用SIM模型计算方差协