Sometime back, my student asked me for help in building a calculator in Excel for pricing options using the Black-Scholes (or what they call as Black-Scholes-Merton) model. So, I thought of doing this tutorial to show how anyone with Excel can build their own BSM calculator.
In the first part, I will calculate the prices of a European-style call and put option.
In the second part, I will calculate the option greeks for the call and put option
Case:
- Underlying price = 307.35
- Exercise price = 335.00
- Volatility = 25% p.a.
- Continuously compounded risk-free rate = 2% p.a.
- Continuously compounded dividend yield = 0% p.a.
- Time to expiration (in years) = 102/365 = 0.2795 year
Let’s start with the first part!
Once you are done with that, you can change the inputs to see the impact on the option prices.
Next, we are going to calculate the option Greeks (i.e. delta, gamma, vega, theta, rho).