Binomial Options Pricing in SQL

Trees are a great way to visualize different states of a process when measuring uncertainty and making predictions. While trees are harder to implement in SQL due to the relational nature of the language, they are powerful tools in finance, for instance, to value derivatives.

In this blog, we discuss how to set up a discrete binomial tree using the binomial options pricing model to value derivatives in Postgres SQL and apply this in an example of pricing a 3-period call option.

Setting Up A Binomial Tree

To create a tree in Postgres, we utilize the generate_series() function. Since the state of a process, which we denote here as a node, can be identified by the depth and width position of the node, we first generate a 4×7 lattice. We then truncate nodes that do not belong to the binomial tree.

with
  a as ( 
    select *
    from
      generate_series(0, 3, 1)
  )
  , b as (
    select *
    from
      generate_series(-3, 3, 1)
  )
  , tree as (
    select a.generate_series as t
      , b.generate_series as branch
    from a, b
    where
      case when mod(a.generate_series, 2) = 1
        then @ b.generate_series <= a.generate_series
          and mod(@ b.generate_series, 2) = 1
        else @ b.generate_series <= a.generate_series
          and mod(@ b.generate_series, 2) = 0
      end
  )

In the above CTEs, the tree table skims off unnecessary nodes that are outside of the symmetrical binomial tree through the where clause.

Binomial Options Pricing

An option is a derivative whose value is derived by the price of the underlying stock. A call option is a contract that gives the option holder the right to purchase the stock at an agreed strike price.

Since the value of the option can change depending on the state of the stock, its value is perfect for modeling out via the branches in a tree.

We will now walk through how to value a 3-period European call option whose underlying stock price is $83, has a strike price of $85, and in each period can move up by a factor of 1.1 or down by a factor of 0.9.

With the 3-period tree, we can build out the option pricing backwards iteratively. First, we need to find the option payout max[(S_3-K,0] at each of the final nodes (t = 3).

, final as (
    select *
      , case when t = 3 
            and 83 * 1.1^(t-(t-branch)/2)
              *0.9^((t-branch)/2) > 85
            then 83 * 1.1^(t-(t-branch)/2)
              *0.9^((t-branch)/2) - 85
          when t = 3
            then 0
          else null 
        end as final_val
    from tree
    where t = 3
  )

To work backwards iteratively for valuing options, we use the following formula for each step backward:

In order to calculate the risk-neutral value at t = 0, we have to take into account not only the expected value at each node, but also the probability of traversing through the node. Thus, to calculate the contribution of the final payout to the expected value of the option at each node, we use the following formula:

Encoding all of this into SQL, we get:

select 
  lt.t
  , lt.branch
  , rt.t as final_t
  , rt.branch as final_branch
  , final_val
  , (1/exp(0.05)^(rt.t-lt.t))*(((rt.t-lt.t)!)/((((rt.t-lt.t)
      -(((rt.t-lt.t)-(rt.branch-lt.branch))/2))!)*((((rt.t-lt.t)
      -(rt.branch-lt.branch))/2)!)))*final_val
      *((exp(0.05)-0.9)/(1.1-0.9))^((rt.t-lt.t)-((rt.t-lt.t)
      -(rt.branch-lt.branch))/2)*(1-(exp(0.05)-0.9)/(1.1-0.9))
      ^(((rt.t-lt.t)-(rt.branch-lt.branch))/2) as disc_val 
from
  tree lt, tree rt 
join final on rt.branch = final.branch 
  and rt.t = final.t
where
  lt.t <= rt.t
  and @(rt.branch - lt.branch) <= @(rt.t - lt.t)
  and rt.t = 3
order by branch desc

One handy tool that Sisense for Cloud Data Teams offers is the Cohort Grid, which aggregates these expected values by node. Here’s what it looks like in tree form:

Now you can price your options through SQL!