###### Common Statistical Operations

# 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 4x7 lattice. We then truncate nodes that do not belong to the binomial tree.

with

aas(

select*

from

generate_series(0,3,1)

)

, bas(

select*

from

generate_series(-3,3,1)

)

, treeas(

selecta.generate_seriesast

, b.generate_seriesasbranch

froma, b

where

casewhenmod(a.generate_series,2)=1

then@b.generate_series<=a.generate_series

andmod(@b.generate_series,2)=1

else@b.generate_series<=a.generate_series

andmod(@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).

,finalas(

select*

,casewhent=3

and83*1.1^(t-(t-branch)/2)

*0.9^((t-branch)/2)>85

then83*1.1^(t-(t-branch)/2)

*0.9^((t-branch)/2)-85

whent=3

then0

elsenull

endasfinal_val

fromtree

wheret=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 the option at each node, we use the following formula:

Encoding all of this into SQL, we get:

select

lt.t

, lt.branch

, rt.tasfinal_t

, rt.branchasfinal_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)asdisc_val

from

tree lt, tree rt

joinfinalonrt.branch=final.branch

andrt.t=final.t

where

lt.t<=rt.t

and@(rt.branch-lt.branch)<=@(rt.t-lt.t)

andrt.t=3

orderbybranchdesc

One handy tool that Periscope Data 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!