Black-Scholes-Merton option pricing spreadsheet

Here’s a copy of a spreadsheet that I authored which uses the Black-Scholes-Merton option pricing formula to price a call option (along with an otherwise identical (same underlying asset, same exercise price, same time to expiration) put option; you can bring up the spreadsheet by clicking on the screenshot below):In order to calculate the arbitrage-free price of a call option, we need to solve the following equation:

C = SN({d_1}) - K{e^{ - rT}}N({d_2}),

where {d_1} = \displaystyle\frac{{\ln (S/K) + (r + .5{\sigma ^2})T}}{{\sigma \sqrt T }} and {d_2} = {d_1} - \sigma \sqrt T .. The arbitrage-free price for the put may be obtained by applying the put-call parity equation. By doing so, we obtain the put pricing equation for an otherwise identical (same underlying, same exercise price, same time to expiration) put:

P = K{e^{ - rT}}N( - {d_2}) - SN( - {d_1}),

where N( - {d_1}) = 1 - N({d_1}) and N( - {d_2}) = 1 - N({d_2}).

For calls and puts, we need five parameter values: S (current underlying asset price), K (exercise price, \sigma (volatility of underlying asset return), T (time to expiration, measured in number of years), and r (the annualized riskless rate of interest). These parameters all show up in cells B1:B5. The first step to solving call and put prices requires determining the values for {d_1}, {d_2}, N({d_1}), N({d_2}),N( - {d_1}), and N( - {d_2}); these values are listed in cells B7:B12. Once we obtain this information, it’s simply a matter of coding the equations for the BSM call and put prices; these prices show up in cells E1:E2.

Finally, I also include the components of the replicating portfolios for the call and put options. Of course, the values of these portfolios must be the same as the call and put option values; otherwise, there would be riskless arbitrage opportunities. As on pp. 17-22 of the Derivatives Theory, part 1 lecture note, one replicates a call option by buying delta units of the underlying asset on margin, whereas one replicates a put option by shorting delta units of the underlying asset and lending money. In the Black-Scholes-Merton pricing model, the call delta corresponds to N({d_1}), whereas the put delta corresponds to N({-d_1}).

Leave a Reply

Your email address will not be published. Required fields are marked *