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.
Learn more about bidirectional 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 đ