Sisense Data Team 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!