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<a href="https://en.wikipedia.org/wiki/Binomial_options_pricing_model" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)"> binomial options pricing model</a> to value derivatives in Postgres SQL and apply this in an example of pricing a 3-period call option.



<h2 class="wp-block-heading">Setting Up A Binomial Tree</h2>



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



<pre class="wp-block-code"><code>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 &lt;= a.generate_series
 and mod(@ b.generate_series, 2) = 1
 else @ b.generate_series &lt;= a.generate_series
 and mod(@ b.generate_series, 2) = 0
 end
 )</code></pre>



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-013.png" alt="Price chart" class="wp-image-77941"/></figure>



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



<h2 class="wp-block-heading">Binomial Options Pricing</h2>



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).



<pre class="wp-block-code"><code>, 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
 )</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-023.png" alt="Binomial tree" class="wp-image-77947"/></figure>



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



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-033.png" alt="Formula" class="wp-image-77916"/></figure>



 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: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-042.png" alt="Formula 2" class="wp-image-77922"/></figure>



Encoding all of this into SQL, we get:



<pre class="wp-block-code"><code>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 &lt;= rt.t
 and @(rt.branch - lt.branch) &lt;= @(rt.t - lt.t)
 and rt.t = 3
order by branch desc</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-052.png" alt="Call option tree" class="wp-image-77928"/></figure>



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:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-062.png" alt="Call option pricing" class="wp-image-77935"/></figure>



Now you can price your options through SQL!

Binomial Options Pricing in SQL

curve-image-unique-image-unique