投资组合模板

江西农业大学经济管理学院

实验报告

股票投资组合实验报告

第一步：从股票软件同花顺中导出 4 只股票2016年的相关指数数据。导出数据为：吉艾科技（JAKJ）、上海家化（SHJH）、国星光电（GXGD）、通用

航空（TYHK ）。导出结果如图 1 所示：

图 1 数据导出结果

第二步：计算各数据日收益率

公式：f x=(B3-B2)/B2 ，以AJKJ 为例，其余数据处理方法与其相同。取AJKJ

在2016 年1 月4 日的收盘价为 1 月5 日收益率基数，输入上述公式，计算出 1

月5 日的收益率为- 4.57%，其他日期的收益率计算方法与此相同，最终计算出

各股的日收益率如图 2 所示：

图2收益率

第三步：计算各股之间的年化收益率

步骤一：采用Excel 函数average(number1,number，2⋯)计算日平均收益率，计算过程如图3所示：

图3日平均收益率计算过程

步骤二：通过日平均收益率×242（2016年共242个交易日）计算年化收益

率

4 年化收益率数据

。

最

终

得

出

各

股

年

化

收

益

4 所示：

第四步： 计算各股之间的年化收益率协方差

步骤一：采用 Excel 函数 VARP(number1,number2，⋯ )计算日收益率之间的

协方差；样本方差用 varp （）函数， 协方差用 covar （）函数， 结果如图5 所示：

图5 日收益率方差计算结果

步

骤

二

：计

算年化收益率协方差（ 2016年共 242 个交易日）

。

最

终

的 图6 所示：

6 年化收益率协方差

第五步： 计算各股权重。 步骤一：采用 Excel 函数 rand 自动生成各年化收益率对应随机数， 确定各股 收益率权重系数，并通过S U M 函数对随机数进行和 步骤二：通过已知各股年化收益率与系数总 确定各股权重， 同步 骤一方式对权重 W 进行加总，1。权重计算图7 所示。

图7 各股权重计算结果

第六步：计算该组合下的方差。

利用MMULT ，TRANSPOSE 两个语法合并计算：

（MMULT(MMULT(B8:E8,B2:E5),TRANSPOSE(B8:E8))

输入定义公式后，按住Ctrl+Shift+Enter

输出结果如图8 所示

图8 方差计算结果

第七步：计算证券组合下资产收益率的期望值。

步骤一：利用SUMPRODUCT(array1，array2，array3，...)函数计算各股年

化收益率与其对应权数的乘积，得到各股在资产组合下的期望收益率，输出结果为- 9.04%，如图9 所示

图9 期望收益率计算结果

第八步：利用Excel 的模拟运算表功能随机产生包含上述证券的1000 个组合的期望收益率和方差

步骤一：选中F8 单元格，输入“1”，在菜单栏中选择“编辑/填充/序列”，将序列产生修改为“列”，步长值为“1，”终止值为“1000，”点击“确定”,如图10、11 所示：

图10模拟运算表运算过程

图11模拟运算表运算结果

第九步：绘制数据散点图

选中G7至H1008单元格，选择“插入/图表”，选中散点图，点击“下一步”，并将图表名称修改为“资产组合”，图表绘制如图12所示

图12 资产组合——收益率与方差组合图