Black Scholes Option Pricing with Power Query in Power BI

The Black Scholes formula returns the value of European put and call options. The version I’m sharing here uses the standard normal cumulative distribution function from my previous blogpost.

Algorithm

For the implementation of this formula in M, I’ve used the R implementation from Financetrain for the ease of use:

BlackScholes <- function(S, K, r, T, sig, type){
  
  if(type=="C"){
  d1 <- (log(S/K) + (r + sig^2/2)*T) / (sig*sqrt(T))
  d2 <- d1 - sig*sqrt(T)
  
  value <- S*pnorm(d1) - K*exp(-r*T)*pnorm(d2)
  return(value)}
  
  if(type=="P"){
  d1 <- (log(S/K) + (r + sig^2/2)*T) / (sig*sqrt(T))
  d2 <- d1 - sig*sqrt(T)
  
  value <-  (K*exp(-r*T)*pnorm(-d2) - S*pnorm(-d1))
  return(value)}
}

In there, the function parameters are defined as:

S = Stock Price
K = Strike Price at Expiration
r = Risk-free Interest Rate
T = Time to Expiration
sig = Volatility of the underlying asset

The article also provides some useful hints on how to generate the input for the last function parameter (Volatility, “sig”).

The Black Scholes M function for Power Query


let func =
(S as number, K as number, r as number, T as number, sig as number, _type as text) =>
let
// Helper function
fnNormDist =
let func =
(X as number, Mean as number, Standard_dev as number, Cumulative as logical) as number =>
if Cumulative = false
then
1/(Standard_dev*(Number.Sqrt(2*Number.PI) ))*
Number.Power(
Number.E,
-0.5* (Number.Power((X-Mean)/Standard_dev,2))
)
else
[ NormZ = Number.Abs( X – Mean) / Standard_dev,
Calc = ( ( 1 / Number.Sqrt( 2 * Number.PI ) ) * ( Number.Power( Number.E, – ( Number.Power ( NormZ, 2 ) / 2 ) ) /
( 0.226 + 0.64 * NormZ + 0.33 * Number.Sqrt ( Number.Power( NormZ, 2 ) + 3 ) ) ) ),
Result = if X < Mean then Calc else 1 – Calc
][Result] ,
documentation = [
Documentation.Name = " Xls.NORMDIST.pq ",
Documentation.Description = " Returns the normal distribution for the specified mean and standard deviation. ",
Documentation.LongDescription = " Returns the normal distribution for the specified mean and standard deviation. https://support.microsoft.com/en-us/office/normdist-function-126db625-c53e-4591-9a22-c9ff422d6d58 ",
Documentation.Category = " Xls.Statistical ",
Documentation.Source = " www.TheBIccountant.com https://www.thebiccountant.com/2021/12/26/excel-norm-dist-function-in-power-query-and-power-bi/",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)),
// Black Scholes formula
d1 = (Number.Log(S / K) + (r + Number.Power(sig, 2) / 2) * T) / (sig * Number.Sqrt(T)),
d2 = d1 – sig * Number.Sqrt(T),
Result = Record.Field(
[
C = S * fnNormDist(d1, 0, 1, true)
– K * Number.Exp(- r * T) * fnNormDist(d2, 0, 1, true),
P = K * Number.Exp(- r * T) * fnNormDist(- d2, 0, 1, true)
– S * fnNormDist(- d1, 0, 1, true)
],
_type
)
in
Result ,
documentation = [
Documentation.Name = " FinDer.BlackScholes.pq ",
Documentation.Description = " Estimates the value of a European call or put option ",
Documentation.LongDescription = " Estimates the value of a European call or put option. Source for Algorithm: https://financetrain.com/black-scholes-options-pricing-model-in-r ",
Documentation.Category = " FinDer",
Documentation.Source = " www.TheBIcountant.com: https://wp.me/p6lgsG-2sg ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

The parameters for this functions are similar to those from the R function mentioned above.

Limitations

Please be aware that the cumulative distribution function used in there, is an approximation still. I might add a more accurate version in the future.

Enjoy & stay queryious 😉

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz