# 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 S0 = 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. Fig 2: Black and Scholes' model - for a European type option on a non dividend paying stock

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