Excel公式和函数 典型案例—多种风险资产的最优投资组合

Excel公式和函数典型案例－多种投资的动态分析模型投资者在进行多种投资时，往往需要对各种风险资产的期望收益率、标准差，以及它们之间的相关系数进行分析，以便了解对投资组合的影响。

此时，就可以建立一个多种风险资产投资组合的动态计算分析模型。

1．练习要点●使用控件●设置控件格式●使用MMULT函数●使用MINVERSE函数●创建图表●设置图表格式2．操作步骤：（1）分别合并B3至F3和B4至F4单元格区域，并输入标题文字和“已知数据”文字。

然后，在B5至F10单元格区域，创建“已知数据”数据表，并设置其格式，效果如图13-26所示。

图13-26 创建“已知数据”数据表提示设置第3行的【行高】为28.5；第4、5行的【行高】为18.75；第6至10行的【行高】为21。

（2）选择【开发工具】选项卡，单击【控件】组中的【插入】下拉按钮，选择【表单控件】栏中的“数值调节钮”选项，并在D6单元格中绘制该控件，如图13-27所示。

创建数据表图13-27 绘制控件注 意单击Office 按钮，并单击【Excel 选项】按钮，在弹出的对话框中，启用【在功能区显示“开发工具”选项卡】复选框。

（3）右击该控件，执行【设置控件格式】命令，在弹出的对话框中，选择【控制】选项卡，并设置【单元格链接】为$D$6，如图13-28所示。

图13-28 设置控件格式提 示 在【设置控件格式】对话框中，选择【大小】选项卡，设置控件的【高度】为“0.64厘米”；【宽度】为“2.28厘米”。

（4）使用相同的方法，在D 列和F列绘制其他的“数值调节钮”控件，并设置其单元格链接均为它们所在的单元格，如图13-29所示。

图13-29 绘制其他控件提 示 另外，用户也可以复制多个“数值调节钮”控件，并更改其单元格链接。

（5）选择C6单元格，在【编辑栏】中输入“=D6/1000”公式，并按Enter 键，即可建立资产P 的期望收益率与调节按钮的关系，如图13-30所示。

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 中，我们可以通过输入和整理投资数据来开始我们的分析之旅。

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

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

假设我们有以下几种投资产品：股票 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中如何进行复杂的财务分析和风险评估在当今的商业世界中，财务分析和风险评估对于企业的决策制定和可持续发展至关重要。

Excel 作为一款强大的电子表格软件，为我们提供了丰富的功能和工具，帮助我们进行复杂的财务分析和风险评估。

接下来，让我们一起深入探讨如何利用 Excel 实现这一目标。

一、准备数据首先，我们需要收集和整理相关的财务数据。

这可能包括资产负债表、利润表、现金流量表等。

确保数据的准确性和完整性是至关重要的，因为任何错误或遗漏都可能导致分析结果的偏差。

在 Excel 中，我们可以将这些数据分别输入到不同的工作表中，或者将它们整合在一个工作表中，并使用合适的列标题来标识不同的数据项目。

二、财务比率分析财务比率是评估企业财务状况和经营绩效的重要指标。

以下是一些常见的财务比率及其在 Excel 中的计算方法：1、偿债能力比率（1）流动比率＝流动资产／流动负债在 Excel 中，可以使用公式“＝SUM(流动资产范围) ／ SUM(流动负债范围)”来计算。

（2）资产负债率＝负债总额／资产总额通过公式“＝SUM(负债总额范围) ／ SUM(资产总额范围)”得出。

2、营运能力比率（1）应收账款周转天数＝ 365 ／应收账款周转率应收账款周转率＝营业收入／平均应收账款余额在 Excel 中，先计算平均应收账款余额，然后通过公式计算周转率和周转天数。

（2）存货周转天数＝ 365 ／存货周转率存货周转率＝营业成本／平均存货余额3、盈利能力比率（1）毛利率＝（营业收入营业成本）／营业收入使用公式“＝（营业收入营业成本) ／营业收入”计算。

（2）净利率＝净利润／营业收入通过计算这些比率，我们可以对企业的财务状况有一个初步的了解。

同时，我们可以将不同时期的比率进行比较，观察企业的发展趋势。

三、图表展示为了更直观地展示财务数据和比率的变化趋势，我们可以使用Excel 的图表功能。

例如，创建折线图来显示净利润的年度变化，或者使用柱状图对比不同年度的营业收入和成本。

如何使用Excel进行投资组合管理和风险分析第一章简介投资组合管理和风险分析是金融领域中非常重要的一部分。

它们涉及到对投资组合的构建、监控和调整，以及对投资风险的评估和控制。

在这一章节中，我们将介绍使用Excel进行投资组合管理和风险分析的基本方法和技巧。

第二章数据准备在进行投资组合管理和风险分析之前，首先需要准备好所需的数据。

这包括历史股票和债券价格、收益率以及市场指数的数据。

Excel提供了强大的数据处理和分析功能，可以帮助我们方便地获取和整理这些数据。

第三章构建投资组合在构建投资组合时，我们需要考虑多个因素，包括风险偏好、期望收益、资产类别和权重分配等等。

Excel提供了诸多函数和工具，如求解器、约束条件和目标函数等，可以帮助我们优化投资组合的效果，并找到最佳的资产配置方案。

第四章投资组合监控与调整投资组合管理并不是一次性的活动，而是一个持续的过程。

我们需要定期监控投资组合的表现，并根据市场情况进行调整。

Excel可以帮助我们实时跟踪和分析投资组合的收益率、波动性以及其它指标，以便及时做出决策。

第五章风险评估与控制风险分析是投资组合管理过程中的重要一环。

Excel提供了多种风险评估模型和工具，如VaR（风险价值）模型、条件风险模型和蒙特卡洛模拟等，可以帮助我们对投资组合的风险水平进行评估和控制，并制定相应的风险管理策略。

第六章数据可视化数据可视化是理解和解释投资组合管理和风险分析结果的重要手段。

Excel提供了丰富的图表和图形功能，可以帮助我们将数据直观地呈现出来，并观察其变化趋势和关联关系。

通过数据可视化，我们可以更加清晰地了解投资组合的表现和风险状况。

第七章实例分析在这一章节中，我们将通过一个实例来演示如何使用Excel进行投资组合管理和风险分析。

我们将选取一些具有代表性的资产，并根据历史数据进行投资组合的构建和分析。

通过实例的分析，我们可以更好地理解和应用Excel在投资组合管理和风险分析中的作用。

运用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 软件系统管理和基本原理,掌握多资产投资组合优化的Excel 应用。

二、预备知识（一）相关的计算机知识： Windows 操作系统的常用操作；数据库的基础知识；Excel 软件的基本操作。

（二）实验理论预备知识现代资产组合理论发端于Markowitz(1952)提出的关于投资组合的理论。

该理论假设投资者只关心金融资产（组合）收益的均值（期望收益）和方差，在一定方差下追求尽可能高的期望收益，或者在一定的期望水平上尽可能降低投资收益的方差。

投资者的效用是关于投资组合的期望回报率和方差的函数，理性的投资者通过选择有效地投资组合以实现期望效用最大。

该理论第一次将统计学中期望与方差的概念引入投资组合的研究，提出用资产收益率的期望来衡量预期收益，用资产预期收益的标准差来度量风险的思想。

1、理论假设（Ⅰ）市场上存在n ≥2种风险资产，资产的收益率服从多元正态分布，允许卖空行为的存在。

{}12(,,,)T n ωωωωω=,代表投资到这n 种资产上的财富（投资资金）相对份额，它是n 维列向量，有11=∑=ni i ω，允许0<i ω，即卖空不受限制。

(Ⅱ) 用e 表示所有由n 种风险资产的期望收益率组成的列向量。

12(,,,)T n e R R R R == （1）p r 表示资产组合的收益率，)(p r E 和)(p r σ分别为资产组合p 的期望收益率和收益率标准差。

∑=⋅=⋅=ni ii Tp e r E 1)(μωω (2)(Ⅲ)假设n 种资产的收益是非共线性的(其经济意义为：没有任何一种资产的期望收益率可以通过其他资产的线性组合来得到，它们的期望收益是线性独立的。

)。

这样它们的方差-协方差矩阵可以表示为:⎪⎪⎭⎪⎪⎬⎫⎪⎪⎩⎪⎪⎨⎧=nn n n n n Q σσσσσσσσσ212222111211 （3）由于总是假定非负的总体方差,它还必须是一个正定矩阵,即对于任何非0的n 维列向量a ,都有0T a Qa >。