Black Scholes Option Pricing with Power Query in Power BI

by

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

https://gist.github.com/ImkeF/91942d88498566ca00f35df4207499e7

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 comment