Excel环境下蒙特卡罗模拟法应用

　　企业在进行项目投资决策时，需要全面考虑影响投资决策的各项因素，其中最关键的因素包括项目原始投资额、经营期各期的净现金流量、折现率及项目预计的寿命周期。由于企业财务管理的外部环境变化莫测，增加了投资决策各影响因素的不确定性，投资决策时需要全面评价项目将面临的财务与经营风险。在传统的投资决策评价方法中，由于手工环境下无法完成复杂环境下的数据运算工作，对于投资决策中的不确定问题通常采用期望值分析，所得到的结果实际上仍然是确定型的，由于遗漏了许多信息，有时可能对决策产生误导；对于不确定型变量的风险评价多采用敏感性分析，而敏感性分析只能是在假定其他因素不变的前提下，某一特定因素变化所导致的评价指标（本文以净现值作为评价指标）的变化程度，无法反映各影响因素同时变化引起评价指标的变化程度。随着会计信息化的普及，在计算机环境下，借助EXCEL软件提供的函数功能，本文拟以固定资产项目投资决策为例，应用蒙特卡罗模拟法评估不确定环境下的项目投资决策的盈利能力和风险水平。 　　一、蒙特卡罗模拟法原理及步骤 　　（一）蒙特卡罗模拟法的基本原理 “蒙特卡罗”分析源自赌场赌博的计算方法，是情景分析法中的一种，在该分析法下，被分析的项目将面临大量的情景。在每一种情景下，计算机为每个变量随机选择一个数值，然后，运用这些数据计算项目的净现值，并将这些净现值存储到计算机中。接下来，再为输入变量随机选取第二组数据，并计算出第二个净现值。通过成千上万次的模拟，可以获得相应概率的分布，通过对大量实验样本进行统计规律分析，得到满足一定精度的结果。通过蒙特卡罗模拟，可以对财务管理中存在的大量不确定与风险型问题进行有效分析，解决常用决策方法所无法解决的难题，从而有利于不确定环境下的决策。 　　（二）蒙特卡罗模拟法分析步骤 以财务管理中的固定资产投资项目为例，蒙特卡罗模拟的分析步骤如下：分析评价参数的特征，并根据历史资料或专家意见，确定随机变量的某些概率分布规律；按照一定的统计分布规律，在计算机上产生随机数，如本文采用正态分布产生初始投资额，年净现金流量、均匀分布产生折现率、项目寿命周期，在此基础上，建立相对应的数学模型；通过成千上万次计算机模拟运算，得到足够数量的参数样本值，根据这些参数样本值，进行统计特征分析，求出项目净现值指标值的概率分布；根据项目参数样本值（净现值）的均值和概率分布指标，分析评价投资项目预期的盈利能力和风险水平。 　　（三）基于Excel的蒙特卡罗模拟需使用的函数Excel软件内含大量的财务与统计函数，可以实时反映各种变量之间的变化，获得不确定环境下的目标函数变动规律，是进行蒙特卡罗模拟较为理想的软件，在进行蒙特卡罗模拟运算时，主要采用的函数包括：RAND：是一个Excel随机数字发生器，产生0到1 之间的一个随机数；NORMINV：是一个返回给定概率、正态分布的均值和标准差的区间点；INT：将数值向下取整为最接近的整数；AVERAGE：计算指标的平均数；STDEV：估算基于样本的标准偏差；MAX：计算样本最大值；MIN：计算样本最小值；NPV：计算投资项目的净现值；INDEX：返回表格或数组中的元素值，此元素由行号和列号的索引值给定；MATCH：返回在指定方式下与指定数值匹配的数组中元素的相应位置。 　　二、实例演示 　　（一）投资项目基本资料某固定资产投资项目只有一个，其初投资额遵循正态分布，期望值μ=2000万元，标准方差σ=100万元，项目折现率服从均匀分布，并在8%～10%之间变化，投资当年即可获得正常收益。年净现金流量估计服从正态分布，期望值μ=300万元，标准方差σ=25万元，项目寿命周期估计12年～15年，项目报废时残值为零。试确定计算NPV并评价项目的可行性。 　　（二）根据项目参数特征选定能够实现相关参数变量的函数 由于上述参数分别符合正态分布与均匀分布，故采用NORMINV、RAND函数来产生随机数，至于项目评价指标NPV则采用相应的函数计算公式，相关参数的表现形式如下： 　　（1）项目期初投资：NORMINV（RAND（），2000，100）； 　　（2）项目折现率：（8+2*RAND（））/100； 　　（3）项目年净现金流量：NORMINV（RAND（），300，25）； 　　（4）项目寿命周期：12+INT（3*RAND（）） 　　（5））项目净现值NPV：NPV（rate，value1，valu2，…） 　　（三）模拟运算过程 具体包括： 　　（1）建立一个如图1所示的工作表， 在单元格中输入项目的基本资料，其中单元格C3、C4、C5、C6内的数字是在其合理的取值范围内随机选定的。 　　（2）在单元格C7内输入公式计算净现值。由于项目寿命周期内的年净现金流量相等，所以可以用公式计算：净现值（NPV）=-C3+PV（C5，C6，-C4），得到图2所示的计算结果： 　　（3）接下来需要解决的问题是在单元格C3、C4、C5、C6中键入适当的公式反映问题规定的项目初始投资额、年净现金流量、折现率和项目寿命周期四个随机变量的数字特征。在单元格C3、C4、C5、C6中分别键入以下公式以生成符合问题要求的随机变量： 　　C3：= NORMINV（RAND（），2000，100）； 　　C4：=NORMINV（RAND（），300，25）； 　　C5：=（8+2*RAND（））/100； 　　C6：=12+INT（3*RAND（））。 　　输入完上述四个参数变量计算公式后，在单元格C7（见图3）中看到的是项目初始投资额、年净现金流量、折现率和项目寿命周期这四个随机变量的一组特定值所代表的一次“实验”下，对于所研究的投资项目求得的一个具体的净现值数值。设计一种方法来将该实验重复大量次数（例如5000次），并将这5000次实验中求得的净现值记录在EXCEL工作表的一个范围内，是问题的关键。 　　在任意一个空白单元格如E2中键入公式“=C7”，将其以相等的方式与模型中的净现值单元格链接起来，再在D3：D5002区域中利用数据填充功能生成从1到5000这样5000个整数，然后选中D2：E5002区域，选择主菜单栏上“数据”命令中的“模拟运算表”选项，在接着出现的“模拟运算表”对话框中点击“输入引用列的单元格”栏右侧的“ ”图标，弹出“模拟运算表――输入引用列的单元格”对话框，再点击任意一个单元格（例如B1），“模拟运算表――输入引用列的单元格”对话框中将显示“B1”，再点击“ ”图标，回到“模拟运算表”对话框，单击“确定”按钮。就可以看到（见图4，由于模拟图太大，只截取部分数据），在E3：E5002中就会生成“实验”重复5000次所得到的净现值数据。 　　（4）由于上述5000次的模拟数据是以随机的形式产生的，因而需要对这5000个净现值数值进行统计分析，寻找其统计规律。在单元格C8、C9中求出其均值和标准差。在这两个单元格中分别键入公式：C8：=AVERAGE（E3：E5002），C9：=STDEV（E3：E5002）。得到图5，图5中E列的数据与图4中不同，这是因为E列的5000个净现值数据是“活”的，只要在工作表中随便做一次修改数据的操作，这些数据就会全部改变一次，这相当于重新再做了5000次实验，这时在单元格C8和C9中的均值和标准差数值会重新修改一次，而且数值变动肯定不会很大，但在每次实验中E列的净现值数字则会发生很大的变化。从图5左右所显示的情况可以得到验证，图5左中求得的净现值均值和标准差分别是246和236 ，图5右中则是244和237，相差不到2万元，而两者的净现值分别是290和-243，一正一负，相差500多万元。

　　（5）为了获得进一步的统计分析资料，可以将工作表中E3：E5002区域的数据复制到一个新工作表的B3：B5002区域中，并且在此过程中将复制后生成的数据固定下来（相当于采集一个固定的实验样本）。利用这些数据制作5000个净现值的样本观测值的样本分布。在单元格D2和D3中使用MAX（）与MIN（）函数求出范围B3：B5002中的极大值和极小值。输入公式：D2：=MAX（B3：B5002），D3：MIN（B3：B5002）。得到图6所示结果： 　　（6）以图7两个极值作为参考，在E3中键入“-470”，在E4中键入“-420”，然后选中E3：E4，利用数据填充功能在E3：E37区域生成一个间隔为50的递增整数系列（-470~1230），如图8所示： 　　（7）选择主菜单栏上“工具”命令中的“数据分析”选项，然后在数据分析对话框中选中“直方图”，然后单击“确定”按钮，在“直方图”对话框中将“输入区域”设置为B3：B5002，将“接收区域”设置为E3：E37，将“输出区域”设置为当前工作表的单元格F2（即从F3开始的一个范围），单击“确定”按钮后，在F2：F37区域中就会得到图8所示的结果，这些数据显示在5000次实验中净现值落在各个相应的间隔内的次数。 　　（8）激活单元格G39，点击“”即自动求和按钮，得到总实验数5000。在单元格H3中输入公式“=G3/5000”。利用数据填充功能自动填充H4：H 37区域，即可获得在该项区域中与各个间隔相应的净现值发生频率。结果见图9。 　　（9）利用这个区域内得到的数据制作一个直方图，就得到了被研究的投资项目的净现值样本分布图，结果见图10。 　　（10）计算5000次实验中净现值大于零的发生概率。首先将B3：B5002区域内的5000个数字做一次从大到小降序的排序操作。然后在A3：A5002区域生成一个初值为0.0002（五千分这一）、终值为1，步长为0.0002的数据系列，再在单元格D5中生成一个表示净现值下限值的数字“0”，在单元格C6中键入公式“=INDEX（A3：A5002，MATCH（D5，B3：B5002，-1）”。这样，在单元格C6中就得到了5000次实验中净现值大于C5中的下限值（即大于零）的那些实验发生的频率，也就是净现值大于零的发生概率，其数值为0.855。见图11。 　　利用Excel软件提供的函数功能，用蒙特卡罗模拟法在计算机模拟5000次，得到拟投资项目净现值NPV满足如下统计特征：最大值1192万元、最小值465万元、平均值246万元、标准差235万元，净现值NPV出现在0～1230的概率为85.5%，净现值NPV出现负数的概率为14.5%，净现值NPV的概率分布图如图11所示，由于NPV出现正数的概率为85.5%，因此该投资项目是可行的，但也存在一定的风险。蒙特卡罗模拟法与现行常用的确定型计算方法相比，能够揭示确定型计算方法所无法取得的大量有用的信息，对全面深刻了解不确定环境下的财务管理问题是非常有益的。除了本文所分析的固定资产投资不确定性问题以外，同样可以将蒙特卡罗模拟法推广到财务管理中的其他不确定性问题，如本量利、最佳现金持有量、筹资决策等。 　　 　　参考文献： 　　［1］刘燕：《技术经济学》，电子科技大学出版社2007年版。 　　（编辑余俊娟） 　　 　　注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文