7 October 2018

The aim of this article is to walk the reader through the implementation of the Black-Scholes model for option pricing in VBA. Firstly, we’ll recap the theoretical framework. Secondly, we’ll provide the code to put the theory into practice and show some basic (but hopefully relevant) applications.

**The Theory – Pricing**

The classical Black-Scholes model for option pricing assumes that stock prices follow a Geometric Brownian Motion (GBM) with constant drift (μ) and constant volatility (σ). Analytically:

where is a Standard Brownian Motion with respect to the *historical measure P*.

It is possible to prove that for pricing purposes the equation becomes

where is a Standard Brownian Motion with respect to the *risk-neutral measure Q* and *q* is the continuous dividend yield. This SDE can be easily solved by applying Ito’s Lemma to the function ln(S(t)). The final result is

which in integral notation is

that finally becomes

Given the above evolution of the stock and the risk-free asset, whose evolution is with , it is possible to prove (under some conditions) that any “well-behaved” European derivative must satisfy the following PDE:

where V is the value of the derivative (e.g. a call option) at given time and for a given price of the underlying.

The solution of the previous PDE under the boundary condition V(T,S(T))=[S(T)-K]^{+} gives the Black-Scholes time-t no-arbitrage price for a European call option with strike K and maturity T:

where N(z) is the cumulative distribution function of standard normal random variable, while

and

The European Put can easily be derived using the Put-Call Parity, i.e. given

the Black-Scholes time-t no-arbitrage price for a European put option with strike K and maturity T is

The Theory – Greeks

In this section we introduce the concept of Greeks as sensitivities and provide the formulae for the basic ones given the Black-Scholes formula just derived.

Delta (Δ) is the first derivative of the option value V with respect to the spot price S, i.e.

For a European Call we have

while for a European Put we have

Gamma (Γ) is the second derivative of the option value V with respect to the spot price S, which is also equal to the first derivative of Delta with respect to the spot price. Gamma is the same for Calls and Puts. Formally:

For European options we have

Theta (Θ) is the first derivative of the option value V with respect to time t , i.e.

For a European Call we have

while for a European Put we have

Vega (ν) is the second derivative of the option value V with respect to volatility σ. Vega is the same for Calls and Puts. Formally:

For European options we have

These four are usually the most important Greeks when it comes to risk management. However, we mention that in real life some contract may warrant the analysis of higher order Greeks as well.

**From Theory to Practice**

In this section we walk the reader through the implementation of the Black-Scholes model for option pricing in VBA. First of all, we recommend writing “Option Explicit” at the top of each new Function or Subroutine, so that VBA requires that you always declare a variable before using it.

Pricing of European Call and Put options

`Function BS_Price(S As Double, K As Double, Vol As Double, r As Double, q As Double, T As Double, CP As String)`

```
```Dim d_1, d_2 As Double

d_1 = (WorksheetFunction.Ln(S \ K) + (r - q + Vol * Vol \ 2) * T) \ (Vol * Sqr(T))

d_2 = d_1 - Vol * Sqr(T)

If CP = "Call" Then

BS_Price = S * Exp(-q * T) * WorksheetFunction.Norm_S_Dist(d_1, True) - K * Exp(-r * T) * WorksheetFunction.Norm_S_Dist(d_2, True)

ElseIf CP = "Put" Then

BS_Price = K * Exp(-r * T) * WorksheetFunction.Norm_S_Dist(-d_2, True) - S * Exp(-q * T) * WorksheetFunction.Norm_S_Dist(-d_1, True)

Else

BS_Price = "Error"

End If

`End Function`

Example of an application: Plotting options prices as a function of spot (see chart below).

Delta

`Function BS_Delta(S As Double, K As Double, Vol As Double, r As Double, q As Double, T As Double, CP As String)`

```
```Dim d_1, d_2 As Double

d_1 = (WorksheetFunction.Ln(S \ K) + (r - q + Vol * Vol \ 2) * T) \ (Vol * Sqr(T))

d_2 = d_1 - Vol * Sqr(T)

If CP = "Call" Then

BS_Delta = Exp(-q * T) * WorksheetFunction.Norm_S_Dist(d_1, True)

ElseIf CP = "Put" Then

BS_Delta = -Exp(-q * T) * WorksheetFunction.Norm_S_Dist(-d_1, True)

Else

BS_Delta = "Error"

End If

`End Function`

Example of an application: Plotting Delta as a function of spot and time (see chart below).

Gamma

`Function BS_Gamma(S As Double, K As Double, Vol As Double, r As Double, q As Double, T As Double)`

```
```Dim d_1, d_2 As Double

d_1 = (WorksheetFunction.Ln(S \ K) + (r - q + Vol * Vol \ 2) * T) \ (Vol * Sqr(T))

d_2 = d_1 - Vol * Sqr(T)

BS_Gamma = Exp(-q * T) * WorksheetFunction.Norm_S_Dist(d_1, False) \ (S * Vol * Sqr(T))

`End Function`

Example of an application: Plotting Gamma as a function of spot and time (see chart below).

Theta

`Function BS_Theta(S As Double, K As Double, Vol As Double, r As Double, q As Double, T As Double, CP As String)`

```
```Dim d_1, d_2 As Double

d_1 = (WorksheetFunction.Ln(S \ K) + (r - q + Vol * Vol \ 2) * T) \ (Vol * Sqr(T))

d_2 = d_1 - Vol * Sqr(T)

If CP = "Call" Then

BS_Theta = -Exp(-q * T) * S * WorksheetFunction.Norm_S_Dist(d_1, False) * Vol \ (2 * Sqr(T)) _

- r * K * Exp(-r * T) * WorksheetFunction.Norm_S_Dist(d_2, True) + q * S * Exp(-q * T) * WorksheetFunction.Norm_S_Dist(d_1, True)

ElseIf CP = "Put" Then

BS_Theta = -Exp(-q * T) * S * WorksheetFunction.Norm_S_Dist(d_1, False) * Vol \ (2 * Sqr(T)) _

+ r * K * Exp(-r * T) * WorksheetFunction.Norm_S_Dist(-d_2, True) - q * S * Exp(-q * T) * WorksheetFunction.Norm_S_Dist(-d_1, True)

Else

BS_Theta = "Error"

End If

`End Function`

Example of an application: Plotting Theta as a function of spot and time (see chart below).

Vega

`Function BS_Vega(S As Double, K As Double, Vol As Double, r As Double, q As Double, T As Double)`

```
```Dim d_1, d_2 As Double

d_1 = (WorksheetFunction.Ln(S \ K) + (r - q + Vol * Vol \ 2) * T) \ (Vol * Sqr(T))

d_2 = d_1 - Vol * Sqr(T)

BS_Vega = S * Exp(-q * T) * WorksheetFunction.Norm_S_Dist(d_1, False) * Sqr(T)

`End Function`

Example of an application: Plotting Vega as a function of spot and time (see chart below).

M | T | W | T | F | S | S |
---|---|---|---|---|---|---|

« May | ||||||

1 | 2 | 3 | 4 | |||

5 | 6 | 7 | 8 | 9 | 10 | 11 |

12 | 13 | 14 | 15 | 16 | 17 | 18 |

19 | 20 | 21 | 22 | 23 | 24 | 25 |

26 | 27 | 28 | 29 | 30 | 31 |

- May 2019
- April 2019
- March 2019
- February 2019
- January 2019
- December 2018
- November 2018
- October 2018
- September 2018
- May 2018
- April 2018
- March 2018
- February 2018
- December 2017
- November 2017
- October 2017
- September 2017
- May 2017
- April 2017
- March 2017
- February 2017
- November 2016
- October 2016
- September 2016
- May 2016
- April 2016
- March 2016
- February 2016
- December 2015
- November 2015
- October 2015
- September 2015
- May 2015
- April 2015
- March 2015
- February 2015
- December 2014
- November 2014
- October 2014
- September 2014
- May 2014
- March 2014
- February 2014
- January 2014
- December 2013
- November 2013
- October 2013
- September 2013
- May 2013
- April 2013
- March 2013
- February 2013