基于Excel的投资项目风险模拟分析.doc
基于Excel的投资项目风险模拟分析 [摘要]借助蒙特卡洛模拟分析方法,在考察投资决策变量(如销售量、销售价格、单位变动成本等)概率分布规律的基础上,对目标变量投资项目净现值的取值情况进行大量随机试验,获取相关风险分析的统计信息,为投资决策提供有力支持。而 Excel 的运用,使得快速取得随机试验结果成为可能。 [关键词] Excel;投资项目净现值;风险分析;蒙特卡洛模拟 一、引言 对投资项目净现值进行风险分析,是资本预算中的一个重要环节。源自于卡西诺赌博计算方法的蒙特卡洛模拟分析( MonteCarloSimulation) ,将敏感性和输入变量的概率分布紧密联系,与常见的分析方法(如敏感性分析、情景分析)相比,充分考虑各变量取值的随机性,通过随机模拟技术,给出了投资项目净现值可能取值的范围和不小于某一特定值的概率,为投资决策提供了更为科学的决策依据。运用 Excel 所提供的数学、财务及其他函数,以及分析工具和图表功能,可以很好地解决该问题。 二、项目投资决策分析方法 1.确定性条件下的投资决策 基于贴现现金流技术的净现值法,是投资项目评估最为常见的方法。该法按照项目的资本成本计算每一年的现金流量(包括现金 流入量和现金流出量)现值,并将贴现的现金流量汇总,得到项目的净现值( NetPresentValue, NPV)。如果项目的净现值大于零,则接受该项目;反之,则放弃该项目。 2.不确定性条件下的投资决策 —— 蒙特卡洛风险模拟分析方法 净现值法的计算和分析基础是每年的现金流量,这是一个同时受到多个随机输入变量影响的随机变量。其中,输入变量包括具有不同概率分布规律的销售数量、销售价格、单位变动成本等。利用蒙特卡洛模拟分析模型,计算机根据已知的各输入变量概率分布规律,随机选择每一个输入变量的数值,然后将这些数值 加以综合,计算出项目的净现值并储存到计算机的记忆中。接着,随机选取第 2 组输入值,计算出第 2 个净现值。重复该过程 100 次或 1000 次,产生相应的 100 个或 1000 个净现值,就可以确定净现值的有关数字特征(如均值、标准差等)。其中,均值可以作为项目预期盈利能力的衡量指标,而标准差作为项目风险的评价指标。同时利用 Excel 的作图功能,还可得到净现值随机变量的概率密度柱形图和累计概率分布图,进一步为投资决策提供相关信息。 三、运用 Excel 进行投资项目风险模拟分析 为了说明 Excel 在投资项目风 险模拟分析中的应用过程,现举例说明如下: [例 ]某公司准备开发一种新产品。有如下预测:初始投资额为 400 万元(新机器),使用期为 5 年,采用直线折旧政策,期末残值为 0。运营后,销售部门预测:第 1 年产品的销量是一个服从均值为 150 万件而标准差为 40 万件的正态分布,以后每年增长 10%,而销售价格是一个服从均值为 6 元 /件、标准差为 2 元 /件的正态分布。生产部门预测:为了维持正常的运营,需要在期初投入营运资本 50 万元。每年的固定经营成本为 150 万元,新产品的单位变动成本是一个服从从 2 元 /件到 4 元 /件均匀分布的随机变 量。如果该投资项目的贴现率为 10%,所得税税率为 35%,试分析此投资项目的风险。 1.输入、输出随机变量分析 项目净现值的大小为输出结果,是每期净现金流量现值之和。根据每期净现金流量的构成与特征不同,计算公式如下: 期初净现金流量(投资支出) =投资金额(设备的购置费与安装运输费) +增加的营运资本经营期期间净现金流量 =(销售收入 -经营成本 -折旧)×( 1-税率) +折旧 =(销售量×销售价格 – 固定经营成本 – 单位可变成本×销售量 – 折旧)×( 1-税率) +折旧 期末净现金流量 =残值的税后收入 +期末回 收的营运资本 项目净现值为各期净现金流量的现值之和(包括投资支出与收入)。 在经营期期间,由于期间净现金流量的高低受到销售量、销售价格、成本(包括固定成本、变动成本)的共同作用,而作为输入变量的销售量、销售价格和变动成本,是服从一定概率分布的随机变量,因此,项目净现值也是一个由以上各随机变量共同决定的随机变量,对此投资项目的风险分析即为对项目净现值的不确定性分析。采用蒙特卡洛模拟,输出变量就是各期净现金流量的净现值。 2.在 Excel 中建立原始数据和输入相关参数(如图 1 所示) 3.生成符合分布规律的随机输入变量(包括销售量、销售价格和单位变动成本) 本例中的随机输入变量有 3 个:服从正态分布的销售量(单元格 B14)和销售价格(单元格 B15)、 均匀分布的单位变动成本(单元格 B16),其各自的分布参数来自图 1相应单元格中的数值,生成随机数的公式如图 2所示。 其中,单元格 B14 和单元格 B15 调用了 Excel 内置的生成正态分布随机数函数NORMINV()和生成大于 0 小于 1 的均匀分布随机数函数 RAND(),分别生成了均值为 150(单元格 B4)、标准差为 40(单元格 B5)的正态分布随机数和均值为 6(单元格 B6)、标准差为 2(单元格 B7)的正态分布随机数。单元格 B16 中公式生成的是 2(单元格 B10)至 4(单元格 B9)的均匀分布随机数。 4.建立项目每 期净现金流量相关数据计算区,并计算项目投资净现值 首先求出投资期期初的净现金流量(流出)(单元格 D15),期初投资等于设备的购置费用(单元格 D2)与投入的营运资本(单元格 D3)之和。 在经营期期间,第1 年的销量(单元格 E4)和销售价格(单元格 E5)以及可变成本(单元格 E8)分别引用了在第 3 个步骤中所计算出的随机数。其他各年的相关数据可由公式复制得到。根据每年经营净现金流量的计算公式,可得到每年的净现金流量。在项目结束期,还需在经营现金流的基础上,加回期初投入的营运资本。 由于每期净现金流量不等, 所以采用 Excel 内置财务函数 NPV()函数进行计算。本例在单元格 E17 中输入项目净现值的计算公式为: =NPV(B11,E15:I15)+D15。 5.对步骤 3 中的随机计算结果进行模拟试验,并记录试验结果进行统计分析在Excel 中,如果直接按 F9 键,单元格 E17 中的数值就会发生变化,这时可将该试验结果记录到工作表的一个空白表格区域。重复该手工操作多次,可以获得所需要的试验结果样本。此种方法尽管可行,但是对于大样本试验结果的 生成,是不可取的。利用 Excel 中所提供的模拟运算表对虚自变量进行分析技术,可有效地解决该问题。本例题中选择完成 1000 次试验,生成一个统计上可称之为大样本的试验结果,基本可以满足大多数统计假设和推论。 试验结果区的位置在单元格区域 E21 至 E1020 中。具体操作如下: 在单元格 E20 中输入计算公式: =E17,单元格区域 D21 至 D1020 中输入模拟次数( 1~1000)。选定单元格区域 D20 至 E1020,选择“数据 /模拟运算表”命令,在出现的“模拟运算表”对话框中,单击“输入引用列的单元格”的输入框后,单 击工作表中的任意空白单元格(如本例中的 D17)。单击“确定”按钮后,即可在该区域内获得指定目标变量(净现值)和试验次数( 1000 次)的模拟试验结果(如图 4 所示)。 6.生成统计分析数据 在获得 1000 次试验结果基础上,利用 Excel 内置的统计分析函数均值函数 AVERAGE()、标准差函数 STDEV()、最大值函数 MAX()、最小值函数 MIN(),计算有关的统计量。计算公式如图 5 所示。 7.生成投资项目净现值各可能取值的概率、累积概率有关数据 为了绘制净现值的概率分布图、累积概率分布图以及投资项 目大于某一净现值的概率图,需要计算出净现值在各个取值范围内的概率,累积概率等数据,本例中(单元格区域 G20至 K50)将净现值的取值范围(最大值与最小值之差)均等的分成 30 个小区域,分别计算在各取值区域中净现值出现次数、频次、累积频次。具体计算公式如图6所示。 相邻的两个 NPV值之间的距离为取值范围总长度的 1/30,因此,在单元格 G20 中为 1000 次随机试验结果中的最小值,与之相邻的单元格G21 的计算公式是在单元格 G20 基础上加上一个固定的步长 ($B$20-$B$21)/30。同样,其他的刻度分别在前一刻度计算结果的基础上加上相同的步长即可。 1000 次随机试验结果,随机分布在所划分的 30 个区域之中,需要计算在每个净现值取值区域中试验结果出现的次数(在 大样本下可近似看作是频次)。频次的计算采用了 Excel 的统计函数 FREQUENCY()。具体的操作为:选中单元格区域 H20至 H50 ,利用函数向导,对该区域输入计算公式:=FREQUENCY(E14:E1013,H20:H50),同时按 ctrl-shift-enter 三键,在该区域中会自动出现所有净现值取值区域中净现值出现的频次。 频率的计算可在各取值区域出现频次的基础上,直接除以随机试验的总次数 1000,即在单元格 I20中输入计算公式: =H20/COUNT($E$14:$E$1013),并将该公式往下拖 动复制到单元格区域 I21至 I50中,得到与频次相应的频率。 累计频率的计算比较简单。首先在单元格 J20 中输入计算公式: =I20,在单元格 J21 中输入计算公式:=J20+I21,然后直接将单元格 J21 中的计算公式复制到单元格区域 J21 至 J50,即可得到相应净现值取值区域的累积概率。小于某一 NPV 数值的概率直接等于 1减去相应区域的累积概率。 8.利用 Excel 的绘图功能,分别绘制模拟试验净现值的概率分布图(如图 7 所示)、累积概率分布图(如图 8 所示)和大于某净现值的概率分布图(如图 9 所示),从而为投资决策提供依据。 其中,投资项目净现值概率分布图的 X 轴取值区域为单元格区域 G20 至 G50, Y 轴取值区域为单元格区域 I20 至 I50;累计概率分布图 X 轴取值区域为单元格区域G20 至 G50, Y 轴取值区域为单元格区域 J20 至 J50;大于某一净现值概率图 X轴取值区域为单元格区域 G20 至 G50, Y 轴取值 区域为单元格区域 K20 至 K50。 四、模型分析总结利用 Excel 的各种函数、分析工具和作图功能,设计蒙特卡洛风险模拟分析模型,通过大量的随机模拟试验,得到随机目标变量净现值的分布规律,能够为投资决策提供必要的依据。相对于常见的概率分析、敏感性分析方法,更加深入考察了决策变量的可能取值,从而决策信息更加全面和客观。 Excel的应用,使得快速获取大量随机试验结果成为可能,是风险分析中的有效工具。