QuantCandy's Blog

The Excel workbook to price options described in the book “GameStonk! The wild rollercoaster ride of GameStop” may be downloaded from this link.

In this example, we will learn to price call and put options on GameStop (though you can use any stock you like) using the famed Black-Scholes option pricing equation. I will also show you how to use an online tool to check your work. This post assumes that you already know the Black-Scholes equation and what a call and put are. If you want a refresher, I will add a new post on this.

Pricing Options on GME

The options prices expiring March 12, 2021 as of market close on March 5, 2021 are shown below (source: Power E*Trade). With the price of the underlying S = $137.74, a call option with a strike K of $135 is in the money, so has an intrinsic value of  = 2.74.  Since there are seven days to expiry, there is time value (theta) as well. Since interest rates are very low, the driver of the option premium in this case is volatility.  The higher the volatility, the greater the chance the stock has to rise over the time period until option expiry.  For this strike price, we see an implied volatility of 255.07%.

The implied volatility is derived from options prices, which are set by supply and demand.   Given the implied volatility, strike price, underlying price, interest rates and time to expiry, we can use Black-Scholes to compute the call (and put) prices.  Alternatively, given the call (or put) price, the time to expiry, interest rates, underlying price and the strike, we can use Black-Scholes to calculate the implied volatility. Let’s do both.

Using Black-Scholes (see post on Black-Scholes for technical details), the price of a call depends on the underlying, strike, time to expiry and risk free rate. The formula for the call price is :

where N() is the cumulative normal distribution for a standardized random variable, T is the expiry date, t = the evaluation date,  is the annualized volatility,  is the annualized interest rate and  d₁ and d₂ are given below.

The price of a put p can be derived from Put-Call Parity relationship. This relationship is based on no-arbitrage conditions. Here, p is given by the relationship between the present value of the strike, the underlying stock and the call.

The Black-Scholes price of a put option is then derived as:

This should be enough to get started building a model in Excel. Start a new workbook and save as type .xlsm. We will price a March 12, 2021 $135 Call as of March 5, 2021. The underlying stock price is 137.44, so the call option (which has an intrinsic value of max(S-K,0)) is out of the money. There is a week to expiry, so the option price reflects time value only. The bid/ask for the March 21 $135 call are 18.35 and 21.15 with the last price 20.1, the implied volatility is 255.07% and the delta is 0.5873.

Create a worksheet like the following in your workbook with the inputs from market data as shown.

The interest rate should be the risk free rate for the same expiry time (one week). (As a tip, if you use the online tool at
The Options Industry Council (OIC) – Optionscalculator (optionseducation.org) and enter the stock ticker, you can retrieve the interest rate and volatility, though only for the current date.) Let’s check the prices using the online OIC calculator to have a reference for our model.

To build this in Excel, we will have to replicate the call and put price formulas above. Insert a new code module using the Developer tab, and start a function as shown:

Function BlackScholesPrice(S, K, sigma, r, d, t As Date, Texp As Date, strategy As String, Optional strReturn As String) As Variant

We will pass the arguments Stock price S, Strike Price K, interest rate r,
dividend yield d, calculation date as date, expiry date as date, strategy (call or put) as string, and an optional argument to return an array of option greeks (delta, gamma, theta, vega and rho.)

Next, add the following code:

‘Example usage: BlackScholesPrice(137.74,135,255.07,.085,.0, 3/12/2021,3/5/2021,”call”,”all”)
‘array enter input cells to get Price, Delta, Gamma, Theta, Vega and Rho in that order
‘note: must use power function to exponentiate for 64bit Excel, or x ^ y with spaces surrounding ^ or it will break.
Dim dblPi As Double
dblPi = WorksheetFunction.Pi()
Dim myArr As Variant
Dim BSArray(5) ‘returns array Price, Delta, Gamma, Theta, Vega and Rho in that order
numdays = numdaysinyear(Year(t)) ‘account for leap years at analysis date (caveat: may be different from actual transaction date)
deltat = (Texp – t) / numdays

d1 = (Log(S / K) + (r – d + 0.5 * Application.WorksheetFunction.Power(sigma, 2)) * deltat) / (sigma * Sqr(deltat))
d2 = d1 – sigma * Sqr(deltat)
Nd1 = WorksheetFunction.NormSDist(d1)
Nd2 = WorksheetFunction.NormSDist(d2)
NPrimed1 = Exp(-Application.WorksheetFunction.Power(d1, 2) / 2) / Sqr(2 * dblPi)
NPrimed2 = Exp(-Application.WorksheetFunction.Power(d2, 2) / 2) / Sqr(2 * dblPi)

Since the user might type Call or Put instead of call or put as the strategy, take the lowercase of the input and return just “c” or “p”. Then define vega and gamma which are independent of the option type (call or put).

strStrategy = Left(LCase(strategy), 1)
BS_vega = S / 100 * Exp(-d * thetat) * Sqr(deltat) / Sqr(2 * dblPi) * Exp(-Application.WorksheetFunction.Power(d1, 2) / 2)
BS_Gamma = Exp(-d * deltat) / (S * sigma * Sqr(deltat)) / Sqr(2 * dblPi) * Exp(-Application.WorksheetFunction.Power(d1, 2) / 2)

Now, we will work with the strategy (c or p) to compute the prices and remaining greeks using Select Case.

Select Case strStrategy
Case “c”:

BSArray(0) = S * Exp(-d * deltat) * Nd1 – K * Exp(-r * deltat) * Nd2 ‘Call Price
Debug.Print “BSArray(0) = “; BSArray(0)
BSArray(1) = Exp(-d * deltat) * Nd1 ‘Delta
BSArray(2) = BS_Gamma
‘Theta
BSArray(3) = -S * sigma * Exp(-d * deltat) * NPrimed1 / (2 * Sqr(deltat)) – r * K * Exp(-r * deltat) * Nd2 + d * S * Exp(-d * deltat) * Nd1
BSArray(3) = BSArray(3) / numdays ‘theta
BSArray(4) = BS_vega
BSArray(5) = K * deltat * Exp(-r * deltat) * Nd2 / 100 ‘rho
Case “p”:
BSArray(0) = K * Exp(-r * deltat) * (1 – Nd2) – S * Exp(-d * deltat) * (1 – Nd1)
BSArray(1) = Exp(-d * deltat) * (Nd1 – 1) ‘put delta
BSArray(2) = BS_Gamma
BSArray(3) = -S * sigma * Exp(-d * deltat) * NPrimed1 / (2 * Sqr(deltat)) + r * K * Exp(-r * deltat) * (1 – Nd2) – d * S * Exp(-d * deltat) * (1 – Nd1)
BSArray(3) = BSArray(3) / numdays ‘theta
BSArray(4) = BS_vega
BSArray(5) = -K * deltat * Exp(-r * deltat) * (1 – Nd2) / 100 ‘put rho
Case Else:
End Select

If strReturn = “” Then
myArr = BSArray(0)
BlackScholesPrice = myArr
Else
BlackScholesPrice = BSArray
End If

End Function

Usage: in your spreadsheet, select a blank cell and type =
=BlackScholesPrice(137.74, 135, 255.07%, 0.01%, 0%, “3/5/2021”, “3/12/2021”, “call”)

You should get the value $20.518. You can just enter this in your spreadsheet for both the call and the put as follows

To show the greeks, you can array-enter the formula. Edit the spreadsheet, typing the headings across the worksheet as shown, then array enter in cells B13:H13 for the call and B14:H14 for the put. I’m showing the formula in B13 for reference.

We can also plot the results by using a table. The implied volatility can be inferred from either the call or put option price using Excel’s Solver (more on this in a later post) or downloaded from a market data provider. The moneyness in column B is just the stock price from column A divided by the strike of 135, and I’ve used named ranges for several inputs.
We can see how the prices are affected by the amount of moneyness by creating a chart. The call price increases as the stock price

That’s it for now! Hopefully this has helped explore how to start building option pricers in Excel VBA. You can try to expand the code to include straddles and other options.

When using the Round() function on a worksheet or referencing this via Application.WorksheetFunction.Round() in code, things work as one would expect:  For example, Round(1.5,0) rounds 1.5 up to 2 and returns 0 decimal places, so the calculation Round(1.5,0) returns 2.  Similarly, Round(2.5,0) returns 3.  All as expected.  But, if you try using Round in VBA, watch out for an odd anomaly known as the Banker’s Round.  Apparently created with the goal of minimizing bias (when you accumulate sums of rounded up numbers, you get an upward bias.  Instead, the Banker’s Round is designed to round to the nearest EVEN integer.  This results in round(1.5,0) = 2, all good, but round(2.5,0) also equals 2.

I came across this today when trying to match primary keys where a one to many relationship exists, so had no other choice but to match on an additional numeric value as a secondary key.  Yes, this is insane.  So I’ll be replacing Round() in my code with Application.WorksheetFunction.Round() and will also return a match if the absolute value of the difference of the rounded numeric values is less than or equal to 1.