Tài chính doanh nghiệp - Topic 13: Monte carlo simulations using excel and @risk: retirement simulation

Topic #13Monte Carlo Simulations using Excel and @Risk: Retirement SimulationFinancial ModelingL. Gattis1Financial Simulation Process1. Create a model that estimates a future outcome (e.g., asset price, payoff, portfolio value) which has a stochastic variable such as asset returnStochastic variables are those in which the future value is uncertain (non-deterministic) – i.e., more than one possible outcome2. Specify the distribution of the stochastic variables (and their correlations)E.g., asset returns are normally If there are multiple stochastic variables -- specify the correlations3. Simulate many possible outcomes by randomly sampling from the specified distribution E.g., sample 5,000 possible values for asset return4. Evaluate the distribution of the outcomeE.g., mean, volatility, skew, confidence interval2Retirement Simulation1. Create a model that estimates a future outcome which has a stochastic variableEstimate retirement portfolio value and annuity where asset portfolio returns are uncertainAssume asset returns follow a random walk (GBM)2. Specify the distribution of the stochastic variables (and their correlations)Asset returns are normally distributedAssume 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 year4. Evaluate the distribution of the outcomeCalculate the mean and 10% highest and lowest real annuities and portfolio values3Geometric 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 SimulationAlt-Print Screen will copy window imagesOr right click on graph and select copy – then paste into excel5Open @RiskOpen Retirement Portfolio Simulation Excel FileChange Sim. Portfolio Formula, then copy down=(J12+C13)*(1+G13+NORMSINV(RAND())*H13)4. Click: Add OutputEnding Portfolio ValueReal Annuity5. Verify Model Window Inputs and Outputs6. Start Simulation (5,000 iter.)Open Fin Mod finplanmodel.xls@Risk Retirement Simulation67. View ResultsClick on Output cell, Click on Browse ResultsClick on detailed StatisticsAlt-Print Screen will copy the window as image to paste results8. Results WindowWhat’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 Seek7@Risk Advanced AnalysisSelect Goal SeekGoal Cell: Real Annuity (last)Statistic: MeanValue: 50,000Changing Cell: Savings Rate@Risk Advanced AnalysisSelect Goal SeekGoal Cell: Real Annuity (last)Statistic: Percentile(.05)Value: 25,000Changing Cell: ContributionBonus Problem8