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"}
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"}
Excel Formula:
=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 |
Last edited by a moderator:
Upvote
0
