# Black-Scholes option pricing in Excel and VBA

## 1. Black-Scholes model

According to the Black-Scholes (1973) model, the theoretical price \(C\) for European call option on a non dividend paying stock is $$\begin{equation} C=S_0 N(d_1)-Xe^{-rT}N(d_2) \end{equation}$$ where

$$d_1=\frac {log \left( \frac{S_0}{X} \right) + \left( r+ \frac {\sigma^2} {2} \right )T}{\sigma \sqrt{T}} $$ $$d_2=\frac {log \left( \frac{S_0}{X} \right) + \left( r - \frac {\sigma^2} {2} \right )T}{\sigma \sqrt{T}} = d_1 - \sigma \sqrt{T}$$

In equation 1, \(S_0\) is the stock price at time 0, \(X\) is the exercise price of the option, \(r\) is the risk free interest rate, \(\sigma\) represents the annual volatility of the underlying asset, and \(T\) is the time to expiration of the option.

From Put-Call parity, the theoretical price \(P\) of European put option on a non dividend paying stock is $$\begin{equation} P=Xe^{-rT}N(-d_2) - S_0 N(-d_1) \end{equation}$$

## 2. The Black-Scholes model in Excel

**Example:** The stock price at time 0, six months before expiration date of the option is $42.00, option exercise price is $40.00, the rate of interest on a government bond with 6 months to expiration is 5%, and the annual volatility of the underlying stock is 20%.

The values used in this example are similar to those in Hull (2009, p294 ) with S_{0} = 42, K (exercise) = 40, r = 0.1, σ = 0.2, and T = 0.5. This set returns c = 4.76 and p = 0.81

Calculation of the call price can be completed as a 5 step process. Step 1. d1; 2. d2; 3. N(d1); 4. N(d2); and step 5, C. The value for d1 and d2 are shown in rows 12 and 13 of figure 1. The probabilities for \(N(\cdot)\) are estimated with the NORM.S.DIST function. The call price from equation 1 is $4.08 (Figure 1 row 18), and the put price from equation 2 is $1.09 (Figure 1 row 19).

**Syntax** ` NORM.S.DIST(z,cumulative)`

. z is the probability value, and cumulative is a LOGICAL value. TRUE returns the cumulative distribution function. FALSE returns the probability mass function.

## 3. The Black-Scholes model in VBA

In this example, separate function procedures are developed for the call (code 1) and put (code 2) equations. The Excel `NORM.S.DIST`

function, line 6 in code 1 and 2, requires that the dot operators be replaced by underscores when the function is called from VBA.

**Code 1:**Function

`BSCall`

returns the call price for a European option on a non dividend paying stock
Function BSCall(Stock As Double, Exercise As Double, Rate As Double, Sigma As Double, Time As Double) As Double Dim d1 As Double, d2 As Double With Application d1 = (.Ln(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) d2 = (.Ln(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) BSCall = Stock * .Norm_S_Dist(d1, True) - Exercise * Exp(-Rate * Time) * .Norm_S_Dist(d2, True) End With End Function

**Code 2:**Function

`BSPut`

returns the put price for a European option on a non dividend paying stock
Function BSPut(Stock As Double, Exercise As Double, Rate As Double, Sigma As Double, Time As Double) As Double Dim d1 As Double, d2 As Double With Application d1 = (.Ln(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) d2 = (.Ln(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) BSPut = Exercise * Exp(-Rate * Time) * .Norm_S_Dist(-d2, True) - Stock * .Norm_S_Dist(-d1, True) End With End Function

The `BScall`

and `BSPut`

functions are tested by the calling procedure in code 3. Output is sent to the Immediate Window with the `Debug.Print`

method.

**Code 3:**Macro

`TestBSModel`

calls the BSCall procedure and the BSPut procedure.
Sub TestBSModel() Dim CallP As Double, PutP As Double CallP = BSCall(42, 40, 0.05, 0.2, 0.5) PutP = BSPut(42, 40, 0.05, 0.2, 0.5) Debug.Print "Time: " & Time Debug.Print "====================================" Debug.Print "BSCall(42, 40, 0.05, 0.2, 0.5): returns " & Format(CallP, "Currency") Debug.Print "BSPut(42, 40, 0.05, 0.2, 0.5): returns " & Format(PutP, "Currency") End Sub

The output from code 3 is shown in the Immediate Window of figure 2.

A combination Call and Put procedure is shown in Code 4.

**Code 4:**Function

`BSOption`

combines the BSCall procedure and the BSPut procedure and adds an Optional argument OptType
Function BSOption(Stock As Double, _ Exercise As Double, _ Rate As Double, _ Sigma As Double, _ Time As Double, _ Optional OptType As Variant) As Variant ' OptType TRUE (default) for Call, FALSE for Put Dim d1 As Double, d2 As Double Dim BSCall As Double, BSPut As Double If IsMissing(OptType) Then OptType = True ' Check that Variant has sub type Boolean If VBA.TypeName(OptType) <> "Boolean" Then GoTo ErrHandler On Error GoTo ErrHandler With Application d1 = (.Ln(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) d2 = (.Ln(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time)) BSCall = Stock * .Norm_S_Dist(d1, True) - Exercise * Exp(-Rate * Time) * .Norm_S_Dist(d2, True) BSPut = Exercise * Exp(-Rate * Time) * .Norm_S_Dist(-d2, True) - Stock * .Norm_S_Dist(-d1, True) End With If OptType Then BSOption = BSCall Else BSOption = BSPut End If Exit Function ErrHandler: BSOption = CVErr(xlErrValue) ' Return #VALUE! error End Function

The BSOption function functions is tested by the calling procedure in code 5.

**Code 5:**Macro

`BSOption_Test`

passes values to BSOption function and prints output to the immediate window
Sub BSOption_Test() Dim Price As Double Dim S As Double, E As Double, R As Double, V As Double, T As Double Dim OT As Boolean S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5 Price = BSOption(S, E, R, V, T) Debug.Print " ================================" Debug.Print " Time: " & Format(Time, "hh:mm:ss") & vbNewLine Debug.Print " S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5" Debug.Print " BSPrice = " & Format(Price, "$0.0000") & vbNewLine S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5: OT = True Price = BSOption(S, E, R, V, T, OT) Debug.Print " S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5: OT = True" Debug.Print " [OT = True = Call] " Debug.Print " BSPrice = " & Format(Price, "$0.0000") & vbNewLine S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5: OT = False Price = BSOption(S, E, R, V, T, OT) Debug.Print " S = 42: E = 40: R = 5 / 100: V = 20 / 100: T = 0.5: OT = False" Debug.Print " [OT = False = Put] " Debug.Print " BSPrice = " & Format(Price, "$0.0000") & vbNewLine Debug.Print " ================================" & vbNewLine End Sub

## References

Black F, and M Scholes, (1973), The pricing of options and corporate liabilities, *Journal of Political Economy*, Vol 81 No 3 pp.637-654.

Hull J, (2009), 'Options, futures, and other derivatives', 7th ed., Pearson Prentice Hall

## Related material

Black Scholes on the HP10bII+ financial calculator

**Download**the Excel file for this module: bs_nondiv.xlsm [29 KB]**Download**the VBA code for this module: xlf-black-scholes-code.txt [4 KB]**Development platform:**Microsoft Excel 2013 Pro 64 bit.**Revised:**Sunday 26th of July 2020 - 09:37 PM, Pacific Time (PT)