代写代考 PowerPoint Presentation

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