How to Use Black Scholes Option Pricing Model [EXCEL MODEL]

Share on Facebook8Tweet about this on Twitter3Share on LinkedIn41Share on Google+2

Excel Model

In this post, we will discuss on modelling option pricing using Black Scholes Option Pricing model and plotting the same for a combination of various options. You can put any number of call and/or put options in the model and use built in macro (named ‘BS’) for calculating the BS model based option pricing for each option. The macro (named ‘PayOff’) is used for plotting the Profit/Loss for the overall combination of the option positions against the spot price.

Sheet1 named Payoff has a table where we specify all option parameters. Column B specifies Expiry data for the options. Column C specifies the option type. Column D has the strike price of the underlying asset. Column E shows the premium amount in INR at which the option is bought. Column F tells us about the number of option contracts we have bought. Coumn G specifies the volatility, column H specifies Black Shcoles price of the option (calculated by the macro “BS”. Column I is the current spot price of the underlying asset, column J shows the time to expiry of the option (calculated using the formula). Column K specifies the Expected PnL of the option (calculated using the formula). It is calculated as the difference between the Black Scholes price and the premium paid multiplied by the number of option contracts. Column L shows the actual premium in the market currently, meaning the current premium should you wish to buy the option.

The 13th row calculates the total investment. Since we have bought two call and put options at a premium of 120 and 152 our total investment is 120*2+152*2=544. The 14th row shows the Expected present value. Since the market has moved after the options are bought, the current expected price of the option multiplied by the number of option contracs gives the expected value. Hence the expected payoff is 170.18*2+124.59*2=589.5475.

The present value in row 15 is calculated similarly by taking the product of actual premium in the market currently and number of options contract. Hence the present value is 150*2+120*2=540.

The graph below shows the plot of expected payoff for the option portfolio. This is done by taking the expected payoff values from sheet4. More on this later.

Options Trading Screenshot

BS Price sheet shows the pricing of an option using Black Scholes model. From Black-Scholes option pricing model, we know the price of call option on a non-dividend stock can be written as:

$$C_t = S_t N(d_1) – Xe^{-r\tau} N(d_2)$$

and the price of put option on a non-dividend stock can be written as:

$$P_t = Xe^{-r\tau} N (-d_2) – S_tN (-d_1)$$


$$d_1 = \frac {{ln ( \frac {S_t} {X}) + (r + \frac {\sigma_s^2} {2}) \tau}} {{\sigma_s} {\sqrt{\tau}}}$$

$$d_1 = \frac {{ln ( \frac {S_t} {X}) + (r + \frac {\sigma_s^2} {2}) \tau}} {{\sigma_s} {\sqrt{\tau}}} = d_1 – \sigma_s \sqrt{\tau}$$

$$\tau = T – t$$

is the cumulative density function of normal distribution.


Current price of the underlying


Strike price


Risk free interest rate


Time to expiry


Natural log

The call and put value using Black Scholes framework is calculated in the 13th and 14th row for the parameters specified in row 1 to 5.

Options Trading Screenshot

“Back-end BS” sheet has the same set of values of Payoff sheet from columns A to G. Column H onwards shows the spot price ranges in the 2nd row. You can change the starting point for the price range of Spot Price in Cell H2. The increment (presently of 10 points) can be changed from Cell I2 and then drag it across the range horizontally. The 3rd row shows the Black Scholes call option for the specified parameters and varying spot price. The 4th row shows the Black Scholes put option for the specified parameters and varying spot price. Please note that though the post shows the calculation for three options, you can go upto for a combination of 10 options by just filling appropriate values in the table in Sheet1. For more than 10 options, you can edit the sheet and the macro.

The 13th row calculates the total payoff from the option position. This is calculated as the difference between the profits from options and the total investment.

Options Trading Screenshot

In this case the profit from overall option position is the sum of H3 and H4. The total investment (calculated in Payoff Sheet 13th row) of 544 has to be subtracted from the sum of H3 and H4 to obtain the final payoff. Similar calculations are done to all other columns henceforth.

Options Trading Screenshot

The Expected Payoff graph in Sheet1 is the plot of total payoff calculated in Sheet3 against the underlying spot price.

Options Trading Screenshot

There are two macros. One in BS Price sheet that calculates Black Scholes option price depending upon the values entered in the Payoff sheet. The other one is in the Payoff sheet that plots the Expected Payoff graph. Please make a note that the Expiry Date in Payoff sheet is set beyond the current date, else the Black Scholes price will not return a numerical value for a negative time period.

Download Excel Model

Login to access this file for FREE!

Existing Users Log In
 Remember Me  
New User Registration
*Required field