程序代写 CW23,">0")/100

PowerPoint Presentation

Chapter 25: Intro to Monte Carlo Methods

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.00.50.51.01.00.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

Related Posts