# Excel Vba Monte Carlo Finance Model - PDF

Document Sample

```					Exotic FX Derivatives Pricing using Monte Carlo Simulation in Excel VBA: FX Lookback Options

FX Lookback Options                                                               Monte Carlo Simulation of the FX spot rate
FX Lookback options are call and put options where the payoff depends on          Monte Carlo simulation is a numerical method used in finance to solve
the maximum or minimum spot exchange rate reached during the life of the          partial differential equations such as the Black Scholes equation for pricing
option.                                                                           FX options.

Suppose that a British company expects to receive a cashflow of 100 million       The basic tenet of Monte Carlo simulation is that there is a stochastic
US dollars at some unknown date over the next year from the company's US          variable in an equation that can be sampled many times over. When the
subsidiary. The company is likely to want to ensure that the amount
result of the many simulations of a function containing the random variable
received in sterling is calculated using the minimum spot rate of USD/GBP
over the year, as this will maximise the amount received in pounds sterling.      is averaged, it approximates to the real mean. This is a result from the
Central Limit Theorem of probability.
The payoff from a European lookback call is the amount that the final spot
exchange rate exceeds the minimum spot exchange rate achieved during              The variable being sampled in Monte Carlo simulation usually comes from
the life of the option. This can be summarised as S(t =T) - S(min, 0 <= t <= T)   the uniform distribution as simulated by a random number generator. In
where S is the spot rate, T is the time to expiry.                                finance the most common transforms are to the normal distribution for use
in lognormal price paths such as those for Black Scholes. However, the
The payoff from the a European lookback put option is the amount by which         random variable can be transformed to Beta, Gamma, Poisson distributions
the maximum spot exchange rate during the life of the option exceeds the
depending on the pattern exhibited by the data that is being modelled.
final spot rate. This can be summarised as S(max, 0 <= t <= T) - S(t=T)
For FX derivatives pricing, Monte Carlo simulation can be used to model the
In this document we look at resolving the prices of the Lookback options via      path of spot FX rates. The spot rates are assumed to follow a geometric
Monte Carlo simulation.                                                           Brownian motion as per the Black Scholes model. The payoffs of the
derivative and therefore the price depend on the path followed by the spot
rate. The spot rate path and payoffs are simulated many times over and
averaged, then discounted by the risk-free interest rate to give a price for
the FX derivative.

With exotic FX derivatives, the payoffs are usually a function of the price
path. As such, derivatives such as Lookback options are called path-
dependent options.

Exotic FX Derivatives Pricing using Monte Carlo Simulation in Excel VBA: FX Lookback Options

Black Scholes FX derivatives – the Spot Rate                                         Coding the MC simulation in Excel VBA
According to the Black Scholes model, the FX spot rate is defined by the              The first coding issue is, do we use a VBA subroutine or a custom function?
following stochastic partial differential equation                                   Typically a function is used for numerical calculations. This is especially
useful in a Monte Carlo simulation context as using subroutines induces
dS = (r - rf -1/2sigma^2)dt + sigma dz                                               complications with the persistence of variables after the simulation has
been run, especially if the variables are declared as static or public. This
where dz is a standard Brownian motion, defined by dz = epsilon * sqrt(dt)           would need to be done for compartmentalisation of code if using a
where epsilon is a standard normal random variable; dS is the change in              subroutine as one would need to code the setting of input ranges, as well as
spot rate, r is the domestic risk-free interest rate, rf is the risk-free interest   output ranges. If using a subroutine, it quickly becomes complicated and
rate of the foreign security, sigma the volatility of the FX spot rate.              necessitates the use of classes. Much easier to use functions.

As such the price at time 0 < t <= T is given by                                     The function therefore takes in 8 parameters, S, K, intervals, r, rf, t, sigma, N
(number of simulations) and should return the call price and the put price.
St = S0 * exp( (r – rf - ½ sigma^2) dt + sigma * epsilon * sqrt(dt))
We need to create simple container variables that store the minimum and
For path dependent options such as Lookback options, we divide the life of
maximum prices at each interval. These will be used to calculate the payoffs.
the option into a number of intervals, and adjust the volatility and the value
of dt accordingly. As such, dt = T/intervals and sigma = sigma / sqrt                To return the call and put prices, we need to create a function that returns
(intervals).
an array.
For Lookback options the maximum and minimum stock price is calculated
The next issue with Monte Carlo simulations is performance. Excel VBA has
at the end of each interval over the life of the option.
late and early binding depending on the data type declarations. Late binding
We then calculate the payoffs as described above and discount them using             slows down VBA code execution noticeably over many different simulation
the discount rate in order to obtain the option prices.                              cycles. As such, it is to be avoided. This is done by not defining the variables
of the function as variant, and by explicitly defining each and every
The next step is to apply the time value of money by discounting the                 variable’s data type, including the array returned by the function.
expected option price by exp (-rt).
So where a ‘lazy’ function declaration may look like

Exotic FX Derivatives Pricing using Monte Carlo Simulation in Excel VBA: FX Lookback Options

Function Black_Scholes (S, K, intervals, r, rf, t, sigma, N)
[Calculate the average call option and put option payoffs]
The ‘proper’ way to define the function would be                                 [Discount the call option and put option payoffs to give the respective price]
[Write the call and put option prices into an array]
Function Black_Scholes (S as double, K as double, intervals as long,             [Return the array]
interval_start as long, interval_end as long, r as double, rf as double, t as
double, sigma as double, N as long) as double
Generating Random Numbers in Excel VBA
Depending on how we wish to use the function in future, we may also want         The random number generator is given by the Excel VBA Rnd function,
to define the parameters as being passed in by value. This would protect         which generates random numbers between 0 and 1. These random numbers
encapsulation of the function if calling it from a subroutine – an important
are supposed to be uniformly distributed across the interval of 0 and 1. The
step when moving towards an object-orientated approach.
degree of uniformity impacts the efficiency of the Monte Carlo simulation as
Function Black_Scholes (ByVal S as double, ByVal K as double, ByVal r as         it impacts the rate of convergence of the simulation-derived price to the
double, ByVal rf as double, ByVal t as double, ByVal sigma as double) as         actual value. The problem with Excel’s random generator is that it produces
double                                                                           clustered numbers, which greatly diminishes the rate of convergence to the
actual mean of the sampled population (in this case the option price). There
We use a simple for....next structure in the code to loop through the            are a number of ways to improve the rate of convergence, and these will be
simulations. So the code structure is as follows:                                covered in a separate document. Suffice it to say for now that the Rnd
function has flaws, and for an entry level and simplistic analysis, will be
[Function declaration]
[Internal variables declaration e.g. loop counter, call and option prices]       adequate provided there is a large number of runs.
[For....<counter> =1 to N]
[While <intervals> = 1 to j]                                                  VBA Lookback Option FX Monte Carlo
[Calculate FX spot rate path, max FX spot rate, min FX spot rate]
[Calculate call option payoff: S(t =T) - S(min, 0 <= t <= T) ]                Simulation
[Calculate put option payoff: S(max, 0 <= t <= T) - S(t=T) ]                  The following code is for pricing Lookback call and put options.
[Sum the call and put option payoffs – essential for averaging these later]
[Wend]                                                                        Function MC_Sim_Black_Scholes(N As Double, intervals As Double, S As
[Next <counter>]                                                                 Double, K As Double, _
r As Double, q As Double, sigma As Double, t As Double) As Variant

Exotic FX Derivatives Pricing using Monte Carlo Simulation in Excel VBA: FX Lookback Options

Dim i As Long, j As Long, call_price As Double, put_price As Double,                              S_t = rhs
call_price_sum As Double
Dim put_price_sum As Double, S_t As Double, payoff_call As Double,                     Wend
payoff_put As Double
Dim sum_payoffs_put As Double, sum_payoffs_call As Double,                             sum_min_price = sum_min_price + min_price
avg_payoff_call As Double                                                              sum_max_price = sum_max_price + max_price
Dim avg_payoff_put As Double, below_zero As Integer, rand_num As
Double, epsilon As Double                                                              payoff_call = S_t - min_price
Dim rhs As Double, min_price As Double, max_price As Double,                           payoff_put = max_price - S_t
avg_max_price As Double                                                                sum_payoffs_call = sum_payoffs_call + payoff_call
Dim avg_min_price As Double, a As Double                                               sum_payoffs_put = sum_payoffs_put + payoff_put

sigma = sigma / Sqr(intervals)                                                Next i
t = t / intervals
a = (r - q - 0.5 * sigma ^ 2) * t                                               avg_payoff_call = sum_payoffs_call / N
avg_payoff_put = sum_payoffs_put / N
For i = 1 To N                                                                  avg_max_price = sum_max_price / (N * intervals)
avg_min_price = sum_min_price / (N * intervals)
S_t = S
j=0                                                                               call_price = avg_payoff_call * Exp(r * t * intervals)
put_price = avg_payoff_put * Exp(r * t * intervals)
While (j < intervals)
MC_Sim_Black_Scholes = Array(call_price, put_price, below_zero,
j=j+1                                                             avg_max_price, avg_min_price)
Application.StatusBar = i & " of " & N & " Price Paths.... Time
Interval " & j & " of " & intervals                                           End Function
rand_num = Rnd()
epsilon = WorksheetFunction.NormSInv(rand_num)
If epsilon < 0 Then below_zero = below_zero + 1 Else
rhs = S_t * Exp(a + sigma * epsilon * Sqr(t))
min_price = WorksheetFunction.Min(rhs, S_t)
max_price = WorksheetFunction.Max(rhs, S_t)

Exotic FX Derivatives Pricing using Monte Carlo Simulation in Excel VBA: FX Lookback Options

Next Steps
to the model above:
 Improved convergence; this can be achieved through antithetic
variates, pseudo-random number generators, low discrepancy
number generators, control variates, stratified sampling, importance
sampling, moment matching, Latin Hypercube sampling
 Greeks – calculation of delta, gamma, rho, vega, theta
Movement away from the classical Black Scholes assumptions re, for
example, volatility and modelling this variable as a Levy or Jump diffusion
process, also dynamic interest rate models for both foreign and domestic
interest rates