PowerPoint Presentation
Chapter 27: Monte Carlo Simulations for Investments
Copyright By PowCoder代写 加微信 powcoder
In this chapter
Simulate performance of single stocks (see Chapter 26?)
Then simulate performance of correlated assets (portfolios)
Simulate beta vs alpha of a stock
PURPOSE of chapter: to try to give some content to the meaning of “investment performance”
Lots of Data Tables!
Suggestion: turn off automatic recomputation of Data Tables
Good technique: “Data Table on Blank Cell”. Chapter 31, section 7
Simulating the investment performance of a single stock (see Chp 26)
Initial stock price: S0
Stock return has mean, m (“mu”) and standard deviation, s (“sigma”)
Time interval: Dt (“delta t”)
We are simulating the stock price at times t = 0, Dt, 2Dt, 3Dt, …
Typically, Dt = 1/12 (monthly simulation) or Dt=1/52 (weekly) or 1/252 (daily)
From Chapter 26:
The Z’s are independent normal random numbers with = 0, = 1. Created using Norm.S.Inv(rand())
Amending the formula
where Z1, Z2, and Z3 are each independent random standard-numbers numbers
Using Excel: Z = Norm.S.Inv(rand())
Each press of F9 (recalculate spreadsheet) produces a new simulation)
Hit F9 to compute a new simulation
Simulating portfolio of two stocks
We have two stocks, whose returns are distributed {m1,s1} and {m2,s2}
Furthermore, the stock returns are correlated with correlation, r (“rho”)
How do we simulate?
Generating correlated standard normal variables
where Z1 and Z2 are independent random standard-normal numbers that are correlated with correlation,
To generate Z1 and Z2:
Using Excel: Z1 = norm.s.inv(rand()), Z3 = norm.s.inv(rand()),
then Z2 = Z1 + Z3
Note: this works for 2 standard normal variables. For more than 2, need to use Cholesky decomposition (pg. 638)
Play with previous slide!
Statistics reminder: When you regress Z2 on Z1 (as in this graph), the expected R2 = r2.
Of course, this is a random simulation, so we will always get different numbers. But, approximately R2 = r2 .
2-stock simulation
Invest $100 in portfolio of two stocks with correlated returns
Proportion of stock 1: x
Proportion of stock 2: 1-x
Portfolio rebalanced at end of each month
How much will you have at the end of the year?
Dt = 1/12 (monthly simulation)
Portfolio simulation
Look at the formulas
Z1 and Z2 are correlated
Portfolio is rebalanced every period (cells D21, E21, F21)
Portfolio simulation: Some graphs
Another simulation: Saving for retirement
Discussed (under certainty) in Section 1.6
Now introduce uncertainty
Question: Can we make it through retirement?
Section 1.6:
Start year 0 with $0 in retirement account
Save for 5 years; withdrawal of $30,000 annually in years 6-13
Save $29,386.55 at beginning of each year, earning 8% on deposits.
This number determined in order to “zero-out” the account at the end of year 13
Retirement: Add uncertainty
This is a slightly revised version of page 712
Risk-free rate: 3%
Risky asset: (mean, sigma) = (12%, 18%)
Annual deposit: $40,000 during years 1 – 5
Annual withdrawal: $30,000 during years 6 – 13
Investment proportions: 30% in risk-free, 70% in risky
Cell D25 is what’s left. It is the bequest that you leave to your heirs!
Different simulation: The return on the risky asset may cause the bequest to be negative.
Run a Data Table on a blank cell
Redo the simulation 10 times
Do some statistics
Try to understand the uncertainty!
Results for Data Table
Use Data Table on Blank Cell (see section 31.7) to replicate the simulation 10 times. The statistics (rows 4-8) show that for this simulation, 30% of the bequests are negative (meaning—we ran out of money!).
This is a great technique! The only problem is that it is very time intensive.
Delta_t0.0833<-- =1/12
Initial price12.00
MonthPrice
110.86<-- =B8*EXP(Mean*Delta_t+Sigma*SQRT(Delta_t)*NORM.S.INV(RAND()))
211.12<-- =B9*EXP(Mean*Delta_t+Sigma*SQRT(Delta_t)*NORM.S.INV(RAND()))
SIMULATING MONTHLY STOCK PRICE
67891011121314150123456789101112Simulating One Year of Stock Prices(Mean,Sigma) = (12.00%,30.00%)
Delta_t0.0833<-- =1/12
Initial price12.00
MonthPrice
112.11<-- =B8*EXP(Mean*Delta_t+Sigma*SQRT(Delta_t)*NORM.S.INV(RAND()))
212.11<-- =B9*EXP(Mean*Delta_t+Sigma*SQRT(Delta_t)*NORM.S.INV(RAND()))
SIMULATING MONTHLY STOCK PRICE
6789101112131415160123456789101112Simulating One Year of Stock Prices(Mean,Sigma) = (12.00%,30.00%)
Correlation-0.4
-1.00813.0753<-- =$B$2*A5+SQRT(1-$B$2)*NORM.S.INV(RAND())
1.6743-0.8241
-0.53990.9334
0.02371.7546
0.68050.2958
1.97100.4585
-0.94982.9086
1.2799-1.1535
2.5279-1.0338
-2.02750.0722
1.2896-0.1756
-1.61331.5755
-1.60960.9865
1.00380.0983
-0.3115-1.1001
0.0646-0.5082
-1.09801.8772
1.4793-1.1419
-1.3880-1.6549
-0.33060.1149
SIMULATING TWO CORRELATED NORMAL DEVIATES
y = -0.4778x + 0.3547R² = 0.2231-2.00-1.000.001.002.003.004.00-3.00-2.00-1.000.001.002.003.00Correlated Standard Normal DeviatesCorrelation = -0.40
Stock1Stock2
Mean 12%15%
Sigma 22%30%
Correlation 0.5
Delta_t 0.0833<-- =1/12
Initial wealth 1,000
Proportion
of stock1, x
Initial investment
Stock1400.00<-- =B10*B9
Stock2600.00<-- =(1-B10)*B9
PORTFOLIO OF 2 STOCKS
Initial wealth 1,000
Proportion
of stock1, x
Initial investment
Stock1400.00<-- =B10*B9
Stock2600.00<-- =(1-B10)*B9
rebalancing
Stock1Stock2
MonthStock1Stock2
0 1,000.00400.00600.000.06540.8737
1405.70655.301,061.00424.40636.60-1.08330.2520
2400.16658.831,059.00423.60635.40-0.24900.2464
3421.14657.261,078.41431.36647.05-0.30071.2785
4427.46731.891,159.35463.74695.61-1.7282-1.7349
5419.71606.101,025.81410.32615.49-0.3563-1.3798
6405.17553.03958.21383.28574.93-0.33391.7453
7379.01677.151,056.16422.46633.70-0.0112-0.4721
8426.41615.961,042.37416.95625.421.27900.5553
9456.77664.491,121.26448.51672.76-0.48940.2513
10439.15696.211,135.36454.14681.22-2.2376-1.3243
11397.94615.041,012.99405.19607.79-0.6821-0.0212
12391.92614.311,006.23402.49603.74
Cell H20=NORM.S.INV(RAND())
Cell I20=corr*H20+SQRT(1-corr^2)*NORM.S.INV(RAND())
Cell B21=E20*EXP(mean1*Delta_t+sigma1*SQRT(Delta_t)*H20)
Cell C21=F20*EXP(mean2*Delta_t+sigma2*SQRT(Delta_t)*I20)
Cell D21=B21+C21
Cell E21=$B$10*D21
Cell F21=(1-$B$10)*D21
Investment at end of
Rebalanced
Simulating portfolio values
02004006008001,0001,2001,4001,6000123456789101112(Mean1,Sigma1)= (12%,22%), (Mean2,Sigma2) = (15%,30%), Correlation = 0.50Stock1Stock2Portfolio value
Interest 8%
Annual deposit 29,386.55
Annual retirement withdrawal 30,000.00
=$B$2*(C7+B7)
Deposit at
during year
10.0029,386.552,350.9231,737.48<-- =D7+C7+B7
231,737.4829,386.554,889.9266,013.95
366,013.9529,386.557,632.04103,032.54
4103,032.5429,386.5510,593.53143,012.62
5143,012.6229,386.5513,791.93186,191.10
6186,191.10-30,000.0012,495.29168,686.39
7168,686.39-30,000.0011,094.91149,781.30
8149,781.30-30,000.009,582.50129,363.81
9129,363.81-30,000.007,949.10107,312.91
10107,312.91-30,000.006,185.0383,497.94
1183,497.94-30,000.004,279.8457,777.78
1257,777.78-30,000.002,222.2230,000.00
1330,000.00-30,000.000.000.00
A RETIREMENT PROBLEM, Section 1.6
: This problem has 5 deposits and 8 annual withdrawals, all made at the beginning of the year. The
beginning of year 13 is the last year of the retirement plan; if the annual deposit is correctly computed, the
balance at the beginning of year 13 after the withdrawal should be zero.
Risk-free rate 3%<-- Cell name: rf
Risky asset
Mean 12%<-- Cell name: mean
Sigma 18%<-- Cell name: sigma
Annual deposit 25,000<-- Years 1-5
Investment policy
Risk-free 30%<-- Cell name: prop
Risky asset 70%<-- =1-prop
Annual retirement withdrawal 30,000<-- Years 6-13
Deposit at
In account at
end of year
1 025,00034,140
234,14025,00056,765
356,76525,000106,016
4106,01625,000131,337
5131,33725,000210,347
6210,347-30,000234,060
7234,060-30,000191,943
8191,943-30,000181,689
9181,689-30,000156,229
10156,229-30,000124,716
11124,716-30,000114,585
12114,585-30,000113,274
13113,274-30,00087,993
A RETIREMENT PROBLEM (pages 712-13)
<-- =(B13+C13)*(prop*EXP(rf)+(1-
prop)*EXP((mean+sigma*NORM.S.INV(RAND()))
Risk-free rate 3%<-- Cell name: rf
Risky asset
Mean 12%<-- Cell name: mean
Sigma 18%<-- Cell name: sigma
Annual deposit 25,000<-- Years 1-5
Investment policy
Risk-free 30%<-- Cell name: prop
Risky asset 70%<-- =1-prop
Annual retirement withdrawal 30,000<-- Years 6-13
Deposit at
In account at
end of year
1 025,00029,639
229,63925,00060,149
360,14925,00073,374
473,37425,000123,632
5123,63225,000160,925
6160,925-30,000165,134
7165,134-30,000104,193
8104,193-30,00077,721
977,721-30,00056,299
1056,299-30,00031,336
1131,336-30,0001,185
121,185-30,000-32,669
13-32,669-30,000-80,021
A RETIREMENT PROBLEM (pages 712-13)
<-- =(B13+C13)*(prop*EXP(rf)+(1-
prop)*EXP((mean+sigma*NORM.S.INV(RAND()))
Average48,158<-- =AVERAGE(H15:H24)
Max247,427<-- =MAX(H15:H24)
Min-87,351<-- =MIN(H15:H24)
Sigma99,210<-- =STDEV.S(H15:H24)
% positive bequests 70%<-- =COUNTIF(H15:H24,">0″)/10
Simulation
-80,021<-- =D25, data table header
Statistics for data table
Data table:
Simulating the bequest
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com