Tài chính doanh nghiệp - Financial modeling - Topic 2: Estimating asset risk

Financial ModelingTopic #2: Estimating Asset RiskL. Gattis12ReferencesFinancial Modeling 3rd Edition by Simon BenningaCh. 8: Portfolio ModelsCh. 11: Estimating BetaCh. 35: Some Excel HintsCh. 36: User Defined Functions with VBALearning ObjectivesExpected asset risk measures are needed to construct optimal portfolios, plan for retirement, value equities and options, and forecast corporate cash flow distributionsIn this lecture, students will Compute asset return variance and standard deviationScale standard deviations across timeCompute moving average volatilityCompute volatility using EWMA modelsCompute implied volatility using the Black-Scholes Option Pricing Model3Return Data – Copy entire range4Arithmetic Mean5Histogram of IBM Monthly Returns6Data, Data Analysis, Histogram, Graph OutputRisk is often defined as the dispersion around the mean – often measured by standard deviation7History of The Normal DistributionDe Moivre (18th Century statistician and gambling consultant) finds the distribution of coin flips has orderDist. of heads in 12 coin flipsGalton BoardThis order is considered nature’s “normal” orderPattern often called a bell curve, or Gaussian distributionCentral Limit Theory: A large sample of independent, random observations is often assumed to be normally distributed (under certain conditions)Normal curves are used inLiving organisms’ height, weight, mass, mortalityE.g.; Male height μ=5’10”, σ =3”Intensity of lightWeather – rain, snow, temperaturesIQ Test Scores: μ=100, σ =10Asset returns? Distribution and RiskIf you assume that asset returns are normally distributed as a computational short-cut, then you can fully describe the distribution of returns (and risk) by the mean and standard deviation statisticsStandard deviation (σ) is the square root of the variance and is a measure or the “dispersion”Variance (σ2) is the average squared deviation from the average (or expected) return over n periods8Where µ=return meann=# of returnsrt=return at time tReturn Variance and Std Dev9Scaling standard deviation across time using the “square root rule”Annual σ = Monthly σ x SQRT(12)Monthly σ = Annual σ / SQRT(12) Daily σ = Annual σ / SQRT(252) assuming 252 trading days in a yearStandard Deviation and The Normal Distribution (two tailed statistics)Suppose the S&P 500 has an expected return of 7% (2% Rf + 5% MRP) and a historical σ of 16% and returns are normally distributed ; written E(R)~N(7% µ,16% σ) 1068.27% confident return between µ +/-1σ68.27% C.I. (7-16 to 7+16), (-9% to 23%) 95.45% confident return between µ +/-2σ95.45% C.I. (7-32 to 7+32), (-25% to 39%) 99.73% confident return between µ +/-3σ99.73% C.I. (7-48 to 7+48), (-41% to 55%) The values of 1, 2 , 3 are often represented by the variable ZTwo-Tailed Tests in ExcelIf E(R)~N(µ,σ)The +/-3σ X% confidence Interval = 99.73%The +/-Zσ X% confidence = 2*NORMSDIST(Z)-1Double click worksheet below to see excel functions11You multiple and divide by 2 because excel stat functions return 1-tail statisticsVolatilityStandard deviation (often called volatility in finance) changes over time.It’s often useful to see how standard deviations change over time.N-day Moving Average Volatility (MAV) graph show how standard deviations change over timelyCommon MAV terms are 20-day, 50-day, 100-day MOVs if using daily dataWe will now show a 12-month MOV for IBM12Moving Average VolatilityMAV is a graph of sub-sample volatilitiesIBM’s full sample annualized volatility is 18.54%, but is 18.67% in last 12 months and has been closer to 15% for the last several years13What volatility would you expect in next 12 months?To Graph:1. Select MAV column, Insert, Line Chart, 2. To add axis month labels: Right click, select data, edit horizontal axis, select axis data3. To reverse order: Right click, format axis, reverse orderEWMA VolatilityStandard deviation and variance compute a weighted average mean deviation where each observation is weighted equally (1/n below).Research has shown that volatility is autocorrelated (e.g., calm is more likely followed by calm)Exponential Weighted Moving Average Volatility (EWMA) is a volatility estimate that weighs recent data more heavily than older data when computing historical volatility (accounting for autocorrelation)1415EWMAHistorical standard deviation is the square root of variance -- variance is the average of squared deviations a. Historical “Equal Weight” Vol (σ) where each t is weighted 1/n b. EWMA: Exponentially Weighted Moving Average Volatility weights more recent deviations more heavilyWhere λ is a constant usually between .90 and .99. - Mean is often assumed to be zeroλ is called the decay factor ; 1- λ is the weight applied to most recent dataOptimal Decay Factors (Lambda)Source: JP Morgan’s RiskMetrics technical document(Although these factors are recommended for daily observations)16IBM EWMA Vol17GARCH is a model similar to EWMA that also incorporates long-term mean reversion of volatilityThe sum of weighting factors approaches 1 as the sample size gets largerSet Lambda as named rangeBSM Implied VolatilityThe Black-Scholes-Merton model computes the value of a call or put option given the asset spot price, the option maturity (in t years), the annualized risk-free rate of maturity t, the annualized dividend yield, and the annualized standard deviation of the asset returns. Using the BSM pricing formula, you can use Excel’s Goal Seek to compute the standard deviation (volatility) that ensures the BSM value is equal to the market priceThis BSM implied volatility has been shown to be a reliable predictor of future realized volatility18Black-Scholes-Merton FunctionsFunction BSCall(s, k, v, r, t, d) d_1 = (Application.Ln(s / k) + (r - d + (v ^ 2) / 2) * t) / (v * t ^ 0.5) nd1 = Application.NormSDist(d_1) d_2 = d_1 - v * t ^ 0.5 nd2 = Application.NormSDist(d_2)BSCall = s * Exp(-d * t) * nd1 - k * Exp(-r * t) * nd2End FunctionFunction BSPut(s, k, v, r, t, d) d_1 = (Application.Ln(s / k) + (r - d + (v ^ 2) / 2) * t) / (v * t ^ 0.5) minus_nd1 = Application.NormSDist(-d_1) d_2 = d_1 - v * t ^ 0.5 minus_nd2 = Application.NormSDist(-d_2)BSPut = -s * Exp(-d * t) * minus_nd1 + k * Exp(-r * t) * minus_nd2End Function19Copy functions, alt-f11, insert module, paste .. Then insert function using the data on the following slideInternal variables (values computed in function)Parameters (values given by users“Application.” object to use many excel built-in functions in user-defined functionsBSM Implied Volatility of ATM 1-yr Call20Implied volatilities have been shown to more accurate than historical values, since forward looking and based on option trader’s view (and money)Use Goal Seek to set BS price = market price, by changing standard deviationBSM Implied Volatility of ATM 1-yr Call21Use Goal Seek to set BS price = market price, by changing standard deviationThe VIX “Fear Index”The VIX is a widely quoted index of implied volatilities for the U.S. equity market for options that expire in the next 30 days2223Suppose you need an IBM σ estimate for two 3 purposes:Valuing a 1-month call optionPerforming a 1-year portfolio optimizationValuing IBM stock using a 10-year horizonJustify your three estimates? You may select one shown here or use your own estimate based on the data.24This course will always use population variance (VARP) and population standard deviation (STDEVP) statistics for all lectures, assignments and exams. Sample statistics should be used when dealing with small samples in the real world. The only difference is that the sample statistics compute the average squared deviation by dividing the sum of squared deviations by N-1 observations (not N). In practice, there are many observations, so the difference is often considered immaterial.Lecture WorksheetLearning ObjectivesExpected asset risk measures are needed to construct optimal portfolios, plan for retirement, value equities and options, and forecast corporate cash flow distributionsIn this lecture, students will Compute asset return variance and standard deviationScale standard deviations across timeCompute moving average volatilityCompute volatility using EWMA modelsCompute implied volatility using the Black-Scholes Option Pricing Model25Want more?