In 1973 Fischer Black and Myron Scholes published the seminal paper for what has become known as Black-Scholes option-pricing theory. Black-Scholes option-pricing theory provided a new way to value stock options, but more importantly it started a revolution in how hedge funds and other market participants think about and value financial assets.
Today, hedge fund traders and other sophisticated market participants understand every financial forecast to be a probability distribution. To value an investment is to evaluate its probability distribution. To evaluate probability distributions, hedge funds and other market participants use geometric-Brownian-motion models, binomial models, Monte Carlo simulations and stochastic calculus.
Black-Scholes Calculation in Excel
It is very easy to calculate the option price using the Black-Scholes formulas in Excel. The most difficult part is the implied volatility calculation (iteration). All Excel sheets found on the internet are using VBA (Visual Basic) to do this calculation. This spreadsheet shows a way to calculate the implied volatility using Excel formulas only (and not VBA). The great advantage is that this calculation method is more precise then the VBA method and usable in other spreadsheet programs which do not have to ability of using VBA (e.g Numbers for the iPad).
This file requires Microsoft Excel running under Microsoft Windows or Mac OS X.
Note: If you intend to distribute a modified version of our calculation, you must ask us for permission first, please contact us for further details. If you intend to distribute an unmodified version of our calculation please provide an appropriate link to this post.