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

EXCEL在投资组合理论中的应用教学内容:一、计算投资组合的数字特征;二 、在没有卖空限制下计算有效前沿组合 (1) 计算有效前沿; (2) 绘制资本市场线; (3) 绘制证券市场线;三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别 四、EGP法计算前沿组合在EXCEL中的实现。一 计算期望收益率、标准差、协方差矩阵和相关系数;1.一个简单的两资产组合的例子（表1）假如有两只股票12个月度的价格数据:股票A和股票B,资料如下: 1.1.收益率与期望收益 1）收益率的计算以股票A为例,计算该股票的月收益率.股票A在第t月的收益率为在第t月月末与第(t-1)月末价格之比的自然对数,计算公式为:注意:对数收益率是对普通收益率泰勒级数展开得到的，t期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。对数收益率的好处是可以直接相加，比如t期到t+n期的对数收益率可以由Rt+R(t+1)+R(t+2)+.得到。(1) 这个公式采用的是连续收益率计算公式,而离散收益率计算公式为(2) 如果在第t月末获得股利收入,记为,则收益率为. (3) 在考虑股利收入下,股票的离散型收益率为. 本例中的收益率的计算采用连续收益率形式,并忽略股利收入. 具体步骤是:使用EXCEL中的LN函数计算股票的收益率.调用Ln函数的方法是:单击EXCEL工具栏下的，或者选择插入菜单中的函数命令，弹出粘贴菜单对话框，在函数分类中选择数学与三角函数。在函数名中选择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来表示，公式如下：（无偏估计）VAR:计算基于给定样本的方差。函数 VAR 假设其参数是样本总体中的一个样本。VARA:计算基于给定样本的方差。不仅数字，文本值和逻辑值（如 TRUE 和 FALSE）也将计算在内.函数 VARA 假设参数为总体的一个样本。如果数据代表的是样本总体，则必须使用函数 VARPA 来计算方差。VARP:计算基于整个样本总体的方差。函数 VARP 假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数 VAR 计算方差。计算基于整个样本总体的方差。函数 VARP 假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数 VAR 计算方差。沿用上面求出的12个月的收益率，通过EXCEL中工具栏下的/统计中的VARP函数和/统计中的STDEVP函数，计算收益率的方差、标准差。1.3协方差协方差是度量两种风险资产收益之间线性关联程度的统计指针。正的协方差表示资产收益同向变动；负的协方差表示它们反向变动。可以通过EXCEL工具栏中/统计/COVAR直接求协方差，具体步骤如下：（1） 单击一空白单元格，选择/统计/COVAR命令，出现COVAR函数对话框。（2） COVAR函数对话框中，Array1选择相应单元格区域，Array2选择相应单元格区域。（3） 完成后单击确定。1.4 相关系数相关系数刻画两个随机变量的线性关联程度。有两种计算方法：（1） 根据定义式计算。（2） 可以通过EXCEL工具栏中/统计/CORREL直接求得。表12投资组合期望收益和方差的计算，及标准差期望收益曲线的绘制（表2）。上一节介绍了单只股票的期望收益，标准差和股票间协方差等数字特征的计算过程，本节介绍两个证券构成投资组合的数字特征的计算。沿用上节的例子，构造一个由股票A和股票B各占50%的投资组合=(50%,50%),式中。投资组合p的期望收益是：方差是：根据上面的公式运用EXCEL可以求出该投资组合p的期望收益和方差。任意改变投资权重，运用EXCEL中的模拟运算表功能可以算出两种股票任意投资组合的期望收益和方差。具体操作步骤如下：（1） 建立工作表，输入的一组设定值和模拟运算表的样板，本例中是在=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 多个风险资产投资组合的期望收益和方差的计算 推广到多个风险资产的投资组合的情形，计算投资组合的数字特征，并得出投资组合标准差和收益之间的关系。31 运用EXCEL进行矩阵运算需要用到的函数：MDETERM（数组）：返回数组所代表的矩阵行列式的值；MINVERSE（数组）：返回数组所代表的矩阵的逆；MMULT（数组1，数组2）：返回两数组矩阵的乘积；TRANSPOSE（数组）：返回数组矩阵的转置矩阵。1） 矩阵的转置计算步骤：（1） 输入矩阵A（3*4阶矩阵）中的数值。（2） 选择结果矩阵区域为，以反白显示。（3） 单击EXCEL工具栏中/查找与引用/TRANSPOSE函数，在屏幕弹出的对话框中，选择Array参数为B22：K33。（4） 用Ctrl+Shift+Enter组合键完成键入。2）矩阵的乘积。3）矩阵的逆。3.2 计算方差协方差的几种方法（本部分Excel操作放置在文档中）1) 用超额收益矩阵Excel函数：求平均值的函数AVERAGE，求矩阵的转置矩阵的函数TRANSPOSE，求矩阵的乘积的函数MMULT。具体操作步骤为： （1） 计算每个资产的超额收益率：根据公式，使用AVERAGE函数计算出股票AMR的收益率。选择B14单元格，在编辑栏中输入=AVERAGE(B4：B13）,应用自动填充单元格命令，可求出其它5只股票的期望收益率，计算结果如图所示。（2） 计算超额收益矩阵：用每只股票各期的收益率减去该只股票的期望收益率。选择C17单元格，在编辑栏中输入=B4-B14，回车后得到股票的超额收益，应用自动填充单元格命令可求出股票AMR其它年份的超额收益，同样的方法可求出其它5只股票在各年份的超额收益。计算结果如图所示。（3） 使用数组函数TRANSPOSE计算超额收益转置矩阵：选择区域B16：G25，单击EXCEL插入栏下函数，出现“插入函数”对话框，选择类别“全部”，从选择函数栏下选择“TRANSPOSE”，按“确定”后出现“函数参数”对话框，在array栏里填入$B$28：$K$23,按确定即可。（4） 计算方差协方差矩阵：使用数组函数MMULT计算和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）模型计算方差协方差 单指数模型建立的前提是假设每种资产的随机收益率与市场随机收益率（市场指数）之间存在着线形回归关系。，其中分别是第i个资产和市场的随机收益率，是回归参数，是残差，满足可得任何资产的期望收益和资产间协方差分别为：具体步骤如下：（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模型计算方差协方差更有效。例：假设有4种风险资产（表3）（1） 计算由该4种风险资产构造的两个给定组合的数字特征。（2） 以此为基础计算由该两个组合构成的标准差期望收益曲线。（3） 以该标准差期望收益曲线为参照，描绘4种资产在标准差期望收益坐标系中的位置。二，计算无卖空限制下的有效前沿（MM模型下） MM模型：方法一：有效前沿的性质：(1) 设c为常数，记列向量，如果向量z为线性方程组R-c=Sz的解(S是方差协方差矩阵)，则，那么为前沿投资组合，其中，即x是z的规范化，满足；反之，任何前沿投资组合都满足以上条件。(2) 任何两个前沿投资组合的线性组合仍为前沿投资组合；反之，所有前沿组合都可以由任意两个不同前沿组合产生。 例：假设证券市场上有4种资产，各自的期望收益，方差和协方差如图，计算有效前沿。(表4)可分为两步进行：（一）寻找两个前沿组合.（二）通过前沿组合计算有效前沿。注：在MM模型下，则使用超额收益矩阵法或OFFSET函数计算证券收益率的方差协方差矩阵，从而求得MM模型下的有效前沿。若在SIM模型下，则使用SIM法计算方差协方差矩阵。从而求得SIM模型下的有效前沿。本例简化步骤，直接给出了方差协方差矩阵。 具体操作：（一）需要假设两个不同的常数C，通过R-c=Sz求解z，就可以算得两个前沿组合。（1） 假设,求矩阵。选择区域B8：B11，单击工具栏中/数学与三角函数/MMULT函数，在MMULT函数参数对话框中Array1和Array2参数项分别输入MINVERSE（D2:G5）和B2:B5。（2） 求前沿组合1，其组合投资比例矩阵为x：选择C8单元格，在公式编辑栏中输入公式=B8/SUM(B$8:B$11),然后使用自动填充功能填充C8：C11区域。（3） 假设，求矩阵：选择E8单元格，在EXCEL工具栏中输入公式=B2-$D$8,然后使用自动填充单元功能，将E8中的结果复制到E8：E11。（4） 求矩阵：选择区域F8：F11，在公式编辑栏中输入公式=MMULT（MINVERSE（D2：G5），E8：E11），以Ctrl+Shift+Enter组合键完成输入。（5） 求前沿组合2，其组合投资比例矩阵为y：选择G8单元格，在公式编辑栏中输入公式=F8/SUM($F$8:$F$11),使用自动填充功能计算G8：G11区域的结果，如下图所示： 下面求组合x和组合y各自的期望收益，标准差和两组合收益之间的协方差，计算步骤如下：（1） 计算期望收益：选择C13单元格，单击EXCEL工具栏中/数学与三角函数/MMULT函数，参数分别输入TRANSPOSE(C8:C11), B2:B5，Ctrl+Shift+Enter确认。（2） 计算方差：选择单元格C14，单击EXCEL工具栏中/数学与三角函数/MMULT函数，参数分别输入矩阵TRANSPOSE(C8:C11),和函数MMULT(D2:G5,C8:C11)，Ctrl+Shift+Enter确认。（3） 计算标准差：选择C15单元格，在公式编辑栏中输入公式=SQRT（C14），回车确认。（4） 计算两组合间的协方差：选择C17单元格，单击EXCEL工具栏/数学与三角函数/MMULT函数，Array1和Array2两项参数分别输入TRANSPOSE(C8:C11),和MMULT(D2:G5,G8:G11)，Ctrl+Shift+Enter确认。（5） 计算两组合间的相关系数：选择B21单元格，输入公式=C17/SQRT(C14*G14)，回车确认。计算结果如图。 （二）计算得到的两个前沿组合的线性组合，可以求出整个可行域的包络线，有效前沿也包括在内。 假设对组合x的投资比例为a，对组合y的比例为(1-a)的组合为p，可得到组合的期望收益和标准差: 令a=0.3,可以得到组合p的期望收益和标准差。此外，使用EXCEL的模拟运算表功能，计算对组合x和组合y不同投资比例下的期望收益和标准差，具体步骤如下：（1） 选择F20：H41单元格区域。（2） 单击数据菜单中的模拟运算表选项，在弹出的模拟运算表对话框编入引用列的单元格中输入$C$20，即在空白区域自动填入不同投资组合比例下组合的标准差和期望收益。计算结果如下：根据获得的数据，使用EXCEL的图表功能可以绘制有效边界曲线。具体操作步骤如下： （1） 单击工具栏上的图表向导，在弹出的对话框中标准类型下选择xy散点图/平滑线散点图。（2） 单击下一步按钮，进入图表源数据对话框，在数据区域中选择G20：H41单元格区域，系列产生在选择“列”，如图。（3） 单击下一步按钮，进入图表选项对话框，在标题标签页中在绘制的曲线图上添加x轴y轴标题，在网格线标签中删除网格线，如图。设置完毕后单击下一步按钮。（4） 在图表位置中选择图表需要嵌入的位置，单击完成按钮，结束操作，即可在指定位置插入绘成的图表。可知：由前沿组合x和y的线性组合构成的所有组合都在可行域的包罗线，但并非所有的组合都是有效组合。方法二：求解问题： 利用Lagrange乘数法，运算得到求解下面的矩阵方程：记作：A*W=C，则。利用EXCEL中的MMULT及MINVERSE函数即可求得对应于每个给定的的最优投资比例，从而可得到有效边界线。注意：两种方法从本质上是一致的，可通过具体操作进行实证，得到的两条有效边界线是重合的。三）资本市场线（Capital Market Line,CML）（表4）指在标准差期望收益坐标系中无风险资产和有效前沿曲线的切线。投资者在资本市场中进行投资组合选择，不仅可以投资于风险资产，通过资产组合降低风险，也可以投资于无风险资产（如短期国库券）。通常无风险资产其收益率记为。CML刻画了在存在无风险证券且无借贷限制时的投资组合有效前沿，此时有效前沿退化为一条直线，即CML。考虑一个有效组合N和无风险资产构成的线形组合p，且无风险资产的投资比例为a,则新的资产组合p的期望收益和标准差分别为：资本市场线表示的是当存在无风险证券时，有效投资组合的风险和收益的比例关系，每一个投资者都可以根据他们对风险的偏好程度在CML上选择各自的组合。当存在无风险资产的收益率已知时，可计算出市场组合M，具体步骤如下：令求解前沿组合时使用的常数c=,则组合M为市场组合的充要条件是： 式中为资产i的市场价值在全部证券市场总价值中的比重。下面计算组合M，数字特征及资本市场线，具体步骤如下：（1） 令，求矩阵：选择J9单元格，在编辑栏输入公式=B3-$I$9,使用自动填充单元格命令求得矩阵，对应单元格区域J9：J12。（2） 求矩阵z：选择单元格区域J9：J12，在编辑栏中输入公式=MMULT(MINVERSE(D3:G6),J9:J12)，以组合键Ctrl+Shift+Enter组合键完成输入。（3） 求市场组合M：选择L9单元格，在编辑栏中输入公式=$K9/SUM(K$9:K$12),使用自动填充单元格命令求得M的投资比例矩阵L9：L12。（4） 求市场组合的各项数字特征。M的期望收益：选择单元格J15，在编辑栏中输入公式=MMULT(TRANSPOSE（L9：L12）,B3:B6)。M的方差：选择单元格，在编辑栏中输入公式=MMULT(TRANSPOSE（L9：L12）,MMULT(D3:G6,L9:L12)，M的标准差：选择J16单元格，在编辑栏中输入公式=SQRT（B61）。结果如图。得到市场组合,市场组合的标准差和期望收益分别为0.2121,0.0726.(5)求出资本市场线：在图的基础上添加一个系列，该系列的x值为0和0.2121,y值为0.050.和0726,得出无风险证券(0,0.05)和市场组合(0.2121,0.0726)之间的连线即资本市场线，结果如图。如图：资本市场线和有效前沿曲线相切，而市场组合正是切点。（四）证券市场线 证券市场线(Security Market Line，SML)是资本资产定价模型的数学方程式，在以为横轴，期望收益率为纵轴的图上的表达。证券市场线刻画任何资产或投资组合的期望收益与其对应的系数之间的线性关系，系数代表证券的系统风险，系数越大，资产的预期收益越大。证券市场线为评估投资业绩提供了一个基准。计算证券市场线的一种简单方法是：若无风险收益率（可以用短期国库券的收益率代替）和市场组合收益率（可以用市场指数代替）是已知的，则两点之间的直线为待求的证券市场线。这种方法实际操作中误差较大。下面介绍一种经常使用的方法将期望收益对系数作回归的方法。绘制证券市场线。（1） 股票A的期望收益：选择B14单元格，在编辑栏中输入公式=AVERAGE（B4：B13），应用自动填充单元格命令可求得其它5只股票的期望收益。（2） 股票A的系数：选择B15单元格，在编辑栏中输入=SLOPE(B4:B13,$H$4:$H$13)；应用自动填充单元格命令可求得其它5只股票的系数，对应单元格区域中的值，计算结果如图： （3） 求解市场证券线的截距和斜率。根据证券市场线的数学表达式： 其截距和斜率可以通过对期望收益率和贝塔系数两组数据进行回归获得。使用统计函数中的INTERCEPT和SLOPE函数，求截距：选择单元格B16，在编辑栏中输入=INTERCEPT(B14:H14,$B$15:$H$15).求斜率：选择单元格B17，在编辑栏中输入=SLOPE（B14：H14，$B$15：$H$15）。（4） 绘制证券市场线：单击工具栏上的图表向导，在弹出的图表向导4步骤之1图表类型的对话框中选择标准类型/xy散点图/散点图。（5） 单击下一步按钮，在图表向导4步骤之2图表数据源对话框中，数据区域选择“B14：H15”。注意这里先计算各股票的期望收益率，后计算系数，而证券市场线的x轴为，y轴为期望收益，因此在系列的卷标中，要将x轴和y轴的值对调。（6） 单击下一步按钮，进入图表向导4步骤之3图表选项对话框，在标题标签中在绘制的曲线图上添加x轴，y轴标题，在网格线标签中删除网格线。设置完毕后单击下一步按钮。（7） 在图表向导4步骤之4图表位置中选择图表需要嵌入的位置，单击完成按钮结束操作，即可在指定位置插入绘成的图表。 五，MM模型下计算有卖空限制的有效前沿（规划求解）当存在卖空限制，即不允许卖空时，求解有效投资组合，即求解下面的线形规划问题： （5.1与无卖空限制时的区别在于是否有约束条件。在有卖空限制下增加了约束条件，可行的投资组合区域就会缩小。下面通过实例介绍在有卖空限制条件下的前沿组合和有效前沿曲线的求解问题，并和无卖空条件下的有效前沿曲线进行比较。由于公式（5.1）是一个规划问题，没有解析形式的解，因此求解有卖空限制下的有效前沿要用到EXCEL菜单栏中工具下的规划求解。例：4只证券的期望收益和协方差矩阵如图所示。（1） 建立如图的工作表：假设4只证券的投资比例，并保证G9单元格中的值始终为1。（2） 根据上面的数