实验四 组合收益风险计算指导书

实验四组合收益风险计算指导书
一、计算单证券、两证券的收益风险
已知股票A和股票B连续12个月的收盘价如图所示，假定在此期间没有支付任何股利，试求每种股票的月收益率、期望收益率、标准差，以及它们之间的协方差和相关系数。

如果某个投资组合由30％的股票A和70％的股票B构成，试求该投资组合的期望收益率和标准差。

（1）在单元格D4中输入公式＝“(B4-B3)/B3, 并将其复制到单元格E4，计算股票A和股票B第1月份的收益率。

（2）选取单元格区域D4：E4，将其往下一直填充复制到单元格区域D15：E15，计算股票A和股票B其他各月的收益率。

（3）在单元格G4中输入公式“＝C9/B9”, 计算A股票的变差系数
（4）在单元格G5中输入公式“=AVERAGE(D4:D15)”，并将其复制到单元格H4，得到股票A和股票B月收益率的期望值。

（5）在单元格G6中输入公式“=COVAR（D4:D15,E4:E15）”，计算股票A和股票B收益率之间的协方差。

（6）在单元格G7中输入公式“=CORREL（D4:D15,E4:E15）”或输入公式“=G6/（G5*H5）”，计算股票A和股票B 收益率之间的相关系数。

（7）在单元格G12中输入公式“=SUMPRODUCT（G4:H4,G11:H11）”，计算投资组合的期望收益率。

（8）在单元格G14中输入公式“=SQRT（（G11*G5）∧2+（H11*H5）∧2+2*G11*H11*G6）”，计算投资组合的标准差。

二、计算两种风险资产（risky portfolio）最优组合
试计算（例2）:
已知A资产的预期收益率为20%，标准差为10%；B资产的预期收益率为25%，标准差为20%；A、B两项资产在投资组合中的比重均为50%。

试分别计算：
（a）A、B两项资产之间的相关系数分别为1、0.5、0、-0.5和-1时，投资组合的期望收益率和标准差。

（b）以标准差为X轴，以期望收益率为Y轴，画出A、B两项资产不同投资组合下的期望收益率和标准差之间的关系曲线。

（a）如图2所示，计算步骤如下：
（1）在单元格B10中输入公式“=SUMPRODUCT（$B$5:$B$6,$D$5:$D$6）”，计算投资组合的期望收益率。

“=SQRT（$D$5∧2*$C$5∧2*$C$6（2）在单元格B11中输入下面的公式，计算相关系数为1时投资组合的标准差。

∧2+2*$D$5*$D$6*B9*$C$5*$C$6）”。

（3）选取单元格区域B10:B11，将其向右一直填充复制到单元格区域F10:F11，得到不同相关系数下投资组合的期望收益率和标准差。

由计算结果可以看出，随着资产A与B之间的相关系数的降低，投资组合的标准差也在降低。

相关系数为1时，投资组合的标准差最大，相关系数为-1时，投资组合的标准差最小，而投资组合的期望收益率不随相关系数的变化而变化。

（b
（1）在单元格区域A17:B28中输入资产A在投资组合中的比重。

（2）在单元格B17中输入公式“=A17*$B$5+(1-A17)*$B$6”，计算投资组合的期望收益率。

（3）在单元格C17中输入公式“=SQRT（A17∧2*$C$5∧2+(1-A17) ∧2*$C%6∧2+2*A17*(1-A17)*$C$16*$C$5*$C$6）”，计算相关系数为1时投资组合的标准差。

（4）在单元格D17中输入公式“=SQRT（A17∧2*$C$5∧2+(1-A17) ∧2*$C%6∧2+2*A17*(1-A17)*$C$16*$C$5*$C$6）”，计算相关系数为0.5时投资组合的标准差。

（5）在单元格E17中输入公式“=SQRT（A17∧2*$C$5∧2+(1-A17) ∧2*$C%6∧2+2*A17*(1-A17)*$C$16*$C$5*$C$6）”，计算相关系数为0时投资组合的标准差。

（6）在单元格F17中输入公式“=SQRT（A17∧2*$C$5∧2+(1-A17) ∧2*$C%6∧2+2*A17*(1-A17)*$C$16*$C$5*$C$6）”，计算相关系数为-0.5时投资组合的标准差。

（7）在单元格G17中输入公式“=SQRT（A17∧2*$C$5∧2+(1-A17) ∧2*$C%6∧
2+2*A17*(1-A17)*$C$16*$C$5*$C$6）”，计算相关系数为-1时投资组合的标准差。

（8）选取单元格区域B17:G17，将其向下一直填充复制到单元格区域B28:G28，得到不同投资组合情况下的期望收益率和标准差。

计算结果如图2－3所示。

（9）选取单元格区域B17：G28，单击工具栏上的【图表向导】按钮，打开【图表向导-4步骤之1-图表类型】对话框，在【标准类型】中选择【XY散点图】，在【子图表类型】中选择【折线散点图】，如图4所示。

（10）单击【下一步】按钮，打开【图表向导-4步骤之2-图表源数据】对话框，如图5所示。

图4 图5
（11）由于系统默认的图表坐标以及系列名称不符合要求，故需要将系列进行重命名，并对X值和Y值进行重新设置。

方法是：选择“系列1”，在【名称】栏中输入“r=1”，在【X值（X）】栏中输入“=Sheetl!$C$17:$C$28”，在【Y值（Y）】栏中输入“=Sheetl!$B$17:$B$28”。

（12）用同样的方法对其他系列进行重新设置，最后的结果如图6所示。

图6
（13）单击【下一步】按钮，打开【图表向导-4步骤之3-图表选项】对话框，如图7所示。

在【图表标题】栏中输入“投资组合的期望收益率与标准差之间的关系曲线”，在【数据（X）轴】栏中输入“标准差”，在【数据（Y）轴】栏中输入“期望收益率”。

图7
（14）单击【下一步】按钮，打开【图表向导-4步骤之4-图表选项】对话框，不做任何输入，保持系统默认，单击【下一步】按钮，即可得到需要的图表。

（15）为了使图表更为美观、清晰，可对坐标轴的刻度、最小值、最大值进行重新设置。

依次用鼠标对准每一条曲线，单击右键，在弹出的快捷菜单中选择【数据系统格式】命令，将其中的“线形”颜色、“数据标记”的“前景色”均设置为“黑体”。

这样，最后得到的图表如图8所示。

图8。