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.

Fig 1: Excel Web App #1: - Excel version of Black and Scholes' model for a European type option on a non dividend paying stock

One cell formula

Row 25 in figure 1 contains the following one cell formula.

  1. 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:

  1. 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)

xlf-bscall-lambda-name
Fig 2: Name Manager :: Edit name - Name: BSCall; Comments: add as appropiate; RefersTo: =LAMBDA(...) excluding the test arguments

xlf-bscall-lambda-para-calc
Fig 3: Edit Name - RefersTo detail [Scroll to view] five Parameters to the LAMBDA function, followed by the Calculation

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))

  1.  

The BSCall function:

xlf-bscall-lambda-3
Fig 4: BSCall function in the worksheet [Scroll to view] BSCall is an fx item in Formula AutoComplete (top); the argument list appears after the opening bracket "()" (centre); but no argument names appear in the Function Arguments dialog, activated here in Edit mode (bottom).

  1. Formula: D33: =BScall(Stock,Exercise,Rate,Sigma,Time)
  2. 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.


Results to immediate window
Fig 5: 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.

Church A, (1985), The Calculi of Lambda-Conversion, Journal of Political Economy, Annals of Mathematics Studies, Princeton University Press.

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


Related material

Black Scholes on the HP10bII+ financial calculator