Tài chính doanh nghiệp - Topic 14: Selecting distributions, distribution fitting and the normal curve using @risk l. gattis

Financial ModelingTopic 14:Selecting Distributions, Distribution Fitting and The Normal Curve using @RiskL. GattisLearning ObjectivesSelect distributions other than the normal distributionSimulate portfolio returns and free cash flows by fitting a distributionInsert distributions using @Risk menuSimulation Process1. Create a model that estimates a future outcome which has a stochastic variableE.g., Estimate PV of FCF/share to value a stock, FCF, Option Value, Profits2. Specify the distribution of the stochastic variables (and their correlations)E.g., Normal, Uniform, Triangular, General DistributionCorrelate or assume zero (uncorrelated independent variables)3. Simulate many possible outcomes by randomly sampling from the specified distribution E.g., Simulate 10,000 sets of values for each stochastic variable (correlated or uncorrelated)4. Evaluate the distribution of the outcomeRiskmean, riskpercentile, risktarget3@Risk DistributionsSymmetry (Skew), Peak, Bounds (Min/Max), Slope, Continuous (or discrete)Coin tossPricesReturnsWait TimeGradesBond Recovery Rates“Fat” NormalFinancial Asset DistributionsAsset Returns: Normal (Gaussian) distribution is often utilized. Unbounded, symmetric, single-peakedAsset Prices: Lognormal distribution is often utilized.Bounded (prices are non-negative) and positively skewedIf asset returns are normally distributed, it follows that asset prices are lognormally distributed. In other words, a lognormal price distribution implies a normal return distribution – and vice versa.Let’s now look at the historical distribution of the S&P 500, T-Bonds, and T-Bills.Data and Portfolio Mean and Vol (Copy into excel)S&P 500 Market Returns and Histogram (1928-2013)S&P 500 Market Returns Impirical DistributionS&P 500 Market Returns and Normal Distribution Formula Predictions@Risk Normal Distribution ModelingInsert RISKNORMAL(μ,σ) Functions in Excel for SP500, Bonds, and Bills using historical μ and σAdd correlation matrix for three @Risk distributions@Risk Normal Distribution ModelingDefine Normal Distributions for 3 AssetsCopy@RiskDefine Dist.Assign mean and vol from ExcelCopy for Bills and BondsAdd correlation MatrixTo add a correlation matrix in @RiskCreate a correlation matrix in excel@Risk Model windowSelect inputs you want to correlateRight click, correlate, Create newBe sure to re-arrange correlations to match your worksheet correlations (@Risk uses alphabetical order), select column and move to correct order as shown in worksheetRight click on matrix, copy coefficients from excel (select range)Select correlation matrix from worksheetSelect “location” for @Risk to output the matrix in the worksheet – this is the one that @Risk uses@Risk Normal Distribution ModelingCompute portfolio mean using @Risk distributions (Weights * @Risk Returns)Add RISKOUTPUT to (Weights * @Risk Returns)Run simulation (10,000 iterations)5% Left Tail (RISKPERCENTILE(Output,.05)Prob PriceEstimate distributions of stochastic values and buy if price distribution mean > PriceInvesting in the Stock MarketStocks have the highest expected return than any other asset class (Bonds, Stocks, Commodities, etc.)  100% StocksStocks have the highest expected return and volatility than any other asset class (Bonds, Stocks, Commodities) and correlations between asset classes are low  Buy portfolio of assets that meets your risk-return tolerance which changes as you ageBuying Insurance (Auto, Home, Life, Health)Most likely not crash, get sick, die, house fire this year  Don’t InsureCrash, sickness, death, house fires are possible and devastating  Insure large assets (and self if you have dependents)Drinking and DrivingMost likely you will not get caught tonight  DrinkYou could get caught and the effects are devastating  Don’t DrinkLearning ObjectivesSelect distribution other than the normal distributionSimulate portfolio returns and free cash flows by fitting a distributionInsert distributions using @Risk menu