Major Assignment 2: Monte Carlo Simulation

For this assignment, you will be pricing an Asian call option on a stock using Monte Carlo simulation.

A brief about options

A basic European style call option is a contract that gives the contract owner the right, but not the
obligation, to purchase the underlying asset (such as a share of stock) at a fixed price (called strike
price, denoted as K), at a particular time in the future. Thus, the option has value if the strike price is
below the current price (or spot price, denoted as S) of the asset, since the contract can be exercised,
underlying asset can be bought at strike K, and immediately resold at the higher spot price S for a profit
of (S - K). If the spot price is lower than the strike, the holder of the option will not choose to exercise
and buy at K, and the option is worthless. Due to the 'option' nature of the contract, it can never be
worth less than zero. Thus, the value of the option can be written as max(S - K, 0).

An Asian call option is an exotic variant of a call option, which has a payoff based on the average price
over the term of the option, rather than the spot price at the time of exercise. Thus, the payoff at
maturity is max(A – K, 0), where A is the average price. Just as a regular call option, its value can never
be negative, profit is capped at 0 at the bottom.

To read more about options, see https://en.wikipedia.org/wiki/Call_option and/or search the web for
other sources.

Monte Carlo Simulation

It is obvious what the value of an Asian call option is at its expiration – it is either 0, or the difference
between average price over its lifetime and the fixed strike price. But how can we figure out a fair price
for it beforehand? If we are buying an option with some time yet until expiration, we don't know what
the future value of it is going to be, so what we need is to come up with an estimate for the expected
value of the possible payout.

The distributional complexity of the payoff makes an analytical solution intractable, so Monte Carlo
simulation is used to estimate the expected value, once we make some reasonable distributional
assumptions about the possible future changes in the price. By simulating the random process
thousands of times and averaging the results, we can get a reasonable estimate of the expected value of
the option.

Read more about Monte Carlo simulation at the end of Chapter 5 of the text, which also goes over a
high-level outline of using Monte Carlo to price Asian options.

Instructions

Use the yellow shaded cells in the template spreadsheet to calculate your results. Do not hard-
code any results – all calculations should be done within your spreadsheet!

For our simulation, we will attempt to price an Asian call option with the following characteristics:
* K = $45 (strike price)
* T = 60 (trading days to maturity)
* Payoff = MAX(Average(S1 ... S60) – K, 0)
We will assume the following characteristics for the underlying stock:
* S = $50 (current spot price of stock)
* Continuous daily returns are distributed as Normal(0.046%, 1.459%)

We will further assume that the 60-day risk free rate is 0.2%. (Not annualized, this is just the rate you
can earn over a 60 day holding period, to keep things simple.)

We will conduct the 60-day simulation 100 times, and thus derive 100 possible outcomes for the price
of our Asian call. We can then calculate expected value and standard deviation for the estimate of value
of the call option.

Open up the template spreadsheet in your favorite spreadsheet software, and follow the steps below.
Enter all your results into the yellow-shaded cells of the template.
Note: the template file has two worksheets!

1. First, let's play around with random number generation, using the first worksheet in the file. Our
bread and butter random number generating function is RAND(), which generates a random number
from a uniform distribution between 0 and 1, U(0,1). Generate 100 U(0,1) variables in the
correspondingly labeled column.

Notice how the spreadsheet automatically recalculates all values whenever you change a cell, which
includes automatically regenerating random numbers! That can be switched to manual recalculation,
but don't let that bother you, unless it is noticeably delaying your work.

2. As discussed in the text, we can transform the U(0,1) distribution into any other distribution by using
the inverse CDF function of our desired distribution. In the next column, transform the U(0,1) variables
into a Normal(0,1) by using the NORMSINV() function, which is the inverse standard normal CDF
function.

3. Create the frequency counts for the U(0,1) and the N(0,1) observations, in the appropriate yellow-
shaded areas for each one, using either COUNTIFS() or FREQUENCY() function. The bin cutoffs
have already been prepopulated for you, just count the observations!

4. Create the histogram plots for each frequency distribution, and place them in the appropriate shaded
areas in the sheet. Feel free to force regeneration of the random variables a few times (hit the F9 key),
and observe how the histograms change.

You should notice how the uniform histogram looks essentially pattern-less, being flat on average,
while the normal histogram looks bell-shaped. That means our random number generation is working
properly!

Let's move on to the second worksheet in the file and try our hand at Monte Carlo simulation.

5. Observe the gray-shaded "play area" in the top left of the spreadsheet, which is here to help you
understand the flow of the simulation.
In the first column we have a timeline. Not too exciting.
Second column generates a bunch of random numbers, from the Uniform(0,1) distribution, using
the RAND() function
 Third column, we transform the U(0,1) variables into a Normal(0,1) by using the NORMSINV()
function. Traditionally N(0,1) observations are denoted as Zi, which is how we will refer to
these observations going forward.
Fourth column, we calculate the daily return, distributed as N(mean, sd) based on the mean and
standard deviation of returns that we are assuming for this underlying asset, with the usual
transform of
Ni = mean + sd*Zi
Finally in the fifth column, we calculate the prices, starting from our starting price of 50, by
accumulating the continuously-compounded daily returns that we generated for each day. Each
day's price is the previous day's price times the daily interest factor. Since these are
continuously compounded returns, the interest factor is not (1+r), but exp(r), so the next price is
calculated as:
St+1 = St * exp(r)
And thus we have simulated the price progression of the underlying stock, based on the assumption
of normally-distributed continuously-compounded daily returns.

We of course don't want to deal with all those columns for every one of our 100 simulation runs, so we
can just put it all together, to generate the price on any given day as:
St+1 = St * exp(r) =
St * exp(mean + sd * Z) =
St * exp(mean + sd * NORMSINV(RAND()))
This is accomplished as an example in the next column, 'price all at once'.

6. Create the price simulation runs in the yellow-shaded area below. First, calculate the price at time 1
for the first run, using the formula above. Make sure to lock in the cell references to our input
parameters, the mean and standard deviation of returns! Lock both the row and the column
coordinate (that is, put $ in front of both row and column, such as "$B$2"). Now, fill it down to
time 60, and then fill the whole column sideways to Run 100.

7. Calculate the average price for each run, as well as the final option payoff for each run. Remember to
use the MAX() function to cap off the option payoff at 0 from the bottom. The option should never be
worth a negative amount!

8. Calculate the mean option payoff and the standard deviation of option payoff.

9. Calculate your best estimate of option value, as the discounted present value of the mean option
payoff, using our assumed 60-day risk free rate as the discount rate.

Discussion

Hit F9 (shortcut to recalculate the spreadsheet) a few times and observe how stable the option price
estimate is. You should see that it can easily change within about a dollar range or so. This is definitely
not enough precision! In real life, we'd need to conduct a much larger number of simulation runs to get
a more precise price estimate.

How large? As we will learn from Chapter 6, Sampling and Estimation, the standard deviation of
sample mean is the sample standard deviation divided by the square root of the sample size. From your
simulation, you should observe that the standard deviation among individual runs is about 3.3. This
means that the standard deviation of the 100-sample means is 3.3/sqrt(100) = 0.33. So if you conduct
million simulation runs, the standard deviation of sample mean would be 3.3/sqrt(1E6) = 0.0033, less
than a penny.

A spreadsheet is not a great tool for such high-volume calculations, since not only does it calculate
things (which computers can do pretty fast), it also displays everything it calculates (which is quite a bit
slower). But it is great for didactic purposes, precisely because it displays everything you are doing!