- 相關推薦
基于Ｅｘｃｅｌ的投資項目風險模擬分析
［摘 要］ 借助蒙特卡洛模擬分析方法，在考察投資決策變量（如銷售量、銷售價格、單位變動成本等）概率分布規律的基礎上，對目標變量投資項目凈現值的取值情況進行大量隨機試驗，獲取相關風險分析的統計信息，為投資決策提供有力支持。而Excel的運用，使得快速取得隨機試驗結果成為可能。�。坳P鍵詞］Excel；投資項目凈現值；風險分析；蒙特卡洛模擬
一、引 言
對投資項目凈現值進行風險分析，是資本預算中的一個重要環節。源自于卡西諾賭博計算方法的蒙特卡洛模擬分析（Monte Carlo Simulation），將敏感性和輸入變量的概率分布緊密聯系，與常見的分析方法（如敏感性分析、情景分析）相比，充分考慮各變量取值的隨機性，通過隨機模擬技術，給出了投資項目凈現值可能取值的范圍和不小于某一特定值的概率，為投資決策提供了更為科學的決策依據。運用Excel所提供的數學、財務及其他函數，以及分析工具和圖表功能，可以很好地解決該問題。
二、項目投資決策分析方法
1． 確定性條件下的投資決策
基于貼現現金流技術的凈現值法，是投資項目評估最為常見的方法。該法按照項目的資本成本計算每一年的現金流量（包括現金流入量和現金流出量）現值，并將貼現的現金流量匯總，得到項目的凈現值（Net Present Value，NPV）。如果項目的凈現值大于零，則接受該項目；反之，則放棄該項目。
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． 輸入、輸出隨機變量分析
項目凈現值的大小為輸出結果，是每期凈現金流量現值之和。根據每期凈現金流量的構成與特征不同，計算公式如下：
期初凈現金流量（投資支出）=投資金額（設備的購置費與安裝運輸費） 增加的營運資本
經營期期間凈現金流量=（銷售收入-經營成本-折舊）×（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中所提供的模擬運算表對虛自變量進行分析技術，可有效地解決該問題。本例題中選擇完成1 000次試驗，生成一個統計上可稱之為大樣本的試驗結果，基本可以滿足大多數統計假設和推論。
試驗結果區的位置在單元格區域E21至E1020中。具體操作如下：
在單元格E20中輸入計算公式：=E17，單元格區域D21至D1020中輸入模擬次數（1~1 000）。選定單元格區域D20至E1020，選擇“數據/模擬運算表”命令，在出現的“模擬運算表”對話框中，單擊“輸入引用列的單元格”的輸入框后，單擊工作表中的任意空白單元格（如本例中的D17）。單擊“確定”按鈕后，即可在該區域內獲得指定目標變量（凈現值）和試驗次數（1 000次）的模擬試驗結果（如圖4所示）。
6． 生成統計分析數據
在獲得1 000次試驗結果基礎上，利用Excel內置的統計分析函數均值函數AVERAGE（ ）、標準差函數STDEV（ ）、最大值函數MAX（ ）、最小值函數MIN（ ），計算有關的統計量。計算公式如圖5所示。
7． 生成投資項目凈現值各可能取值的概率、累積概率有關數據
為了繪制凈現值的概率分布圖、累積概率分布圖以及投資項目大于某一凈現值的概率圖，需要計算出凈現值在各個取值范圍內的概率，累積概率等數據，本例中（單元格區域G20至K50）將凈現值的取值范圍（最大值與最小值之差）均等的分成30個小區域，分別計算在各取值區域中凈現值出現次數、頻次、累積頻次。具體計算公式如圖6所示。
相鄰的兩個NPV值之間的距離為取值范圍總長度的1/30，因此，在單元格G20中為1 000次隨機試驗結果中的最小值，與之相鄰的單元格G21的計算公式是在單元格G20基礎上加上一個固定的步長($B$20-$B$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的繪圖功能，分別繪制模擬試驗凈現值的概率分布圖（如圖7所示）、累積概率分布圖（如圖8所示）和大于某凈現值的概率分布圖（如圖9所示），從而為投資決策提供依據。
其中，投資項目凈現值概率分布圖的X軸取值區域為單元格區域G20至G50，Y軸取值區域為單元格區域I20至I50；累計概率分布圖X軸取值區域為單元格區域G20至G50，Y軸取值區域為單元格區域J20至J50；大于某一凈現值概率圖X軸取值區域為單元格區域G20至G50，Y軸取值區域為單元格區域K20至K50。
四、模型分析總結
利用Excel的各種函數、分析工具和作圖功能，設計蒙特卡洛風險模擬分析模型，通過大量的隨機模擬試驗，得到隨機目標變量凈現值的分布規律，能夠為投資決策提供必要的依據。相對于常見的概率分析、敏感性分析方法，更加深入考察了決策變量的可能取值，從而決策信息更加全面和客觀。Excel的應用，使得快速獲取大量隨機試驗結果成為可能，是風險分析中的有效工具。
【基于Ｅｘｃｅｌ的投資項目風險模擬分析】相關文章：
Ｅｘｃｅｌ在投資決策中的應用12-08
基于ＥＶＡ的投資決策分析03-20
基于期權理論的資本投資決策分析03-21
基于分組主成分法的科技板投資價值分析03-21
基于粗集的神經網絡的項目風險評估02-27
基于汽車駕駛模擬器的動力學建模分析03-07
工程項目風險分析、控制與轉移03-23