Excel公式和函数--典型案例—多种风险资产的最优投资组合

1、Excel 公式和函数 典型案例多种风险资产的最优投 资组合在进行投资的过程中，可以根据投资组合中各项资产的投资比重，计算出所对 应的投资组合的期望收益率，而根据不同投资组合的期望收益率，又可以计算出对 应投资组合的标准差，将这些结果绘制成图形，即可得到多种风险资产构成的投资 组合的关系曲线。 本例将利用 Excel中的 MMULT 函数和图表功能， 制作多种风险资1练习要点协方差矩阵数组公式定义名称设置图表格式2操作步骤：（1）合并 B2至 N2单元格区域， 输入标题文字， 并设置其 【字体】 为“微软雅黑” ；【字 号】为 18；【填充颜色】为“橙色，强调文字颜色6，淡色 40”如图 13

2、-65 所示。图 13-65 设置标题格式（2）在 B3至 D9 单元格区域中，创建“已知数据”数据表。然后，选择C5至 D9 单元格区域，设置其【数字格式】为“百分比” ；【小数位数】为 1，如图 13-66 所示。图 13-66 设置数字格式提示设置 B3 至 D4 单元格区域的【填 充颜色】为“水绿色，强调文字 颜色 5，淡色 60”。然后，为该 数据表添加边框效果。（ 3）在 B11 至 G16 单元格区域中，输入各资产之间的相关系数数据，并设置 B11 至 B16、C11至 G11 单元格区域的【填充颜色】为“水绿色，强调文字颜色5，淡色 60”，如图 13-67 所示。图 13-6

3、7 相关系数输入相应的数据内容，如图 13-68 所示。（ 4）分别合并 I3 至 N3、I4至 N4 单元格区域，图 13-68 协方差矩阵（ 5）选择 J6单元格，在【编辑栏】中输入 图 13-69 所示。=C12*$D$5*D5 ”公式，并按 Enter 键，如图 13-69 资产 A 与资产 A 之间的参数（ 6）选择 K6 单元格，在【编辑栏】中输入 如图 13-70 所示。=D12*$D$6*D5，并按 Enter 键，图 13-70 资产 A 和资产 B 之间的参数=E12*$D$7*D5 ”公式并按 Enter 键，如（7）选择 L6 单元格，在【编辑栏】中输入 图 13-71

4、 所示。图 13-71 资产 A 与资产 C 之间的参数（ 8）选择 M6 单元格，在【编辑栏】中输入“ 如图 13-72 所示。=F12*$D$8*D5 ”公式，并按Enter 键，效果显示图 13-72 资产 A 和资产 D 之间的参数（ 9）选择 N6 单元格，在【编辑栏】中输入“ 如图 13-73 所示。=G12*$D$9*D5 ”公式，并按Enter 键，图 13-73 资产 A 和资产 E 之间的参数（ 10）选择 J6 至 N6 单元格区域，向下拖动该区域的填充柄，将公式复制到 单元格区域，如图 13-74 所示。J7 至 N10图 13-74 其他资产之间的参数数据（11）在

5、I11 至 N17 单元格区域中，创建计算参数的区域，以及“最优投资组合” 表，如图 13-75 所示。效果显示图 13-75 “最优投资组合”数据表( 12)依次选择 J12、J13、M12 和 M13 单元格，分别将其名称定义为“参数A”、“参数 B”、“参数 C”和“参数 D”，如图 13-76 所示。图 13-76 定义名称(13)选择 J12 单元格，在【编辑栏】中输入“ =MMULT(MMULT(TRANSPOSE(C5:C9),MI NVERSE(J6:N10),1;1;1;1;1)”公式，并按 Ctrl+Shift+Enter 组合键，即可计算出参数 A 的值， 如图 13-7

6、7 所示。图 13-77 计算参数 A (14)选择 J13 单元格，在【编辑栏】中输入“ =MMULT(MMULT(TRANSPOSE(C5:C9),MINVERSE(J6:N10),C5:C9”)公式，并按 Ctrl+Shift+Enter 组合键，即可计算参数 B 的值，如图 1 3-78 所示。图 13-78 计算参数 B( 15)选择 M12 单元格，在【编辑栏】中，输入“ =MMULT(MMULT(1,1,1,1,1,MINVER SE(J6:N10),1;1;1;1;1)”公式，并按 Ctrl+Shift+Enter 组合键，即可计算出参数 C 的值，如图 13-79 所示。输入

7、效果显示图 13-79 计算参数 C（ 16）选择 M13 单元格，在【编辑栏】中输入“ =参数 B*参数 C-参数 A2”公式，并 按 Enter 键，即可求出参数 D 的值，如图 13-80 所示。输入效果显示图 13-80 计算参数（17）分别选择DJ12至 K12、J13至 K13、M12 至 N12和 M13 至 N13单元格区域，单击图 13-81 合并单元格提示由于在计算各参数值时，使用的 是数组公式，因此必须在计算参 数之后才能合并该单元格区域。（ 18）选择 J15 单元格，在【编辑栏】中输入“=参数 A/ 参数 C”公式，并按 Enter 键。然后，设置该单元格的【数字格式

8、】为“百分比” ；【小数位数】为 2，如图 13-82 所示。提示用户可以通过单击【减少小数位 数】按钮，来控制数据保留的小 数位数。图 13-82 计算期望收益率（ 19）选择 M15 单元格，在【编辑栏】中输入“ =SQRT（1/参数 C）”公式，并按 Enter 键，即可计算出最优投资组合的标准差，如图 13-83 所示。输入效果显示图 13-83 计算标准差提示设置 M15 单元格的【数字格式】 为“百分比”，【小数位数】为 2(20)选择 J17至 N17 单元格区域，在【编辑栏】中，输入“=TRANSPOSE参(数 C*J15-参数 A)/ 参数 D*MMULT(MINVERSE(

9、J6:N10),C5:C9)+参( 数 B-参数 A*J15)/ 参数 D*MMULT(MINV ERSE(J6:N10),1;1;1;1;1)”公式，并按 Ctrl+Shift+Enter 组合键，如图 13-84 所示。图 13-84 计算各资产的比重（21）在 B18至C34单元格区域中，创建“绘图数据”数据表。然后，设置B20至 B34单元格区域的【数字格式】为“百分比” ；【小数位数】为 0，如图 13-85 所示。图 13-85 绘图数据=SQRT（参数 C/参数 D*（B20-参数 A/ 参数22）选择 C20 单元格，在【编辑栏】中输入图 13-86 期望收益率为 0的标准差（

10、 23）选择 C20 单元格，拖动其右下角的填充柄，将该公式复制到 区域中，如图 13-87 所示。C21 至 C34 单元格图 13-87 填充公式（ 24）选择 B20 至 C34 单元格区域，单击【图表】组中的“散点图”下拉按钮，选择 “带平滑线和数据标记的散点图”选项，如图 13-88 所示。图 13-88 创建图表（25）在【选择数据源】对话框中，单击【编辑】按钮，在弹出的【编辑数据系列】对 话框中，设置【 X轴系列值】为 C20至 C34单元格区域； 【 Y轴系列值】为 B20至 B34单元 格区域，如图 13-89 所示。图 13-89 编辑数据系列提示选择【设计】选项卡，单击【

11、数 据】组中的【选择数据】按钮， 即可弹出【选择数据源】对话框（26）在【选择数据源】对话框中，单击【添加】按钮，在【编辑数据系列】对话框， 设置【 X轴系列值】为 D5至 D9单元格区域； 【Y轴系列值】为 C5至 C9单元格区域，其效 果如图 13-90 所示。图 13-90 添加数据系列（ 27）将“系列 2”的图表类型更改为“仅带数据标记的散点图”类型。然后，在【设 置数据系列格式】对话框中，选择【数据标记选项】选项卡，并选择【内置】单选按钮，设 置【类型】为“圆形” ；【大小】为 7，如图 13-91 所示。图 13-91 设置“系列 2”格式提示在【设置数据系列格式】对话框 中，选

12、择【数据标记填充】选项 卡，并选择【纯色填充】单选按 钮，【颜色】为“橙色” 。（ 28）选择“系列 1”，在【设置数据系列格式】对话框中，设置数据标记的填充颜色 和线条颜色均为“紫色，强调文字颜色4，深色 25”，其效果如图 13-92 所示。图 13-92 设置“系列 1”格式（29）在【设置坐标轴格式】对话框中，选择【数字】选项卡，设置横坐标轴和纵坐标 轴的【小数位数】均为 0，如图 13-93 所示。图 13-93 设置坐标轴格式（30）选择图表，并选择【格式】选项卡，单击【形状样式】组中的【形状效果】下拉 按钮，选择【棱台】级联菜单中的“凸起”项，如图 13-94 所示。图 13-94 设置棱台格式（31）在【设置图表区格式】 对话框中， 选择【阴影】 选项卡， 设置【颜色】为“黑色”； 【透明度】为 60；【大小】为 100；【模糊】为“