BLACK.SCHOLES

[vzczc]

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.xlsx
	A	B	C	D	E	F	G	H
1	Asset price	s	105.00
2	Strike price	x	100.00
3	Time (in years)	t	0.50
4	Volatility	v	20%
5	Interest rate	rr	1%
6	Option type	ot	call
7	Calculation type	ct	value
8
9			optionPrice
10	B & S formula		8.924622622
11
12			value	delta	gamma	vega	theta	rho
13	B & S formula with greeks		8.92462	0.67403	0.02427	26.75493	-5.96947	30.92418
blackScholes

Cell Formulas
Range	Formula
C10	C10	=BLACK.SCHOLES(C1,C2,C3,C4,C5,C6,C7)
C13:H13	C13	=BLACK.SCHOLES($C1,$C2,$C3,$C4,$C5,$C6,const_bsGreeks)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
const_bsGreeks	=blackScholes!$C$12:$H$12	C13