# BLACK.SCHOLES  	BLACK.SCHOLES calculates the price of an option using the Black & Scholes option pricing formula.



## vzczc (Mar 3, 2021)

BLACK.SCHOLES calculates the price of an option using the Black & Scholes option pricing formula. It's a well-known formula that calculates theoretical values of an investment based on the price of an asset, the strike price, time to expiry, interest rate, and volatility.

The Black Scholes Calculator is defined in these formulas:
*Call option*
optionType= "C"

s* N(d1) - x* EXP(-r*t)*N(d2)

*Put option*
x* EXP(-r*t)*N(-d2)-s* N(-d1)

where
N is the cumulative normal distribution function (NORM.S.DIST in Excel)
d1=(LN(s/x)+r*t)/(v*SQRT(t))+0.5*v*SQRT(t)
d2=d1-v*SQRT(t)

The formula also optionally calculates the derivatives with respect to each of the parameters. These are commonly referred to as "greeks", and are delta, gamma, vega, rho, and theta.

The last parameter, calcType, should be "value" if you want to calculate the option price, but can also contain a constant array with each of the required outputs. So, if you want the value and all the derivatives this parameter should be {"value","delta","gamma", "vega", "rho", "theta"}


```
=LAMBDA(s,x,t,v,r,optionType,calcType,
  LET(sqrtT,SQRT(t),
    vSqrtT,v*sqrtT,
    bs_os2pi,0.398942280401432,
    bs_d1,(LN(s/x)+r*t)/vSqrtT+0.5*vSqrtT,
    bs_d2,bs_d1-vSqrtT,
    ert,EXP(-r*t),
    oType,UPPER(LEFT(optionType,1)),
    sign,IF(oType="C",1,-1),
    bs_nd1,EXP(-(bs_d1^2/2))*bs_os2pi,
    ns_d1,NORM.S.DIST(sign*bs_d1,TRUE),
    ns_d2,NORM.S.DIST(sign*bs_d2,TRUE),
    SWITCH(calcType,
      "value",sign*s*ns_d1-sign*x*ert*ns_d2,
      "delta",sign*ns_d1,
      "gamma",bs_nd1/(s*vSqrtT),
      "vega",s*sqrtT*bs_nd1,
      "theta",(-(s*v*bs_nd1)/(2*sqrtT)-sign*r*x*ert*ns_d2),
      "rho",sign*x*t*ert*ns_d2)
  )
)
```

OptionLambda3.xlsxABCDEFGH1Asset prices105.002Strike pricex100.003Time (in years)t0.504Volatilityv20%5Interest rate rr1%6Option typeotcall7Calculation typectvalue89optionPrice10B & S formula8.9246226221112valuedeltagammavegathetarho13B & S formula with greeks8.924620.674030.0242726.75493-5.9694730.92418blackScholesCell FormulasRangeFormulaC10C10=BLACK.SCHOLES(C1,C2,C3,C4,C5,C6,C7)C13:H13C13=BLACK.SCHOLES($C1,$C2,$C3,$C4,$C5,$C6,const_bsGreeks)Dynamic array formulas.Named RangesNameRefers ToCellsconst_bsGreeks=blackScholes!$C$12:$H$12C13


----------

