运用ExcelSolver构建最优投资组合王世臻
如何用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中的Solver工具。
以下是一个使用Excel Solver的简单例子：1. 打开一个新的Excel工作簿。
2. 在A列中输入可选项目的名称，例如"A1"单元格中输入项目1，"A2"单元格中输入项目2，以此类推。
3. 在B列中输入各个项目的成本/价值，例如"B1"单元格中输入项目1的成本/价值，"B2"单元格中输入项目2的成本/价值，以此类推。
4. 在C列中输入一个1或0来表示是否选择该项目，例如"C1"单元格中输入1表示选择项目1，输入0表示不选择项目1，以此类推。
5. 在D列中计算项目的总成本/总价值，例如"D1"单元格中输入公式"=B1*C1"来计算选择项目1的成本/价值，以此类推。
6. 将总成本/总价值的指标放在一个单独的单元格中，例如"E1"单元格中输入公式"=SUM(D1:Dn)"来计算总成本/总价值，其中n是项目的数量。
7. 在Excel菜单栏中选择"数据"选项卡，然后单击"Solver"按钮。
8. 在Solver参数对话框中，将目标单元格设置为总成本/总价值的单元格。
9. 设置目标是最小化还是最大化，根据具体问题选择。
10. 在约束条件中选中C列中的单元格，并设置其值为1或0，以限定选择项目的数量。
11. 单击"确定"按钮运行Solver。
通过以上步骤，Solver将会试图找到使得总成本/总价值最小或最大的最优组合。
使用Excel进行投资组合分析与优化
使用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规划求解进行最优投资组合的求解【实验目的】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进行优化问题求解
如何在Excel中使用Solver进行优化问题求解Excel中的Solver是一种强大的工具，可以帮助我们解决优化问题。
无论是在学习、工作还是日常生活中，我们都会遇到一些需要在给定的条件下寻找最优解的情况。
本文将介绍如何在Excel中使用Solver进行优化问题求解。
1. 引言优化问题是数学和工程领域中常见的问题。
在很多情况下，我们需要找到一个最优解，使得满足一定的条件，并使目标函数值最大或最小化。
在Excel中使用Solver工具可以帮助我们自动寻找最优解，而无需手动尝试不同的解决方案。
2. 准备工作在开始使用Solver之前，我们首先需要在Excel中安装和启用Solver插件。
在Excel 2010及以上版本中，我们可以通过以下步骤来启用Solver插件：a. 点击Excel菜单中的“文件”选项；b. 选择“选项”；c. 在弹出的对话框中选择“加载项”；d. 在加载项管理器中，勾选“Solver插件”；e. 点击“确定”按钮，完成插件的启用。
3. 设置优化问题在Excel中，我们通常将优化问题建模为一个规划问题。
我们需要明确定义目标函数、约束条件和可调整的变量。
（下面仅以线性规划问题为例，后续也可适用于非线性规划问题。
）目标函数：我们首先需要明确我们的优化目标是什么，是最大化还是最小化。
在Excel中，我们可以利用单元格来表示目标函数，并将其命名为目标函数。
约束条件：我们需要确保我们的解决方案满足一定的条件。
这些条件可以是等式、不等式或者其他限制条件。
同样地，在Excel中，我们可以使用单元格来表示约束条件，并将其命名为约束条件。
可调整的变量：我们需要确定哪些变量是可调整的，它们是我们希望Solver来求解的。
在Excel中，我们可以使用单元格来表示这些可调整的变量，并将其命名为变量。
4. 使用Solver求解问题在完成优化问题的设置后，我们可以开始使用Solver来求解问题了。
我们可以按照以下步骤进行操作：a. 点击Excel菜单中的“数据”选项；b. 在数据选项中，找到“分析”工具，点击打开；c. 在弹出的对话框中选择“Solver”；d. 在Solver对话框中，我们需要对一些设置进行配置：- 设置目标单元格为我们定义的目标函数单元格；- 设置可调整单元格为我们定义的变量单元格；- 设置约束条件为我们定义的约束条件单元格；- 设置约束条件的限制类型，可以是等式、不等式或者其他限制条件；- 如果有需要，我们还可以设置其他选项，如求解方法、可行域还是全局解等；e. 点击“求解”按钮，Solver将自动寻找最优解，并将结果显示在Excel中。
运用Excel Solver构建最优投资组合(王世臻)
运用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规划求解进行最优投资组合的求解
实验报告证券投资学院名称专业班级提交日期评阅人____________评阅分数____________实验五：运用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在财务投资与资产配置中的应用指南
Excel在财务投资与资产配置中的应用指南一、引言财务投资与资产配置是现代金融领域中的重要课题，它涉及到投资者如何通过合理的配置资产来实现财务目标。
在这个过程中，Excel作为一款强大的电子表格软件，发挥着不可或缺的作用。
本文将介绍如何运用Excel进行财务投资与资产配置，以提供一些实用的指导。
二、投资组合分析1. 数据收集与整理首先，我们需要收集相关的金融数据，如股票价格、债券收益率等。
然后，利用Excel的数据整理功能，将这些数据导入到工作表中，并进行必要的清洗和格式化。
2. 统计分析与风险评估接下来，可以利用Excel中的函数和工具进行统计分析，如计算股票或债券的平均收益率、标准差等。
同时，也可以利用Excel绘制图表来直观地展示投资组合的风险特征，并通过排序、筛选等功能进行风险评估。
3. 投资组合优化在具备了相关的统计数据后，可以利用Excel的求解器功能进行投资组合优化。
通过设定优化目标和约束条件，求解器可以帮助我们找到最优的资产配置方案，以实现风险最小或收益最大化的目标。
三、资产负债管理1. 资产负债表建模资产负债表是公司财务状况的重要表现形式，可以通过Excel进行建模和分析。
首先，我们可以利用Excel的电子表格功能来构建资产负债表的框架，然后填入相应的数据。
通过对资产负债表进行分析，可以帮助我们了解公司的资产结构、负债结构以及净资产状况。
2. 财务指标计算在有了资产负债表的数据后，可以利用Excel的函数和公式计算一些关键的财务指标，如资产负债率、流动比率等。
这些指标可以帮助我们评估公司的偿债能力、盈利能力等财务状况。
3. 情景分析与策略制定通过对资产负债表进行情景分析，可以模拟不同的经营决策对公司财务状况的影响。
例如，我们可以利用Excel的数据表功能，构建多个不同的情景模型，然后通过更改参数进行比较分析，以制定合适的财务策略。
四、投资决策分析1. 现金流量预测在进行投资决策时，需要对相关项目的现金流量进行预测。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
运用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 有效边界与资本配置线图
选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。
我们
标准差
风险溢价
以(3)式为目标函数，以40
1
1i i w ==∑为约束条件运用Excel solver 求解可以得到最
优风险组合的标准差为0.0446，此时的风险溢价为0.0477 ，夏普比率为1.069507，得到图1。
（二）不允许卖空
这种情况下，我们以(2)式为目标函数，可以找出最小的方差组合，以
40
1
1i i w ==∑和0i
w
≥为约束条件运用Excel solver 求解可以得到最小的标准差为
0.0414，此时的风险溢价为0.05127 ，同时可以得到有效边界如图2所示：
图2 未卖空下的有效边界与最优资本配置线
同理有：在知道有效边界之后，寻找有效边界边界上夏普比率最高的组合，即有效边界上的最优的风险组合。
我们已(3)式为目标函数，以40
11i i w ==∑为约束条
件运用Excel solver 求解可以得到最优风险组合的标准差为0.0467，此时的风险溢价以及夏普比率分别为0.051和1.09207，得到上图。
0.04
0.0450.050.0550.06
0.0650.070.0750.080.0850.09
标准差
风险溢价。