②在表格中输入无风险资产的投资比重，并逐步递减。由于有无卖空限制，所以风险资产的投资比重依次递增，两者之和为1。在组合的期望投资收益率单元格输入公式，本例中为A8*0.0414+B8*0.152。同理得到组合的标准差，当无风险资产与风险资产组合时组合的标准差公式为σp=|θσ|，本例为B8*0.3662。

③画出资本配置线。在excel菜单中点击“插入”、“图表”，选择XY散点图，平滑线散点图。点击下一步，在图表源数据对话框中修改数据区域，X轴选择标准差数据D8:D28，Y轴选择期望收益率数据C8:C28。点击下一步，选择图表保存位置，得到了资本配置线。

2.多种证券组合的最优组合

如果只有两种风险证券组合在一起，组合的期望收益率和标准差可以用公式求出，并得到相应的可行集曲线，但是，当组合的证券超过两种时，必须要更复杂的计算工具。本实验选取了其中的一种，采取规划求解这一工具来达到实验目的。

(1)基础数据的收集。实验中试图计算多种股票组合在一起的时候的可行集，因此，还是要按照实验一的方法获得四种股票的年度收益率，期望收益率和标准差。选取四支股票，除了要计算每支股票的期望收益率和标准差，还要计算他们之间的协方差，这里运用COVAR这个函数，计算方正科技和邯郸钢铁的协方差就可以在单元格输入COVAR(C3:C12,F3:F12)，同理计算出其他协方差，就可以得到四支股票的协方差阵。

(2)四种股票最优组合的计算。

①规划求解的安装。在excel菜单中点击“工具”、“加载宏”，出现加载宏的对话框，在对话框中选择规划求解，然后“确定”，这时规划求解已经成功安装。②在excel表格中输入已知数据。

③建立运算区域。把期望收益率数据填入到相关表格，在单元格中预留最优投资比重、投资组合收益率、投资组合方差、标准差等。预设最优投资比重为1、0、0、0，即全部投资于邯郸钢铁这支股票上运用矩阵运算的方法计算出组合方差。并对组合方差开方。

这样我们就建立了一个运算区，建立了各单元格数据之间的关系。一个单元格数据的变动就会引起其他数据做出相应变动。

④通过规划求解求出最优解。在excel里建立约束条件区域，把相应的约束条件列出，规划求解的原理就在于电脑自动对符合条件的解进行筛选，得到最优解，因此，必须准确设定筛选条件。在这个约束条件区，投资的比重相加应该等于1，在相应单元格输入=SUM()。如果是无卖空情况，每个股票的投资比重都是>=0的，当人为设定一个目标收益率，电脑就会自动的计算符合条件的标准差最小的解，这也就是所要找的最优解。不断的变换目标收益率就得到了很多组最优解就是要找的有效前沿。

点击工具菜单，就会在其中找到规划求解这一选项，点击打开规划求解对话框。在对话框中设置约束条件，最优解就会自动输出到相应运算区。假设设置某一目标单元格选择“最小值”。约束条件在无卖空时应该有三个，一个是投资比重都应该>=0，投资比重之和应该等于1，然后输入0.2，即目标收益率先预设20%。目标项、可变项和约束条件都输入完毕就可以开始计算了，点击“求解”，电脑会自动运算出结果，点击保存，就会发现在原来的计算区数据已经更改。

在这个计算结果中，得到四种股票组合在一起，目标收益是20%的时候，组合标准差最小的解，这时候得到的解就是四个投资比重，投资比重分别为0.36、0.63、0.1、0，这就是找到的最优的组合。

⑤建立数据区。前边得到的最优组合只是有效前沿的一个点，要得到有效前沿的其他点，就必须不断的变换目标收益率，得到不同的最优解，最终画出有效前沿。为了得到这样一系列数据，要建立数据区来保存不断计算求出的结果。把组合收益为0.2，标准差0.33写入到数据区。接下来继续运用规划求解工具，把约束条件中的目标收益率20%变为其他数据，比如25%，求解就会得到另外一个最优解，依次不断变化该单元格，就会得到需要的一些组合，不变计算的结果就是我们最终得到了完整的数据，

(3)既定目标收益率最优投资比重的求解。假如要投资于四支股票上，要求投资的收益率为28%，那么应该怎么分配风险最小呢?前面的规划求解实际上就可以解决这个问题。只要在约束条件中添加0.28，即当收益率要求28%时，最优的投资比重应该是0、0.79、0.21、0。有卖空的时候也是如此计算，最终得到结果。

参考文献

[1] 兹维博迪.投资学(第6版)[M].机械工业出版社.

[2]孙家瑜.证券投资学实验教学改革探讨[J].产业与科技论坛,2008(6) .

[3]刘迎春.证券投资学课程实盘案例辅助教学的探索[J].教育与职业,2007(6)

小编为您准备的浅谈《证券投资学》课程实验内容设计初探，希望可以帮到您！

相关推荐：

企业资产证券化融资相关财务风险分析