This post is about simply making the formula work. First manually, then with excel. At the end of this post there is an excel file I had put. Why you need to know how to make the formula work yourself? Because this way, you’d understand the mechanism better, and it’s weakness.
So without further ado, the B & S formula for a call option, looks like this:
Where
and
Where = interest rate,
= time to expiration,
= Strike price,
= Current price of stock,
= future volatility of the stock. All these can be taken from the market, except for future volatility which needs to be estimated.
Although this looks complicated, it’s a simple formula. Let’s look at an example.
Suppose we have a stock that is worth 140$, with strike price set at 160$. Assume also the monthly interest rate is 0.0015% and that the volatility is 0.5. The option expires in 5 months. Then:
This is the part where we use excel. means the cumulative normal distribution of
, or in simple words, what is the probability that
, is less than or equal the value itself.
In excel, goto insert->function-> There you can select categories. Select Statistical. You would now see an array of functions you can chose from. Chose NORMSDIST. The first function you’d see is NORMDIST, without the ‘S’, but it is not the same! After you click that, just insert the number you want to compute, in our case 0.446. We get that
and
Now, we need to plug these in our main function to get the final result:
This is the fair price of a call option! Now, naturally you don’t need to do it all manually, but you do need to plug in all the data (current price, time to expiry and etc)
So for the lazy of you, you can download it here. Just change the variables to suit your needs.
If you liked this post, buy me a beer
Hello Speculator,
Can you tell us more about this B&S in context of smile volatility so I’ll be able to compare mispriced options near the money.
I wuold like to know the diferences relatively (purhapse in per-cent) etween each option price.
With best regards
Shay
Hey Shay, sorry for not commenting sooner. I’m about to write soon about the volatility smile in more detail. Bear in mind it is also my thesis topic so I have a lot to talk in that respect :)
This is first time in my life when I don’t know what to say. After reading all comments I’m shocked. What has happened with our society, with our young generation? They are commenting terrible information, their lexis sounds terrible. Is that true, that only words they know is “cool”, “cute” and cussing words? What are they thinking of? It’s better not to think about it, it’s too sad
wtf you talking about Laydee D?
I believe the Black-Scholes Formula requires inputs measured per year, not per month. Calculated that way the option price is very different from what you have in your example.
here is an online BS calculator
http://indoorworkbench.com/?financerisk/black-scholes-option-calculator.html