Pricing Derivatives

To determine the value of a call option, one can use the Black-Scholes model, replicated here:

Black-Scholes formula:

(1)
\begin{align} c_0 = S_0 \cdot N(d_1) - PV(K) \cdot N(d_1 - \sigma\sqrt{T}) \end{align}

where

(2)
\begin{align} d_1 = \frac{ln(\frac{S_0}{PV(K)})}{\sigma\sqrt{T}}+ \frac{\sigma\sqrt{T}}{2} \end{align}

and $N(d_1)$ is the NORMSDIST function in Excel, or read from Cumulative Normal Distribution tables, as listed in Table A.5 on page 864 of the Grinblatt text.

For an Excel formula, follow this example generously provided by Marvin Powell: pricing call options with Excel