计算方差 EXCEL在投资组合理论中的应用
如何用Excel的规划求解功能实现一个投资组合在均值-方差法下的最优化？
如何⽤Excel的规划求解功能实现⼀个投资组合在均值-⽅差法下的最优化？⽂中的计算⽅法参考了Samir Khan的“Mean-Variance Optimization with Transaction Costs”。
⼀般来讲，⼀个投资组合中各项资产的价格变动特征是不⼀样的，⽐如有的资产价格波动率很⾼，但有可能带来更⾼的回报；⽽其他的资产会在⼤盘下跌之时反⽽上涨。
在构建投资组合时通过精⼼挑选具有不同价格波动特点的资产，就可以在确保收益最⼤化的同时实现投资组合的风险最⼩化，⽽均值-⽅差法就可以实现这⼀点。
均值-⽅差法是⼀种⽐较传统的优化投资组合的做法，来源于美国经济学家、1990年诺贝尔经济学奖获得者Harry Markowitz于1950年代创⽴的基于均值-⽅差模型现代组合投资理论。
均值-⽅差模型的理论是解决投资者如何从所有可能的证券组合中选择⼀个最优组合的问题。
投资者的决策⽬标通常有两个：尽可能⾼的收益率和尽可能低的不确定性风险。
即先确⽴⼀个⽬标收益率，然后确定各项资产在投资组合中的权重，使整个投资组合的风险值即整个组合的价格波动的⽅差值最低，最终使这两个相互制约的⽬标达到最佳平衡。
本⽂的主题就是探讨如何⽤Excel的规划求解功能实现⼀个由四只股票构成的投资组合在均值-⽅差法下的最优化，即价格波动风险最低，回报率最⾼。
由于在对现有投资组合中各项资产的⽐率进⾏调整时交易成本会成为⼀个很⼤的影响组合回报率的因素，因此为贴近实际操作，⽂中的案例考虑到了交易成本，并将资产权重每变动1%的交易成本设定为0.1%；四只股票的初始权重均为25%，投资组合的⽉度预期回报率为1%。
1、按以下格式设置Excel表格2、通过雅虎财经⽹站下载美孚⽯油公司XOM、卡特彼勒公司CAT、可⼝可乐公司KO和波⾳公司BA在2018年2⽉1⽇⾄2019年2⽉1⽇这1年间的⽉度收盘价。
3、⽤LN()函数计算4只股票的⽉度回报率，（）内为⽉度收盘价所在的单元格4、⽤AVERAGE()函数计算这四只股票⽉度回报率的均值5、形成协⽅差矩阵。
使用Excel进行投资组合分析和风险管理
使用Excel进行投资组合分析和风险管理1. 引言投资组合分析和风险管理是金融领域中重要的主题之一。
在投资过程中，投资者需要选择合适的资产组合，通过分析和管理风险来提高收益和降低风险。
Excel是一种功能强大的工具，可以帮助投资者进行投资组合分析和风险管理。
2. 投资组合建立在使用Excel进行投资组合分析之前，首先需要建立一个投资组合。
投资者可以通过Excel创建一个包含各种不同资产的投资组合。
首先，列出不同的资产，并给出它们的预期收益率和风险水平。
然后，通过在Excel中创建一个投资组合工作表，将各种资产组合起来，赋予它们不同的配比。
最后，计算出整个投资组合的预期收益率和风险水平。
3. 投资组合分析使用Excel可以进行多种投资组合分析。
首先，可以通过计算投资组合的期望收益率和方差来评估投资组合的风险和回报。
通过Excel的函数，例如AVERAGE和VAR，可以轻松计算这些指标。
其次，可以使用散点图和线性回归分析来进行风险和回报之间的关联性分析。
通过Excel的数据分析工具包，可以方便地进行这些分析。
4. 风险管理在投资组合分析过程中，风险管理起着至关重要的作用。
Excel 可以帮助投资者进行风险度量和风险控制。
首先，使用Excel的函数，例如STDEV和CORREL，可以计算出资产的标准差和相关系数，从而量化资产的风险。
其次，可以使用Excel的条件格式和图表功能来进行风险可视化，以便更直观地理解和管理风险。
最后，可以使用Excel的内置的求解器工具来进行投资组合的最优化，以在给定风险水平下获得最大收益或最小风险。
5. 实例分析为了更好地理解如何使用Excel进行投资组合分析和风险管理，下面将通过一个实例来说明。
假设投资者有三类资产：股票、债券和黄金。
通过Excel的数据处理和分析函数，他们可以计算出每种资产的预期收益率和标准差，并通过线性回归分析衡量它们之间的相关性。
然后，他们可以通过Excel的条件格式和图表功能，将风险可视化，并使用Excel的求解器工具计算出最优的资产配置。
学会使用Excel进行股票和投资组合分析
学会使用Excel进行股票和投资组合分析第一章：Excel基础知识在进行股票和投资组合分析之前，了解Excel的基础知识是必不可少的。
Excel是一款功能强大的电子表格软件，可以用于数据的收集、整理和分析等多种用途。
在这一章节中，将介绍Excel的基本操作，如单元格、公式和函数的使用，以及数据的导入和导出等技巧。
1.1 单元格和工作表Excel的最基本单位是单元格，我们可以在单元格中输入文本、数字和公式等。
单元格还可以通过合并、拆分和格式化等操作进行美化和整理。
另外，Excel的工作簿可以包含多个工作表，通过使用不同的工作表可以更好地组织和分析数据。
1.2 公式和函数Excel的公式是用于进行计算的表达式，可以使用各种数学函数、逻辑函数和文本函数等。
通过灵活地应用公式和函数，我们可以快速进行数据的运算和分析。
在股票和投资组合分析中，常用的函数包括SUM、AVERAGE、MAX、MIN、IF等。
1.3 数据的导入和导出在进行股票和投资组合分析时，我们通常需要从外部数据源导入数据，或者将分析结果导出到其他软件或文件中。
Excel提供了多种导入和导出数据的方法，如从文本文件、数据库和Web导入数据，以及将数据导出为文本文件、图像和PDF等。
第二章：股票分析股票分析是投资者判断个股投资价值的关键步骤。
在这一章节中，将介绍如何使用Excel进行股票基本面分析和技术面分析，并通过实例演示具体的分析方法和技巧。
2.1 基本面分析基本面分析是通过研究公司的财务状况、经营情况和行业发展等因素，来评估股票的投资价值。
在Excel中，我们可以通过导入财务报表数据和其他相关数据，计算关键指标如市盈率、市净率和ROE等，并进行比较和分析。
2.2 技术面分析技术面分析是根据股票的历史价格和交易量等信息，来判断股票的走势和买卖时机。
在Excel中，我们可以使用图表和函数等工具，绘制股票价格和交易量的趋势图，并计算技术指标如移动平均线、相对强弱指标和MACD等，以辅助投资决策。
专题：EXCEL在投资学中的应用
例： 假如某人两年后需要一笔比较大的学习 费用支出，计划从现在起每月初存入 2000元，如果按年利2.25%，按月计息 （月利为2.25%/12），那么两年以后该 账户的存款额会是多少呢？
求投资的净现值NPV
NPV函数基于一系列现金流和固定的各期 贴现率，返回一项投资的净现值。投资 的净现值是指未来各期支出（负值）和 收入（正值）的当前值的总和。
例： 同样是小潘参赛，能否使用上面的函数 算出有效平均分？
其它统计函数 求数据集中的中位数MEDIAN； 方差VAR； ……
财务函数
财务函数可以进行一般的财务计算，如 确定贷款的支付额、投资的未来值或净 现值，以及债券或息票的价值。这些财 务函数大体上可分为四类：投资计算函 数、折旧计算函数、偿还率计算函数、 债券及其他金融函数。它们为财务分析 提供了极大的便利。
思考： 设计你自己的买卖提醒： 一只股票若连续5天上涨，在第6天出现 小实体的K线，KDJ指标出现死亡交叉， 则卖出。
求数据集的内部平均值TRIMMEAN 函数TRIMMEAN先从数据集的头部和尾 部除去一定百分比的数据点，然后再求 平均值。当希望在分析中剔除一部分数 据的计算时，可以使用此函数。比如， 我们在计算选手平均分数中常用去掉一 个最高分，去掉一个最低分，XX号选手 的最后得分，就可以使用该函数来计算。
其它函数举例 COUNTIF 语法形式为COUNTIF(range,criteria)。其中 Range为需要计算其中满足条件的单元格数目 的单元格区域。Criteria确定哪些单元格将被计 算在内的条件，其形式可以为数字、表达式或 文本。例如，条件可以表示为 32、“32”、 “>32”、“apples”。 例：统计每人有多少门课优秀。
学习利用Excel进行投资组合分析
学习利用Excel进行投资组合分析Excel是一种非常强大的工具，可以帮助投资者进行投资组合分析。
通过合理地使用Excel，投资者可以更好地评估投资组合的风险和回报，并做出更明智的投资决策。
本文将详细介绍如何学习利用Excel进行投资组合分析，并逐章讨论各个方面的内容。
第一章：Excel基础知识在开始学习如何使用Excel进行投资组合分析之前，投资者需要掌握一些Excel基础知识。
这包括使用Excel的基本功能、常用公式和函数、数据的输入和格式化等。
投资者可以通过自学教程或参加培训课程来快速掌握这些知识。
第二章：数据获取与整理在进行投资组合分析之前，投资者需要收集和整理大量的数据。
这些数据可以包括各个资产的历史价格、市场指数、经济数据等。
通过Excel的数据导入和整理功能，投资者可以方便地将数据导入到Excel中，并进行必要的清洗和整理，以便下一步的分析。
第三章：投资组合的构建投资组合是由多种资产组成的，而投资者需要在不同的资产之间进行权衡和选择。
通过Excel的数据分析工具，投资者可以对各种资产的历史表现、风险和回报进行详细的分析和比较。
同时，投资者还可以使用Excel的优化算法来寻找最佳的资产配置策略。
第四章：风险评估投资组合的风险评估是投资组合分析的重要一环。
通过Excel 的风险分析功能，投资者可以计算投资组合的风险指标，如标准差、下行风险等。
此外，还可以使用Excel的模拟方法来估计投资组合的风险价值，以便更好地控制和管理风险。
第五章：回报评估除了风险评估，投资者还需要评估投资组合的回报。
通过Excel的回报分析功能，投资者可以计算投资组合的收益率、累积收益、夏普比率等指标，并与市场指数进行比较。
这些指标可以帮助投资者了解投资组合的表现，从而做出相应的调整和决策。
第六章：投资组合优化投资组合优化是投资组合分析的重要一环。
通过Excel的优化算法，投资者可以寻找最佳的资产配置策略，以最大化回报或最小化风险。
使用Excel进行投资组合分析教程
使用Excel进行投资组合分析教程第一章：投资组合分析简介在金融领域，投资组合是指将不同的资产按照一定的比例组合在一起，以达到分散风险和实现预期收益的目标。
而投资组合分析则是评估和优化投资组合的方法。
第二章：Excel基础在进行投资组合分析之前，我们需要掌握一些基本的Excel操作技巧。
这包括如何输入数据、如何进行数据排序和筛选、如何进行公式的计算等等。
第三章：构建投资组合首先，我们需要确定投资组合中包含的资产。
然后，我们可以在Excel中创建一个表格，将每个资产的名称、代码、投资金额等信息输入其中。
接着，我们可以添加一些公式来计算每个资产的权重和总投资金额。
第四章：计算投资组合的收益与风险在投资组合分析中，我们常常关心的是收益和风险。
在Excel 中，我们可以利用一些内置的函数来计算投资组合的收益和风险。
如计算收益使用“=SUMPRODUCT(权重范围, 资产收益范围)”函数，计算风险可以使用“=SQRT(SUMPRODUCT(权重范围^2, 资产风险范围^2))”函数。
第五章：优化投资组合优化投资组合是指在给定的约束条件下，寻找最佳的投资组合。
在Excel中，我们可以使用“Solver”工具来进行投资组合的优化。
首先，我们设定一些约束条件，如投资金额的总额、各个资产的权重之和等等。
然后，我们通过调整权重的数值，使得投资组合的风险最小或者收益最大。
第六章：风险与收益的均衡在实际投资中，投资者往往会追求收益最大化但同时也要控制风险。
Excel提供了一些分析工具，可以帮助我们找到风险与收益之间的均衡点。
例如，我们可以使用“曲线拟合”功能来拟合资产收益和风险之间的关系，并根据这个关系来选择最合适的投资组合。
第七章：实例分析在本章中，我们将通过一个实例来演示如何使用Excel进行投资组合分析。
我们将选择几个常见的资产，包括股票、债券和黄金，然后利用Excel的各种功能来构建、分析和优化投资组合。
第八章：总结与展望在本章中，我们对整个教程进行总结，并展望未来可能的发展方向。
用Excel进行金融行业投资组合分析和风险管理的六种方法
用Excel进行金融行业投资组合分析和风险管理的六种方法投资组合分析和风险管理是金融行业中非常重要的任务。
为了提高投资决策的准确性和效率，许多金融从业者选择使用Excel这一强大的工具进行分析和管理。
本文将介绍用Excel进行金融行业投资组合分析和风险管理的六种方法。
一、历史回报率计算在进行投资组合分析之前，我们需要先计算每支投资工具的历史回报率。
通过Excel的内置函数和数据导入功能，可以轻松地获取各种金融工具的历史价格数据并进行计算。
将历史价格数据输入Excel中的工作表，然后使用平均函数等函数计算每支工具的历史回报率。
二、风险评估和分散投资在投资组合分析中，风险评估是一个不可或缺的环节。
通过Excel的统计函数，我们可以计算出每支投资工具的风险指标，例如标准差、方差和协方差。
然后，根据投资期望目标和风险承受能力，选择适当的投资组合，实现分散投资。
三、收益-风险分析通过将每支投资工具的收益和风险指标输入Excel中的散点图或折线图，我们可以直观地分析不同投资组合的收益-风险特征。
在图表中，我们可以清楚地看到不同投资组合的位置和变化规律，从而作出更明智的投资决策。
四、马科维茨均值-方差模型马科维茨均值-方差模型是投资组合分析中的经典方法。
通过Excel的线性规划插件或自定义函数，我们可以实现该模型的计算和优化。
通过输入各支投资工具的预期收益率、协方差矩阵和目标收益率，Excel可以自动计算出最优的投资组合权重，并给出有效前沿曲线。
五、价值暴露和敞口控制在风险管理中，价值暴露和敞口控制是非常重要的步骤。
通过Excel的逻辑函数和筛选功能，我们可以对投资组合进行敞口的检查和管控。
通过设定敞口限制和预警条件，Excel可以及时发出警报，并提供相应的调整建议，从而帮助我们更好地管理风险。
六、场景分析和压力测试场景分析和压力测试是评估投资组合鲁棒性和抗风险性的重要手段。
通过Excel的数据表和数据透视表功能，我们可以方便地建立各种场景和压力测试模型。
运用ExcelSolver构建最优投资组合王世臻
运用Excel Solver构建最优投资组合王世臻〔20121563〕黄燕宁〔20121941〕王爽〔20125204〕汪雅娴〔20121336〕杨瑞(20121799)潘晓玉〔20123384〕本文运用马科维茨投资组合优化程序来说明股票市场的分散化投资，借助Excel Solver构建最优投资组合。
我们从Resset金融研究数据库中从电子信息行业选取启明星辰等40只股票2010年至2013年的月收益率以与对应的无风险收益率等数据。
一、数据收集处理来源于Resset 金融研究数据库表1 启明星辰等40只股票数据二、 模型设定我们可以设第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 =+++++(1)整体的标准差为：124040[(,)]11i j i jp w w Cov r r i j σ=∑∑==(2) 夏普比率为： p (r )p pE S σ=(3)三、构建组合我们分卖空和未卖空两种情况分别进展讨论： 〔一〕允许进展卖空在这种情况下，为了找出最小的方差组合，我们以(2)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最小的标准差为，此时的风险溢价为 ，夏普比率为0.94525，同时可以得到此时的风险组合如表。
为了画出风险组合的有效边界，我们以(2)式为目标函数，通过改变(1)式的值利用Excel solver 画出如下图1：0.040.0450.050.0550.060.0650.070.0750.080.0850.09标准差风险溢价图1 有效边界与资本配置线图选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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:计算基于给定样本的方差。
函数 VAR 假设其参数是样本总体中的一个样本。
VARA:计算基于给定样本的方差。
不仅数字，文本值和逻辑值（如 TRUE 和 FALSE）也将计算在内.函数 VARA 假设参数为总体的一个样本。
如果数据代表的是样本总体，则必须使用函数 VARPA 来计算方差。
VARP:计算基于整个样本总体的方差。
函数 VARP 假设其参数为样本总体。
如果数据只是代表样本总体中的一个样本，使用函数 VAR 计算方差。
计算基于整个样本总体的方差。
函数 VARP 假设其参数为样本总体。
如果数据只是代表样本总体中的一个样本，使用函数 VAR 计算方差。
沿用上面求出的12个月的收益率，通过EXCEL中[工具栏]下的[]/[统计]中的VARP函数和[]/[统计]中的STDEVP函数，计算收益率的方差、标准差。
1.3协方差协方差是度量两种风险资产收益之间线性关联程度的统计指针。
正的协方差表示资产收益同向变动；负的协方差表示它们反向变动。
可以通过EXCEL工具栏中[]/[统计]/[COVAR]直接求协方差，具体步骤如下：（1）单击一空白单元格，选择[]/[统计]/[COVAR]命令，出现[COVAR]函数对话框。
（2）COVAR函数对话框中，[Array1]选择相应单元格区域，[Array2]选择相应单元格区域。
（3）完成后单击确定。
1.4 相关系数相关系数刻画两个随机变量的线性关联程度。
有两种计算方法：（1）根据定义式计算。
（2）可以通过EXCEL工具栏中[]/[统计]/[CORREL]直接求得。
表12．投资组合期望收益和方差的计算，及标准差—期望收益曲线的绘制（表2）。
上一节介绍了单只股票的期望收益，标准差和股票间协方差等数字特征的计算过程，本节介绍两个证券构成投资组合的数字特征的计算。
沿用上节的例子，构造一个由股票A和股票B各占50%的投资组合=(50%,50%),式中。
投资组合p的期望收益是：方差是：根据上面的公式运用EXCEL可以求出该投资组合p的期望收益和方差。
任意改变投资权重，运用EXCEL中的[模拟运算表]功能可以算出两种股票任意投资组合的期望收益和方差。
具体操作步骤如下：（1）建立工作表，输入的一组设定值和模拟运算表的样板，本例中是在=50%的情况下求得投资组合标准差和期望收益。
单击J2单元格，在编辑栏中输入=SQRT（F19），单击K2 单元格，在编辑栏中输入=AVERAGE(F5:F16),或者输入=F18。
预留空白单元格区域J3:K19以备填写计算结果。
（2）选定需计算的单元格区域I2:K19，以反白显示。
（3）单击[数据]菜单中的[模拟运算表]选项。
由于工作表中的“组合的标准差”和“组合的期望收益”各成一列，故在屏幕弹出的对话框中的[输入引用列的单元格]中输入$C$1，如图。
（4）单击[确定]按钮，在空白区域将自动填入不同投资比例下组合的标准差和期望收益。
计算结果如图。
根据获得的数据，使用EXCEL的图表功能可以绘制标准差—收益曲线。
具体操作步骤如下：（1）选定作图需要的数据，在本例中为J3:K19区域。
（2）单击[插入]菜单下的[图表]选项，或直接单击工具栏上的[图表向导]按钮。
屏幕弹出[图表向导—4步骤之1—图表类型]的对话框，选择[标准类型]/[xy散点图]/[无数据点平滑线散点图]。
（3）单击[下一步]按钮，进入[图表向导—4步骤之2—图表源数据]对话框。
因为第一步中已经选定了所需数据，因此一般不需要改动步骤二中的任何设置。
（4）单击[下一步]按钮，进入[图表向导—4步骤之3—图表选项]对话框。
在这步中可以修改所绘图表，如在[标题]标签中可以在绘制的曲线图上添加图表，x轴y轴标题，在[网格线]标签中增加或删除网格线等。
设置完毕后单击[下一步]按钮。
（5）在[图表向导—4步骤之4—图表位置]中选择图表需要嵌入的位置，单击[完成]按钮结束操作，即可在指定位置插入绘成的图表。
（6）可以对图表进行修改。
3 多个风险资产投资组合的期望收益和方差的计算推广到多个风险资产的投资组合的情形，计算投资组合的数字特征，并得出投资组合标准差和收益之间的关系。
3．1 运用EXCEL进行矩阵运算需要用到的函数：MDETERM（数组）：返回数组所代表的矩阵行列式的值；MINVERSE（数组）：返回数组所代表的矩阵的逆；MMULT（数组1，数组2）：返回两数组矩阵的乘积；TRANSPOSE（数组）：返回数组矩阵的转置矩阵。
1）矩阵的转置计算步骤：（1）输入矩阵A（3*4阶矩阵）中的数值。
（2）选择结果矩阵区域为，以反白显示。
（3）单击EXCEL工具栏中[]/[查找与引用]/[TRANSPOSE]函数，在屏幕弹出的对话框中，选择[Array]参数为B22：K33。
（4）用[Ctrl+Shift+Enter]组合键完成键入。
2）矩阵的乘积。
3）矩阵的逆。
3.2 计算方差—协方差的几种方法（本部分Excel操作放置在文档中）1) 用超额收益矩阵Excel函数：求平均值的函数AVERAGE，求矩阵的转置矩阵的函数TRANSPOSE，求矩阵的乘积的函数MMULT。
具体操作步骤为：（1）计算每个资产的超额收益率：根据公式，使用AVERAGE函数计算出股票AMR的收益率。
选择B14单元格，在编辑栏中输入=AVERAGE(B4：B13）,应用自动填充单元格命令，可求出其它5只股票的期望收益率，计算结果如图所示。
（2）计算超额收益矩阵：用每只股票各期的收益率减去该只股票的期望收益率。
选择C17单元格，在编辑栏中输入=B4-＄B＄14，回车后得到股票的超额收益，应用自动填充单元格命令可求出股票AMR其它年份的超额收益，同样的方法可求出其它5只股票在各年份的超额收益。
计算结果如图所示。
（3）使用数组函数TRANSPOSE计算超额收益转置矩阵：选择区域B16：G25，单击EXCEL插入栏下[函数]，出现“插入函数”对话框，选择类别“全部”，从选择函数栏下选择“TRANSPOSE”，按“确定”后出现“函数参数”对话框，在array栏里填入$B$28：$K$23,按确定即可。
（4）计算方差—协方差矩阵：使用数组函数MMULT计算和A的乘积，再除以期数M（本例中M=10），即可求得方差—协方差矩阵。
2)用OFFSET函数计算方差—协方差矩阵OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新引用的函数，返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。
OFFSET函数的参数依次为引用区域，下偏移行数，右偏移列数，返回区域行数和返回区域列数。
如果省略后面的两个参数，则假设其高度或宽度与应用区域相同。
沿用上例中的数据，用OFFSET函数计算方差—协方差矩阵。
为方便引用参数，在计算前先将偏移量（0，1，2，3）输入到方差协方差矩阵中。
具体操作步骤如下：（1）选择单元格B16.（2）在编辑栏中输=COVAR(OFFSET($B$4:$B$13,0,B$15),OFFSET($B$4:$B$13,0,$A16),在公式单元格中出现股票A的方差值。
（3）使用EXCEL自动填充单元格命令求出单元格区域B16：G21的值，即求得方差协方差矩阵。
3)用单指数（SIM）模型计算方差—协方差单指数模型建立的前提是假设每种资产的随机收益率与市场随机收益率（市场指数）之间存在着线形回归关系。
，其中分别是第i个资产和市场的随机收益率，是回归参数，是残差，满足可得任何资产的期望收益和资产间协方差分别为：具体步骤如下：（1）计算市场指数的方差：选择H13单元格，在编辑栏中输入=VARP（H2：H11）（2）股票A的系数：选择B12单元格，在编辑栏输入=SLOPE(B2:B11,$H$2:$H$11)，回车后即出现股票A的系数。
应用自动填充功能得到其他股票的系数.（3）将各公司的值列于待求的方差协方差矩阵的左边缘。