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 as \( \left[\frac {log \left( \frac{S_0}{X} \right) + \left( r - \frac {\sigma^2} {2} \right )T}{\sigma \sqrt{T}} \right]\); 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.
Using helper cells
Rows 13 to 17 in figure 1 are helper cells for the Call and Put formulas in rows 19 and 20.
One cell formula
Row 25 in figure 1 contains the following one cell formula.
- Formula:
D25: =Stock*NORM.S.DIST((LN(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE)-Exercise*EXP(-Rate*Time)*NORM.S.DIST((LN(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE)
LAMBDA function formula version
The LAMBDA brings VBA type custom functions directly into the Excel worksheet. The function is named after the lambda calculus developed by Alonzo Church (1903-1995).
Development:
- Formula:
D29: =LAMBDA(Stock,Exercise,Rate,Sigma,Time,Stock*NORM.S.DIST((LN(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE)-Exercise*EXP(-Rate*Time)*NORM.S.DIST((LN(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE))(42,40,0.05,0.2,0.5)
The LAMBDA function is then assigned to a Defined Name :: BSCall in this example (figure 2)


LAMBA formula string in figure 3: =LAMBDA(Stock,Exercise,Rate,Sigma,Time,Stock*NORM.S.DIST((LN(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE)-Exercise*EXP(-Rate*Time)*NORM.S.DIST((LN(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*SQRT(Time)),TRUE))
The BSCall function:

- Formula:
D33: =BScall(Stock,Exercise,Rate,Sigma,Time)
- Formula:
D34: =BScall(42,40,5%,20%,0.5)
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 5.

A combination Call and Put procedure is shown in Code 4.
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