怎麼用 Excel 做蒙特卡洛模擬？

11-24

聽說可以用 excel 做。行么？有例子沒有。

想不到隨便一寫居然還有人關注，受寵若驚！

下面再寫一點，作為補充。

-------以下是原答案-------

可以。
下面是在Excel中模擬一隻股票價格的例子。假設股票價格
的對數收益率服從正態分布，均值為0，每日變動標準差為0.1，
模擬股票價格1年的路徑，過程如下：

用到兩個內置函數，即用rand()來產生0到1之間的隨機數，然後用norminv()來獲得服從既定分布的隨機數，即收益率樣本=norminv(rand(), 0, 0.1)。假定股票價格的初始值是100元，那麼模擬的價格就是 S=100 * exp(cumsum(收益率樣本))。
其中的cumsum（）不是Excel的內置函數，其意思就是收益率樣本的累積，每個時刻的值都是當前樣本及此前所有樣本的和，如，收益率樣本從單元格C3開始，當前計算C15對應的模擬價格，則模擬價格計算公式是：100 * exp(sum($C$3:C15))。
由此可以得到股票價格的一條模擬路徑。

其他非正態分布也可以通過類似方式得到分布的抽樣，即分布函數的逆函數，這些函數Excel都內置了。所以，做蒙特卡洛模擬的時候，關鍵是先確定所需模擬的分布，然後進行抽樣，然後應用層面的各種公式就可以在抽樣的基礎上進行計算了。

--------以下是補充的--------

根據上面提到的思路，其實可以很便捷地為期權做定價。下面就用蒙特卡洛方法為一個普通的歐式看漲期權定價（蒙特卡洛在為普通期權plain vanilla option定價時不佔優勢，因為相對於解析法而言計算量很大。但是，如果要給結構比較複雜的奇異期權定價時，可能蒙特卡洛法就比較實用，有時可能成為唯一的方法）。

1）假設這個期權是歐式看漲期權，行權價格為50元，標的股票當前的價格也是50元，期權剩餘時間是1天。

2）假設標的股票的價格服從對數正態分布，即股票的每日收益率服從正態分布，均值為0，每日標準差為1%。

根據分布假設，首先用rand()函數產生在0到1之間的均勻分布樣本。為了提高精確度，這裡抽樣的數量為1000個（其實1000個是很少的了，通常需要10萬個甚至50萬個，但是在Excel表格中操作這麼多數字，不方便，這是Excel的不足之處）。

下一步，用norminv(probability, mean, std)函數來獲得股票收益率分布的1000個抽樣，其中的probability參數由rand()產生的抽樣逐個代入，mean=0.0, std = 0.01。注意這裡抽樣得到的日度收益率。也就是說，這個樣本對應的下一個交易日股票價格的收益率分布。

下一步，股票價格=50×exp(收益率樣本)，得到股票價格分布的抽樣，有1000個樣本。

根據我做的實驗，這1000個樣本的分布圖形（histogram）跟對數正態分布是比較接近的，如下圖所示：

圖的橫軸是股票價格，縱軸是樣本中出現的頻率。

得到了股票價格未來一天分布的樣本之後，就可以以此樣本來計算期權的價格了。

歐式看漲期權的定義為：

C=max(S-K,0)

所以，根據這個計算公式可以計算出在到期那天在特定的價格下期權的價值。在Excel中，相當於期權價值=max(股票價格樣本 - 50，0)。由此就可以得到了該期權未來1天價值的樣本。

然後，將未來價值貼現回來（用無風險利率貼現，假設無風險利率為0.05，則貼現公式是=exp(-0.05/360）×期權價值，得到期權價格的1000個樣本。

最後，對期權價格的1000個樣本求平均，Excel函數average(期權價格樣本)，就可以得到期權的價格了。

我這裡算出來的是：0.2015元。

而根據Black-Scholes期權定價公式算出來的理論價格則是0.2103元。二者比較接近，但是還是有差距。

而且，每次刷新Excel表格，就重新做一次模擬，得到的模擬價格變動比較大，有時是0.2043元，有時是0.1989元。由於這個抽樣的數量比較小（1000個樣本），所以估算的結果受到樣本的影響會比較大。如果把抽樣數量提高100倍甚至500倍，那麼樣本變動的影響可能會小一個或者兩個數量級。但是計算量就大了，如果計算機性能不夠高，那麼利用Excel來做的話，比較困難。

這就是我的工作台：

------ 再來一個 --------

看到有人提到利用蒙特卡洛方法來估計圓周率Pi，挺有意思，也簡單，所以就在Excel中做了一個實驗。

基本原理在於在直角坐標系中的第一個象限中的一個單位圓，如下圖所示：

在這個面積為1的正方形中，有四分之一的圓，圓的半徑與正方向的邊長都是1。那麼根據圓的面積公式，這個圖形中陰影部分的面積應該是 Pi/4。

下面開始進入蒙特卡洛的解法。

即，如果我們對這個正方形平面中的點進行均勻地抽樣，隨著抽樣點的增多，那麼落入陰影內的點的數量與總抽樣數量的比，應該基本上等於陰影的面積Pi/4與整個正方形面積1的比，即Pi/4。用數學表示，就是

陰影內的樣本點數量 ÷ 總數量 = Pi/4

所以，Pi = 4 × 陰影內的樣本點數量 ÷ 總數量。

下面就在Excel中進行實驗。

用rand()函數生成2000個隨機數，作為隨機樣本點的X軸坐標，

再用rand()函數生成2000個隨機數，作為隨機樣本點的Y軸坐標。

如此就得到了2000個隨機樣本點，這些點的X軸坐標和Y軸坐標都大於零且小於1，所以是在前面所說的正方形之中的點。

下一步，判斷樣本點是否處於陰影之內，由於這個陰影就是單位圓在直角坐標系第一想像的四分之一，所以圓陰影內的點都符合如下不等式：

$X^2 +Y^2leq 1$

翻譯到Excel中，就是用IF函數來判斷，例如：

IF(A2^2 + B2^2 &<=1, 1, 0)

即，如果樣本點在陰影中，得到1，否則得到0。這樣就把樣本點區分開來了。

最後，把所有得到的1和0加總，就知道所有樣本點中處於陰影中樣本點的數量了。

最後根據

Pi = 4 × 陰影內的樣本點數量 ÷ 總數量

就可以算出Pi來了。

我這個試驗中算出來的 Pi=3.142。

以下是樣本點的散點圖：

由於樣本數量有限，所以計算出來的Pi的精度並不高。

以下是工作界面，挺簡單的。

為 @科西嘉的答案做一些補充。
早些時候對股票走勢模擬主要通過帶有garch（1,1）的蒙特卡洛模擬法，作為學渣一隻在此感謝真學霸無雙@陳斯在建模過程中提供的無私幫助。在（經過許可）拆解了SG的Ye老闆的模型後發現外資投行基本都使用維納過程(Wiener process)。話說根據風險中性（risk-neutral)原理似乎後者用於股票價格路徑模擬更加合適，也更簡單一些...
公式：Sn=S0*(1+aΔt+bεSqrt(Δt))，S0是上一步股票價格，Δt為單位步長，a是單步長內的預期收益率，bεSqrt(Δt)則是擾動項，其中b為常數，ε則與 @科西嘉文中提及的正態分布隨機數類似，通過excel函數=nomsinv(rand())計算，在這個公式的基礎上，我們就可以模擬單路徑的股票走勢。

第1步：確定要素
計算開始前我們還要確定4個要素，分別是股息率（這個很容易查到）、無風險利率（OIS/Libor/Shibor或者隨便什麼勞什子，但一般都會在Libor基礎上再上浮一定的BP）、到期日和隱含波動率。確定隱含波動率比較麻煩，我建議找擁有6塊屏彭博機的投行壕例如 @NERV諮詢（通常虵們會要求一些回報，比如腹肌或者(.)_(.)）。請確認所有利率均為連續複利

第2步：日期
起始日一般是當天，那麼可以設置=today()函數，或者隨便指定一個日期作為起始日。之後我們要篩選出交易日期內所有的工作日，函數見圖。

同時Δt也就出來了，需要注意的是計息天數習慣（360/365）

第3步：計算擾動項bεSqrt(Δt)
首先拉出一列=norminsv(rand())的隨機數，用這些隨機數去定義隨機抽取的標準差的倍數。bεSqrt(Δt)中，b就是隱含波動率。

第4步：模擬股價
在模擬股價開始之前，還有一個項aΔt需要解決，在風險中性的前提下，市場要求的預期收益率至少為無風險利率，但該部分收益應該剔除掉股息（因為它們被支付掉了，剩餘收益才反映在股價上）。aΔt=（無風險利率-股息率）*步長。所有要素完整後，就可以求出股價，如果需要計算新的路徑，按f9就可以刷新：

最後，無圖無逼格

當然，也可能模擬出虧損路徑

我們可以模擬500/5000/500000000次最後股價然後取均值μ，μ即可視作對股價的預測。

同學你是在做美賽嗎

謝邀！

在一般金融研究生階段，蒙特卡洛模型作為期權定價的三座大山（餘下兩個是BS公式和二叉樹），在衍生品投資課程中是絕對繞不開的。其運算需要大量的隨機模擬，所以一般考試是沒法考的，多半是拿來做上機練習題。

下面就和大家一起回味下我研究生時做的一道題：

這是一道關於原油期權的題，有這麼幾個題乾和假設。如下圖：

以上就是題目的題干，看著好像很複雜，其實公式也給你了，基本參數也給你了，只要按照公式乖乖的做模擬，就能得到答案。如果不想看題，我就簡單概括一下：題目中給了一個期權，然後要計算它的價格，這個期權的價格呢，滿足一個公式：

然後需要根據蒙特卡洛模擬，來計算題目中相應的L、K、C、H，最終得到其價格。

先算T=0時刻的，把一些基本參數羅列出來。

L0 = 75

c0 = LN(85.8/EXP(1.25%*57/365))

C0 = EXP(c0)

K0 = 2

h0 = LN(2.0429/EXP(1.25%*57/365))

H0 = EXP(h0)

這些都是題中已經給出，注意l、k、c、h分別為L、K、C、H取對數。0時刻之後，就是對210個時刻（還有210天到期）進行模擬了。按照圖中那幾個複雜的公式，模擬L、K、C、H的取值。先算T1時刻：

L1 = L0+1.25%*L0/365+20%*L0/SQRT(365)* εL

c1 = c0+0.9*(c0-LN(L0))/365+40%/SQRT(365)* εC

K1 = K0+1.25%*K0/365+30%*K0/SQRT(365)* εK

h1 = h0+0.96*(h0-LN(K0))/365+50%/SQRT(365)*
εH

然後就是重複210次計算。

這其實是對題中的公式進行帶入，不想細看就略過。最後的ε即為隨機序列，需要模擬1000次。所以每個係數的整個sheet就需要210*1000規模。

先看L：

很好對應，最開始的是一些基本參數，即公式中有提及的，注意其中的△t=1/365，即每一天。εl就是210*1000個隨機數，用excel的RAND函數獲取。每一個隨機數都對應一個L取值，L的公式已經在上文列出。

接下來是剩下的三個參數：C、K、H。計算過程完全一樣，代公式就行。

要注意，公式中出現的是c和h，為求出C和H，多一步取對數過程。好了，現在C和H都有了，套公式！

這就是蒙特卡洛過程1000次的結果，重複了公式一千次，最後取平均，得到了一個average13.8973，即為其在到期日的價格。然後在考慮時間價值因素，Price=Average*EXP(-1.25%*210/365)。這道題就完成了！

注意，這個數字不是一個固定的數值，由於RAND函數的原因，每進行一次寫入，Excel就會重新全部取一次隨機值。如果你取隨機過程無數次，那麼這個數值就是真正的期權價格，換句話來說，隨機過程越多，越接近其真實價格。這就是蒙特卡洛過程的關鍵所在。

以上如果你耐心看完就知道用Excel做蒙特卡洛多不方便了吧。隨機值太多，往往運行起來巨慢無比。用MATLAB的話，輕輕鬆鬆兩頁紙搞定。

我也說一個用利用excel做蒙特卡洛模擬大數定律的例子吧。
在數學與統計學中，大數定律又稱大數法則、大數律，是描述相當多次數重複實驗的結果的定律。根據這個定律知道，樣本數量越多，則其平均就越趨近期望值。
現以擲骰子的過程來展示大數定律。隨著投擲次數的增加，得到骰子的點數的平均值將趨於3.5，也就是骰子點數的期望值。
首先利用excel中的Randbetween函數來產生一定數量的1-6的隨機數。例如要模擬2000次實驗，則可通過以下過程來實現：
A1:A2000=

randbetween(1,6)

B1:B2000=

{=AVERAGE($A$1:INDEX($A$1:$A$2000,ROW()))}

其中A列用於產生2000個隨機數，B列用於生成骰子點數均值
選擇B1:B2000插入折線圖

不同時候做的這個實驗會在投擲數量較小的時候（左部）會表現出不同的形狀，不斷改變A列產生的隨機數即可得到不同的形狀，當投擲數量增加到一定數量時，則骰子點數的均值將祛瘀3.5

NORMSINV(RAND())

基本的tricky point就在這裡. 你可以去國外院校的searching bar上寫一下monte carlo基本上可以找到現成可以下載的excel；至少我記得Columbia是有的，網上也應該是有的。。。

Try Option pricing Monte Carlo Simulation in excel

統計學老師曾經給我們安利過一個神器，叫crystalball。就是題主說的Excel中專門做蒙特卡洛的插件。課上曾見過一次它的神威，但是自己沒用過。貌似國外用它比較多，題主可以去查一下～

建議去看一本名叫《financial modeling, with Excel》的書，裡面有各種通過excel實現包括蒙特卡洛模擬在內的案例。

這個我正好在金融建模(Financial Modelling)的課上學過，拋磚引玉的來答一下。

先佔個坑，晚上回家有空再細說。

我給題主找到了那個當初老師分享給我們，我自己也覺得很有用，用了之後覺得mind-blown的Excel，附帶例子。戳這裡: http://www.umlet.com/monte_carlo_tool/MonteCarlito_v1_10.xls
應該會直接跳轉成一個下載文件的界面。

這個文件中，具體分4塊闡述了怎麼用Monte Carlo。
1. 隨機投骰子的實驗
2. 怎樣求圓周率
3. 項目管理(project management)
4. 怎樣run多次的實驗並生出結果數據表

本科畢業課題就是蒙特卡羅……matlab什麼的至今已經如數還給導師了。之所以抽這個課題是因為導師她，之所以沒心情弄好它，是因為後來發現她已經嫁了個搓男！

可以用@Risk這個Excel的插件做

excel有一個add-in，叫做「@risk」，我上課時就是用這個做的蒙特卡洛，很好用。但這個add in好像價格很高（250刀？），palisade公司出的，package里還有其他的插件工具，比如說樹狀圖之類的，也挺好用。

做financial modeling 的時候可以用crystal ball, 先設定好參數的分配和各個參數的correlation, 然後還有目標值。然後按個鍵就行了~

請問圖是怎麼做出來的

佔位哪天想起來了寫..很久沒碰了要看看模型和相關資料。

關鍵詞：
做含有path depandent feature的convertible bond的valuation的時候BS與二叉樹不好處理，因此用monte carlo simulation ("MCS")。

股價依從geometric brownian motion ("GBM")

wiener process

需要linear regression

excel部分vba用於自動複製粘貼單元格等

找本書自己去看看的。

用excel的rand函數生產隨機數，接下來就簡單了

我想請教各位大神一個問題？如何用」蒙特卡洛法「評估排放清單的不確定性，求比較詳細的步驟，謝謝指教

畢業論文選了蒙特卡羅模擬。。。。。給自己挖坑。。。。看都看不懂。。。。還要寫。。。我不會啊！！！

用rand()做。只要能生成隨機數的任何一種工具，都可以做蒙特卡洛模擬。