CAPM投资组合excel模板

Excel公式和函数典型案例－多种投资的动态分析模型投资者在进行多种投资时，往往需要对各种风险资产的期望收益率、标准差，以及它们之间的相关系数进行分析，以便了解对投资组合的影响。

此时，就可以建立一个多种风险资产投资组合的动态计算分析模型。

1．练习要点●使用控件●设置控件格式●使用MMULT函数●使用MINVERSE函数●创建图表●设置图表格式2．操作步骤：（1）分别合并B3至F3和B4至F4单元格区域，并输入标题文字和“已知数据”文字。

然后，在B5至F10单元格区域，创建“已知数据”数据表，并设置其格式，效果如图13-26所示。

图13-26 创建“已知数据”数据表提示设置第3行的【行高】为28.5；第4、5行的【行高】为18.75；第6至10行的【行高】为21。

（2）选择【开发工具】选项卡，单击【控件】组中的【插入】下拉按钮，选择【表单控件】栏中的“数值调节钮”选项，并在D6单元格中绘制该控件，如图13-27所示。

创建数据表图13-27 绘制控件注 意单击Office 按钮，并单击【Excel 选项】按钮，在弹出的对话框中，启用【在功能区显示“开发工具”选项卡】复选框。

（3）右击该控件，执行【设置控件格式】命令，在弹出的对话框中，选择【控制】选项卡，并设置【单元格链接】为$D$6，如图13-28所示。

图13-28 设置控件格式提 示 在【设置控件格式】对话框中，选择【大小】选项卡，设置控件的【高度】为“0.64厘米”；【宽度】为“2.28厘米”。

（4）使用相同的方法，在D 列和F列绘制其他的“数值调节钮”控件，并设置其单元格链接均为它们所在的单元格，如图13-29所示。

图13-29 绘制其他控件提 示 另外，用户也可以复制多个“数值调节钮”控件，并更改其单元格链接。

（5）选择C6单元格，在【编辑栏】中输入“=D6/1000”公式，并按Enter 键，即可建立资产P 的期望收益率与调节按钮的关系，如图13-30所示。

价值分析之投资组合管理——资本资产定价模型CAPM在马柯威茨的基础上，资本市场理论又拓展了新的风险资产定价模型——资本资产定价模型（Capital Asset Pricing Model ,CAPM）。

CAMP模型的应用非常广，还记得我们在介绍资本成本的时候介绍过这种方法，但是没有对其基本原理进行过介绍；下面本文就主要介绍一下CAMP模型。

1 主要假设1）所有投资者都追求均值-方差最优化，也就是受益最大，风险最小。

2）所有投资者都有同质预期（指的是投资者们对证券收益率的均值(mean)、方差(variance)和协方差（covariance ）具有相同的期望值。

）；3）市场是完美的：没有套利机会，没有交易成本，没有买卖差价，资产数量无限且无限可分，所有资产都公开交易；4）没有卖空限制；5）所有投资者可以以相同的无风险利率借债；6）所有投资者的持有期是相同的；7）市场上有很多投资者，单个投资者的买卖行为不会影响到市场价格，即所有投资者都是市场价格的接受者；从上述假设来看，每一项都与现实不符，那么这样的假设的出来的结论有价值么？有，因为基本上所有的模型都是错误的，但是模型本身只要是有用的即可。

在正式介绍CAMP之前，我们先介绍一下无风险利率的概念：所谓无风险利率是指无风险资产的回报率，一般情况下为国债，而实际情况下，即使国债也会有风险，所以无风险利率是一个无限趋近的概念；资产配置线既然存在无风险利率，那么投资组合的收益肯定要比无风险利率高，否则就不成立了，投资组合也就没有必要存在了。

在上图中，CAL斜率代表的是收益与风险的比率，也可以称为盈亏比，当然，斜率越大，则越好。

在前面介绍有效边界的时候，我们的假设是投资组合内的所有资产都是有风险的，但实际上，人们都会投资一部分无风险资产。

当一个无风险资产和N个风险组合在一起的时候，资本配置线CAL与有效边界相切的点，而这个切点只有一个，也就意味着对所有投资者而言，收益-风险比都是一致的。

基于excel的最优资产组合求解作者：殷海娜来源：《时代金融》2012年第11期【摘要】在投资证券市场的决策中，收益与风险的权衡是投资决策的核心问题。

本文借助excel强大的线性规划及函数功能建立证券投资模型，进行有效集的绘制及最优组合求解。

包括风险资产与无风险资产的最优组合，以及收益或风险固定的有条件下的最优资产组合求解。

并在最后对模型进行评价。

【关键词】最优资产组合规划求解模型有效集一、引言1952年马克维茨（Markowits）提出“资产组合选择”的理论，第一次阐述了概念明确，可操作性强的选择投资组合的理论。

1964年威廉·夏普（Sharpe）則在其基础上提出了资本资产定价模型(CAPM)，指出无风险资产收益率与有效率风险资产组合收益率之间的连线代表了各种风险偏好的投资者组合。

而在实际操作中，利用excel的函数运算及规划求解功能即可完成资产组合最优解的求解，并在不同的收益、风险限定条件下确定资产的最优投资决策。

二、最优资产组合求解首先从市场上选取不同行业领域的股票共十只，截取这十只股票在2011年3月至2012年三月的日收盘价数据，利用excel平均值求值公式AVERAGE计算出其各自的日平均收益率。

以上证综指作为市场指标并计算出市场日平均收益率。

利用VAR公式求得各个资产的方差及与上证综指的协方差，由公式β=■求得各只资产的β系数。

β系数是衡量资产对市场风险贡献率的指标，其值越大说明该资产的风险水平越高。

观察各只β系数，选取β值水平不同的股票三只，记为股票1、2、3。

如可选择β1的一只。

（一）求解可行区域以0.05为单位跨度赋予三只股票权重ω1、ω2、ω3，由公式E(r)=■■■ω■r■求得在不同权重赋予下资产组合的收益率。

利用公式σ■■=■■ω■ω■σ■求得不同权重组合的方差，具体步骤如下：σ■■=(ω1ω2,…ωn)·σ■σ■…σ■σ■σ■…σ■……σ■σ■…σ■·ω■ω■…ω■首先利用矩阵原理及excel的MMULT公式计算出前两个矩阵的乘积矩阵，然后用公式SUMPRODUCT求得资产组合风险的方差，即各个资产的加权平均值。

投资组合管理资产配置与收益 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所示。

capm模型检验（Excel版,含推导,分析,数据,结论）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。

如何使用Excel进行投资组合分析和优化第一章：投资组合分析的基本概念和目的1.1 投资组合的定义和意义投资组合是指将资金分散投资于多种不同资产的方式，通过不同的资产配置来降低投资风险，提高回报率。

1.2 投资组合分析的目的投资组合分析的目的是为了找到一个最佳的资产配置方案，使得在给定的风险下，获得最大的回报。

第二章：Excel基础知识及投资组合相关函数的应用2.1 Excel基础知识回顾介绍Excel常用的基本操作和函数，如数据输入、排序、筛选等。

2.2 Excel中的投资组合相关函数介绍Excel中一些常用的投资组合分析和优化函数的使用方法，如均值、方差、协方差等。

第三章：投资组合的风险和回报的衡量3.1 风险的衡量介绍常用的风险衡量指标，如标准差、VaR、CVaR等，并用Excel计算示例。

3.2 回报的衡量介绍常用的回报衡量指标，如年化收益率、夏普比率等，并用Excel计算示例。

第四章：投资组合的有效边界和无风险资产的引入4.1 有效边界的概念介绍有效边界的概念和意义，以及使用Excel绘制有效边界的方法。

4.2 无风险资产的引入介绍无风险资产的特点和作用，并以Excel计算示例展示风险资产与无风险资产组合的投资组合优化方法。

第五章：马科维茨投资组合理论在Excel中的应用5.1 马科维茨投资组合理论的基本原理简要介绍马科维茨投资组合理论的基本原理和数学模型。

5.2 在Excel中应用马科维茨投资组合理论以Excel实例展示如何使用马科维茨投资组合理论进行投资组合分析和优化。

第六章：Excel插件工具在投资组合分析中的应用6.1 Excel插件工具的介绍介绍一些常用的Excel插件工具，如Solver和Analysis ToolPak，并解释这些工具在投资组合分析中的应用。

6.2 Excel插件工具的使用方法示例以具体的Excel计算示例，演示如何使用Excel插件工具进行投资组合分析和优化。

实验报告证券投资分析学院名称专业班级提交日期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 =+两种证券组合的预期收益率是两种证券预期收益率的加权平均，权数是投资在该证券上的资金比例。

《投资组合管理》课程实验指导用书实验项目一 组合投资最优化的构建【实验目的】1．对组合投资的优化过程能够通过EXCEL 实现2．掌握组合投资的核心思想，通过求解风险一定、收益最大化的原则利用规划求解的方法画出给定股票的组合的有效边界。

【预备知识】1．对Markowitz 的均值-方差模型的掌握，明确组合的期望收益与组合风险之间的数学关系，即：1min 211..T T T w w w s t z w μ∑⎧=⎪⎨=⎪⎩ （1） 12(,,,)T n w w w w =为组合中不同资产的权重向量，12(,,,)n z z z z =表示组合中不同资产的期望收益值向量，μ为给定的组合收益水平。

2．了解理论推导的最优权重的确定：由公式（1）构建拉格朗日乘数：()()1112T T T L w w w z w λγμ≡∑+-+-（2） 分别对w 求一阶偏导：*11011L w z w z wλγλγ--∂==∑--⇒=∑-∑∂ （3） 在式（3）的两边同时乘以1T 、T z 可得：111111111T T T T z z z zλγμλγ----⎧=∑+∑⎪⎨=∑+∑⎪⎩ （4） 定义：0111>=≡∑∑==-N i N j ij T vA 1Σ1，z Σ11-≡TB ∑∑===Nj ij N i j v z 11， 01>=≡∑∑-ij j i T v z z C z Σz ，02>-≡∆B AC可解得：,∆-=B C μλ∆-=B A μγ 代入（3），可得 +=-*1Σw 1λ z Σ1-γ∑∑==*+=N j j ij N j ij i z v vw 11γλ其中：()ij v1-Σ3．熟悉EXCEL部分数学函数Average( )：返回一个数据序列的算术平均值Var()：返回一个数据序列的方差Covar()：返回两个数据序列的方差Stdev()：返回一个数据序列的标准差Transpose()：将一个矩阵进行转置Mmult()：两个矩阵的相乘（注意第一个矩阵的列数应与第二个矩阵的行数相等）Minverse()：矩阵的逆矩阵4．熟悉规划求解的方法主要掌握如何加载项，熟悉“数据分析”中相关函数的含义及应用，掌握规划求解的方法。