Excel在蒙特卡罗模拟法中的应用.pdf

《Excel在蒙特卡罗模拟法中的应用.pdf》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、海峡财经导报/2006年/11月/9日/第021版财税·财务管理Excel在蒙特卡罗模拟法中的应用钟爱军蒙特卡罗模拟法(MonteCarlosimulation)是一种以数理统计理论为指导的模拟技术，其实质是按概率分布产生随机数的方法来模拟可能出现的随机现象。在投资项目不确定性风险分析中，我们可以将敏感性分析与各个不确定因素的概率分布进行结合来衡量投资项目风险，即根据随机数对影响因素的概率分布进行随机抽样，使模拟值涵盖各种可能出现的情况，再根据每次随机抽样值来计算投资项目的净现值期望值和标准差，以此衡量投资项目风险。蒙特卡

2、罗模拟方法的优点在于它是一种全值估计方法，它所产生的大量情景可以解决非线性、大幅波动问题；其缺点在于因依赖所确定的随机过程而有可能导致模型风险，当然如果模型做得正确，则此方法可能是衡量风险最全面的方法。在Excel中，利用蒙特卡罗模拟法分析投资项目风险的基本思路和方法是：估计影响投资项目净现值的各关键因素各种可能结果的概率，列每个因素根据其各种可能出现结果的概率分配相应的随机数。形成每个因素所服从的概率分布。利用Excel随机函数RANDBETWEEN(如果该函数不可用，并返回错误值#NAME?，请在Excel工作表中执行

3、“工具”—“加载宏”—“分析工具库”)产生随机数。然后利用VLOOKUP函数来查找对应随机数的变量数值，根据每个因素选定的数值。应用评价模型计算投资项目的净现值指标，完成一次模拟过程，将结果储存起来，重复地进行模拟计算，进行足够的模拟计算次数后，得到投资项目净现值的期望值、标准差及变差系数，从而判断投资项目的风险。案例：某公司计划投资一个项目用来生产A产品，A产品在未来3年的销售价不会发生变动，但销售量和单位变动成本有着很大的不确定性，其可能达到的水平值及发生的概率。试用蒙特卡罗模拟法对该投资项目进行风险分析。已经按计算要

4、求对概率和变量值进行分区，如以第1年销售量为例，随机数为0、30、60、90时对应销售量分别为50000件、60000件、70000件、80000件，也就是说当产生的随机数分别为0－29、30－59、60－89、90－99时，对应的销售量分别为50000件、60000件、70000件、80000件，对应的累计概率分别为0.20、0.55、085和1.00。用蒙特卡罗模拟法对该投资项目风险进行计算分析的具体步骤为：1、设计的计算分析模型。2、在A20单元格中录入第1年销售量的随机数计算公式：=RANDBETWEEN(0，99

5、)，并将此单元格公式复制到C20、F20、H20、K20、M20单元格中，以计算出第1年单位变动成本随机数以及第2年、第3年的销售量和单位变动成本的随机数。3、在B20、G20、L20单元格中分别录入第1年、第年、第3年销售量的可能值查找公式。B20＝VLOOKUP(A20，$D$8：$E$11，2)G20＝VLOOKUP(F20，$H$8：$I$11，2)L20＝VLOOKUP(K20，$L$8：$M$11，2)4、在D20、120、N20单元格中分别录入第1年、第2年、第3年单位变动成本的可能值查找公式。D20＝VLO

6、OKUP(C20，$D$12：$E$15，2)I20＝VLOOKUP(H20，$H$12：$1$15，2)N20＝VLOOKUP(M20，$L$12：$M$15，2)第1页共3页5、在E20、J20、020单元格中分别录入第1年、第2年、第3年的净现金流量的模拟计算公式。E20＝(B20*($L$5－D20)－$L$4)*(1－$L$3)＋($D$2－$D$4)／$D$3*$L$3J20＝(G20$($L$5－120)－$L$4)*(1－$L$3)＋($D$2－$D$4)／$D$3*$L$3020=(L20*($L$5－N

7、20)－$L$4)*(1－$L$3)＋($D$2－$D$4)／$D$3*$L$36、在P20单元格中录入：＝NPV($L$2，E20，J20，020)－$D$2，对投资项目的净现值进行第一次模拟计算。7、利用Excel的自动填充功能快速进行公式的复制：选取单元格区域A20：P20，拖动A20：P20区域右下角的填充柄至A5019：P5019(本例进行的模拟计算为5000次)。8、通过Excel的自动计算，得到5000次模拟计算结果，进一步进行统计分析，设计如表3所示的计算分析模型，其中的单元格计算公式为：P9＝AVERAG

8、E(P20：P5019)，P10=STDEV(P20：P5019)P11=COUNTIF(P20.P5019，"<0")／COUNT(P20：P5019)9、每按一次F9键，可以得到变化的模拟计算结果。由于销售量和单位变动成本这两个变动因素的概率次数较少，得到的结果有一定的差别，当模拟次数足够多时，结果