实验五运用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 作为一款强大的电子表格软件，为投资者提供了便捷且实用的工具，帮助他们进行投资组合的分析与优化。

接下来，让我们深入探讨如何利用 Excel 来实现这一重要的任务。

首先，我们需要明确投资组合的概念。

投资组合简单来说，就是投资者将资金分配到不同的资产类别（如股票、债券、基金、房地产等）中，以达到分散风险和提高收益的目的。

而分析和优化投资组合的目的，就是找到最适合自己的资产配置比例，使得在可接受的风险水平下，获得最大的收益。

在 Excel 中，我们可以通过输入和整理投资数据来开始我们的分析之旅。

这些数据包括各种投资产品的历史价格、收益率、波动率等。

为了获取这些数据，我们可以从金融网站、数据库或者相关的财经报告中收集。

假设我们有以下几种投资产品：股票 A、股票 B、债券 C 和基金 D。

我们将它们的历史价格数据输入到 Excel 表格中，然后通过简单的函数计算，就可以得出它们的平均收益率和波动率。

收益率的计算可以使用“平均函数（AVERAGE）”，而波动率则可以通过计算收益率的标准差来得到，在 Excel 中可以使用“STDEV 函数”。

有了这些基础数据，我们就可以构建投资组合了。

在 Excel 中，我们可以通过假设不同的资产配置比例，来计算组合的预期收益率和风险。

例如，我们假设股票 A 占投资组合的 30%，股票 B 占 20%，债券C 占 30%，基金D 占 20%。

然后，我们使用“SUMPRODUCT 函数”来计算组合的预期收益率。

这个函数可以将每种资产的收益率乘以其在组合中的权重，然后将结果相加。

对于组合的风险（波动率），由于投资组合中不同资产之间的相关性会影响整体风险，所以计算会相对复杂一些。

但在 Excel 中，我们可以通过使用“协方差函数（COVAR）”和“方差函数（VAR）”来进行计算。

学会使用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进行最优值规划在生产和生活中，有时会遇到需要最优值规划分析的事情。

例如装修房子时买多少桶油漆合适，商品打几折既吸引顾客又能获得尽可能大的利润等等。

用Excel来解决此类问题，可以很快地得到准确方案。

在Excel中有一个增益工具——规划求解，它能够自动计算出Excel工作表中某些单元格数值达到最优时的解决方案，而且能够自动生成一些有价值的分析报表。

下面就以计算某公司产品利润的最大化为例，来看看这一切是如何实现的。

这家公司的基本生产情况是：生产A、B两种产品，其中每生产A产品1kg需要耗用原材料40kg，耗用工时30小时，单位利润为137元/kg；每生产B产品1kg需要耗用原材料39kg，耗用工时33小时，单位利润为136元/kg，按照公司原料采购计划，每月原料供应量为9000kg，工时为7000小时。

根据以上条件，就可以运用规划求解，计算出该公司在一个月内可以实现的最大利润额以及相应的各种产品生产量最佳组合。

一、构建模型启动Excel，新建一个表格，在其中输入产品名称、单位耗用原料、单位耗时时间、单位利润、计划产量，另外在其下面输入月度原料配额、月度时间配额、原料总用量、总生产时间、总利润等项目（图1）。

然后在这个工作表中，将前面已知的生产相关数据添加进去，如单位耗用原料量、单位耗用时间、单位利润、月度原料限额、月度时间限额等，同时还必须输入相应公式以确定在一定的计划产量下，预计的原料总用量、总生产时间以及总利润。

图1由于原料用量＝计划产量×单位耗用原料量，而原料总用量就等于A、B产品二者的原料用量之和，在此工作表中即：原料总用量＝D4×G4+D5×G5，而总生产时间＝E4×G4+E5×G5，总利润＝F4×G4+F5×G5。

这里可以使用数组乘积函数SUMPRODUCT来快速完成所求积之和，在D10单元格内输入公式“=SUMPRODUCT(D4:D5,G4:G5)”即可（图2），采用同样的方法，在D11、D12单元格内分别输入：=SUMPRODUCT(E4:E5,G4:G5)，=SUMPRODUCT(F4:F5,G4:G5)，用来计算总生产时间以及总利润。

运用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 =+++++(1)整体的标准差为：124040[(,)]11i j i j p w w Cov r r i j σ=∑∑==(2) 夏普比率为： p (r )p pE S σ= (3)三、构建组合我们分卖空和未卖空两种情况分别进行讨论： （一）允许进行卖空在这种情况下，为了找出最小的方差组合，我们以(2)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最小的标准差为0.04127，此时的风险溢价为0.03901 ，夏普比率为0.94525，同时可以得到此时的风险组合如表。

为了画出风险组合的有效边界，我们以(2)式为目标函数，通过改变(1)式的值利用Excel solver 画出下图1：图1 有效边界与资本配置线图选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。

我们标准差风险溢价以(3)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最优风险组合的标准差为0.0446，此时的风险溢价为0.0477 ，夏普比率为1.069507，得到图1。

用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里的规划求解在Microsoft Excel 中，"规划求解"（在英文版本中称为"Solver"）是一个强大的工具，允许你为一组约束条件下的目标单元格找到最优解。

你可以使用规划求解来进行如线性规划、非线性规划和整数规划等复杂的优化任务。

以下是如何在Excel 中使用规划求解的基本步骤：1. 启用规划求解插件:打开Excel，点击“文件”或“File”。

选择“选项”或“Options”。

在“Excel 选项”对话框中，选择“加载项”或“Add-Ins”。

在底部的管理下拉框中选择“Excel 加载项”或“Excel Add-ins”，然后点击“转到”或“Go…”。

勾选“规划求解”或“Solver Add-in”然后点击“确定”或“OK”。

2. 设置和运行规划求解:打开你要使用的工作表。

点击“数据”或“Data”选项卡。

在“分析”组中，你会看到“规划求解”或“Solver”按钮。

点击“规划求解”或“Solver”，打开“规划求解参数”对话框。

在“设置目标”或“Set Objective”字段中，选择你希望优化的单元格。

选择目标是“最大化”、“最小化”或“值为”。

在“调整的单元格”或“By Changing Variable Cells”字段中，选择需要调整的单元格。

点击“添加”或“Add”按钮来定义约束条件。

一旦所有约束都已定义，点击“求解”或“Solve”。

3. 查看结果:如果找到了一个解，规划求解将提供一个报告，描述目标单元格的最优值以及如何达到该值的输入值。

你可以选择接受这个解或继续探索其他可能的解。

注意：规划求解不总是能找到解，尤其是在非线性和整数约束的情况下。

确保理解你的问题的数学性质，以及它与所使用的求解方法之间的关系。

这是使用规划求解的基本步骤，你可能需要根据具体任务进行适当的调整。

实验报告证券投资学院名称专业班级提交日期评阅人____________评阅分数____________实验五：运用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》电子书第四章P83F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？【实验项目步骤与结果】A.B．使用规划求解C．投资比例为负值说明该证券风险远远大于其收益率，已经不适合投资。

F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？通过可卖空和不可卖空有效前沿图的对比可以看到，在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高，并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。

运用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 有效边界与资本配置线图选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。

Excel 使用规划求解单变量求解只能计算出某一个特定值，当要预测的问题含有多个变量或有一定取值范围时，应使用Excel提供的规划求解功能，来确定目标单元格的最优值。

“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。

其中，财务管理中涉及到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归及非线性回归等等，均可用到规划求解。

1．安装规划求解加载项规划求解是一个加载宏的程序，在使用前应先确定该程序已经安装到计算机上。

如果还没有安装，用户可以单击Office按钮，并单击【Excel选项】按钮，在弹出的对话框中，选择【加载项】选项卡。

然后在【加载项】栏中选择【规划求解加载项】项，并单击【转到】按钮，如图8-8所示。

选择单击图8-Excel 设置加载项在弹出的【加载宏】对话框中，启用【规划求解加载项】复选框，单击【确定】按钮，即可安装。

单击选择9-9 加载规划求解项2．使用规划求解规划求解是一组命令的组成部分，也可以称为假设分析。

假设分析的过程是通过更改单元格中的值来查看这些更改对工作表中公式结果的影响。

例如，更改分期支付表中的利率可以调整支付金额。

规划求解的主要功能如下：●可以求出工作表上某个单元格（称为目标单元格）中公式的最优值。

●规划求解将对直接或间接与目标单元格中的公式相关的一组单元格进行处理。

●将调整所指定的变动单元格（称为可变单元格）中的值，从目标单元格公式中求得所指定的结果。

●可以应用约束条件来限制“规划求解”在模型中使用的值，而且约束条件可以引用并影响目标单元格公式的其他单元格。

例如，企业在某月份生产甲、乙两种产品，其有关资料如图8-10所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润。

选择【数据】选项卡，单击【分析】组中的【规划求解】按钮，弹出【规划求解参数】对话框。

然后在【设置目标单元格】文本框中，输入“$D$6”单元格；在【可变单元格】文本框中，输入“$C$8,$C$9”单元格，如图8-11所示。

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所示。

excel在投资管理中的应用心得Excel在投资管理中的应用心得Excel作为一款常见的电子表格软件，在投资管理中有着广泛的应用。

本文将从投资组合管理、风险控制和投资决策等方面，探讨Excel 在投资管理中的应用心得。

一、投资组合管理投资组合管理是投资管理中的重要环节，也是投资者追求最大收益和最小风险的关键。

通过Excel，我们可以轻松地建立投资组合模型，实现资产配置、风险分散等目标。

具体而言，我们可以通过以下几个步骤完成投资组合管理：1.建立投资组合模型我们需要建立投资组合模型，该模型包括投资标的、投资金额、预期收益率、预期风险等信息。

其中，投资标的可以是股票、债券、基金等多种资产；投资金额则根据投资者的实际情况进行设定；预期收益率和预期风险可以通过历史数据、分析师预测等方式进行估算。

建立投资组合模型的过程中，我们可以使用Excel的数据透视表、图表等功能，对数据进行可视化呈现，方便投资者进行分析和决策。

2.优化投资组合在建立投资组合模型后，我们需要对投资组合进行优化，以达到最大化收益和最小化风险的目标。

优化投资组合的过程中，我们可以使用Excel的线性规划、求解器等工具，对投资组合进行优化计算。

通过调整投资比例、选择不同的投资标的等方式，可以使投资者在风险可控的前提下，获得更高的收益。

二、风险控制风险控制是投资管理中的重要环节，也是投资者保值增值的重要手段。

通过Excel，我们可以进行风险评估、风险控制、风险管理等操作，帮助投资者掌控风险，规避风险。

1.风险评估风险评估是投资管理中的基础工作，通过对不同标的的风险进行评估，可以帮助投资者制定出合理的投资策略。

在Excel中，我们可以使用各种函数和工具，如VAR、STDEV、CORREL等，对标的的风险进行计算和分析。

通过对风险的评估，投资者可以更加理性地进行资产配置和投资决策。

2.风险控制在投资过程中，风险控制是至关重要的。

通过Excel，我们可以建立风险控制模型，对投资组合的风险进行监测和管理。

excel 投资组合的回测Excel投资组合的回测投资组合的回测是投资者常用的一种方法，通过历史数据对投资组合的表现进行模拟和评估。

在Excel中，我们可以利用各种工具和函数来进行投资组合的回测分析，从而为投资决策提供参考和依据。

我们需要准备投资组合的相关数据。

这包括各种资产的历史价格数据、收益率数据以及权重分配等信息。

我们可以通过一些网站或者专业的金融数据提供商获取这些数据并导入到Excel中。

接下来，我们可以利用Excel中的函数和工具进行投资组合的回测分析。

首先，我们可以使用“=SUMPRODUCT”函数来计算投资组合的总收益率。

这个函数可以根据各个资产的收益率和权重来计算投资组合的收益率。

通过调整权重的分配，我们可以模拟不同的投资组合，并比较它们的收益率。

除了总收益率，我们还可以通过Excel的函数来计算投资组合的波动率、夏普比率、最大回撤等指标。

这些指标可以帮助我们评估投资组合的风险和收益水平。

例如，我们可以使用“=STDEV.P”函数来计算投资组合的年化波动率，使用“=AVERAGE”函数来计算投资组合的年化收益率，然后用夏普比率公式来计算夏普比率。

在进行回测分析时，我们还可以利用Excel的数据透视表功能来对投资组合的表现进行更详细的分析。

通过透视表，我们可以按照不同的时间段、资产类别等维度来查看投资组合的收益情况。

这可以帮助我们发现不同时间段和不同资产类别的表现差异，从而更好地调整投资组合的结构和权重。

在回测分析完成后，我们可以利用Excel的图表功能来可视化投资组合的表现。

通过绘制折线图、柱状图等图表，我们可以直观地观察投资组合的收益变化和波动情况。

这可以帮助我们更好地理解投资组合的表现，并作出相应的调整和决策。

需要注意的是，在进行投资组合的回测分析时，我们应该遵循一些原则和方法。

首先，我们需要选择合适的历史数据来进行回测，尽量使用长期和全面的数据。

其次，我们需要合理选择投资组合的权重分配，避免过度集中或过于分散。

如何用EXCEL的规划求解功能优化投资组合的阿尔法值（最小二乘估计法）？文中的计算方法参考了Agnes Paul的“MARKET RISK METRICS –JENSEN’S ALPHA”詹森阿尔法作为一种投资风险衡量指标，衡量的是一项资产或一个投资组合相对于所参考的绩效指标（如标准普尔500指数）的回报表现。

如果阿尔法值等于零，就意味着投资组合的回报率并没有跑赢所参考的业绩指数，而是与大盘涨跌幅一致。

阿尔法值如果是正的意味着投资组合回报率的涨幅高于业绩参考指数，反之，则意味着投资组合回报率的涨幅低于业绩参考指数。

为确定阿尔法的值需要借助回归分析的方法，尤其是最小二乘估计法。

通过最小二乘估计法可以求得资产的实际回报率与预期回报率之间差值的平方的和的最小值。

最小二乘估计法，又称最小平方法，是一种数学优化技术，通过最小化误差的平方和寻找数据的最佳函数匹配。

利用最小二乘估计法可以简便地求得未知的数据，并使得这些求得的数据与实际数据之间误差的平方和为最小。

公式如下：其中，RIt = 资产I在t日的每日回报率Rf =每日的无风险回报率，报价的时候一般是报年化的无风险回报率，为了将年化的无风险回报率折算成每日的无风险回报率，需要借助以下公式(假设一年有252个交易日):每日的无风险回报率=(1+ 年化无风险回报率)1/252-1RMt = 指数M在t日的每日回报率βI=资产I的回报率相对于指数M走势的贝塔值αI= 资产在t日的每日超额回报率I年化的αI= 资产I的每日回报率超过指数M每日涨跌幅的部分的年化值，也就是詹森阿尔法下面用EXCEL的规划求解功能演绎如何优化投资组合的阿尔法值。

1、在雅虎财经网站上下载标准普尔500指数(^GSPC)，卡特彼勒CAT和宝洁公司PG在2019年1月份的每日收盘价，将经调整后的收盘价整理如下：2、用公式LN(当前收盘价/前收盘价)计算股票和指数的每日回报率3、分别计算卡特彼勒和宝洁公司股价回报率与标准普尔500指数走势之间的贝塔值。

实验报告证券投资学院名称专业班级提交日期评阅人 ______________评阅分数 ______________实验五：运用Excel规划求解进行最优投资组合的求解【实验目的】1理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序;2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；【实验条件】1个人计算机一台，预装Windows操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、matlab 或者Excel 软件。

【知识准备】理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM实验参考资料：《金融建模一使用EXCEL 和VBA》电子书第三章，第四章，第五章【实验项目内容】请打开参考《金融建模一使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验A .打开“实验五组合优化∙xls”，翻到“用规划求解计算最优组合”子数据表；B •调用规划求解功能进行求解。

点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。

C∙（⅛中总上盟皐0忡空鼻阳皐住∣∙. I：∙nr ■■H QT kιd⅞ι⅞ P⅛B⅛L0J⅛M Fdimult!- Di财R⅛vi⅛wChPbeMrd Mgrwneni:A B C D E F H1J K L O I 131⅛A4∣⅛fi⅛rtS中世还祥中孟董令三一甫范梆5平均回抿C-JClα⅛Jβ.7≥5Q.367 3.A⅛'0.3⅛D6⅛*≠0.3M0⅛450駆0.7Gfi33S⅛ C.656S K>3T≡⅛T51OPΛ9.⅛⅛÷8il甲*证漲中≤Λ⅛三Lj E工尿蛋LD融帳行AlM0-1?S Odlfi CiJSl 3.1±!Il Ii mlE# D.17Iβ.32?0.23?0.1⅛7, C.27312D.110gj2.? C.E^E∣D.112 C.22313三1运工 D.153□ 2J7 C.11Ξ D.49E 3 ZM C.1E3U C.1H50 357 C.1SE D.23B 3.M5- C.2D7L5 D.12-⅞0 273 D.2M D.1E33JΠ7 C.43ΠLlS EkKKl1?^M-D⅛C>S- S Et&r≠UALUE∣Q 5^0』0.4555≠Λ M I19F sVAlUEI D≡∣7⅛2C ≡=55B3盗产财和帕血报］用规划車解订a有故组台，可夷空言⅞⅛边畀J可卖空有册CRMd)∣,■ ■ ■ •誰13-Font三h½cUrttyWArntangi Eome■■商k< >E⅝r⅞erv1 his b«en disabled.OPtIaFis...F弋;I谋件∖⅛≡⅛⅛t揍2M0∖E JT诃文档溶诃2"7tel［Q4章坦合挠忙揆型'茹］魏据和弼盎H辽∙⅞i□lSOlVer ParameterSEqUal To:θMa× ® Mirl O ValUe of; DEy Changing Cells ：D •在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。

利用Excel进行股票和投资分析在当今快节奏的社会中，股票和投资已经成为人们追求财富增长的重要手段之一。

然而，许多投资者在进行股票和投资分析时缺乏有效的工具和方法。

幸运的是，Excel作为一种强大的数据处理和分析工具，为我们提供了丰富的功能和灵活性。

本文将介绍如何利用Excel进行股票和投资分析，帮助投资者更好地进行决策。

一、数据获取和整理要进行股票和投资分析，首先需要获取和整理足够的数据。

Excel可以通过插入外部数据连接到股票交易平台或金融数据提供商，例如雅虎财经、谷歌财经等，获取实时或历史股票数据。

将数据导入Excel 后，进行必要的整理和清洗，确保数据的准确性和完整性。

二、计算基本指标在进行股票和投资分析时，我们通常会计算一些基本指标，例如收益率、波动率、市盈率等。

利用Excel的函数和公式，可以轻松地计算这些指标。

例如，要计算某支股票的年化收益率，可以使用“RATE”函数，并结合其他必要的参数，如交易日数、初始价格和结束价格。

通过使用Excel的内置函数和公式，可以快速地计算出所需的指标，为后续的分析提供依据。

三、绘制股票走势图股票走势图是进行股票分析的重要工具之一。

通过将股票的价格数据用折线图或蜡烛图表现出来，可以清晰地观察股票的价格走势和趋势。

在Excel中，可以利用图表功能，选择合适的图表类型和样式，将股票价格数据绘制出来。

此外，还可以添加移动平均线、趋势线等辅助工具，帮助分析股票的长期和短期趋势。

四、构建投资组合对于投资者来说，构建一个均衡的投资组合是至关重要的。

通过将不同的资产按一定比例组合在一起，可以实现风险的分散和收益的最大化。

在Excel中，可以利用“IF”函数、“VLOOKUP”函数等来计算不同资产的权重和收益率，并利用“SOLVER”插件来找到最优的组合。

通过不断调整权重和资产配置，可以优化投资组合的风险收益特征。

五、风险评估和模拟在进行股票和投资分析时，必须充分考虑风险因素。

使用Excel进行投资组合分析与优化投资组合分析是投资者在选择投资标的时的一种常用方法。

通过对多种不同资产的组合进行分析和优化，投资者可以实现风险的分散和收益的最大化。

在这篇文章中，我们将介绍如何使用Excel进行投资组合分析与优化。

第一步是收集数据。

在进行投资组合分析之前，我们需要收集各个资产的历史数据。

这些数据可以包括股票、债券、商品等各种不同类型的资产。

我们可以从金融网站或者金融数据提供商那里获取这些数据，然后将其导入到Excel中。

第二步是计算各个资产的收益率。

在Excel中，我们可以使用“=（当期价格-上期价格）/上期价格”这个公式来计算各个资产的收益率。

将这个公式应用到每个资产的数据上，我们就可以得到每个资产的收益率序列。

第三步是计算各个资产的相关系数矩阵。

相关系数矩阵可以反映不同资产之间的相关性。

在Excel中，我们可以使用“=CORREL(数据范围1，数据范围2)”这个函数来计算两个资产之间的相关系数。

将这个函数应用到每对资产的数据上，我们就可以得到一个相关系数矩阵。

第四步是构建投资组合。

在Excel中，我们可以使用“=S UMPRODUCT(权重范围，收益率范围)”这个公式来计算投资组合的预期收益率。

将这个公式应用到每个投资组合上，我们就可以得到每个投资组合的预期收益率。

同时，我们还可以使用“=SQRT(MMULT(TRANSPOSE(权重范围)，MMULT(相关系数矩阵，权重范围)))”这个公式来计算投资组合的标准差，即风险。

将这个公式应用到每个投资组合上，我们就可以得到每个投资组合的标准差。

第五步是进行投资组合优化。

在Excel中，我们可以使用“=SolverAdd(Cells(1,1),1,1,Cells(2,1),Cells(3,1),Cells(4,1))”这个VBA宏来添加Solver插件。

然后，我们可以使用Solver插件来寻找最优的投资组合。

我们可以设置目标为最大化预期收益率或者最小化风险，约束为权重之和等于1。

EXCEL在投资组合理论教学中的应用李吉栋（河北经贸大学金融学院，石家庄，050061）摘要：投资组合理论是金融学科的一个重要理论，内容比较抽象，数学模型多，学生理解起来很困难。

在投资组合理论的教学过程中，利用EXCEL的数据运算和图表功能，将抽象的理论知识直观地演示出来，不但使教学内容深入浅出，易于理解，也可以帮助学生将理论知识与投资实践结合起来，激发学生们的学习兴趣。

关键词：EXCEL；投资组合理论；规划求解For personal use only in study and research; not for commercial use投资组合理论是金融学科的一个重要理论。

该理论认为，在由若干证券构成的所有可能的投资组合中，只有部分组合是有效的，理性投资者在这些有效组合中选择最适合自己的组合。

基于对证券期望收益率、标准差以及协方差的估计，我们可以找到这些有效组合，即投资组合前沿。

在以往的教学实践中，学生们普遍反映这部分内容非常抽象，数学模型多，难以把这些理论和模型与投资实践联系起来，理解起来很困难。

如果在教学过程中能够利用中国证券市场的真实数据，将这些数学模型演算一下，再借助于必要的图形分析，使这些抽象的理论和模型能够直观地演示出来，对学生理解教学内容会大有帮助。

EXCEL是一款功能强大的电子表格数据处理软件，而且具备丰富的图表演示功能，非常适合在投资组合理论教学中使用。

下面笔者将结合自己的教学实践，介绍在投资组合理论教学中如何应用EXCEL。

一、运用EXCEL演示证券的相关性对组合风险的影响投资组合理论的核心思想就是风险的分散化。

投资者之所以要持有多个证券，其根本原因就在于部分风险会随着持有证券个数的增加而有所降低。

影响证券组合风险分散化效果的一个重要因素就是证券之间的相关性。

我们一般是通过分析两个风险证券的组合来帮助学生理解证券相关性与风险分散化之间的关系。

For personal use only in study and research; not for commercial use首先，假设市场上有两个风险证券，知道这两个证券的期望收益率、标准差和相关系数，计算出这两个证券的不同权重组合的期望收益率和标准差，画出投资组合曲线；然后改变两个证券的相关系数，比较投资组合曲线的变动情况。