# BSI mp Vol1

Document Sample

```					                Black-Scholes Implied Volatility Function
BSImpVol1.xls

Inputs:                            Output:
Asset price (S0)         125.9375 Implied volatility    #NAME?
Exercise price (X)            125
Risk-free rate (rc)        4.46%
Time to Expiration (T)     0.0959
Dividend yield (dc)        0.00% Check result:
Select call or put             call Black-Scholes model price at this volatility:
Market price                  13.5      #NAME?
Maximum error                 0.01

Instructions:

This program is an Excel function that calculates the implied volatility.
It is used as follows:

=BlackScholesImpliedVolatility(StockPrice,ExercisePrice,Risk-freerate,Time,Volatility,         Yield,
"choice")

where the arguments are references to cells or range names. Enter the stock price, exercise
price, risk-free rate, time to expiration, volatility, and dividend yield. The "choice" variable is a
text variable that can be only the word "call" or "put", which can be selected by using the pull
down menu as indicated. The maximum error you specify determines how close the option price
computed using the implied volatility must be to the actual option price.

The procedure uses an algorithm presented in S. Manaster and G. Koehler, "The Calculation of
Implied Variances from the Black-Scholes Model: A Note," The Journal of Finance 37 (March,
1982), 227-230.

This program also uses another function called BlackScholesFunction, which calculates the
Black-Scholes value directly in a single cell, although this is provided only as a check and is not
necessary for calculating the implied volatility.

Because this procedure is a function, you can drag and drop it into any spreadsheet and make it
available as one of your standard Excel functions. Open this spreadsheet as well as the
spreadsheet in which you wish to use this procedure. Select Tools/Macro/Visual Basic Editor.
Then choose View/Project Explorer. You will see a folder called Modules. Drag and drop the
item called Procedures into the folder of the project you are working on. This will make the
implied volatility procedure available as an Excel User-Defined Function.
Yield,

exercise
is a
pull
price

of
(March,

the
not

make it
the
Editor.
the
the
Written by Don M. Chance, dchance@lsu.edu
For use with An Introduction to Derivatives        and   Risk
Management, 6th ed.
(Mason, Ohio: South-Western Thomson, Inc., 2004)
Date: 10/02
Last updated: 7/7/03

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 6 posted: 9/15/2012 language: Latin pages: 4