Financial Functions: Applying Black & Scholes Model in Excel

The Black & Scholes model is widely applied in finance especially to price options on the equity markets. This pricing model assumes the following:

• An interest rate compounded continuously that will be noted as r.
• Geometric Brownian motion dynamics for equity price that will be noted with S_t
• The equity is paying a dividend noted with c.
• The maturity and the strike are noted with T and K respectively.

The formula for the Black & Scholes pricing model for an European call option is:

Given the put-call parity between the options' prices, the formula for the European put option is:

To apply these formulae in Excel, do the following:

1. Specify the option's parameters: the spot price of the underlying security, the maturity of the option, the dividend yield of the underlying and its volatility, the rate of the option and its strike.
2. Input the formulae for the two discount factors as follows:
For the first discount factor, add the displayed in this screenshot, adjusted to your spreadsheet: 


For the second discount factor, input the formula used in the next screenshot:


You can now calculate the price of the option.
3. Input the formula shown in the following figure: 
   

Note that for the parameters of my instrument, the resulting price of a call option is 14.63. To calculate the price of the put option, use the put-call parity formula:

That's about it! You now know how to use this pricing model in an Excel spreadsheet. If you have any questions, don't hesitate to contact me by writing a comment on this page.
See you at the next tutorial!

PS: For more quality content, visit https://bitheap.tech.