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.
a as (
generate_series(0, 3, 1)
, b as (
generate_series(-3, 3, 1)
, tree as (
select a.generate_series as t
, b.generate_series as branch
from a, b
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
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 (
, 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
end as final_val
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 the option at each node, we use the following formula:
Encoding all of this into SQL, we get:
, rt.t as final_t
, rt.branch as final_branch
^(((rt.t-lt.t)-(rt.branch-lt.branch))/2) as disc_val
tree lt, tree rt
join final on rt.branch = final.branch
and rt.t = final.t
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 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!