How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

Equity Barrier Options: Knock-Out Calls & Puts

Equity barrier options are exotic options with discontinuous payoffs. An example of a equity barrier option is a knock-out option. A knock-out option ceases to exist if the stock price hits a certain barrier at any point. As such, knock-out options have the same payoff as regular options as long as the barrier is not reached. Their price is lower than regular options due to the probability of the knock-out effect of the stock price reaching the barrier. A knock-out option pays off nothing if the stock price ends up below the strike price at expiry, and pays the difference between the stock price and the strike price if the stock price ends up above the strike price. In this document we look at resolving the prices of the knock-out options via Monte Carlo simulation.

Monte Carlo Simulation of the Stock Price Path

Monte Carlo simulation is a numerical method used in finance to solve partial differential equations such as the Black Scholes equation for pricing stock options. The basic tenet of Monte Carlo simulation is that there is a stochastic variable in an equation that can be sampled many times over. When the result of the many simulations of a function containing the random variable is averaged, it approximates to the real mean. This is a result from the Central Limit Theorem of probability. The variable being sampled in Monte Carlo simulation usually comes from the uniform distribution as simulated by a random number generator. In finance the most common transforms are to the normal distribution for use in lognormal price paths such as those for Black Scholes. However, the random variable can be transformed to Beta, Gamma, Poisson distributions depending on the pattern exhibited by the data that is being modelled. Monte Carlo simulation of barrier options involves modelling the price path of the stock price by dividing the life of the option into small intervals. If the price hits the barrier at the end of any interval then the payoff for that pricing path is set to zero. This has the effect of lowering the average payoff and consequently the price of the option, relative to regular options.

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

Black Scholes Stock Price Model

According to the Black Scholes model, the stock price is defined by the following stochastic partial differential equation dS = (r - q -1/2sigma^2)dt + sigma dz where dz is a standard Brownian motion, defined by dz = epsilon * sqrt(dt) where epsilon is a standard normal random variable; dS is the change in spot rate, r is the risk-free interest rate, q is the dividend yield, sigma the volatility of the stock. As such the price at time 0 < t <= T is given by St = S0 * exp( (r q - sigma^2) dt + sigma * epsilon * sqrt(dt)) The price of a European call option is given by Max (St K, 0) where St is the final spot rate at expiry. For a knock-out option, the stock price path is adjusted to accommodate the smaller intervals at the end of which each stock price is tested for having reached the barrier. sigma = sigma / Sqr(N); t = T / N where N is the number of intervals, T is the time to expiry. If the price reaches the barrier at the end of any interval, the payoff for the entire path from t=0 to T is set to zero, otherwise its as per reg ular options. For call options the payoff is max (S K,0), for put options is max(K-S,0), provided the barrier has not been reached at any time.

Coding the MC simulation in Excel VBA

The first coding issue is, do we use a VBA subroutine or a custom function? Typically a function is used for numerical calculations. This is especially useful in a Monte Carlo simulation context as using subroutines induces complications with the persistence of variables after the simulation has been run, especially if the variables are declared as static or public. This would need to be done for compartmentalisation of code if using a subroutine as one would need to code the setting of input ranges, as well as output ranges. If using a subroutine, it quickly becomes complicated and necessitates the use of classes. Much easier to use functions. The function therefore takes in 7 parameters, S, K, r, q, t, sigma, N (number of simulations), barrier and should return the call price and the put price. To return the two values, we need to create a function that returns an array. The next issue with Monte Carlo simulations is performance. Excel VBA has late and early binding depending on the data type declarations. Late binding slows down VBA code execution noticeably over many different simulation 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 variables data type, including the array returned by the function. So where a lazy function declaration may look like Function Black_Scholes (S, K, r, q, t, sigma, N, barrier)

The proper way to define the function would be

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options
Function Black_Scholes (S as double, K as double, r as double, q as double, t as double, sigma as double, N as long, barrier as double) as double Depending on how we wish to use the function in future, we may also want to define the parameters as being passed in by value. This would protect encapsulation of the function if calling it from a subroutine an important step when moving towards an object-orientated approach. Function Black_Scholes (ByVal S as double, ByVal K as double, ByVal r as double, ByVal q as double, ByVal t as double, ByVal sigma as double) as double We use a simple for....next structure in the code to loop through the simulations. So the code structure is as follows: [Function declaration] [Internal variables declaration e.g. loop counter, call and option prices] [reduce time and volatility into smaller intervals for tracking price vs barrier] [For....<simulations counter> =1 to N] [While....<interval counter> =1 to M and S<Barrier] [Calculate stock price path] [Wend] [Calculate the call and put option payoffs] [Sum the call and put option payoffs essential for averaging these later] [Next <simulation counter>] [Calculate the average call option and put option payoffs] [Discount the call option and put option payoffs to give the respective price] Dim call_price_sum As Double Dim put_price_sum As Double

Generating Random Numbers in Excel VBA

The random number generator is given by the Excel VBA Rnd function, which generates random numbers between 0 and 1. These random numbers are supposed to be uniformly distributed across the interval of 0 and 1. The degree of uniformity impacts the efficiency of the Monte Carlo simulation as it impacts the rate of convergence of the simulation-derived price to the actual value. The problem with Excels random generator is that it produces clustered numbers, which greatly diminishes the rate of convergence to the actual mean of the sampled population (in this case the option price). There are a number of ways to improve the rate of convergence, and these will be 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 adequate provided there is a large number of runs.

VBA Barrier Option Monte Carlo Simulation

The following code is for cash-or-nothing call and put options. For an assetor-nothing call, the variable Cash should be changed to S_t in the assignments call_payoff = Cash and put_payoff=Cash on lines 22 and 23. Function MC_Sim_Black_Scholes(N As Double, intervals As Double, S As Double, K As Double, _ r As Double, q As Double, sigma As Double, t As Double, barrier As Double) As Double Dim i As Long, j As Long Dim call_price As Double Dim put_price As Double

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options
Dim S_t As Double Dim a As Double Dim b As Double sigma = sigma / Sqr(intervals) t = t / intervals a = (r - q - 0.5 * sigma ^ 2) * t For i = 1 To N S_t = S j=0 While (j < intervals And S_t > barrier) j=j+1 b = Rnd Application.StatusBar = i & " of " & N & " Price Paths.... Interval " & j & " of " & intervals S_t = S_t * Exp(a + sigma * WorksheetFunction.NormSInv(b) * Sqr(t)) Wend If (j = intervals And S_t > barrier) Then call_price = WorksheetFunction.Max(S_t - K, 0) put_price = WorksheetFunction.Max(K - S_t, 0) call_price_sum = call_price_sum + call_price put_price_sum = put_price_sum + put_price Else End If Next i call_price = call_price_sum / N call_price = call_price * Exp(r * t * intervals) put_price = put_price_sum / N put_price = put_price * Exp(r * t * intervals) MC_Sim_Black_Scholes = Array(call_price, put_price)

End Function Next Steps

There are various improvements and additional features that can be made 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 interest rates

How VBA Developer.net Can Save You Time and money 4

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options
You can get complete Excel apps from VBA Developer.net containing the code in this document, customisation, VBA development of any Excel, Access and Outlook apps, as well as C# and C++ add-ins and technical documentation.

Visit VBA Developer.net

Examples of VBA documents from VBA Developer.net

How to build a Black Scholes VBA Option Pricer How to build a Black Scholes C# Option Pricer How to build a Black Scholes VBA Option Pricer for FX Options How to build a Black Scholes VBA Option Pricer for Equity Options How to build a Black Scholes VBA Option Pricer using Monte Carlo Simulation How to build a Black Scholes VBA Option Pricer for Binary Options How to build a Black Scholes VBA Option Pricer for Equity Barrier Options How to build a Black Scholes VBA Option Pricer for Exotic Asian Options How to build a Black Scholes VBA Option Pricer for Exotic Lookback Options How to build an Equity Option Pricer using the Binomial Tree in Excel VBA How to code a Choleskey Decomposition in VBA (Numerical Methods for Excel) 3 ways to sort in VBA

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options
How to Code a Multivariate Value at Risk (VaR) VBA Monte Carlo Simulation How To Code the Newton-Raphson Method in Excel VBA How to Model Volatility Smiles, Volatility Term Structure and the Volatility Surface in Excel VBA How To Write Use Cases for a Portfolio Reporting VBA Tool How To Write a User Interface Model For a Portfolio Reporting VBA Tool How To Create a Semantic Object Model For a Portfolio Reporting VBA Tool How To Normalise a Database For VBA Apps How To Create a Database using SQL Scripts for a Portfolio Reporting VBA App How to Write Stored Procedures in SQL/Access/VBA for a Portfolio Reporting VBA App How to Use Cursors in SQL for a Portfolio Reporting VBA Tool How to Move Data from Access to Excel with SQL for a Portfolio Reporting VBA App Portfolio Reporting VBA Tool: Inserting Data into SQL/Access Databases from Excel Portfolio Reporting VBA Tool: Connecting Excel with SQL & Access Databases How To Design Classes For an Option Pricer in VBA: UML Concepts How To Design Classes for Object Orientated VBA Programming

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options

Nyasha Madavo, VBA Developer.net

How to build a Black Scholes VBA Option Pricer for Equity Barrier Options