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所示。

输入
效果显示
输入
效果显示
设置
输入
效果显示
效果显示
设置
图13-85 绘图数据
（22）选择C20单元格，在【编辑栏】中输入“=SQRT(参数C/参数D*(B20-参数A/参数C)^2+1/参数C)”公式，并按Enter键，如图13-86所示。

输入
效果显示
图13-86 期望收益率为0％的标准差
（23）选择C20单元格，拖动其右下角的填充柄，将该公式复制到C21至C34单元格区域中，如图13-87所示。

效果显示
图13-87 填充公式
（24）选择B20至C34单元格区域，单击【图表】组中的“散点图”下拉按钮，选择“带平滑线和数据标记的散点图”选项，如图13-88所示。

设置
效果显示
图13-88 创建图表
（25）在【选择数据源】对话框中，单击【编辑】按钮，在弹出的【编辑数据系列】对话框中，设置【X 轴系列值】为C20至C34单元格区域；【Y 轴系列值】为B20至B34单元格区域，如图13-89所示。

图13-89 编辑数据系列
提 示 选择【设计】选项卡，单击【数据】组中的【选择数据】按钮，即可弹出【选择数据源】对话框。

（26）在【选择数据源】对话框中，单击【添加】按钮，在【编辑数据系列】对话框，设置【X 轴系列值】为D5至D9单元格区域；【Y 轴系列值】为C5至C9单元格区域，其效果如图13-90所示。

图13-90 添加数据系列
（27）将“系列2”的图表类型更改为“仅带数据标记的散点图”类型。

然后，在【设置数据系列格式】对话框中，选择【数据标记选项】选项卡，并选择【内置】单选按钮，设置【类型】为“圆形”；【大小】为7，如图13-91所示。

图13-91 设置“系列2”格式
单击
设置
设置
效果显示
效果显示
设置
提 示 在【设置数据系列格式】对话框中，选择【数据标记填充】选项卡，并选择【纯色填充】单选按钮，【颜色】为“橙色”。

（28）选择“系列1”，在【设置数据系列格式】对话框中，设置数据标记的填充颜色和线条颜色均为“紫色，强调文字颜色4，深色25％”，其效果如图13-92所示。

图13-92 设置“系列1”格式
（29）在【设置坐标轴格式】对话框中，选择【数字】选项卡，设置横坐标轴和纵坐标轴的【小数位数】均为0，如图13-93所示。

图13-93 设置坐标轴格式
（30）选择图表，并选择【格式】选项卡，单击【形状样式】组中的【形状效果】下拉按钮，选择【棱台】级联菜单中的“凸起”项，如图13-94所示。

图13-94 设置棱台格式
（31）在【设置图表区格式】对话框中，选择【阴影】选项卡，设置【颜色】为“黑色”；【透明度】为60％；【大小】为100％；【模糊】为“4磅”；【角度】为45°；【距离】为“7磅”，如图13-95所示。

效果显示
设置
效果显示
选择
效果显示
Excel 公式和函数 典型案例—多种风险资产的最优投资组合
图13-95 设置阴影格式
（32）为图表添加图表标题，然后，设置图表的填充格式为图片填充，并调整其【透明度】为35％，如图13-96所示。

图
13-96 设置填充格式
提 示
右击绘图区，执行【设置绘图区格式】命令，在【设置绘图区格式】对话框的【填充】选项卡中，选择【无填充】单选按钮。

设置
效果显示。