Question

What would be the Excel formula in Spreadsheet 16.1 for the Black-Scholes value of a straddle position?

Short answer

$\text{E6 + E7 = B5×E×P}\left(\text{- B7×B3}\right)\text{×}\left(2\times \text{E4 - 1}\right)\text{+B6×E×P}\left(\text{- B4×B3}\right)\text{×}\left(\text{1 - 2×E5}\right)$

Step-by-step solution

Definition of Straddle

An options strategy in which an investor holds a position in both a call and a put with the same strike price is known as a straddle

Spreadsheet formulae for Black Scholes value

The Black Scholes value formulae in a spreadsheet, as above =

$\text{E6 + E7 = B5×E×P}\left(\text{- B7×B3}\right)\text{×}\left(2\times \text{E4 - 1}\right)\text{+B6×E×P}\left(\text{- B4×B3}\right)\text{×}\left(\text{1 - 2×E5}\right)$

The formula that is used in the excel sheet is showed in actual formula of Black Scholes:

$\text{C = N}\left({\text{d}}_{\text{1}}\right){\text{S}}_{\text{t}}\text{- N}\left({\text{d}}_{\text{2}}\right){\text{Ke}}^{\text{- rt}}$

Where d1 is:

${\text{d}}_1\text{=}\frac{\text{In}\frac{{\text{S}}_{\text{t}}}{\text{K}}\text{+}\left(r+{\displaystyle \frac{{\sigma }^2}{2}}\right)\text{t}}{\sigma \sqrt{t}}$

And d2 is:

${\text{d}}_2{\text{=d}}_1\text{-σ}\sqrt{t}$