1 / 33

Financial Simulation Using @Risk N. Gershun

Financial Simulation Using @Risk N. Gershun. Overview of @RISK. @Risk is a spreadsheet add-in that has two advantages. @Risk provides easy access to many probability distributions. Add-ins make it easier to develop simulation models than Excel alone. Overview of @Risk features:

joannar
Download Presentation

Financial Simulation Using @Risk N. Gershun

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Financial Simulation Using @Risk N. Gershun

  2. Overview of @RISK • @Risk is a spreadsheet add-in that has two advantages. • @Risk provides easy access to many probability distributions. • Add-ins make it easier to develop simulation models than Excel alone. • Overview of @Risk features: • Many built-in input probability distributions. • Output cells can be specified. @Risk maintains statistics for these cells across replications. • The Risksimtable command allows the user to run the simulation several times for different values of input parameters.

  3. Overview of @RISK • The development of a simulation model is a two-step process: • Build the model, that is, the logic that transforms input (e.g., stock price) into output (e.g., profit/loss on a portfolio). • @Risk automatically replicates the model with many random input values, reporting output as requested. • @Risk takes any spreadsheet (e.g., income statement, cash flow statement) and specifies that some of the cells are uncertain. @Risk then repeats the simulation many times and computes statistics across replications. • @Risk is really an advanced sensitivity analysis since it studies how changes to key input parameters impacts output. • Although we use @Risk, a similar add-in is Crystal Ball.

  4. Fitting a Probability Distribution • Historical data are needed to estimate probability distributions and values for uncertain parameters. • Suppose we believe that the probability distribution of stock returns can be estimated using the past data that was gathered and appears in the returns.xls file.

  5. Fitting a Probability Distribution • We can use the “Fit Distributions to Data” button in @Risk to analyze the data. After selecting this button, enter the following information: Excel Data Range, Type of Data (Sampled Values), No Filtering Options, Continuous Domain, and OK. • @Risk will analyze the data and use goodness of fit statistical tests to rank order probability distributions and their parameters for the data entered. • We are now ready to create our first @Risk model.

  6. The problem • Suppose today is October 9th 2010, and the current price of oil is $3.05 per gallon. • You want to purchase 500,000 gallons of heating oil 5 months from now (March 9th, 2011). • To hedge your risk, today you long x futures which give you the right to receive x gallons of heating oil for $3.10 on April 9th, 2011. • Suppose you buy the heating oil and also close out your futures contract on March 9th, 2011. What can happen?

  7. What can happen? • The price of heating oil increases between now and March 9th, 2011 • Cost of buying oil goes up (bad). • The value of futures contracts increases because you have the right to receive a more valuable commodity, sell it on the open market and make some money (good) . • If the price of heating oil drops • Cost of purchasing oil decreases • the value of the futures drops  Going long the future has created a hedge that reduces the effect of change in the price of heating oil between now and March.

  8. Brief Review of Futures Contracts • Buying (shorting) a futures contract means that you bought a right to buy (sell) a pre-specified asset (underlying asset) at a pre-specified price (called futures price, F) at a pre-specified time. • Pricing Formula (ignoring storage costs): F = Sert (1) S = the current spot price of an underlying asset r = risk free rate t = time to expiration

  9. What do you need to decide? You need to determine how many futures to go long.

  10. Assumptions: • Oil prices follow a Lognormal distribution • mean growth rate  = 0.38 • standard deviation of the growth rate  = 0.30 • We are hedging by buying oil futures that expire on April 9th, 2011. We close out the futures on March 9th, 2011.

  11. Assumptions (cont.): • Equation (1) tells us that given the current risk free rate r and the current spot price S, a futures contract of duration t should sell for F= Sert. • In reality futures prices will vary around this “expected price.” • Futures price follows normal distribution with the mean given by equation (1) and the standard deviation of 10%.

  12. Assumptions (cont.): • Assume for the moment that we are buying 500,000 futures, the same number as the number of gallons of oil we have to purchase. • The risk free rate is constant at 2% per annum. PROCEED TO THE SIMULATION

  13. Step 1: Input Parameters • In B6 enter the current market price of oil ($3.05) • In B7 enter the annual risk free rate (2%) • In B8 enter the annual volatility (standard deviation) of the oil prices (0.3) • In B9 enter the mean growth rate (drift) of oil prices (0.38) • In B10 enter the standard deviation of futures prices (0.1)

  14. Step 1: Input Parameters (cont.) In B11 enter the futures time to expiration (0.5 year or 6 months) In B12 enter the price of April 9th futures as of today (October 9th). It is $3.10 In B13 enter the amount of oil you want to buy (500,000 gallons) Number of futures bought (500,000 as a starting point).

  15. Step 2: Input Formulas • In B15 enter the formula to generate the March 9th spot price of oil, five months from now (t = 5/12). We use the formula for a log-normal random variable: St = S0exp[(-0.52)t+Z(0,1)t] (2) St = price of oil at time t S0 = current spot price of oil Z(0,1) = standard normal random variable

  16. Step 2: Input Formulas(cont.) In B15 enter the formula: =B6*EXP((B9-0.5*B8^2)*(5/12) +risknormal(0,1)*SQRT(5/12)*B8) In B16 enter the formula determining the mean price of oil on March 9th, 2001 (use equation (1)): =B15*EXP((1/12)*B7)

  17. Step 2: Input Formulas (cont.) • In B17 build in the 10% standard deviation of the actual future price from the expected future price, thus getting the actual price of the April 9 futures on March 9. = risknormal(B15,B10*B16) • In B19 compute the cost of buying oil at the March 9th spot price =B15*B13 • In B20 compute the revenue earned from selling futures on March 9th as =B17*B14

  18. Step 2: Input Formulas (cont.) • In B21 we compute the cost of buying our futures on October 9th with the formula =B12*B14 • In B22 find the total cost as: oil purchase cost +futures purchase cost –futures sales revenue =B19 + B21 – B20

  19. Step 3: Select Simulation Settings • Select B22 (total cost with futures) and B19 (total cost without futures, i.e., just buying the oil) as the outputs and run the simulation. With cell B22 highlighted, choose the Add Output button to make this the output cell. Do the same with B19. • Click on the Simulation Settings button and select 1000 iterations and 1 simulation in the Iterations tab. • In the sampling tab of the Simulation Settings button, select Latin Hypercube, Standard Recalc (Monte Carlo), Fixed = 1, All, check Save as Default, and OK.

  20. Note on Sampling • Sampling is the process by which values are randomly drawn from the selected distribution. • In @Risk, during each iteration of the simulation, one observation is chosen from the input distribution. • As the number of iterations increases, the sample of observations more closely resembles the input distribution. • When running a simulation, it is important that all areas of the input distribution get sampled, especially the low probability (high uncertainty) areas. If not, uncertainty will seem less than it actually is.

  21. The Concept of “Efficiency” • Statisticians have developed different ways to sample (or draw) from distributions. • If we could do an infinite number of iterations in our simulation, these methods would produce equal results. • However, since we use a finite number of iterations, different sampling methods do not produce equivalent results. • A sampling method is considered more efficient than another if it approximates a distribution with fewer iterations. • Two popular sampling methods: • Monte Carlo Simulation • Latin Hypercube

  22. MonteCarloSimulation • Monte Carlo simulation draws samples from the full range of the distribution on each draw. • It is an entirely random sampling technique. • Requires a large number of iterations to adequately approximate the input distribution. • Why? Most observations drawn are closer to the mean. Creates clustering. The tails (areas of high uncertainty) are usually underrepresented in the sampling.

  23. Latin Hypercube samples from all parts of the distribution, reducing clustering. Not entirely random (is a “stratified” sampling method) Latin Hypercube divides a distribution into intervals (strata) of equal probability and randomly draws from each interval. Insures that all portions of the distribution are sampled, including the tails. Latin Hypercube sampling is more efficient than Monte Carlo sampling: Requires fewer iterations. Latin Hypercube

  24. Example: • Suppose we sample 8 times from a normal distribution. With Monte Carlo sampling we might get: Notice that the tails (high uncertainty areas) are not adequately represented. This results in underestimating risk Source: Modeling the Future: The Full Monte, the Latin Hypercube and Other Curiositiesby Glenn Kautt, CFP, and Fred Wieland, Ph.D., FPA Journal

  25. Example continued • With Latin Hypercube, we would get: Notice that even with only 8 observations, the tails are much more adequately represented. This results in a truer representation of risk. The area in each strata is equal but the width of each strata varies. Source: Modeling the Future: The Full Monte, the Latin Hypercube and Other Curiositiesby Glenn Kautt, CFP, and Fred Wieland, Ph.D., FPA Journal

  26. Step 4: Select Report Settings • Click on the Report Settings button and select: • Show Interactive @Risk Results Window • Generate Excel Reports Selected Below • Simulation Summary • Output Graphs, • Active Workbook • Metafile • Check Save as Default, and OK.

  27. Step 5: Run the Simulation • To run the simulation, select the Start Simulation button. • @Risk will create a Results window. • Choose the Detailed Statistics Window to display additional output.

  28. Simulation Results

  29. Step 6: Choosing the Number of Futures to Buy (Long) • The objective of this problem is to choose the number of futures contracts that minimizes the cost of buying oil and reduces the risk of this transaction. • @Risk can automatically evaluate several possibilities on the same set of random oil prices and futures prices. The Risksimtable command accomplishes this. • Enter “Number of Futures to Buy” in cell A25 and the desired quantities in cells B25 – F25: 200,000, 300,000, 400,000, 500,000, and 600,000.

  30. Step 6: Choosing the Number of Futures (cont.) • In cell B9 enter: =Risksimtable(B25:F25) • Set number of iterations to 1000. • Set number of simulations to 5. • Run the simulation.

  31. Simulation Results

More Related