The Black-Scholes formula

I came across the Black-Scholes model many times and hence I decided to invest a little time to find out more about the Black-Scholes formula to derive the price of the call option using the excel spreadsheet today. I tried to apply what I learnt from this book “Financial Modeling for Managers with Excel Applications”, with real life call option chains and I’m surprise I can get a good approximate of the call option price with different maturity, strike price, dividend yield etc.

I was very excited and showed my colleague what I did on the spreadsheet with slight modification. I would like to share with the readers of my blog too. If you are interested, just follow the steps I’m going to illustrate here and put them onto a spreadsheet to see how it can work for you.

The Black-Scholes model derives the following equation for the price c of a call option: where: N(d) is the value of the Normal distribution function at the point d; d = d1 or d2. The other symbols are defined as follows:

You will notice in this formula that the price does not depend upon the mean μ. In everyday language, investor expectations are irrelevant to the price of the option. This may seem like a startling statement. However, it is simply a reflection of the fact that investor expectations do play a role, but only in the price of the underlying physical. The option itself is purely a play on the physical, and the effects of changing expectations can be hedged away by means of the corresponding effect on the physical (the latter being the hedge).

However, this hedging process does not apply to the volatility σ, which appears unavoidably in the price of the option. You can see intuitively why this must be true. Compare two call options, one with a high volatility and the other with low. Suppose that the option is a call option, which is currently a bit out of the money. In other words, the current physical price is slightly less than the strike price of the option, so you would get nothing if you decided to cash in your chip at this point in time. Of course you wouldn’t decide to cash in now anyway, because though your chip is out of the money right now, it could well be in the money at some time in the future. It is definitely worth something because of that possibility; hence, the option price is by no means zero. The option with a higher volatility has more chance of getting back in the money next period than does the one with the lower volatility, so it makes sense that it is worth more.

Nevertheless, you do have to be able to form an estimate of the physical’s volatility, and we shall discuss this below. In the meantime, we note that if we knew what the option was currently trading for, then we could use an equation to reverse the process and solve for the implied volatility in terms of the current price of the option. This process is called backing out the implied volatility.

Backing out implied volatility using Goal Seek

I'm going to use Coca cola company call option chain as an example here. Here is the based data I'm going to use and I recommend you use these data first before you used your spreadsheet to model other option chains. You can use OptionXpress to get most of the information required to compute the Coca cola call option price and do a comparison with what is shown on the Coca cola call option chain in OptionXpress as well.

Open an Excel workbook and on one of the worksheets, type in the following data:

1. In cell A1, type in "Current Stock Value" and use this link provided to get the last traded Coca cola stock price. At this point of writing, the last traded price was US$62.97. Type in this value in B1.
2. In cell A2, type in "Implied Volatility". Used the Coca cola call option chain link to get the implied volatility for the call option with symbol KOBM - Feb 2008 call option with a strike price of US$65.00. At this point of writing, the implied volatility was 18.7%. Type this value in B2.
3. In cell A3, type in "6-month CD rate (annualized)". You can use this link here to compare the different 6-month CD rate. What I did was I used the best rate available on the site as my 6-month annualized rate. You should look under the heading Annual Percentage Yield for this information. At this point of writing, the best Annual Percentage Yield was provided by Country Wide Bank with an Annual Percentage Yield of 5.35%. Type this value in B3.
4. In cell A4, type in "Dividend Yield". Use the same link from step 1 to get the information. You need to do a little of computation here since the dividend yield is not provided. However, you can simply take the dividend payout per share and divide that value with the share price in step 1. At this point of writing, the dividend payout is US$0.34 per share and the last traded price was US$62.97. Hence the dividend yield is US$0.34/US$62.97 which gave us an approximate 0.54% dividend yield. Type this value in B4.
5. In cell A5, type "Days to expiration". Use the same link from step 2 to get the information. At this point of writing, the number of days to expiration for KOBM was 65 days. Type in this value in B5.
6. In cell A6, type "Strike Price". Again, use the link from step 2 to get the strike price information. The call option we are using here is the KOBM, which has a strike price of US$65.00. Type this value in B6.
7. In cell A7, type in "Discounted Share Price". Type the following formula in cell B7. =B1*EXP(-B4*B6/365). Hit the Enter key and you should get a value of US$62.91.
8. In cell A8, type in "Call Option Price (Approximate):". This is the most complicated formula in the entire process here. I suggest you copy what I have here and paste it in cell B8. The formula you should type in cell B8 is =B7*NORMSDIST(SUM(LN(B7/B6),SUM(B3,POWER(B2,2)/2)*B5/365)/(B2*POWER(B5/365,0.5)))-B6*EXP(-B3*B5/365)*NORMSDIST(SUM(LN(B7/B6),SUM(B3,POWER(B2,2)/2)*B5/365)/(B2*POWER(B5/365,0.5))-B2*POWER(B5/365,0.5)). Hit the Enter key and you should get a value of US$1.35. At this point of writing, the last traded price for KOBM is indeed US$1.35.

I guess it's really coincident to get an exact match for the option price for KOBM. Use the spreadsheet to test out the price of the other call options. The last step of this is to use the Goal Seek function to compute the Implied Volatility for different option prices. Click on Tools > Goal Seek or if you are using Excel 2007, click Data > What-If Analysis > Goal Seek. Follow the steps below and compute the implied volatility when the option price goes to US$2.00.

1. In the "Set cell:", select the cell B9.
2. In the "To value:", type in 2.
3. In the "By changing cell:", select cell B2.
4. Click Ok. You should see the implied volatility changed to 24.96% in cell B2.

Hope you have as much fun as I do. I'm trying to see how I can apply the same formula to put option as well as warrants in Singapore. Cheers