Ｅｘｃｅｌ在蒙特卡羅模擬分析中的應用

［摘 要］ 蒙特卡羅模擬法是進行項目不確定性分析的一種常用方法，Excel是具有強大數據分析功能的軟件，可以很好地解決手工計算煩瑣的問題。基于此，本文結合案例研究了如何運用Excel進行蒙特卡羅模擬分析。

［關鍵詞］ Excel；蒙特卡羅模擬；數據分析

［中圖分類號］F232；F275［文獻標識碼］A［文章編號］1673-0194（2008）07-0044-04

一、蒙特卡羅模擬分析概述

在工程項目的經濟評價中，通常假定有關數據都是不變的。但項目分析中采用的數據大部分來自預測或估計，它們在很大程度上受到未來可變因素的影響；如果把它們作為固定值看待進行分析，計算結果將與實際情況不符，從而帶來某種程度的風險。近年來的經濟評價中越來越注意分析研究這種風險的程度和可能性，蒙特卡羅模擬法就是解決這類問題十分方便的方法。

采用蒙特卡羅模擬法可以直接從不確定性因素變化情況入手，通過建立模型直接模擬問題，從而使不確定性因素對項目經濟效果的影響直接反映在對項目經濟評價指標的計算中。由于采用了隨機抽樣獲取不確定性因素的大量觀測值，將其代入模型中進行模擬分析，不僅可以獲得評價指標的概率分布信息，還可以從指標的離散程度、變異系數等方面來評價項目的風險；同時可以通過事先設定置信度來控制模擬的準確度，所得的信息完整性遠遠高于其他分析方法。

二、利用Excel進行蒙特卡羅模擬分析的優勢

蒙特卡羅模擬技術的應用是與計算機技術的發展密不可分的，只有借助計算機高效、便捷的計算功能，蒙特卡羅模擬在實踐中的應用才能成為可能。經過最近十幾年計算機軟硬件技術的飛速發展，Excel軟件已成為大眾首選的決策分析工具軟件。在它的幫助下，管理人員完全可以根據實際問題，在相應的理論基礎上，自己動手建立計算模型并對這些模型進行各種深入的分析，特別是對于蒙特卡羅模擬這類需要建立在一定模型基礎上進行分析的方法來說，Excel 在建模、數據分析等方面的優勢更是得到了充分的體現和發揮：

第一，Excel具有十分強大的數據分析功能，并且與各種數據庫具有良好的接口，這使得操作者可以通過簡單的操作從具有不同結構的數據庫中獲取所需要的數據，利用數據分析功能直接進行一些復雜的計算、統計工作等。

第二，Excel具有大量內建函數，操作者可以利用Excel提供的各種函數公式，通過確定參數的數值，按特定的順序或結構自動執行復雜計算，大大簡化了計算模型建立的過程，并有助于提高模型的準確性，特別是Excel的財務函數、統計函數等對于財務分析相關模型的建立有很大的幫助。

第三，Excel還具有良好的圖形顯示能力，將這種功能與其他功能相結合，決策者可以在決策模型的基礎上經過簡單的操作制作高質量的動態圖形，將抽象的決策模型與直觀生動的圖形完美結合，從而以最有效的方法幫助決策者進行各種必要的決策分析。

三、利用Excel進行蒙特卡羅模擬分析的實施

利用Excel進行蒙特卡羅模擬分析首先應根據待解決問題的具體情況，分析影響項目經濟效果的不確定性因素，確定與決策相關的目標變量與隨機變量；然后根據變量分析結果在電子表格中建立經濟評價指標的計算模型，同時根據項目情況預測隨機變量概率分布；通過計算機程序生成符合特定分布隨機變量的觀測值，并將其代入模型中進行模擬計算，從而獲得目標變量大量的觀測值；計算目標變量各種統計指標并收集頻數分布，生成直方圖，對模擬結果進行解釋。利用Excel進行蒙特卡羅模擬的具體步驟，如圖1所示。

在利用Excel進行蒙特卡羅模擬的過程中，應注意以下幾個方面：

1. 隨機變量概率分布的選擇

對既定分布的隨機變量進行隨機抽樣產生隨機變量觀測值在蒙特卡羅模擬法中占有非常重要的地位，選擇確定隨機變量的概率分布類型，才能利用計算機產生符合特定概率分布的隨機數，進而才能利用隨機數運行模型進行模擬分析。

對于進行模擬分析的許多項目來說，大都可以利用經驗數據來選擇模型中隨機變量概率分布的類型。無論是歷史數據，還是努力收集的其他數據，都可以通過這些數據建立直方圖，從考察直方圖入手，尋求特定分布的特有形狀，從而確定一個經驗分布。

2. 特定分布隨機數的產生

從概率分布中得到樣本的基礎在于隨機數這一概念，生成符合概率分布的隨機數是蒙特卡羅模擬的關鍵步驟之一。

可以利用Excel 內建函數生成特定分布隨機數，如可以利用Excel的 RAND（）函數生成一個在（0，1）區間中均勻分布的隨機數，也可以利用RANDBETWEEN（a，b）函數生成一個在[a，b]區間中取整數值的均勻分布隨機數。

3. 模擬次數的選擇

模擬的重復次數直接影響模擬結果的質量，通常，重復的次數越多，對輸出目標變量各項統計特征的描述就越精確，對于模擬次數的確定，需要運用統計中有關樣本容量的知識。有時也會根據歷史經驗取一個數值。

4. 模擬運行結果的記錄

在進行蒙特卡羅模擬分析時有一個關鍵問題必須解決，即將在各個變量或參數不同隨機值條件下多次運行模型所產生的目標變量的相應取值記錄下來，這些目標變量值正好就是進行模擬結果分析的數據來源。

5. 模擬結果的分析

實現蒙特卡羅模擬分析還有一個非常重要的問題就是對模擬運行結果進行統計分析，內容包括樣本均值、樣本標準差等在內的各種描述性統計指標的計算，此外，還應當分析樣本數據的頻數分布，根據樣本數據繪制直方圖，以及求出數據的某些具有特殊意義的概率值，如投資項目中凈現值大于零、內部收益率大于基準收益率的概率值等。

（1）主要統計指標計算方法。

①利用Excel內建函數計算統計指標。Excel內建的統計函數提供了數據統計指標計算的功能，如使用VAR（data range）計算樣本方差、STDEV（data range） 計算樣本均值、CORREL （array1，array2） 計算相關系數等。

②利用Excel數據分析功能計算統計指標。首先需要選擇Excel“工具”菜單下的“數據分析”項，在其對話框中選中“描述統計”項，然后屏幕將出現一個描述統計對話框，選中對話框中的相關輸入輸出信息，就可以得到相應的統計結果。

利用數據分析功能可以生成一個完整的描述統計量列表，但是利用內建函數計算統計量較為靈活方便，更值得一提的是Excel的自動重算功能，只要數據發生變化，所返回的統計值量也會發生相應的變化，從而使數據與統計量之間保持著動態的聯系。

（2）利用Excel的Frequency函數建立直方圖。在進行操作之前，需要先確定待分析數據的極大值與極小值，以此為參照來規定待分析數據區間的劃分寬度，然后在工作表的一列范圍內，按升序鍵入作為劃分區間分界點的分格數據，也就是接收數據。

接收數據確定后，就將一個與接收數據范圍具有相同高度的一列單元格選黑，并鍵入公式Frequency（Data-array，Bins-array），將被分析的樣本數據所在范圍與接收數據所在范圍的單元格引用分別作為該函數的第一和第二個參數，然后按“Ctrl+Shift+Enter”組合鍵，這樣在選黑的單元格范圍內便可得到樣本出現在各個子區間的頻數了。

創建了頻數分布以后，就可以利用Excel柱形圖的圖表向導創建直方圖了。這樣，頻數分布和數據建立了聯系，由此生成的樣本數據直方圖就是可以隨樣本數據變化而自動更新的“活”的直方圖了。

四、案例分析

1. NPV（凈現值）計算函數簡介

Excel的內建函數具有強大的數據計算和數據分析功能，特別是NPV（凈現值）計算函數對于研究利用 Excel實現蒙特卡羅模擬在財務評價上的應用有很大幫助。

NPV函數表達式為：NPV（Rate，Value1，Value2，…）

式中，Rate表示各期現金流折算成當前值的貼現率，它的取值在各期中應是固定不變的，Valuel，Value2，…代表支出或收入的1~29個參數，它們在時間上必須具有相等間隔，并且都發生在期末。NPV函數中Value指定的是每一周期期末發生的現金流，如果第一個周期的期初有另外的現金流，那么在計算時，需要將這一現金流加到NPV函數返回值中，這樣才能計算出正確的凈現值。

2. 模擬變量構成分析

不管是對凈現值的預測，還是對內部收益率以及投資回收期的預測，都與項目計算期內各年凈現金流量有密不可分的關系，所以應當從項目的凈現金流量從手，分析凈現金流量的構成要素及其計算模型。各年的現金流量Yt的計算公式為：Y0 =投資（負值），Yt =（R-C-Dt）（1-Te）+Dt （t =1，2，…，n）。其中，R表示年收入，C表示年支出，Te表示稅率，采用直線折舊法進行，即折舊Dt =（投資-殘值）/壽命期（t =1，2，…，n）。

3. 模擬變量構成及概率分布

某投資項目期投資、壽命期、殘值以及各年的收入、支出，以及應付稅金的稅率、項目的資本成本等都是獨立的隨機變量，概率密度函數如表1所示。

表1 模擬變量構成及概率分布

4. 各獨立變量的模擬過程

（1）將概率對應的可能值標準化為0~99之間的數，投資概率及對應的隨機數如表2所示，其他變量的轉化方法與之相同。

表2 投資概率及對應的隨機數

（2）得到各獨立變量的模擬值，下面仍以投資為例對模擬的過程進行說明。

首先使用RANDBETWEEN（0，99）函數在A31:A5030區域產生5 000個隨機數，然后使用VLOOKUP（）函數搜索表格首列滿足條件的元素，確定待檢查單元格在區域中的行序號，再進一步返回選定單元格的值。例如，在A31產生一個隨機數61，然后使用VLOOKUP（A31，$C$3:$D$5，2）就可以找到隨機數對應的投資額550。

將各獨立變量進行模擬后，運用凈現金流量的計算公式，得到其模擬值，然后運用凈現值計算公式NPV，在Q31:Q5030區域得到凈現值的模擬值。

5. 運用Excel函數計算凈現值模擬運算結果（見表3）

表3 凈現值模擬計算結果

6. 利用Excel的Frequency函數建立直方圖（見圖2）

五、結 論

利用 Excel 程序可以完成對項目經濟效果的蒙特卡羅模擬分析，獲得經濟評價指標的各項統計信息，不僅可以從經濟評價指標高于或低于基準指標的概率上來分析項目的可行性，還可以從指標的變異系數、離散程度等方面來分析項目的風險情況。因此利用Excel對項目經濟效果進行蒙特卡羅模擬分析所得的信息比較全面、科學，根據這些信息投資者可以更加科學、合理地判斷項目投資的風險性。

主要參考文獻

［1］ 劉清志. 石油技術經濟學［M］. 東營：中國石油大學出版社，1998.

［2］ ［美］ 戴維·R ·安德森，丹尼斯·J ·斯威尼.數據模型與決策［M］. 于淼譯. 北京：機械工業出版社，2003.

［3］ ［美］肯·布萊克等. 以Excel為決策工具的商務與經濟統計［M］.北京：機械工業出版社，2003.

［4］ 張瑞君. 計算機財務管理［M］. 北京：中國人民大學出版社，2005.

［5］ 韓良智. Excel在財務管理與分析中的應用［M］. 北京：中國水利水電出版社，2004.