Transcript do simulation monte carlo with excel

Introduction of Monte Carlo
Simulation
1. 單變數模擬
2. 多變數模擬
3. Contract pricing with the Monte Carlo simulation
4. 信賴區間
5. 變異數削減技巧
6. 範例
7. Homework 4
1
1. 單變數模擬

模擬抽樣方法: 均勻分配抽樣 + 欲模擬分配之累積機率函數
的反函數
均勻分配 (uniform distribution)
※ RAND() provided by Excel or RND() provided by VBA can draw random
samples from the uniform distribution
2

欲模擬分配之累積機率函數 𝑁(𝑥)
※ 𝑁 −1 (𝑦)為欲模擬分配之累積機率函數的反函數，即輸入𝑦為一個介於0與1之間
的數，則可以得到一個𝑥之值
※ 當𝑦符合均勻分配，依上述方法所產生的𝑥會服從欲模擬之分配
※ NORMSINV() in Excel is the inverse function of the standard normal distribution
※ To generate random samples from the stand normal distribution, one can use
NORMSINV(RAND()) in Excel and Application.WorksheetFunction.NormSInv
(Rnd()) in VBA
※ Please refer to “Count.xls”
3

對於沒提供欲模擬分配之累積機率函數的反函數之程式語言，
例如C或C++，一般使用Box-Muller Method來產生標準常態
分配的抽樣

Box-Muller Method:
Step 1: Draw random samples 𝑥1 and 𝑥2 from the uniform distribution
Step 2: 𝑦1 =
−2 ln 𝑥1 cos(2𝜋𝑥2 ) and 𝑦2 =
−2 ln 𝑥1 sin(2𝜋𝑥2 )
𝑦1 and 𝑦2 are independent random samples drawn from the standard
normal distribution
4
2. 多變數模擬

對於bivariate normal distribution
𝑟1
𝑟2 ~
𝜎12
0
,
0
𝜌𝜎1 𝜎2
𝜌𝜎1 𝜎2
𝜎22
如何抽樣𝑟1 與𝑟2 以滿足上述分配？
Step 1: Draw random samples 𝑧1 and 𝑧2 from the bivariate standard
normal distribution, i.e.,
𝑧1
𝑧2 ~
0
1 0
,
0
0 1
(Since 𝑧1 and 𝑧2 are independently normal distribution, in practice, we draw
𝑧1 and 𝑧2 from the standard normal distribution separately)
Step 2:
𝑟1 = 𝜎1 𝑧1
𝑟2 = 𝜎2 𝑧1 𝜌 + 𝑧2 1 − 𝜌2
5

對於multivariate normal distribution，使用Cholesky
decomposition method來完成抽樣
Step 1: Decompose the covariance matrix 𝐶 = 𝐴𝑇 𝐴, where 𝐴 is an upper
triangular matrix
Step 2: 𝑟1 𝑟2 ⋯ 𝑟𝑛 = 𝑧1 𝑧2 ⋯ 𝑧𝑛 𝐴, where 𝑧1 𝑧2 ⋯ 𝑧𝑛 are random
samples from multivariate standard normal distribution
Take the bivariate normal distribution for example:
𝛼
𝐴=
0
𝜎12
𝛽
⇒𝐶=
𝜙
𝜌𝜎1 𝜎2
𝜌𝜎1 𝜎2
𝛼2
=
2
𝛼𝛽
𝜎2
𝛼𝛽
𝛽2 + 𝜙 2
One can solve 𝛼 = 𝜎1 , 𝛽 = 𝜎2 𝜌, and 𝜙 = 𝜎2 1 − 𝜌2 and obtain random
samples through
𝑟1 𝑟2 = 𝑧1 𝑧2 𝐴 = 𝑧1 𝑧2
𝜎1
𝜎2 𝜌
0
𝜎2 1 − 𝜌2
※ The results of the Cholesky decomposition method are exactly the same as the
method in the previous slide
6

The reason behind the Cholesky decomposition method
var
𝑟1
𝑟
= 𝐸 1 𝑟1 𝑟2
𝑟2
𝑟2
= 𝐴𝑇 𝐸
= 𝐸 𝐴𝑇
𝑧1
𝑧 𝑧 𝐴
𝑧2 1 2
𝑧1
𝑧1 𝑧2 𝐴 = 𝐴𝑇 𝐼𝐴 = 𝐴𝑇 𝐴 =C
𝑧2
※ In fact, if we can decompose the covariance matrix in any way to be 𝐶 = 𝑀𝑇 𝑀,
then we can generate the random samples from multivariate normal distribution
through 𝑟1 𝑟2 ⋯ 𝑟𝑛 = 𝑧1 𝑧2 ⋯ 𝑧𝑛 𝑀
7
3. Contract pricing with the Monte Carlo Simulation (MCS)

How to price a bonus contract with the following payoff
$10,000,000 × max 𝑟 − 𝐾, 0 ,
where 𝑟 is the growth rate of your firm and follows a normal
distribution with the mean to be 5% and the standard
deviation to be 20%, and 𝐾 = 4% represents the threshold to
earn the bonus?
Step 1: Draw 𝑁 random samples for 𝑟 following the designated normal
distribution, i.e., normally distributed 𝑥1 , 𝑥2 , …, 𝑥𝑁 with the mean to be 5%
and the standard deviation to be 20%
Step 2: For each 𝑥𝑖 (which represents one possible scenario), calculate the
corresponding payoff to be 𝑝𝑖 = $10,000,000 × max 𝑥𝑖 − 𝐾, 0
Step 3: The expected value of this contract is the average of all payoffs, i.e.,
𝑁
𝑖=1 𝑝𝑖 /𝑁
※ Please refer to “Contract pricing with MCS.xlsx”
8
4. 信賴區間

Standard Deviation and Standard Error

Standard deviation is a simple measure of the variability or dispersion of a
population, a data set, or a probability distribution. For 𝑁 simulated random
samples, the standard deviation of this set can be calculated through 𝜎 =
𝑁
𝑖=1


𝑥𝑖 − 𝑥 2 /𝑁
Standard error 𝑠𝐸 : The standard error of an estimation method is the
standard deviation of the sample distribution associated with the
estimation method
A sampling distribution is the probability distribution, under repeated
sampling of the population, of a given estimation
– Suppose we repeatedly draw sample sets with a given size from a
population and calculate the sample mean for each sample sets
– Different sample sets leads to different sample means
– The distribution of these means is the “sampling distribution of the
sample mean”
※ For the estimates of the sample variance, skewness, kurtosis, etc., it is also possible
to derive the “sampling distributions” of these estimates
9

Standard errors for the MCS, which in essence a method for
estimating the mean

Method 1: The standard error of the mean is usually estimated by the
sample standard deviation divided by the square root of the sample size,
𝜎
i.e., 𝑠𝐸,1 =
𝑁

Method 2: Generate the sampling distribution of the results of MCS by
repeating the MCS for many times, e.g., 20-30 times, and next calculate
the standard deviation of the sampling distribution to be 𝑠𝐸,2
※ Please refer to “Standard Error.xlsx”

Confidence interval (CI) for the MCS


Method 1: 𝑁 = 10000 MCS for calculating 𝑠𝐸,1 , and CI = [mean of 𝑁 =
10000 MCS – 2× 𝑠𝐸,1 , mean of 𝑁 = 10000 MCS + 2× 𝑠𝐸,1 ]
Method 2: Repeat 𝑁 = 10000 MCS for 𝑀 times for calculating 𝑠𝐸,2 , and
CI = [mean of 𝑀 repetitions – 2× 𝑠𝐸,2 , mean of 𝑀 repetitions + 2× 𝑠𝐸,2 ]
※ According to the central limit theorem, the sampling distribution of the mean is
asymptotically normal, and 𝑍2.5% = −1.96 and 𝑍97.5% = +1.96. So, the use of
± 2 × 𝑠𝐸 is approximately correct and common in practice
10

Method 1 vs. 2 for standard errors and CIs


Method 2, which follows the definition to derive the standard error, is
more intuitive
Method 1 does not work sometimes after conducting variance reduction
techniques. The possible reason may be that the random samples are no
more independent after using the antithetic variate approach
※ Please refer to “Effect of Antithetic.xlsx”
11
5. 變異數削減技巧 (Variance-Reduction Techniques)


To improve the accuracy (or said to decrease the standard
errors) of the MCS, one should consider a larger value of 𝑁 and
thus the MCS become more time consuming
Variance-reduction techniques are approaches which can
narrow the CIs of the MCS without increasing the number of
simulation 𝑁

Antithetic variate approach (反向變異法): satisfying the
zero mean and the symmetric feature of the standard normal
distribution
𝑧1 , 𝑧2 , …, 𝑧𝑁 , 𝑧𝑁+1 , 𝑧𝑁+2 , …, 𝑧𝑁
2
2
2
||
||
||
−𝑧1
−𝑧2
−𝑧𝑁
2
12

Moment matching: matching the first two moments of the
standard normal distribution
Step 1: Draw random samples 𝑧1 , 𝑧2 ,…, 𝑧𝑁
Step 2: Suppose the mean and standard deviation of 𝑧1 , 𝑧2 ,…, 𝑧𝑁 are
𝑚 and 𝑠, respectively
Step 3: Generate 𝑦𝑖 =
𝑧𝑖 −𝑚
𝑠
(The mean of standard deviation of 𝑦1 , 𝑦2 ,…, 𝑦𝑁 will be 0 and 1,
respectively)

Antithetic variate approach + Moment matching:
–
–
Use the antithetic variate approach to generate 𝑧1 , 𝑧2 ,…, 𝑧𝑁 in Step 1 in
the moment matching method
Since the mean of 𝑧1 , 𝑧2 ,…, 𝑧𝑁 based on the antithetic variate approach
𝑧
is 0, Step 3 can be reduced to 𝑦𝑖 = 𝑖
𝑠
–
This method is very common in practice
13
6. 範例

Calculate N(c) by the Monte Carlo Simulation
Monte Carlo Simulation: generate some random scenarios
and conduct further analysis based on these scenarios
By definition, N(c) = prob (x  c) for x ~ N(0,1)
Draw, for example, 1000 random samples from a standard
normal distribution x ~ N(0,1)
Then N(c) can be estimated with #(x  c) / 1000




※ Please refer to “MCS applications.xls”
14

Analyze a project with expanding and aborting options by the
Monte Carlo Simulation
Time 0: initial investment V0
Time t1: the value of the project is V1 ~ N (V0 (1  k )t ,  1 ), and
the firm can double the investment if V1  u (i.e., V1  2V1),
abort the investment if V1  d (i.e., V1  0), or otherwise
maintain the same investment amount such that V1  V1 , where
k is the expected growth rate of the investment
Time t2: the value of the project is V2 ~ N (V1(1  k )(t t ) ,  2 )



1
2
1
(Through the Monte Carlo Simulation, you can generate
thousands of scenarios for the final payoff of the project, and
further analyze the associated mean and s.d. or even the whole
distribution of the payoff of the project)
※ Please refer to “MCS applications.xls”
15
7. Homework 4

Estimate the expected value of the following bonus contract
$10,000,000 × max 𝑟 − 𝑟𝐼 , 0 ,
where 𝑟 is the growth rate of your firm and follows a normal
distribution with the mean to be 𝜇 and the standard deviation to be
𝜎, and 𝑟𝐼 is the growth rate of the industry where your firm
belongs and follows a normal distribution with the mean to be 𝜇𝐼
and the standard deviation to be 𝜎𝐼 . The correlation between 𝑟 and
𝑟𝐼 is 𝜌.
Given the number of simulations to be 𝑁 and the number of
repetitions to be 𝑀, calculate the expected value and the 95%
confidence interval of this contract
Inputs: 𝜇, 𝜎, 𝜇𝐼 , 𝜎𝐼 , 𝜌, 𝑁, and 𝑀; Output (with one
CommandButton): expected contract value and the 95%
confidence interval


※ Total points for this homework is 10
16