实验五：运用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进行投资组合分析与优化在当今的投资领域，有效地管理和优化投资组合是实现长期财务目标的关键。
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 进行多项目最优投资组合及投资安排决策韩良智〔北京科技大学管理学院，北京，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的“规划求解”功能进行优化
如何使用Excel的“规划求解”功能进行优化在日常工作和生活中，我们经常会遇到需要优化的问题，比如如何在有限的资源条件下实现最大的效益，或者如何找到满足多个条件的最优方案。
这时候，Excel 的“规划求解”功能就可以派上用场了。
“规划求解”是 Excel 中一个强大的工具，它可以帮助我们通过建立数学模型来找到最优解。
接下来，让我们详细了解一下如何使用这个功能。
首先，确保您的 Excel 中已经加载了“规划求解”功能。
如果没有，可以通过以下步骤进行加载：点击“文件”选项卡，选择“选项”，在弹出的“Excel 选项”对话框中，选择“加载项”，然后在“管理”下拉菜单中选择“Excel 加载项”，点击“转到”按钮，在弹出的“加载宏”对话框中勾选“规划求解加载项”，点击“确定”即可。
在使用“规划求解”之前，我们需要明确问题的目标和约束条件，并将其转化为数学模型。
例如，假设我们有一个生产问题，需要决定生产两种产品 A 和 B 的数量，已知产品 A 的单位利润为 10 元，产品 B 的单位利润为 15 元，我们拥有的原材料限制为 100 单位，生产产品 A 每单位需要消耗 2 单位原材料，生产产品 B 每单位需要消耗 3 单位原材料。
我们的目标是最大化总利润。
接下来，我们在 Excel 中建立表格来表示这个问题。
在第一列中输入产品名称（A 和 B），第二列输入生产数量（假设初始值为 10），第三列输入单位利润（分别为 10 和 15），第四列计算每种产品的利润（数量乘以单位利润），第五列输入每种产品消耗的原材料数量（分别为 2 和 3），第六列计算总的原材料消耗（数量乘以消耗的原材料数量）。
然后，我们设置目标单元格。
在这个例子中，目标是最大化总利润，所以我们选择计算总利润的单元格作为目标单元格。
接下来，设置变量单元格，即生产数量所在的单元格。
再然后，添加约束条件。
在这个例子中，约束条件是总的原材料消耗不能超过 100 单位，所以我们添加这个约束条件。
用Excel进行最优值规划
用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 规划求解
excel 规划求解Excel是一款功能强大的电子表格软件，可以用于数据分析、数据处理、数据可视化以及进行规划求解等多种任务。
在Excel中进行规划求解可以帮助我们优化问题的解决方案，提高效率和准确性。
下面我将介绍一下在Excel中进行规划求解的基本步骤和方法。
首先，我们需要确定需求或者问题，明确目标。
比如我们要求解一个优化问题，如如何在预算有限的情况下，获得最大的利润。
接下来，我们需要在Excel中建立一个模型，将问题转化为一个数学模型。
对于优化问题，我们需要确定决策变量、约束条件和目标函数。
决策变量是我们要优化的变量，是我们需要调整的参数。
在上述问题中，我们可以考虑不同的投资方案和预算分配方式作为决策变量。
约束条件是我们需要满足的条件，它们限制了决策变量的取值范围。
在上述问题中，预算是一个约束条件，我们不能超出预算。
目标函数是我们要最大化或最小化的函数。
在上述问题中，我们的目标是最大化利润，所以利润就是我们的目标函数。
然后，我们可以使用Excel中的规划求解工具来求解问题。
在Excel中，我们可以使用“规划求解”工具来实现。
首先，我们需要将问题转化为Excel能够理解的形式，比如将决策变量和目标函数写入Excel表格的某一列或一行，将约束条件写入Excel表格的某一区域。
然后，我们可以打开Excel中的“数据”选项卡，点击“规划求解”按钮，选择求解目标和约束条件，然后点击“求解”按钮。
Excel会自动寻找最优解，并将结果显示在对应的单元格中。
最后，我们需要分析求解结果，并根据需要进行调整和优化。
如果求解结果不符合需求，我们可以根据结果进行适当的调整，重新运行规划求解工具，直到达到满意的结果为止。
总而言之，Excel提供了方便实用的规划求解工具，可以帮助我们解决各种优化问题。
通过正确使用Excel中的规划求解功能，我们可以提高问题求解的效率和准确性，实现更好的决策和结果。
希望本文能对大家在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实现多个资产的投资组合优化作者：祝媛博来源：《时代经贸》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规划求解在多方案组合排队投资决策中的应用
万方数据
Ｅ郁ｘ玉ｃ环ｅｌ“规划求解”在多方案组合排队投资决策中的应用 ２第中ＣＭＩＦＶ（［过【１多在可方合利量（授Ｅ的ｌＡ资求方按序３以完８巨逐内素任上的披人（目责认单露一露ｆｃ不比任制大始企会熟负独信主究ｅ０２Ｈ０ｈａｎ收基作８ＡＮ１２３摘文案０３４５关中、０１２湖ｘｂ１０Ｉ０１ｉｎｆ。ＮＦ要］，程文行以案法指照下善０大步进质学也露）。任为，内侧同较报责的债．０Ｎ７国方企和应额作案，０）信员独的多业后立息９２ｎａｏ稿金者ｃＡＯ山刘季７Ａ（要章例０在统虽年卷ａｇｒ键图硕Ｂ万当对ＧＲ参２，１Ｙ７北ｅ较献的实“”数最自结建行历仅工信。如部也重告逐任披表ｅｍ２日项０简ＥＭ哑）长０晓管案业比用、出组计息实列信立企社，的。２。】编８【原一然．２第ｍａ士ｌＭＡ考００词分９元限会月ｅｔ为标，现］的期目介项优制产影合立逐会占作据息可环有不ＥＴ侧是步披从露、男翠６东０Ｎ汽）“４理ｎｉ组投较。Ｃ净合算。施示息ＮＩ的业会可社文（多号１有行随９ｏ资期ｔｏ］】ＴＯ．１复】类识它任即大’、目投定度生响我社步计计仅１的披过以保准利同重最推露应ｎ２湖助Ｎ献０车方】对规５会业着信合资。现决累经考来社责要会４ｉ０郁Ｉ略孔）企杂码们意在小。组资对来，国会推人占％要露于投十用终行的由ｚＥ号０北Ｄ４投人Ｚ．案１这计内社ａ８玉Ａ论晓业息ｘ排决从、值总策计５调核披可会说任求责工划ｔ。】６既组投．－省合其我实责广员被求和分现资限进Ｔ一趋编内企简ｃ］０资员ｉ０环Ｉ企婷社组７种报部会化ｏｅＦ队策中７教Ｅ、额。投万查露以Ｏ责改信企任本Ａ３不合资结法进对国际任。能涮这文散有费制行正个势容业单业求ｎ—（Ｎ业．会ｌ２五总进合—投表标发育１文；７投中属或限合选净分”资元：行企可国信够发查种需以字、报有在常统。任革独息和社模业利报３９社责学解排０资的准展５１厅６个额行利１于排额资３首规业以情息用总素要表不污并一但立披会式润会任规；资，出现别额、提关会报的披在告社一队９方基。，院”投为社责信用４相队允本先范产参，披更人质定述易基染有内很的就社露责逐表划Ｆ决如能会）值为与６，高的计告成露现形投（案础对我，任息２资０３会管在Ｅ２互，许限按。生考露恰数结期相于础治选部有口目会模任渐及科求３策果使率累０我会社报。本在有式资ｘ进上国女会披ｃ２０独则的额学项各万Ｓ制责当构进结查上理择必径前责式披向社理多，计露决ｅ９行Ａ增会企解程一净和０计万国会表因太我财全研ｌ）立这范进方度的１，行合找．费性信要来任等露高会目万元任湖信初工策“０决方６加责业；序组现究内净元企人责附为高国务面４，些围行案方％还专。及在地息．看报的级责北息探为元进会在规－策社任编“程案投方值部现时业员任注此。还报系－又方内各汽净由式，难业为缺某售进对设衡。告有实模任披【十非、行计实划０所会项制车露Ｊ资０案不案则种之收现值。点披目而以培的解少些后信中行比置量只方应。处待际式告统系组一］务求２采责目独互４投专Ｔ［的．８属被需组值向露具满训决数科服社。权有于来推金决中和五业益５，最前素息进法先于基地Ｊ，合中解－用任设立］实江斥０资业０于称按合”学率面企有足，据目务会不重极进看流．策各最率万然优会质。行不从探础披证苏采”３的信置湖排规院方组知黑相作净排的，业中社并等下费责同可助少一。．量研商；投大数元后计。对文是一索上露用工方息权社划管龙北队互组现队案大合在识的级会逐披问添等任行以于数步企待表究论的具法重会组资的项理据、分人于字很部阶编企江排合值，小全社以责年露题加信业将行的探条。【，．。培十投目工对方，是科。责合项最如６别组员不说切分段报业Ｊ２斥或率反排国会上任对的，明在息信各业企索社件最程有０反训外］０堰法方“资统．０或目优表０按合具便明大．社，排或复范责职信会社笔细报披息行问业与成终系经５关万复。是便项会（４决排都而队获衡组教１投为围有称息计会者科表用。披业的编实企社责会以贸１４计）“快目原所元比高。．是中２策队是合资大数际业责研项捷组０始示时较方。限专字开产任０中目准投．多得２案额以披对组确）要到合地法得”到，不需同列投出资各限种额组的合最，优反投复资衡组量合和结比果较。。决策
利用EXCEL进行多项目最优投资组合及投资安排决策
利用EXCEL进行多项目最优投资组合及投资安排决策
韩良智
【期刊名称】《石油化工技术与经济》
【年(卷),期】2004(020)001
【摘要】资金限额条件下投资项目的最优投资组合及投资安排是某些企业经常遇到的问题,企业对这些项目进行组合与投资安排时,不仅要考虑各项目的投资额大小,还要考虑项目投资的先后顺序.文章介绍了在EXCEL上进行这类投资决策问题求解的具体方法和步骤.
【总页数】4页(P59-62)
【作者】韩良智
【作者单位】北京科技大学管理学院,北京,100083
【正文语种】中文
【中图分类】F8
【相关文献】
1.利用Excel进行投资决策--兼谈Excel的NPV函数之不足及弥补办法 [J], 郭文燕
2.Excel规划求解工具在多项目投资决策中的应用 [J], 王洁
3.新会计准则下的投资决策--如何更好地利用新会计准则的相关规定进行投资决策[J], 陈雪梅
4.利用NPV法则进行投资决策的悖论──论《财务管理》投资决策教学中应注意的
问题 [J], 邹彩芬
5.用EXCEL进行长期投资决策分析──更新固定资产的决策 [J], 石红;许聆
因版权原因，仅展示原文概要，查看原文内容请购买。
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所示。
如何用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的投资组合决策分析模型
三、 投资期为多期， 且既考虑前期的投资 剩余资金，又考虑先期投产投资项目收益的 投资组合分析模型
大多数大ห้องสมุดไป่ตู้投资项目组合往往不能在一年内全部 投入 ( 而是需要一定年份的时期 , 在这种情况下 ( 先期 投入项目投产后获得的收益以及先期剩余投资资金可 以补充后期投资项目的资金需求 , 已知各个项目的初 始投资及净现值 ( 企业需要根据制订的投资年度计划 及各投资年度的资金限额 ( 优化组合及安排各个投资 项目 ( 即第 ) 年先投资哪些项目 ( 第 $ 年再根据第 ) 年 剩余的投资金加上本年的资金限额安排哪些项目 .. 等等 , 在此种情况下 ( 后期投资的可用资金数额 ( 除了当 年资金限额 ( 还要包括前期投资的剩余资金和先期投 产项目的所获收益 , 这里不计剩余投资资金的时间价 值, 例 * +某企业现有 + 个备选项目 (投资分两期进行 ( 两期的投资限额分别为 5%) 万元和 +)) 万元 ( 各个项 目的净现值已计算完毕 & 见图 * 原始数据区 ’, 由于技 术工艺或市场原因 (项目 0 -#-1 为三择一项目 ( 项目 # 和 2 为关联项目 ( 各项目若第 ) 年被选中 ( 则其第 ) 年 末收益为下表 2 列 , 所有项目的投资期均为 $ 年 , 企业 的基准收益率为 $%8,
财务管理信息化
基于 !"#$% 的投资组合决策分析模型
蔡明杰
（湖南商学院 会计系， 湖南 长沙 0%-"-7）
【摘 要】企业有时会面对投资方案组合决策问题，但由于计算复杂（如 ! 个方案的组合情况有 "#$% 个， 逐个组合进行测试的工作量极大， 加上其他情况， 如方案之间的关联、 互斥等， 几乎不可能求出正确 的解） ， 决策者往往凭感觉作出判断， 造成严重后果。本文试着以 &’()* 电子表格软件中的 “规划求解” 为 工具， 精确求出合理的方案组合+为决策提供依据。 【关键词】投资组合 规划求解 计算机模型
运筹学实训实验报告
一、实验背景运筹学是一门应用数学的分支，它运用数学模型和算法来解决各种优化问题。
随着现代科技的发展，运筹学在各个领域的应用越来越广泛，如生产管理、物流运输、资源分配等。
为了提高学生运用运筹学知识解决实际问题的能力，我们开展了运筹学实训实验。
二、实验目的1. 熟悉运筹学的基本概念和常用方法；2. 掌握线性规划、整数规划、运输问题、目标规划等运筹学模型；3. 学会运用计算机软件解决实际问题；4. 培养学生的团队合作精神和创新意识。
三、实验内容本次实验主要包括以下内容：1. 线性规划：以生产计划问题为例，建立数学模型，并运用Excel规划求解器求解最优解。
2. 整数规划：以人员排班问题为例，建立数学模型，并运用Lingo软件求解最优解。
3. 运输问题：以物流配送问题为例，建立数学模型，并运用Lingo软件求解最优解。
4. 目标规划：以投资组合问题为例，建立数学模型，并运用Lingo软件求解最优解。
四、实验步骤1. 线性规划实验（1）问题分析：某企业需要生产甲、乙两种产品，已知生产甲、乙两种产品所需的原料、劳动力及设备等资源消耗量，以及产品的售价和利润。
（2）模型建立：根据问题分析，建立线性规划模型，目标函数为最大化利润，约束条件为资源消耗量不超过限制。
（3）求解：运用Excel规划求解器求解最优解。
2. 整数规划实验（1）问题分析：某公司需要安排员工值班，要求每天至少有3名员工值班，且员工值班时间不能超过一周。
（2）模型建立：根据问题分析，建立整数规划模型，目标函数为最小化员工值班成本，约束条件为员工值班时间不超过限制。
（3）求解：运用Lingo软件求解最优解。
3. 运输问题实验（1）问题分析：某物流公司需要将货物从A、B两个仓库运送到C、D两个销售点，已知各仓库的货物量、各销售点的需求量以及运输成本。
（2）模型建立：根据问题分析，建立运输问题模型，目标函数为最小化运输成本，约束条件为各仓库的货物量不超过需求量。
如何利用Excel进行股票投资组合分析和优化
如何利用Excel进行股票投资组合分析和优化股票投资组合分析是投资者在进行股票投资决策时的重要工具。
通过对不同股票的收益率、风险和相关性进行分析，投资者可以构建一个优化的投资组合，以提高收益并降低风险。
Excel是一款功能强大的电子表格软件，提供了许多功能和工具，可以帮助投资者进行股票投资组合分析和优化。
本文将介绍如何利用Excel进行股票投资组合分析和优化的方法。
一、数据获取和整理首先，投资者需要获取股票的历史价格数据。
可以通过金融数据提供商、证券交易所的官方网站或第三方网站等渠道获取。
将股票的历史价格数据按照日期和股票代码整理到Excel电子表格中，以便后续的分析和计算。
二、计算股票收益率在进行股票投资组合分析和优化之前，需要计算每只股票的收益率。
可以通过以下公式计算每只股票的收益率：收益率 = （当前价格 - 上一期价格）/ 上一期价格将该公式应用于每只股票的历史价格数据，即可计算出每只股票的收益率。
三、计算股票收益率的均值和标准差在投资组合分析中，投资者不仅关注单只股票的收益率，还需要考虑整个组合的平均收益率和风险。
通过计算股票收益率的均值和标准差，可以评估单只股票的风险和收益分布。
在Excel中，可以使用AVERAGE函数和STDEV函数分别计算股票收益率的均值和标准差。
将股票收益率数据输入到一个列中，然后使用AVERAGE函数和STDEV函数分别对该列进行计算，即可得到股票收益率的均值和标准差。
四、计算股票的相关性投资者在构建投资组合时还需要考虑股票之间的相关性。
相关性是指两只股票之间的价格走势的相似程度。
如果股票之间存在较高的正相关性，那么它们的价格走势会相似；如果股票之间存在较高的负相关性，那么它们的价格走势会相反。
通过计算股票的相关性，可以了解股票之间的相互关系，以便进行投资组合的优化。
在Excel中，可以使用CORREL函数计算股票之间的相关性。
将两只股票的收益率数据输入到两个列中，然后使用CORREL函数对这两个列进行计算，即可得到股票的相关性。
实验五：运用Excel规划求解进行最优投资组合的求解
实验报告证券投资学院名称专业班级提交日期评阅人 ______________评阅分数 ______________实验五：运用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的最优资产组合求解
据分析，创立一套适合地产企业的预算控制体系，将房地产项目开 体系十分必要。预算控制是一个不断积累、不断完善、不断改进的过
发成本、费用科目等板块精细划分，做好成本的原始数据积累。房地 程。对于地产行业来说，由于其自身的特殊性，预算控制的成功推行
产行业是一个资金密集程度很高的行业, 资金紧张是整个行业的普 不可能一蹴而就，并且任何盲目照搬其他公司的预算控制管理模式
条件的格式，如图 3 所示，假设固定收益要求为 0. 005 时，先赋 ω1、 ω2、ω3 值为 0。约束条件一的单元格设置公式：E2= B2+ B3+ B4，约
束条件二为组合收益率，即 F2= ω1r 1+ ω2r 2+ ω3r 3。
利用公式 MMU LT(α, β)求得矩阵[x, y, z]，α 为权重 ω1、ω2、
作者简介：殷海娜（1991- ），汉族，河南濮阳人，就读于北京交通 大学经济管理学院，研究方向：金融学。
74 Times Finance
时代金融
Times Finance
NO.04，2012 （CumulativetyNO.477）
基于 excel 的最优资产组合求解
殷海娜
（北京交通大学经济管理经济系，北京 100044）
【摘要】在投资证券市场的决策中，收益与风险的权衡是投资决策的核心问题。本文借助 excel 强大的线性规划及函数功能建立证券投 资模型，进行有效集的绘制及最优组合求解。包括风险资产与无风险资产的最优组合，以及收益或风险固定的有条件下的最优资产组合求 解。并在最后对模型进行评价。
工作的基础上，房地产企业的项目预算与年度预算才有意义，项目 挂钩。依据各责任部门对预算的执行结果，实施量化的绩效评估，比
如何利用Excel进行投资组合分析
如何利用Excel进行投资组合分析在当今的投资领域，构建一个合理的投资组合对于实现资产的稳健增长和风险控制至关重要。
而 Excel 作为一款强大的电子表格软件，为我们进行投资组合分析提供了便捷且有效的工具。
接下来，让我们一起深入探讨如何利用 Excel 来完成这项重要的任务。
一、数据收集与整理首先，我们需要收集相关的投资数据。
这包括不同投资资产（如股票、债券、基金等）的历史价格、收益率、风险指标（如标准差）以及它们之间的相关性等信息。
这些数据可以从金融网站、财经数据库或者证券公司的研究报告中获取。
在 Excel 中，创建一个新的工作表，并将收集到的数据按照资产类别分别列示。
确保数据的准确性和完整性，对缺失的数据进行合理的估计或处理。
二、计算资产的收益率在数据整理完毕后，接下来要计算每种资产的收益率。
收益率可以简单地通过（期末价格期初价格＋期间分红）／期初价格来计算。
在 Excel 中，使用公式功能可以轻松完成这一计算。
例如，如果资产的价格数据分别列在 A 列（时间序列）和 B 列（价格），那么可以在 C 列使用公式来计算收益率。
三、计算资产的风险（标准差）风险是投资中不可忽视的因素。
标准差是衡量资产风险的常用指标。
在Excel 中，可以使用STDEV 函数来计算收益率的标准差。
同样，以收益率数据所在列为基础进行计算。
四、分析资产之间的相关性不同资产之间的相关性对于构建投资组合有着重要影响。
相关性高的资产组合在一起并不能有效地分散风险，而相关性低甚至负相关的资产组合则能够更好地降低风险。
在 Excel 中，可以使用 CORREL 函数来计算不同资产收益率之间的相关性。
五、构建投资组合基于上述计算和分析的结果，我们可以开始构建投资组合。
可以通过试错的方法，给定不同资产的权重，计算组合的预期收益率和风险。
例如，假设有三种资产 A、B、C，权重分别为 wA、wB、wC（且wA ＋ wB ＋ wC ＝ 1），则组合的预期收益率可以通过 wA 资产 A 的预期收益率＋ wB 资产 B 的预期收益率＋ wC 资产 C 的预期收益率来计算。
使用Excel进行投资组合分析与优化
使用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。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验报告
证券投资
学院名称
专业班级
提交日期
评阅人____________
评阅分数____________
实验五：运用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》电子书第四章P83
F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？
【实验项目步骤与结果】
A.
B．使用规划求解
C．
投资比例为负值说明该证券风险远远大于其收益率，已经不适合投资。
F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？
通过可卖空和不可卖空有效前沿图的对比可以看到，在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高，并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。
【实验项目结论与心得】
通过本次实验，学会了用excel进行相关组合最优的计算，同时也画出了风险收益曲线，为将来进行实盘操作打下了坚实的基础。
【教师评语与评分】。