下载文档原格式
用EXCEL实现多个资产的投资组合优化作者：祝媛博来源：《时代经贸》2012年第17期【摘要】我们可以用EXCEL来构建多个资产的投资组合，实现收益最大化或者风险最小化，并计算达到目标收益的概率。
【关键词】投资组合；最优一、风险资产数据假设我们要构建含五个风险资产的投资组合。
根据统计以往10年的五个资产的历史数据，我们得到以下数据相关系数（Correlation）风险资产1 风险资产2 风险资产3 风险资产4 风险资产5风险资产1 1 0.51 0.49 0.27 0.47风险资产2 0.51 1 0.98 0.5 0.94风险资产3 0.49 0.98 1 0.48 0.9风险资产4 0.27 0.5 0.48 1 0.46风险资产5 0.47 0.94 0.9 0.46 1预期收益（E（r）） 0.085 0.13 0.135 0.13 0.11收益标准差（） 0.091 0.206 0.212 0.19 0.12占组合最大百分比（%） 100 40 80 30 10占组合最小百分比（%） 0 10 0 0 0二、假设为了简化计算过程，我们做了一下假设：1.根据中心极限理论，我们假设五个资产的收益分布为正态分布。
2.我们假设资产的相关系数，预期收益，收益的标准差在短期内保持不变。
后面我们会通过压力测试来检验构建的投资组合对这些条件变动的敏感程度。
三、数学模型首先，我们计算投资组合的期望收益，是每个资产的期望收益，是将要构建的投资组合中每个资产的比重。
然后计算投资组合的收益的标准差，是两个资产间的协方差。
如果用矩阵的方式来计算，会有以下等式是五个资产的收益期望值的矩阵：是单位矩阵：只要确定了五个资产的比重，我们就可以计算出投资组合的收益期望值，标准差和达到目标收益的可能性（因为收益为正态分布，可以通过NORM.DIS公式，输入目标收益、投资组合期望、方差，得到概率值）。
相反地，我们也可以用EXCEL的规划求解功能，通过设定目标收益期望，标准差或者达到目标收益的概率，算出各资产的比例。
投资组合管理资产配置与收益 Excel 表格投资组合管理是投资者根据自身风险偏好和收益目标，将投资资金分配给不同资产类别的过程。
在投资组合管理中，资产配置被认为是最重要的决策之一，它决定了投资组合中不同资产类别的权重分配。
为了更好地进行资产配置和实时跟踪投资组合的收益情况，Excel 表格成为了金融领域中常用的工具之一。
Excel 表格可以有效地帮助投资者管理投资组合并进行资产配置。
通过使用 Excel 表格，投资者可以将投资资金分配给不同的资产类别，并根据资产的预期收益、风险和相关性来计算和优化资产配置。
以下是利用 Excel 表格进行资产配置与收益管理的步骤：1. 建立资产类别：在 Excel 表格中，首先需要建立资产类别列表。
可以按照投资者的需求和偏好，将不同资产类别（如股票、债券、房地产等）列举并安排在表格中的一列中。
2. 输入投资资金：在 Excel 表格中的另一列中，输入投资者准备分配给各个资产类别的资金量。
可以根据投资者的实际情况进行输入。
3. 计算权重和比例：在 Excel 表格中，可以使用数学函数将每个资产类别的资金量转化为权重或比例。
根据投资者的需求，可以选择计算资金量占总投资资金的比例或计算资产类别的权重。
4. 输入预期收益和风险：在 Excel 表格中的另两列中，输入各个资产类别的预期收益和风险。
这些数据可以通过研究分析、历史数据或专业机构提供的信息来获取。
5. 计算资产相关性：在 Excel 表格中的另一部分，可以计算各个资产类别之间的相关性。
相关性反映了不同资产之间的联动情况，对于投资组合的风险管理和多样化非常重要。
6. 优化资产配置：利用 Excel 提供的优化函数或插件，可以根据预期收益、风险和相关性等因素，通过数学模型计算出最优的资产配置方案。
投资者可以根据自身需求和投资目标，选择最适合自己的资产配置解决方案。
7. 实时跟踪投资组合收益：利用 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所示。
2、油茶鲜果出油率7.06%，可以由剩余产能倒推需购进的茶油鲜果量，再乘于1.5元每公斤即0.15万元/吨产重购重提折旧），水利设施提10年折旧（10年后清理旧资产重建重提折旧）2、生产性生物资产、厂房、加工设备折旧进生产成本3、固定资产维护费与折旧费一样多2、第三年的劈草抚育、管理费和化肥农药要转入油茶林资产，以后按照折旧进入生产成本2、宣传广告费按照销售收入的10%，运输仓储费按照销售收入的3%3、无形资产摊销按照林地承包权每年支付的款项，及100万元4、研发费用按照销售收入的5%，差旅费按照销售收入的1%，坏账准备按照应收账款余额的5%格售出，故茶油产值不计入年度产值，仅用于倒推茶油鲜果的产值1.5元每公斤即0.15万元/吨乘于1.5元每公斤即0.15万335.00232.50382.50382.50382.50旧）#REF!#REF!#REF!#REF!#REF!25.0025.0025.0025.0025.00300.00300.00300.00300.00300.00#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!60.0060.0060.0060.0060.00300.00300.00300.00300.00300.00216.00216.00216.00216.00216.00#REF!210.00210.0060.00旧进入生产成本余额的5%255.00345.00232.50382.50382.50#REF!#REF!#REF!#REF!#REF!25.0025.0025.0025.0025.00300.00300.00300.00300.00300.00 #REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!60.0060.0060.0060.0060.00300.00300.00300.00300.00300.00 216.00216.00216.00216.00216.0035.00。
实验报告证券投资分析学院名称专业班级提交日期2013年12月5评阅人____________评阅分数____________证券实验二 证券投资组合，CAPM 检验与EXCEL 金融计算 【实验目的】1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；3、进一步理解资本资产定价模型（CAPM ）的基本思想以及在股票分析中的应用。
【实验条件】1、个人计算机一台，预装Windows 操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、统计软件或者Excel 软件。
第一部分实验内容：运用Excel 进行股票投资以及投资组合收益率与风险的计算；【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章【实验项目内容】1.单只股票收益率与风险的计算2.两资产股票收益率与风险的计算3.组合收益率样本均值与样本方差的计算4.组合投资分散风险的作用是不是十分明显？第二部分实验内容：运用Excel 进行最优投资组合的求解； 【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章 【实验项目内容】1．.打开2组合优化模型。
Xls 文档，翻到“用规划求解计算最优组合”子数据表；2．调用规划求解功能进行求解。
点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL 规划求解功能的安装”。
【实验项目原理】投资组合是指将资金配置在一种以上的证券上，获取在风险可控下最大化回报的投资方式。
1、两种证券组合的收益和风险假设有两种证券A 、B ，投资者将资金按照A X 、B X 的比例构建证券组合，则该证券组合的收益率可以表示为：P A A B BR X R X R =+，其中A X +B X =12、两种证券组合的预期收益率：()()()P A A B B E R X E R X E R =+两种证券组合的预期收益率是两种证券预期收益率的加权平均，权数是投资在该证券上的资金比例。
Excel中的投资计算及举例(一)——FV函数在Excel中某些函数在计算投资数据时经常使用，那就是投资计算函数。
投资计算函数可分为与未来值fv有关，与付款pmt有关，与现值pv有关，与复利计算有关及与期间数有关几类函数：与未来值fv有关的函数：FV、FVSCHEDULE与付款pmt有关的函数：PMT、IPMT、ISPMT、PPMT与现值pv有关的函数： PV、NPV、XNPV与复利计算有关的函数：EFFECT、NOMINAL与期间数有关的函数：NPER在以上投资计算函数中，PV、FV、NPV、PMT函数又是最常见的。
在说明这几类函数之前，我先介绍一下与这几类函数相关的参数：rate：为各期利率，可为银行年利率或年收益率等。
nper：为总投资期或贷款期，即该项投资或贷款的付款期总数。
pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。
通常 pmt 包括本金和利息，但不包括其他费用及税款。
pv：为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。
如果省略 pv，则假设其值为零，并且必须包括 pmt 参数。
fv：为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零，即一笔贷款的未来值为零。
type：数字 0 或 1，用以指定各期的付款时间是在期初还是期末。
0或缺省表示期末，1表示期初。
求某项投资的未来值-Excel函数FV的使用： FV(Future Value)为返回投资的未来值。
FV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。
FV函数的语法为：FV(rate,nper,pmt,pv,type)注意：所指定的 rate 和 nper 单位要保持一致。
例如，年利率为10%贷款3年时，按年计息则rate=10%，nper＝3。
若按月计息则rate=10%/12，nper＝3*12。
FV函数举例：1.整存整取如下图所示范例：这是一个简单的整存整取的例子，在图中绿色高亮区域你可以看到FV函数公式，其中A4，A5，A6，A7分别对应各单元格中的内容。
CAPM模型其实质是讨论风险与收益的关系,其基本的验证思路是考察是否只有股票β系数表)与其收益有关,而且这两者为线性正相关。
它是对股票收益率的事前预测，把其变成类似计量经济学归的表达式也就是CAPM模型的事后形式，本次通过EVIEWS进行回归分析验证CAPM模型在此股票上是否有。
见下式： E(Rj)-Rf=(E(Rm)-Rf)βj (1)//这是CAPM的原本模型股票名称：股票代码：Variable Coefficient Std. Error1.三一重工600031X-2.4479543.243226C-0.1942910.15955R-squared0.015579 Mean dependent varAdjusted R-squared-0.011766 S.D. dependent varS.E. of regression0.163904 Akaike infSum squared resid0.967127 Schwarz criterionLog likelihood15.82955 F-statisticDurbin-Watson stat 1.012855 Prob(F-statistic)Variable Coefficient Std. Error2.航天机电600152X-2.2681723.287982C-0.1761940.161752R-squared0.013046 Mean dependent varAdjusted R-squared-0.014369 S.D. dependent varS.E. of regression0.166166 Akaike infSum squared resid0.994004 Schwarz criterionLog likelihood15.30874 F-statisticDurbin-Watson stat 1.060726 Prob(F-statistic)3.四川路桥600039Variable Coefficient Std. ErrorX-2.139265 3.276311C-0.1773710.161178R-squared0.011704 Mean dependent varAdjusted R-squared-0.015748 S.D. dependent varS.E. of regression0.165576 Akaike infSum squared resid0.98696 Schwarz criterionLog likelihood15.44387 F-statisticDurbin-Watson stat 1.044981 Prob(F-statistic)4.凤凰光学600071Variable Coefficient Std. ErrorX-2.135465 3.263153C-0.1792890.16053R-squared0.011756 Mean dependent varS.E. of regression0.164911 Akaike infSum squared resid0.979048 Schwarz criterionLog likelihood15.59678 F-statisticDurbin-Watson stat 1.050367 Prob(F-statistic) 5.中金黄金600489Variable Coefficient Std. ErrorX-2.892332 3.228677C-0.2173140.158834R-squared0.021806 Mean dependent varAdjusted R-squared-0.005366 S.D. dependent varS.E. of regression0.163169 Akaike infSum squared resid0.95847 Schwarz criterionLog likelihood16.0004 F-statisticDurbin-Watson stat 1.029506 Prob(F-statistic) 6.方兴科技600552Variable Coefficient Std. ErrorX-2.436679 3.288756C-0.191880.16179R-squared0.01502 Mean dependent varAdjusted R-squared-0.012341 S.D. dependent varS.E. of regression0.166205 Akaike infSum squared resid0.994472 Schwarz criterionLog likelihood15.2998 F-statisticDurbin-Watson stat 1.115256 Prob(F-statistic) 7.江苏舜天600827Variable Coefficient Std. ErrorX-2.552558 3.254945C-0.1947030.160127R-squared0.016796 Mean dependent varAdjusted R-squared-0.010515 S.D. dependent varS.E. of regression0.164497 Akaike infSum squared resid0.974129 Schwarz criterionLog likelihood15.69249 F-statisticDurbin-Watson stat 1.018081 Prob(F-statistic) 8.凯乐科技600260Variable Coefficient Std. ErrorX-3.110213 3.242644C-0.2230970.159521R-squared0.024918 Mean dependent varS.E. of regression0.163875 Akaike infSum squared resid0.966781 Schwarz criterionLog likelihood15.83636 F-statisticDurbin-Watson stat 1.045083 Prob(F-statistic) 9.古越龙山600059Variable Coefficient Std. ErrorX-2.535157 3.252968C-0.1968760.160029R-squared0.016591 Mean dependent varAdjusted R-squared-0.010726 S.D. dependent varS.E. of regression0.164397 Akaike infSum squared resid0.972946 Schwarz criterionLog likelihood15.71558 F-statisticDurbin-Watson stat 1.012261 Prob(F-statistic) 10.鄂尔多斯600295Variable Coefficient Std. ErrorX-2.574677 3.241956C-0.1976850.159488R-squared0.017218 Mean dependent varAdjusted R-squared-0.010081 S.D. dependent varS.E. of regression0.16384 Akaike infSum squared resid0.96637 Schwarz criterionLog likelihood15.84443 F-statisticDurbin-Watson stat 1.035606 Prob(F-statistic)t-Statistic Prob.-0.754790.4553-1.2177450.2312 Mean dependent var-0.075549 S.D. dependent var0.162949e info criterion-0.727871 Schwarz criterion-0.641682 F-statistic0.569708 Prob(F-statistic)0.455285t-Statistic Prob.-0.6898370.4947-1.0892850.2833 Mean dependent var-0.066172 S.D. dependent var0.164985e info criterion-0.70046 Schwarz criterion-0.614271 F-statistic0.475875 Prob(F-statistic)0.49472t-Statistic Prob.-0.6529490.5179-1.1004710.2784 Mean dependent var-0.073602 S.D. dependent var0.164288e info criterion-0.707572 Schwarz criterion-0.621383 F-statistic0.426343 Prob(F-statistic)0.517937t-Statistic Prob.-0.6544170.517-1.1168560.2715 Mean dependent var-0.075704有股票的系统风险(用β系数代事前预测，把其变成类似计量经济学回分析验证CAPM模型在此股票上是否有效e info criterion-0.71562 Schwarz criterion-0.629431 F-statistic0.428262 Prob(F-statistic)0.517002t-Statistic Prob.-0.8958260.3763-1.3681830.1797 Mean dependent var-0.077016 S.D. dependent var0.162733e info criterion-0.736863 Schwarz criterion-0.650674 F-statistic0.802504 Prob(F-statistic)0.376297t-Statistic Prob.-0.7409120.4636-1.1859820.2434 Mean dependent var-0.073684 S.D. dependent var0.165189e info criterion-0.699989 Schwarz criterion-0.613801 F-statistic0.548951 Prob(F-statistic)0.463552t-Statistic Prob.-0.7842090.438-1.2159330.2319 Mean dependent var-0.070886 S.D. dependent var0.163639e info criterion-0.720657 Schwarz criterion-0.634469 F-statistic0.614984 Prob(F-statistic)0.438047t-Statistic Prob.-0.959160.3439-1.3985380.1705 Mean dependent var-0.07223e info criterion-0.72823 Schwarz criterion-0.642041 F-statistic0.919987 Prob(F-statistic)0.343876t-Statistic Prob.-0.7793360.4409-1.2302490.2266 Mean dependent var-0.073903 S.D. dependent var0.163522e info criterion-0.721873 Schwarz criterion-0.635684 F-statistic0.607365 Prob(F-statistic)0.440875t-Statistic Prob.-0.7941740.4323-1.2394980.2232 Mean dependent var-0.072795 S.D. dependent var0.163021e info criterion-0.728654 Schwarz criterion-0.642465 F-statistic0.630712 Prob(F-statistic)0.432299。
