Excel在多方案投资组合决策中的应用

运用ExcelSolver构建最优投资组合王世臻

运用Excel Solver构建最优投资组合 王世臻（20121563）黄燕宁（20121941）王爽（20125204）汪雅娴（20121336）杨瑞(20121799)潘晓玉（20123384）本文运用马科维茨投资组合优化程序来说明股票市场的分散化投资，借助Excel Solver构建最优投资组合。我们从Resset金融研究数据库中从电子信息行业选取启明星辰等40只股票2010年至2013年的月收益率以及对应的无风险收益率等数据。 来源于Resset金融研究数据库

二、模型设定 我们可以设第i 只股票的期望风险溢价为i (r )E ，第i 只股票的权重为i w ，整体的期望风险溢价为p (r )E ，标准差为p σ，夏普比率为p S ，因此我们可以得到组合的期望风险溢价为： 11224040()()()()()p i i E r w E r w E r w E r w E r =+++++L L (1) 整体的标准差为： 1 24040[(,)]11 i j i j p w w Cov r r i j σ=∑ ∑== (2) 夏普比率为： p (r ) p p E S σ= (3) 三、构建组合 我们分卖空和未卖空两种情况分别进行讨论： （一）允许进行卖空 在这种情况下，为了找出最小的方差组合，我们以(2)式为目标函数，以40 11 i i w ==∑为约束条件运用Excel solver 求解可以得到最小的标准差为0.04127，此时的风险溢价为0.03901 ，夏普比率为0.94525，同时可以得到此时的风险组合如表。 为了画出风险组合的有效边界，我们以(2)式为目标函数，通过改变(1)式的值利用Excel solver 画出下图1： 图1 有效边界与资本配置线图 选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。我们 标准差 风险溢价

投资组合Excel练习

请用Excel软件完成以下要求： 1.请利用Exhibit4的数据求出这12类资产的方差－协方差矩阵。 2.请以Exhibit5中预期收益为6.5%的那一列为例：（1）演示如何在已知资产 权重和Exhibit4数据的情况下，计算出投资组合的期望收益（本例为6.5%）和标准差（本例为7.83%）；（2）演示如何在约束条件下（本表中Constraints 部分），得到要求收益率（本例为6.5%）下的最优的组合权重（即标准差最小的投资组合）。 3.Exhibit8中的Policy组合，请去掉Inflation-Indexed和Cash后，把原来 的权重重新标准化（即剩余的10种资产的相对比例不变），重新构造一个新组合（称为RiskyP1），然后计算TIPS（即表中的Inflation-Indexed）和这个新组合RiskyP1的相关系数。 4.参考Exhibit TN-4，画出由RiskyP1和TIPS构造的“有效前沿”（注意：严 格来说并不能算是均值－方差有效前沿），然后再画出从CASH出发的上述“前沿”的切线（注意：虽然前面Exhibit4里CASH的S.D.不为0，但这里假设它为0，即无风险）。

201-053 -9- E x h i b i t 4A s s u m e d R e a l E x p e c t e d R e t u r n s , V o l a t i l i t i e s , a n d C o r r e l a t i o n s C o r r e l a t i o n s E x p e c t e d R e a l R e t u r n (%) S .D . (%) D o m e s t i c E q u i t y F o r e i g n E q u i t y E m e r g i n g M a r k e t s P r i v a t e E q u i t y A b s o l u t e R e t u r n H i g h Y i e l d C o m m o d i t i e s R e a l E s t a t e D o m e s t i c B o n d s F o r e i g n B o n d s I n f l -I n d e x e d B o n d s C a s h 1 D o m e s t i c E q u i t y 6.5 16.0 1.00 0.50 0.40 0.40 0.60 0.55 (0.05) 0.20 0.40 0.15 0.10 0.10 2 F o r e i g n E q u i t y 6.5 17.0 0.50 1.00 0.35 0.30 0.50 0.35 (0.05) 0.15 0.25 0.40 (0.05) 0.05 3 E m e r g i n g M a r k e t s 8.5 20.0 0.40 0.35 1.00 0.25 0.30 0.35 0.00 0.15 0.15 0.10 0.00 0.00 4 P r i v a t e E q u i t y 9.5 22.0 0.40 0.30 0.25 1.00 0.30 0.20 (0.10) 0.15 0.20 0.10 0.10 0.05 5 A b s o l u t e R e t u r n 5.5 12.0 0.60 0.50 0.30 0.30 1.00 0.40 0.00 0.15 0.30 0.20 0.20 0.10 6 H i g h Y i e l d 5.5 12.0 0.55 0.35 0.35 0.20 0.40 1.00 0.10 0.10 0.45 0.15 0.30 0.10 7 C o m m o d i t i e s 4.5 12.0 (0.05) (0.05) 0.00 (0.10) 0.00 0.10 1.00 0.00 (0.15) (0.10) 0.20 (0.05)8 R e a l E s t a t e 5.5 12.0 0.20 0.15 0.15 0.15 0.15 0.10 0.00 1.00 0.20 0.10 0.20 0.15 9 D o m e s t i c B o n d s 4.3 7.0 0.40 0.25 0.15 0.20 0.30 0.45 (0.15) 0.20 1.00 0.40 0.50 0.15 10 F o r e i g n B o n d s 4.3 8.0 0.15 0.40 0.10 0.10 0.20 0.15 (0.10) 0.10 0.40 1.00 0.10 0.10 11 I n f l -I n d e x e d B o n d s 4.0 3.0 0.10 (0.05) 0.00 0.10 0.20 0.30 0.20 0.20 0.50 0.10 1.00 (0.10) 12 C a s h 3.5 1.0 0.10 0.05 0.00 0.05 0.10 0.10 (0.05) 0.15 0.15 0.10 (0.10) 1.00 S o u r c e : H a r v a r d M a n a g e m e n t C o m p a n y .

实验五运用Excel规划求解进行最优投资组合地求解

实验报告 证券投资 学院名称 专业班级 提交日期 评阅人____________ 评阅分数____________

实验五：运用Excel规划求解进行最优投资组合的求解 【实验目的】 1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序； 2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合； 【实验条件】 1、个人计算机一台，预装Windows操作系统和浏览器； 2、计算机通过局域网形式接入互联网； 3、matlab或者Excel软件。 【知识准备】 理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM 实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，第四章，第五章 【实验项目内容】 请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验 A．打开“实验五组合优化.xls”，翻到“用规划求解计算最优组合”子数据表； B．调用规划求解功能进行求解。 点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。 C．

D．在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明什么？ E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。 参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83 F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？ 【实验项目步骤与结果】

计算方差 EXCEL在投资组合理论中的应用

EXCEL在投资组合理论中的应用 教学内容: 一、计算投资组合的数字特征; 二、在没有卖空限制下计算有效前沿组合 (1) 计算有效前沿; (2) 绘制资本市场线; (3) 绘制证券市场线; 三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别 四、EGP法计算前沿组合在EXCEL中的实现。 一计算期望收益率、标准差、协方差矩阵和相关系数; 1.一个简单的两资产组合的例子（表1） 假如有两只股票12个月度的价格数据:股票A和股票B,资料如下: 1.1.收益率与期望收益 1）收益率的计算 以股票A为例,计算该股票的月收益率.股票A在第t月的收益率为在第t月月末与第(t-1)月末价格之比的自然对数,计算公式为: 注意: 对数收益率是对普通收益率泰勒级数展开得到的，t期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。对数收益率的好处是可以直接相加，比如t期到t+n期的对数收益率可以由Rt+R(t+1)+R(t+2)+...得到。 (1) 这个公式采用的是连续收益率计算公式,而离散收益率计算公式为 (2) 如果在第t月末获得股利收入,记为,则收益率为 .

(3) 在考虑股利收入下,股票的离散型收益率为 . 本例中的收益率的计算采用连续收益率形式,并忽略股利收入. 具体步骤是:使用EXCEL中的LN函数计算股票的收益率.调用Ln函数的方法是:单击EXCEL工具栏下的，或者选择[插入]菜单中的[函数]命令，弹出[粘贴菜单]对话框，在[函数分类]中选择[数学与三角函数]。在[函数名]中选择[LN]函数，单击[确定]按钮即可。 2）期望收益的计算 期望收益是指持有股票的投资者在下一个时期所能获得的收益预期。单个证券的期望收益可以通过计算历史数据的样本均值来估计。 在EXCEL中可以通过[统计]中的[AVERAGE]函数实现对期望收益的计算（见表1）。具体操作步骤如下： （1）股票A每月的收益率：单击C4单元格，在编辑栏输入=LN（B4/B3），应用自动填充单元格命令即可求出各月收益率对应的C4：C15单元格区域的值。同样可求出股票B 的月收益率。 （2）股票A的月期望收益率：选择C16单元格，在编辑栏中输入=AVERAGE （C4：C15）。股票B的月期望收益率：选择E16单元格，在编辑栏中输入=AVERAGE（E4：E15）。 （3）股票A的年期望收益率：选择C17单元格，在编辑栏中输入=12*C16。同样的方法可得股票B的年期望收益率。 1.2 方差与标准差 方差与标准差刻画证券收益率变动，是风险的常用度量指标，在EXCEL中方差，样本方差，标准差，样本标准差分别用VAR（计算基于给定样本的方差），VARP（计算基于给定的样本总体的方差），STEDV，STDEVP 来表示，公式如下： （无偏估计） VAR:

计算方差EXCEL在投资组合理论中的应用

EXCEL在投资组合理论中的应用

教学内容: 一、计算投资组合的数字特征; 二、在没有卖空限制下计算有效前沿组合 (1) 计算有效前沿; (2) 绘制资本市场线; (3) 绘制证券市场线; 三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别 四、EGP法计算前沿组合在EXCEL中的实现。

一 计算期望收益率、标准差、协方差矩阵和相关系数; 1.一个简单的两资产组合的例子（表1） 假如有两只股票12个月度的价格数据:股票A 和股票B,资料如下: 月份 股票Ａ股票Ｂ025.0045.00124.8844.74224.4146.90323.5945.36426.4650.77526.8753.22627.9153.31728.6462.65829.7265.60932.9866.761036.2278.601137.2478.141237.0368.53 股票价格 1.1.收益率与期望收益 1）收益率的计算 以股票A 为例,计算该股票的月收益率.股票A 在第t 月的收益率为在第t 月 月末与第(t-1)月末价格之比的自然对数,计算公式为: 1 ln()At At At P r P -= 注意: 对数收益率是对普通收益率泰勒级数展开得到的，t 期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。对数收益率的好处是可以直接相加，比如t 期到t+n 期的对数收益率可以由Rt+R(t+1)+R(t+2)+...得到。 (1) 这个公式采用的是连续收益率计算公式,而离散收益率计算公式为 ,,1 1A t At A t P r P -= - (2) 如果在第t 月末获得股利收入,记为t Div ,则收益率为

EXCEL在投资组合理论教学中的应用

EXCEL在投资组合理论教学中的应用 李吉栋 （河北经贸大学金融学院，石家庄，050061） 摘要：投资组合理论是金融学科的一个重要理论，内容比较抽象，数学模型多，学生理解起来很困难。在投资组合理论的教学过程中，利用EXCEL的数据运算和图表功能，将抽象的理论知识直观地演示出来，不但使教学内容深入浅出，易于理解，也可以帮助学生将理论知识与投资实践结合起来，激发学生们的学习兴趣。 关键词：EXCEL；投资组合理论；规划求解 投资组合理论是金融学科的一个重要理论。该理论认为，在由若干证券构成的所有可能的投资组合中，只有部分组合是有效的，理性投资者在这些有效组合中选择最适合自己的组合。基于对证券期望收益率、标准差以及协方差的估计，我们可以找到这些有效组合，即投资组合前沿。在以往的教学实践中，学生们普遍反映这部分内容非常抽象，数学模型多，难以把这些理论和模型与投资实践联系起来，理解起来很困难。如果在教学过程中能够利用中国证券市场的真实数据，将这些数学模型演算一下，再借助于必要的图形分析，使这些抽象的理论和模型能够直观地演示出来，对学生理解教学内容会大有帮助。EXCEL是一款功能强大的电子表格数据处理软件，而且具备丰富的图表演示功能，非常适合在投资组合理论教学中使用。下面笔者将结合自己的教学实践，介绍在投资组合理论教学中如何应用EXCEL。一、运用EXCEL演示证券的相关性对组合风险的影响 投资组合理论的核心思想就是风险的分散化。投资者之所以要持有多个证券，其根本原因就在于部分风险会随着持有证券个数的增加而有所降低。影响证券组合风险分散化效果的一个重要因素就是证券之间的相关性。我们一般是通过分析两个风险证券的组合来帮助学生理解证券相关性与风险分散化之间的关系。 首先，假设市场上有两个风险证券，知道这两个证券的期望收益率、标准差和相关系数，计算出这两个证券的不同权重组合的期望收益率和标准差，画出投资组合曲线；然后改变两个证券的相关系数，比较投资组合曲线的变动情况。市场上两个风险证券的相关数据如图1所示。按照期望收益率和标准差的计算公式，计算出一系列不同权重组合的期望收益率和标准差，用EXCEL绘图功能中的散点图，画出这一系列不同权重组合期望收益率和标准差的相关关系图，如图1 所示。

实验五：运用Excel规划求解进行最优投资组合的求解

实验五：运用Excel规划求解进行最优投资组合的求解

实验报告 证券投资 学院名称 专业班级 提交日期 评阅人____________ 评阅分数____________

实验五：运用Excel规划求解进行最优投资组合的求解 【实验目的】 1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序； 2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合； 【实验条件】 1、个人计算机一台，预装Windows操作系统和浏览器； 2、计算机通过局域网形式接入互联网； 3、matlab或者Excel软件。 【知识准备】

理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM 实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，第四章，第五章 【实验项目内容】 请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验A．打开“实验五组合优化.xls”，翻到“用规 划求解计算最优组合”子数据表； B．调用规划求解功能进行求解。 点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才 能使用，如何加载见实验补充文档“EXCEL 规划求解功能的安装”。

C．

D．在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明什么？E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。 参考实验参考电子书《金融建模—使用EXCEL 和VBA》电子书第四章P83 F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？