Binomial Option Pricing (Excel VBA)

This tutorial is part 2 of the Binomial Option Pricing Tutorial Series. For part one, please go to Binomial Option Pricing (Excel Formula).

In the last article, we briefly introduced option pricing and the use of Excel formula to price a simple 2-period European call option. Now, let’s shift our focus to using Excel VBA to achieve a more dynamic and flexible option pricing in Excel.

Before we begin, we again welcome you to download the workbook below and follow through with the examples we discuss in this article.

Setup

Using the same set of values in our last article, we extend what we have to more than 2 periods, and ideally create a solution that can price the option in however many periods we want. Before we begin to write some codes, let’s take a step back and think about how to structure the VBA program.

Let’s start with what we want as an output. We want to show a binomial tree that contains the option price at different nodes for multiple periods, similar to what we have in the previous article. In our example, the binomial tree is a recombining tree, meaning that at each period N, there will be N+1 nodes. This means in our program, we’ll at least need 2 arrays of size ( N + 1 ) x ( N + 1 ) to store the stock price and the call option price evolutions, although we’ll only use the upper triangle of the arrays.

We’ll also need to define several parameters in our VBA program to make it dynamic, nothing new here but just to have it as our checklist:

• S0 (initial stock price)
• K (strike price)
• r (risk-free interest rate)
• u (up-movement)
• N (number of periods)

Algorithm

Now we are ready to lay the grounds for how our program will structure with the following algorithm to calculate the option price in backward analysis:

1. Generate a tree of the stock price for each node at each period
2. Compute the call option value at maturity
3. Here is the loop:
   1. Using the call option value and stock price for the next period, compute d*
   2. Using d*, compute the risk-free discounted value of the replicating portfolio
   3. Using the present value of the replicating portfolio, back out the call option price
4. Output the binomial tree to the worksheet

The VBA code

In the 43 lines of code below, we implement the above algorithm and compute the call option price for arbitrary parameters, which makes the program very flexible and dynamic. Note that a good VBA coding practice is to use Option Explicit, so you never have to worry about weird debugging issues especially with wrong parameter names.

After running the VBA code, you’ll see a upper-triangle output to the worksheet, which we have nicely formatted in the image below. Cell A1 represents the option price at initiation, which is what we are looking for! Let’s discuss some interesting observations after you have a read of the codes.

Option Explicit
Sub optionPricing()
    ' Step 1: Declare variables
    Dim S0, K, u, d, r, N, i, j, d_star, repPort
    S0 = 100
    K = 100
    u = 1.1
    r = 0.02
    N = 5
    
    ' Step 2: Create an array to contain the results
    ReDim S(0 To N, 0 To N), C(0 To N, 0 To N)
    
    ' Step 3: Compute the stock price at each node
    S(0, 0) = S0
    For i = 1 To UBound(S, 1)
        For j = 0 To i
            S(i, j) = S(0, 0) * (u ^ (i - j)) * ((1 / u) ^ j)
        Next
    Next
    
    ' Step 4: Compute the call option price at maturity
    For j = 0 To N
        C(N, j) = WorksheetFunction.Max(S(N, j) - K, 0)
    Next
    
    ' Step 5: Compute the call option price using backward analysis
    For i = N - 1 To 0 Step -1
        For j = 0 To i
            d_star = (C(i + 1, j) - C(i + 1, j + 1)) _
                        / _
                     (S(i + 1, j) - S(i + 1, j + 1))
            
            repPort = S(i + 1, j) * d_star - C(i + 1, j)
            
            C(i, j) = S(i, j) * d_star - Exp(-r) * repPort
        Next
    Next
    
    ' Step 6: Output the call option price into cell A1
    ActiveSheet.Range("A1").Resize(UBound(C, 1) - (LBound(C, 1) - 1), UBound(C, 2) - (LBound(C, 2) - 1)) = WorksheetFunction.Transpose(C)
    
End Sub

Observation and Analysis

Because VBA allows us to play around and tweak the parameters, we can do things like N = 1,000 to compute what is impossible for us to do by hand. As N increase, holding other parameters constant, the call optional value is closer and closer to the current stock price. This is completely normal, because if the call option never expires, you are in effect holding the stock itself, and the strike price means nothing to you!

A second observation is that d* is a number between 0 and 1, and holding other parameters constant, it tends to increase for every node if the strike price is lower. d* has a special name, Delta, which is basically a measure of sensitivity of the call option value to the stock price. As the option becomes more and more in-the-money (strike price is lower than the current stock price), Delta is approaching 1, meaning that your call option begins to behave like its underlying stock! Delta is a member of the “Greeks” family, which are important tools to measure the risk of holding the options.

Final Thoughts

The model we show is a very simplified version of what real-life option pricing models would look like. However, the key here is to understand and digest the concept presented here (such as no-arbitrage pricing) and learn some simple VBA syntax/tricks to work with the binomial model in Excel.

In future series, we are going to discuss a more complicated but powerful model – the Black-Scholes model of option pricing. Stay tuned!

Do you find this article helpful? Subscribe to our newsletter to get regular Excel tips and exclusive free Excel resources.