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

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

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

在进行投资的过程中，可以根据投资组合中各项资产的投资比重，计算出所对应的投资组合的期望收益率，而根据不同投资组合的期望收益率，又可以计算出对应投资组合的标准差，将这些结果绘制成图形，即可得到多种风险资产构成的投资组合的关系曲线。本例将利用Excel中的MMULT函数和图表功能，制作多种风险资产的最优投资组合图表。

1．练习要点

● 协方差矩阵 ● 数组公式 ● 定义名称 ● 设置图表格式 2．操作步骤：

（1）合并B2至N2单元格区域，输入标题文字，并设置其【字体】为“微软雅黑”；【字号】为18；【填充颜色】为“橙色，强调文字颜色6，淡色40％”如图13-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-67 相关系数

（4）分别合并I3至N3、I4至N4单元格区域，输入相应的数据内容，如图13-68所示。

创建数据表

图13-68 协方差矩阵

（5）选择J6单元格，在【编辑栏】中输入“=C12*$D$5*D5”公式，并按Enter键，如图13-69所示。

输入

效果显示

图13-69 资产A与资产A之间的参数

（6）选择K6单元格，在【编辑栏】中输入“=D12*$D$6*D5”公式，并按Enter键，如图13-70所示。

输入

效果显示

图13-70 资产A和资产B之间的参数

（7）选择L6单元格，在【编辑栏】中输入“=E12*$D$7*D5”公式，并按Enter键，如图13-71所示。

输入

效果显示

图13-71 资产A与资产C之间的参数

（8）选择M6单元格，在【编辑栏】中输入“=F12*$D$8*D5”公式，并按Enter键，如图13-72所示。

输入

效果显示

图13-72 资产A和资产D之间的参数

（9）选择N6单元格，在【编辑栏】中输入“=G12*$D$9*D5”公式，并按Enter键，如图13-73所示。

输入

效果

显示

图13-73 资产A和资产E之间的参数

（10）选择J6至N6单元格区域，向下拖动该区域的填充柄，将公式复制到J7至N10单元格区域，如图13-74所示。

效果显示

图13-74 其他资产之间的参数

（11）在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-77所示。

输入

效果显示

图13-77 计算参数A

（14）选择J13单元格，在【编辑栏】中输入“=MMULT(MMULT(TRANSPOSE(C5:C9),MI NVERSE(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所示。

图13-79 计算参数C

（16）选择M13单元格，在【编辑栏】中输入“=参数B*参数C -参数A^2”公式，并按Enter 键，即可求出参数D 的值，如图13-80所示。

图13-80 计算参数D

（17）分别选择J12至K12、J13至K13、M12至N12和M13至N13单元格区域，单击【合并后居中】按钮，将其合并，如图13-

81所示。

图13-81 合并单元格 提 示 由于在计算各参数值时，使用的

是数组公式，因此必须在计算参数之后才能合并该单元格区域。

（18）选择J15单元格，在【编辑栏】中输入“=参数A/参数C ”公式，并按Enter 键。然后，设置该单元格的【数字格式】为“百分比”；【小数位数】为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(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所示。

输入

效果显示

输入

效果显示

设置

输入

效果显示