基于Excel的投资项目风险模拟分析.docx

基于Ｅｘｃｅｌ的投资项目风险模拟分析

　　［摘 要］ 借助蒙特卡洛模拟分析方法，在考察投资决策变量概率分布规律的基础上，对目标变量投资项目净现值的取值情况进行大量随机试验，获取相关风险分析的统计信息，为投资决策提供有力支持。而Excel的运用，使得快速取得随机试验结果成为可能。
　　［关键词］Excel；投资项目净现值；风险分析；蒙特卡洛模拟
　　一、引 言
　　对投资项目净现值进行风险分析，是资本预算中的一个重要环节。源自于卡西诺赌博计算方法的蒙特卡洛模拟分析，将敏感性和输入变量的概率分布紧密联系，与常见的分析方法相比，充分考虑各变量取值的随机性，通过随机模拟技术，给出了投资项目净现值可能取值的范围和不小于某一特定值的概率，为投资决策提供了更为科学的决策依据。运用Excel所提供的数学、财务及其他函数，以及分析工具和图表功能，可以很好地解决该问题。
　　二、项目投资决策分析方法
　　1． 确定性条件下的投资决策
　　基于贴现现金流技术的净现值法，是投资项目评估最为常见的方法。该法按照项目的资本成本计算每一年的现金流量现值，并将贴现的现金流量汇总，得到项目的净现值。如果项目的净现值大于零，则接受该项目；反之，则放弃该项目。
　　2．不确定性条件下的投资决策——蒙特卡洛风险模拟分析方法
　　净现值法的计算和分析基础是每年的现金流量，这是一个同时受到多个随机输入变量影响的随机变量。其中，输入变量包括具有不同概率分布规律的销售数量、销售价格、单位变动成本等。利用蒙特卡洛模拟分析模型，计算机根据已知的各输入变量概率分布规律，随机选择每一个输入变量的数值，然后将这些数值加以综合，计算出项目的净现值并储存到计算机的记忆中。接着，随机选取第2组输入值，计算出第2个净现值。重复该过程100次或1 000次，产生相应的100个或1 000个净现值，就可以确定净现值的有关数字特征。其中，均值可以作为项目预期盈利能力的衡量指标，而标准差作为项目风险的评价指标。同时利用Excel的作图功能，还可得到净现值随机变量的概率密度柱形图和累计概率分布图，进一步为投资决策提供相关信息。
　　三、运用Excel进行投资项目风险模拟分析
　　为了说明Excel在投资项目风险模拟分析中的应用过程，现举例说明
　　[例]某公司准备开发一种新产品。有如下预测：初始投资额为400万元，使用期为5年，采用直线折旧政策，期末残值为0。运营后，销售部门预测：第1年产品的销量是一个服从均值为150万件而标准差为40万件的正态分布，以后每年增长10%，而销售价格是一个服从均值为6元/件、标准差为2元/件的正态分布。生产部门预测：为了维持正常的运营，需要在期初投入营运资本50万元。每年的固定经营成本为150万元，新产品的单位变动成本是一个服从从2元/件到4元/件均匀分布的随机变量。如果该投资项目的贴现率为10%，所得税税率为35%，试分析此投资项目的风险。
　　1． 输入、输出随机变量分析
　　项目净现值的大小为输出结果，是每期净现金流量现值之和。根据每期净现金流量的构成与特征不同，计算公式
　　期初净现金流量=投资金额 增加的营运资本
　　经营期期间净现金流量=× 折旧
　　=× 折旧
　　期末净现金流量 = 残值的税后收入 期末回收的营运资本
　　项目净现值为各期净现金流量的现值之和。
　　在经营期期间，由于期间净现金流量的高低受到销售量、销售价格、成本的共同作用，而作为输入变量的销售量、销售价格和变动成本，是服从一定概率分布的随机变量，因此，项目净现值也是一个由以上各随机变量共同决定的随机变量，对此投资项目的风险分析即为对项目净现值的不确定性分析。采用蒙特卡洛模拟，输出变量就是各期净现金流量的净现值。
　　2． 在Excel中建立原始数据和输入相关参数

　　3． 生成符合分布规律的随机输入变量
　　本例中的随机输入变量有3个：服从正态分布的销售量和销售价格、均匀分布的单位变动成本，其各自的分布参数来自图1相应单元格中的数值，生成随机数的公式如图2所示。
　　其中，单元格B14和单元格B15调用了Excel内置的生成正态分布随机数函数NORMINV( )和生成大于0小于1的均匀分布随机数函数RAND( )，分别生成了均值为150标准差为40的正态分布随机数和均值为6、标准差为2的正态分布随机数。单元格B16中公式生成的是2至4的均匀分布随机数。
　　4． 建立项目每期净现金流量相关数据计算区，并计算项目投资净现值
　　首先求出投资期期初的净现金流量，期初投资等于设备的购置费用与投入的营运资本之和。
　　在经营期期间，第1年的销量和销售价格以及可变成本分别引用了在第3个步骤中所计算出的随机数。其他各年的相关数据可由公式复制得到。根据每年经营净现金流量的计算公式，可得到每年的净现金流量。在项目结束期，还需在经营现金流的基础上，加回期初投入的营运资本。
　　由于每期净现金流量不等，所以采用Excel内置财务函数NPV( )函数进行计算。本例在单元格E17中输入项目净现值的计算公式为：=NPV(B11,E15:I15) D15。

　　5． 对步骤3中的随机计算结果进行模拟试验，并记录试验结果进行统计分析
　　在Excel中，如果直接按F9键，单元格E17中的数值就会发生变化，这时可将该试验结果记录到工作表的一个空白表格区域。重复该手工操作多次，可以获得所需要的试验结果样本。此种方法尽管可行，但是对于大样本试验结果的生成，是不可取的。利用Excel中所提供的模拟运算表对虚自变量进行分析技术，可有效地解决该问题。本例题中选择完成1 000次试验，生成一个统计上可称之为大样本的试验结果，基本可以满足大多数统计假设和推论。
　　试验结果区的位置在单元格区域E21至E1020中。具体操作
　　在单元格E20中输入计算公式：=E17，单元格区域D21至D1020中输入模拟次数。选定单元格区域D20至E1020，选择“数据/模拟运算表”命令，在出现的“模拟运算表”对话框中，单击“输入引用列的单元格”的输入框后，单击工作表中的任意空白单元格。单击“确定”按钮后，即可在该区域内获得指定目标变量和试验次数的模拟试验结果。

　　6． 生成统计分析数据
　　在获得1 000次试验结果基础上，利用Excel内置的统计分析函数均值函数AVERAGE、标准差函数STDEV、最大值函数MAX、最小值函数MIN，计算有关的统计量。计算公式如图5所示。
　　7． 生成投资项目净现值各可能取值的概率、累积概率有关数据
　　为了绘制净现值的概率分布图、累积概率分布图以及投资项目大于某一净现值的概率图，需要计算出净现值在各个取值范围内的概率，累积概率等数据，本例中将净现值的取值范围均等的分成30个小区域，分别计算在各取值区域中净现值出现次数、频次、累积频次。具体计算公式如图6所示。

　　相邻的两个NPV值之间的距离为取值范围总长度的1/30，因此，在单元格G20中为1 000次随机试验结果中的最小值，与之相邻的单元格G21的计算公式是在单元格G20基础上加上一个固定的步长($B$20B$21)/30。同样，其他的刻度分别在前一刻度计算结果的基础上加上相同的步长即可。
　　1 000次随机试验结果，随机分布在所划分的30个区域之中，需要计算在每个净现值取值区域中试验结果出现的次数。频次的计算采用了Excel的统计函数FREQUENCY( )。具体的操作为：选中单元格区域H20至H50，利用函数向导，对该区域输入计算公式：=FREQUENCY(E14:E1013,H20:H50)，同时按ctrl-shift-enter三键，在该区域中会自动出现所有净现值取值区域中净现值出现的频次。
　　频率的计算可在各取值区域出现频次的基础上，直接除以随机试验的总次数1 000，即在单元格I20中输入计算公式：=H20/COUNT($E$14:$E$1013)，并将该公式往下拖动复制到单元格区域I21至I50中，得到与频次相应的频率。
　　累计频率的计算比较简单。首先在单元格J20中输入计算公式：=I20，在单元格J21中输入计算公式：=J20 I21，然后直接将单元格J21中的计算公式复制到单元格区域J21至J50，即可得到相应净现值取值区域的累积概率。小于某一NPV数值的概率直接等于1减去相应区域的累积概率。

　　8． 利用Excel的绘图功能，分别绘制模拟试验净现值的概率分布图、累积概率分布图和大于某净现值的概率分布图，从而为投资决策提供依据。
　　其中，投资项目净现值概率分布图的X轴取值区域为单元格区域G20至G50，Y轴取值区域为单元格区域I20至I50；累计概率分布图X轴取值区域为单元格区域G20至G50，Y轴取值区域为单元格区域J20至J50；大于某一净现值概率图X轴取值区域为单元格区域G20至G50，Y轴取值区域为单元格区域K20至K50。
　　四、模型分析总结
　　利用Excel的各种函数、分析工具和作图功能，设计蒙特卡洛风险模拟分析模型，通过大量的随机模拟试验，得到随机目标变量净现值的分布规律，能够为投资决策提供必要的依据。相对于常见的概率分析、敏感性分析方法，更加深入考察了决策变量的可能取值，从而决策信息更加全面和客观。Excel的应用，使得快速获取大量随机试验结果成为可能，是风险分析中的有效工具。