实验五运用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 财务应用收益最大化的投资组合问题作为一家企业，没有投资就没有发展，投资是寻找新的盈利机会的唯一途径，也贯穿于企业经营的始终。

投资组合的目的在于分散风险，它是将资金按照一定的比例分别投资于不同种类的项目上，如房地产、债券、股票等。

投资的目的是获得更多的经济利润，因此，收益最大化是企业财务管理的最终目标。

所谓收益最大化，是指企业利润总额和全部资本之比最大，它反映了资本投入与产出之间的比例关系。

这里所说的收益最大化是一种长期的、稳定的、真实的和不损害社会利益的资本收益。

本节就利用规划求解的功能，来分析计算收益最大化的投资组合问题。

例如，某公司计划要投资三种国债，其中，每种债券每年的投资额与净现值如图9-12所示。

已知目前该公司有活动资金30万可供投资，预计1年后，又可以获得20万元，2年后可以获得另外25万元，3年后可以获得10万元。

如何在目前回报率的基础上，确定该公司能够获得最大收益的投资组合？在确定最大收益之前，首先要计算出各债券每的年累计投资额。

若要计算每年累计的投资额，需要使用SUM函数，对各债券进行分别计算，如选择C14单元格，即“国债L06512”债券第3年所需投资额对应的单元格，在【编辑栏】中输入“=SUM(C3:C6)”公式，即可得到该债券第3年的累计投资额。

再分别使用SUM函数，计算其他债券各年的累计投资额，计算结果如图9-13所示。

已知条件计算累计投资图9-12 债券投资所需的资金和净现值图9-13 每年的累计投资额根据已知的投资项目所需的投资资金以及累计投资额，可以在Excel工作表中，创建如图9-14所示的线性规划模型。

创建模型图9-14 线性规划模型由此可以看出，只有合理分配该公司在各债券中的投资比例，才能使公司获得最大收益。

其中，在该模型中，带有“茶色，背景2”填充格式的单元格区域表示该问题所要求的变量，其默认值为0。

接下来使用SUMPRODUCT函数，计算投资总计额以及目标函数值。

如何利用Excel进行股票投资分析股票投资分析是指通过研究和评估股票市场的一系列因素，从而做出明智的投资决策。

而Excel作为一款功能强大的电子表格软件，可以提供许多工具和函数，帮助投资者进行股票投资分析。

本文将介绍如何利用Excel进行股票投资分析，以帮助投资者更好地了解市场并做出正确的决策。

一、数据导入与整理首先，要进行股票投资分析，我们需要将相关的股票数据导入Excel，并进行整理。

首先，我们可以通过在Excel中打开“数据”选项卡，并选择“从文本”导入股票数据。

在导入数据时，我们可以选择合适的分隔符，例如逗号或制表符，以确保数据能够正确地列在表格中。

导入数据后，我们可以创建一个数据表，将每个数据字段放在合适的列中。

可以包括股票代码、日期、开盘价、收盘价、最高价、最低价等。

通过将数据整理到一个表格中，我们可以更好地进行后续的分析和制图。

二、计算股票的收益率和波动性在进行股票投资分析时，收益率和波动性是两个重要的指标。

通过计算股票的收益率，我们可以了解股票的盈利情况。

而波动性则可以帮助我们评估股票价格的变动范围。

为了计算股票的收益率，我们可以利用Excel提供的函数，例如“= (B2-B1)/B1”，其中B2表示当前日期的收盘价，B1表示前一天的收盘价。

通过将这个公式应用到整个数据表中，我们可以计算每个交易日的收益率。

对于波动性的计算，我们可以使用标准差函数。

标准差可以测量股票价格的变动性，并作为评估风险以及股票预测的指标。

通过在Excel 中使用“STDEV.S”函数，我们可以计算股票价格的标准差。

三、绘制股票图表可视化股票数据是进行股票投资分析的重要手段之一。

通过绘制股票图表，我们可以更直观地分析股票的走势、价格变化以及其他指标的关系。

在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规划求解进行最优投资组合的求解【实验目的】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 进行多项目最优投资组合及投资安排决策韩良智〔北京科技大学管理学院，北京，100083〕摘要：资金限额条件下投资项目的最优投资组合及投资安排是某些企业经常遇到的问题，企业对这些项目进行组合与投资安排时，不仅要考虑各项目的投资额大小，还要考虑项目投资的先后顺序。

本文介绍了在EXCEL 上进行这类投资决策问题求解的具体方法和步骤。

关键词：资金限额 投资 优化在某些企业，很可能面对多个具有可行性的投资项目，但由于筹集资金数额以及筹资时间的限制，这些项目既不可能全部采用，也不可能在一年内全部投资，而是需要在这些项目中作出取舍，并分散在几个投资年度进行投资，这就要求企业对这些项目进行最优组合及作出投资安排计划，使企业取得最大效益〔净现值〕。

笔者结合实例说明利用EXCEL 解决这类投资决策问题的具体方法和步骤。

在下述的计算中，均假设项目无论在何年投资，其初始投资、净现金流量、以及相对于该项目投资年度的净现值均不变。

1 利用EXCEL 进行多项目最优投资组合及投资安排方法和步骤1.1 所有项目均在某年内一次性投资并于当年投产的情况在这种情况下，已知各个项目的初始投资及净现值，企业需要根据制订的投资年度计划及各投资年度的资金限额，优化组合及安排各个投资项目，即第0年先投资哪些项目，第1年再根据第0年剩余的投资资金加上本年的资金限额安排哪些项目，……，等等。

设第t 年安排i 项目的投资，以x i ,t 表示项目i 在第t 年投资的决策变量，x i ,t =1表示在第t 年对项目i 进行投资，x i ,t =0表示在第t 年不对项目i 进行投资，则选取的投资项目以第t 年为投资起点的总净现值为∑=⋅m i i t i NPV x 1,，将各投资年度选取的投资项目的总净现值∑=⋅mi i t i NPV x 1,看作是一个综合项目的净现金流量，则此综合项目的净现值〔以第0年为起点〕为：∑∑∑-==⎥⎥⎦⎤⎢⎢⎣⎡⋅+=101,)()1(1p t m i i t i t NPVx k NPV〔1〕式中：NPV i 为项目i 的的净现值〔以该项目的投资年度为起点〕，m 为项目的个数，p 为企业计划安排投资的年数，k 为企业的基准收益率。

运用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的最优资产组合求解作者：殷海娜来源：《时代金融》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中的数据导入功能，将数据从外部文件（如.csv、.txt）导入到Excel中。

第二章：计算资产收益率和协方差矩阵在Excel中，可以使用函数来计算资产的收益率和协方差矩阵。

假设有n个资产，那么可以使用Excel的AVERAGE函数计算每个资产的平均收益率，使用COVARIANCE.S函数计算资产之间的协方差，使用VAR.S函数计算资产的方差。

通过这些计算，可以得到一个n*n的协方差矩阵。

第三章：计算投资组合的预期收益率和风险在进行投资组合分析时，需要计算投资组合的预期收益率和风险。

预期收益率可以通过资产的权重和各资产的预期收益率的加权平均来计算。

在Excel中，可以使用SUMPRODUCT函数来实现这个计算。

风险可以使用投资组合的方差或标准差来衡量。

在Excel中，可以使用COVAR函数计算投资组合的协方差，使用STDEV函数计算投资组合的标准差。

第四章：计算投资组合的有效边界有效边界是指在给定风险下，可以获得最大预期收益的投资组合。

在Excel中，可以使用Solver插件来求解有效边界。

首先需要在Excel中构建一个目标函数和一系列约束条件，然后通过调整资产的权重来使目标函数最大化。

通过这样的方式，就可以得到有效边界上的一系列投资组合。

第五章：风险评估和资产配置在选择投资组合时，需要进行风险评估和资产配置。

在Excel 中，可以通过计算投资组合的风险收益比、夏普比率等指标来评估风险。