PowerPoint Presentation
Chapter 25: Intro to Monte Carlo Methods
Copyright By PowCoder代写 加微信 powcoder
What is a Monte Carlo Method?
Computational method using random numbers
Examples in this chapter
What is the value of p
Random wealth and consumption over time
Chapter 30: Valuing options by Monte Carlo
Wikipedia’s explanation
Monte Carlo is often time-consuming, computationally wasteful
Our first example (computing p) is a good illustration
Analytical methods (formulas … ) are better if available
Sometimes there’s nothing else
Monte Carlo is fun!
The square has area 4
Unit circle has area p
Circle in upper right-hand quadrant has area p/4
Computing p: Unit circle inscribed in unit square
Computing p: Monte Carlo
Strategy: “Shoot” random point {x,y} at upper-right hand quadrant
Point is inside circle if x2+y2 <1
Use Excel function Rand() twice to simulate x and y coordinates
Count number of points inside circle as ratio of total number of points shot
This ratio ≈ p/4
Use Excel function rand() to simulate x and y coordinates of random point.
Columns B and C have function Rand().
Column D uses Boolean function to determine if point is inside unit circle
Cell B4 approximates p by
Computing ratio of inside/total points
Multiplying this ratio by 4
Different experiments give different approximations
Here we’ve done 4,124 experiments. Accuracy much improved!
Increasing number of experiments improves approximation
In 34 seconds, VBA does 50 million simulations.
Better done with VBA
This is NOT a good way to estimate p
End-of-chapter exercise shows method developed by brilliant Indian mathematician Srinivasan Ramanujan (1887-1920)
Ramanujan’s formula gets 15-digit accuracy in only two iterations
Ramanujan has an even faster formula
See wonderful article http://www.cecm.sfu.ca/organics/papers/borwein/index.html
Read a biography of Ramanujan:
The Man Who Knew Infinity: A Life of the Genius Ramanujan by
Another wonderful article: “Ramanujan for Lowbrows” by . Berndt and S. Bharghava
http://mathdl.maa.org/images/upload_library/22/Ford/Berndt-Bhargava644-656.pdf
VBA uses cell names Number and Estimate (cells B2 and B3)
Chapter 22 includes a variation of this routine which times the simulation.
Note that the random number generator in VBA is Rnd; same as Rand() in Excel
VBA routine
One more Monte Carlo simulation
You are invested in a combination of risky and riskless asset
You want to draw down an annual sum and still have money left over
Can you do it?
Applications:
Retirement strategies
Endowment management
You have $1,000,000 at age 65
Want to withdraw $150,000 at end of each year for next 10 years
Money is invested in risky asset and risk-free asset
Risky has mean annual return m = 8% and
Risk-free rate = 6%
If you invest 70% in risky and 30% in risk free, will you have positive wealth at end of 10 years?
Retirement simulation
Simulation strategy
Wealth at end of each year is sum of:
Value of risky investment
Initial wealth * 70% * exp[m+s*Z] , where Z is a draw from standard normal distribution
See Chapter 17 on lognormal for explanation
Z is simulated with Excel function Norm.S.Inv(Rand())
Value of riskless investment: Initial wealth * 30% * exp[rf]
From this sum subtract annual draw of $150,000
Can bonds outperform stocks?
Four simulations
Stocks versus bonds: Daily, one year
Retirement simulation
After 100 simulations
1.00.50.51.01.00.50.51.0
Number of data points 30<-- =COUNT(A:A)
Inside circle 23<-- =COUNTIF(D:D,TRUE)
Pi? 3.066666667<-- =B3/B2*4
ExperimentRandom1Random2
10.234370.03080TRUE<-- =(B8^2+C8^2<=1)
20.923220.61267FALSE
30.955350.25644TRUE
Each cell in these columns contains the Excel
COMPUTING PI USING MONTE CARLO METHODS
INITIAL EXPERIMENT
Number of data points 30<-- =COUNT(A:A)
Inside circle 21<-- =COUNTIF(D:D,TRUE)
Pi? 2.8<-- =B3/B2*4
ExperimentRandom1Random2
10.882550.06206TRUE<-- =(B8^2+C8^2<=1)
20.375580.41664TRUE
30.810440.65635FALSE
Each cell in these columns contains the Excel
COMPUTING PI USING MONTE CARLO METHODS
INITIAL EXPERIMENT
Number of data points 30<-- =COUNT(A:A)
Inside circle 24<-- =COUNTIF(D:D,TRUE)
Pi? 3.2<-- =B3/B2*4
ExperimentRandom1Random2
10.275440.04368TRUE<-- =(B8^2+C8^2<=1)
20.812040.52793TRUE
30.917310.90177FALSE
Each cell in these columns contains the Excel
COMPUTING PI USING MONTE CARLO METHODS
INITIAL EXPERIMENT
Number of data points 4124<-- =COUNT(A:A)
Inside circle 3240<-- =COUNTIF(D:D,TRUE)
Pi? 3.142580019<-- =B3/B2*4
ExperimentRandom1Random2
10.790010.89301FALSE<-- =(B8^2+C8^2<=1)
20.248010.07722TRUE
30.287350.06308TRUE
40.631390.59986TRUE
Each cell in these columns contains the Excel
COMPUTING PI USING MONTE CARLO METHODS
INITIAL EXPERIMENT
Number of data points 50,000,000<-- This cell called "Number"
Pi? 3.14162752<-- This cell called "Estimate"
StartTime 12:13:33<-- This cell called "StartTime"
StopTime 12:14:07<-- This cell called "StopTime"
Elapsed 0:00:34<-- =Stoptime-StartTime
[Ctrl]+a runs the macro "MonteCarlo"
[Ctrl]+q runs the macro "MonteCarloTime" which also records the time
COMPUTING PI USING VBA
[Ctrl]+t runs the macro "MonteCarloTimeRecord" which records the
results as they are generated. For large number of points, this takes a
very long time!
Sub MonteCarlo()
n = Range("Number")
For Index = 1 To n
If Rnd ^ 2 + Rnd ^ 2 < 1 Then Hits = Hits + 1
Next Index
Range("Estimate") = 4 * Hits / n
ABCDEFGHIJ
Mean return11%5%
Sigma of return20%0%
Initial price5050
Deltat1.00001.0000
050.000050.0000
155.821752.5636
259.829755.2585
363.752858.0917
473.138861.0701
588.416464.2013
666.670967.4929
763.657970.9534
873.448074.5912
980.873078.4156
1078.835382.4361
11115.993086.6627
12106.210891.1059
13140.751395.7770
14204.2189100.6876
15225.7249105.8500
16249.2923111.2770
17367.6058116.9823
18210.1872122.9802
19208.9168129.2855
20207.4941135.9141
STOCKS VERSUS BONDS: Simulation
05010015020025030035040002468101214161820Can Bonds Outperform Stocks?Stock priceBond price
02004006008001,0001,2001,4001,6001,80002468101214161820Can Bonds Outperform Stocks?Stock priceBond price
01002003004005006007008009001,00002468101214161820Can Bonds Outperform Stocks?Stock priceBond price
010020030040050060070002468101214161820Can Bonds Outperform Stocks?Stock priceBond price
02040608010012014016018020002468101214161820Can Bonds Outperform Stocks?Stock priceBond price
40424446485052540255075100125150175200225250Can Stocks Outperm Bonds? Daily, one yearStock priceBond price
4045505560650255075100125150175200225250Can Stocks Outperm Bonds? Daily, one yearStock priceBond price
4045505560650255075100125150175200225250Can Stocks Outperm Bonds? Daily, one yearStock priceBond price
404550556065700255075100125150175200225250Can Stocks Outperm Bonds? Daily, one yearStock priceBond price
Current wealth 1,000,000
Riskfree rate 6%
Parameters of risky investment
Expected annual return 8%
Standard deviation of return 20%
Proportion invested in risky 70%
Annual drawdown 150,000
Invested in
Invested in
distributed
1+return on
investment
11,000,000700,000300,000-1.24590.8444909,604150,000
2759,604531,723227,8810.83731.2808922,985150,000
3772,985541,090231,8960.24681.1381862,046150,000
4712,046498,432213,614-0.14901.0515750,918150,000
5600,918420,643180,276-0.47240.9856606,022150,000
6456,022319,215136,807-1.17690.8561418,543150,000
7268,543187,98080,5631.33471.4147351,485150,000
8201,485141,04060,446-0.02541.0778216,195150,000
966,19546,33719,8591.08221.345183,412150,000
10-66,588-46,611-19,976-0.95490.8950-62,927150,000-212,927
Investment in risky asset =B20*$B$7
PLANNING YOUR RETIREMENT
=C20*F20+D20*EXP($B$3)
Normally-distributed random numbers
generated by =NORMSINV(RAND())
1+return on risky investment
=EXP($B$5+$B$6*E20)
Wealth at beginning of year =G19-H19
ABCDEFGHIJ
Initial pension money 1,000,000
Percent invested in risky asset 60%
Percent in riskless 40%
Riskless interest rate 4%
Risky asset mean return 12%
Risky asset sigma 30%
Annual draw 150,000
Prob of ending > 0 53%<-- =COUNTIF(B23:CW23,">0″)/100
Experiment 123456789
YearEnd-year cash
01,000,0001,000,0001,000,0001,000,0001,000,0001,000,0001,000,0001,000,0001,000,000
11,284,691973,810880,108842,6331,023,9321,136,845966,3051,039,363749,577
2991,7231,070,7291,010,2021,353,411897,0971,190,5301,228,205900,988462,381
31,109,2001,047,2931,054,5001,160,359817,7481,471,3981,591,855563,572504,556
4979,8991,143,8611,191,9991,051,6231,075,4361,080,5541,943,674463,740407,396
51,097,4561,867,8891,020,197761,7751,401,917795,2711,865,281444,431236,792
6702,0921,959,6451,336,467614,0171,379,433787,4681,546,223304,11188,082
7614,2452,681,4791,349,789582,4621,180,372684,2391,922,739113,335-47,447
8500,3521,980,347991,949672,5881,273,185491,1722,452,886-1,630-209,745
9466,1712,415,202726,830430,377978,177334,5162,274,070-151,710-381,150
10550,5861,701,000894,870306,099726,845304,7782,293,821-322,958-543,943
THIS IS 100 SIMULATIONS
Cell D:14 contains formula
=D13*($B$3*EXP(mu+sigma*NORMSINV(RAN
D()))+$B$4*EXP(riskfree))-draw
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com