TAILIEUCHUNG - Lecture Financial risks management - Topic 13: Monte carlo simulations using excel and @risk: Retirement simulation
Topic 13 - Monte carlo simulations using excel and @risk: Retirement simulation. The main contents of the chapter consist of the following: Financial simulation process, retirement simulation, geometric brownian motion asset return model (random walk),. | Topic #13 Monte Carlo Simulations using Excel and @Risk: Retirement Simulation Financial Modeling L. Gattis 1 Financial Simulation Process 1. Create a model that estimates a future outcome (., asset price, payoff, portfolio value) which has a stochastic variable such as asset return Stochastic variables are those in which the future value is uncertain (non-deterministic) – ., more than one possible outcome 2. Specify the distribution of the stochastic variables (and their correlations) ., asset returns are normally If there are multiple stochastic variables -- specify the correlations 3. Simulate many possible outcomes by randomly sampling from the specified distribution ., sample 5,000 possible values for asset return 4. Evaluate the distribution of the outcome ., mean, volatility, skew, confidence interval 2 Retirement Simulation 1. Create a model that estimates a future outcome which has a stochastic variable Estimate retirement portfolio value and annuity where asset portfolio returns are uncertain Assume asset returns follow a random walk (GBM) 2. Specify the distribution of the stochastic variables (and their correlations) Asset returns are normally distributed Assume annual asset returns are uncorrelated (@Risk assumes variables are uncorrelated if not specified) 3. Simulate many possible outcomes by randomly sampling from the specified distribution Sample 5,000 possible values for asset returns in each year 4. Evaluate the distribution of the outcome Calculate the mean and 10% highest and lowest real annuities and portfolio values 3 Geometric Brownian Motion Asset Return Model (Random Walk) Discrete Compounding If modeling only one price change per year, set h=t=1; set d=0 for dividend reinvestment @Risk Formula (where d=0, h=t=1) 4 @Risk Retirement Simulation Alt-Print Screen will copy window images Or right click on graph and select copy – then paste into excel 5 Open @Risk Open Retirement Portfolio Simulation Excel File Change Sim. Portfolio Formula, then copy down =(J12+C13)*(1+G13+NORMSINV(RAND())*H13) 4. Click: Add Output Ending Portfolio Value Real Annuity 5. Verify Model Window Inputs and Outputs 6. Start Simulation (5,000 iter.) Open Fin Mod @Risk Retirement Simulation 6 7. View Results Click on Output cell, Click on Browse Results Click on detailed Statistics Alt-Print Screen will copy the window as image to paste results 8. Results Window What’s probability that annuity is < 100k (type 100 in value field) What’s the 95% confidence interval portfolio value? (type 95 in the probability field) Alt-Print Screen will copy window images =riskmean(output) =risktarget(output,val) @Risk Goal Seek 7 @Risk Advanced Analysis Select Goal Seek Goal Cell: Real Annuity (last) Statistic: Mean Value: 50,000 Changing Cell: Savings Rate @Risk Advanced Analysis Select Goal Seek Goal Cell: Real Annuity (last) Statistic: Percentile(.05) Value: 25,000 Changing Cell: Contribution Bonus Problem 8
đang nạp các trang xem trước
