Tài chính doanh nghiệp - Topic 6: Computing portfolio value - At - risk (var), random walk simulations, macros and @ risk simulations

Financial ModelingTopic #6: Computing Portfolio Value-At-Risk (VaR), Random Walk Simulations, Macros and @Risk SimulationsL. Gattis12ReferencesFinancial Modeling 3rd Edition by Simon BenningaCh. 15: Value at RiskCh. 22: Monte Carlo simulation Ch. 29: Generating Random NumbersLearning ObjectivesUnderstand how VaR measures the risk of a portfolioCompute static portfolio VaR using Formulas and normal distribution functions,A Monte Carlo simulation of a random walk model of asset returns,and @RiskWrite VBA Macros using “For Loops” and the “Cells” objects3Value at Risk (VaR)VaR is the most you should expect to lose with a given confidence interval and time period.E.g. if the 95% confidence interval, 1-Year, VaR = $10,000. 95% confident that losses will not exceed $10,0005% probability of losing $10,000 or moreOne-tailed test statistic4$10,000 Loss95%5%5Analytical VaRIf portfolio returns are normally distributed with expected return “µ” and standard deviation “σ”, then the X% Confidence Interval VaR for a portfolio value of V$ is:Where Zx is derived from the normal distribution functionX=90%Tail=10%Z90%=1.28VaR(1Yr, 90%)=V(μ-1.28σ)VaR(1Yr, 95%)=V(μ-1.65σ)VaR(1Yr, 99%)=V(μ-2.33σ)X=95%Tail=5%Z95%=1.65X=99%Tail=1%Z99%=2.33Data6Analytical VaR7VaR=-v*((ua/252)*t-z*(sa/252^0.5)*t^0.5)Simulated VaR and GBMTo estimate the risk of a portfolio over multiple periods with contributions you must simulate periodic asset price movementsA standard model for simulating asset prices is Geometric Brownian Motion (GBM)-- also called the Random Walk ModelThe GBM model has two components(1) Drift: the likely price appreciation of the asset (r-d)Expected Total Return – Dividend YieldIf assume d=0, it is assumed that dividend are reinvested(2) Noise: Random shocks which are assumed to be normally distributed 8GBM model of asset valuesAssuming returns are normally distributed with annual mean (μ) and standard deviation (σ), the simulated value of portfolio V in one year is: - In this case, Z is a random number drawn from a standard normal distribution (written Z~N(0,1)) - Excel Z approximation is: Normsinv(rand())Simulating Portfolio Values(Using Geometric Brownian Motion / Random Walk Model)DriftNoise/ShockSimulating Portfolio Values10To compute the Monte Carlo simulated VaR, Hit F9 (Recalc) 5,000 times and record the portfolio valuesThe 95%, 1-year VaR is the difference between the portfolio value ($1,000,000) and the 5% (250th) smallest simulated value11MacroA Function is used to make calculations and return a value to a cell or range of cellsA macro is used to automate tasks, for example:Copy value or formula from one cell to another cellChange formatsDisplay messagesMacros (also called Subroutines) are also written in modules and have a similar structure as functionsSub macroname ()executable statementsEnd subMacros are subroutines that have no parameters, ()Macros are run from developer/macro or alt-f8 or attached to an art objectTo attach a macro to an art object: Insert object, right click, assign macroSimulation Macro“For Loops” in VBA are used to repeat statements for a known number of timesSub simvar5000 () For i = 1 To 5000 Cells(3, 9) = 5000-i Cells(5 + i, 9) = Cells(17, 7) Next iEnd Sub12Simulating Portfolio Values: 1-Year13@Risk@Risk is a simulation add-in for Excel used to run Monte Carl Simulations@Risk automates the task of running many iterations for a variableIn additional to the normal distribution, @Risk also allows you to choose other distributions Launch @Risk after opening Excel to access @Risk menu14@Risk SoftwareStudent Version ($50) or Free 15-Day Trial Version of Palisades @Risk (Excel Add-in)The Full Version price is $1,595. The student version price is $50: Trial Versions are free and expire 15 days after installation. You may want to wait to install the trial version until the first class. @Risk is not compatible with Mac OS. Run on Windows or join a team for assignments. Steps to Create an @Risk SimulationBuild an excel model with at least one stochastic variable and one outputStochastic variables have more than one possible outcome (such as portfolio return)Eg. Stochastic Variable = Random Zs, Output = Portfolio ValueDefine distributions for the stochastic variableWe will us the normal distribution for asset returnsRun many iterations of the stochastic variables and evaluate the distribution of the output16@Risk VaRReplace NORMSINV(RAND()) with RISKNORMAL(0,1) in simulated valueClick on Simulated Value and select “Add Output” from @Risk ModelSelect 10,000 iterationsRun SimulationClick on final portfolio value and browse resultsVaR is the difference between the initial portfolio value and the 5% lowest valueExplore distribution in @Risk17@Risk18What is the 95% confidence interval portfolio value?What is the probability that losses will exceed $100k?You can copy graphs in @Risk and use in excel or presentations.. Right click, copy @Risk Simulation Results19What is the 95% confidence interval portfolio value?What is the probability that losses will exceed $100k?You can copy graphs in @Risk and use in excel or presentations.. Right click, copy Learning ObjectivesUnderstand how VaR measures the risk a portfolioCompute static portfolio VaR using Formulas and normal distribution functions A Monte Carlo simulation of a random walk model of asset returnsand @RiskWrite VBA Macros using “For Loops” and the “Cells” objects20