# 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

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.

 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 😉

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