Black-Scholes Model for Option Valuation : Excel Based

About the Course

This course has a single objective which is to build up an Excel based Black-Scholes-Metron Model used for option pricing and calculating option price on a future date. Two of the three persons after whom the model is named are winner of Nobel Memorial Prize for Economic Sciences. The third person would also have got the prize, but he died before the prize was announced and  this prize is not given posthumously. Since 1973 till today this model is widely used by traders to understand movement of European Option price. I have developed this model in Excel and have back-tested with actual market data.

You can first understand the theory behind this model. Then you need to understand the difference between European and American stock options.  Then you can understand three important Excel function NORMSDIST(), EXP() and  LN(). If you are using the latest version of Excel you can use the NORM.S.DIST() function of Excel also. Then I shall take you through the concepts of Option Expiry Date, Option Valuation Date, Underlying Price, Exercise Price, Time to Maturity, Risk Free Rate of Interest, Dividend Yield and Volatility. Then I shall develop,step by step, the valuation model for both PUT and Call European options. Input in each cell will be explained and how they are linked with formula will also be explained. Finally you shall be able to download the entire spreadsheet and view all the formula that are working in the background. The beauty of this spreadsheet is, it has no user-defined functions and no macros. It works in all the versions of Excel from 2007 to any later version of Excel. In case of any difficulty I am always available through the Udemy system. I shall try to answer any query that you may have in 48 hours flat.

About the Black-Scholes-Merton model

Black-Scholes-Merton  is a pricing model used to determine the fair price or theoretical value for an European call or a put option based on five variables such as volatility, underlying stock price, time to maturity, strike price, and risk-free rate of interest.

The Black-Scholes-Merton model assumes that stock prices follow a log normal distribution based on the principle that asset prices cannot take a negative value; they are bounded by zero.

The model assumes that the options can only be exercised on its expiration or maturity date. And so it is extensively used in the European options market. Hence, it does not accurately price American options.

The Excel functions used

Black-Scholes-Metron model tries to value the options, call and put, in a random walk situation. That is why it is anchored on the underlying stock price. To that it applies the concept of volatility, time to maturity, strike price and risk free rate of interest to arrive at the correct price of option. An additional input that is considered is dividend yield, in case of a dividend paying stock.

The probability of underlying stock price being higher or lower than the strike price determines a part of the option price. Hence the Excel function of NORMSDIST() or NORM.S.DIST() is used.

And the underlying stock price follows a log normal distribution and can never be zero. That is why Exp() and LN() functions are used.

Please follow the lessons sequentially only to get the full benefit of the course.

Ultimately,  you shall get a downloadable  fully developed model in Excel, which can be used at all times in the future. I shall not only develop the model, I shall show you how to use it.

Happy learning !