Transcript Decision Technology - Parallel Programming Laboratory

Financial Simulation
Using @Risk
N. Gershun
Overview of @RISK

@Risk is a spreadsheet add-in that has two
advantages.
1.
2.

@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.
Overview of @RISK




The development of a simulation model is a two-step
process:
1.
Build the model, that is, the logic that transforms input
(e.g., stock price) into output (e.g., profit/loss on a
portfolio).
2.
@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.
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.
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.
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?
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.
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=
rt
Se
(1)
S = the current spot price of an underlying asset
r = risk free rate
t = time to expiration
What do you need to decide?
You need to determine how
many futures to go long.
Assumptions:
 Oil
prices follow a Lognormal distribution
growth rate  = 0.38
 standard deviation of the growth rate  = 0.30
 mean
 We
are hedging by buying oil futures that
expire on April 9th, 2011. We close out the
futures on March 9th, 2011.
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%.
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
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)
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).
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 lognormal 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
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)
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
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
A
B
Hedging Petroleum Risk
October 9 oil price per gallon
3.05
Risk free rate
0.02
Volatility of oil price
0.3
Mean growth rate of oil price (drift)
0.38
Percentage deviation of futures price
from its expected value
0.1
Futures duration
0.5
April futures price on October 9th
3.1
Gallons of oil bought
500,000
Number of futures long
500,000
March 9th price of oil
=B6*EXP((B9-0.5*B8^2)*(5/12)+RiskNormal(0,1)*SQRT(
Mean of March 9th price of April
futures
=B15*EXP((1/12)*B7)
Actual March 9th futures price
=RiskNormal(B16,B10*B16)
Cost of buying oil
=B15*B13
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.
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.
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

Monte Carlo Simulation
 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.

Latin Hypercube
 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.
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 Curiosities
by Glenn Kautt, CFP, and Fred Wieland, Ph.D., FPA Journal
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 Curiosities
by Glenn Kautt, CFP, and Fred Wieland, Ph.D., FPA Journal
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.
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.
Simulation Results
Name
Cell
Min
Mean
Max
95%
percentile
Total cost without
futures
B19
$903,680
$1,786,812
$3,644,913
$2,406,835
Total cost with
futures
B22
$789,661
$1,544,200
$2,145,186
$1,842,001
Total Cost with Futures
5.0%
90.0%
1.253
0.01
1.842
5.0%
0.008
0.006
Total Cost with Futures
0.004
0.002
Values in Millions ($)
2.2
2
1.8
1.6
1.4
1.2
1
0.8
0
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.
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.
Simulation Results
Number of Futures
to Buy
200,000
300,000
400,000
500,000
600,000
Average Total Cost
$1,690,943
$1,643,084
$1,595,225
$1,547,366
$1,499,507
SD Total Cost
$223,889
$179,717
$163,056
$181,640
$226,971
95% Risk Percentile
(VaR)
$2,103,529
$1,972,014
$1,897,501
$1,835,359
$1,852,763