FINA 6750 Urgent
chamankaka2017
BlackScholesMertonImpliedVolatility10e.xlsmImplied Volatility
| Inputs: | Output: | ||
| Asset price (S0) | 125.94 | Implied volatility | 0.8262 |
| 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-Merton model price at this volatility: | |
| Market price | 13.5 | 13.4938 | |
| Maximum error | 0.01 | ||
| call | |||
| put |
Black-Scholes-Merton Implied Volatility Function 10e
Instructions
Instructions: This program is an Excel function that calculates the implied volatility. It is used as follows: =BlackScholesMertonImpliedVolatility(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 BlackScholesMertonFunction, 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. For Excel 2007 and higher, you access the Visual Basic code through the Developer tab, which you must activate from Excel Options.
About
Written by Don M. Chance and Robert Brooks For use with An Introduction to Derivatives and Risk Management, 10th ed. (Mason, Ohio: South-Western Thomson, Inc., 2015) Date: 7/09 Last updated: 3/18/14 © 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
s
s
s
=
=
++
=
=
-
'
012
-
'
201
'2
0
1
21
--
()-()
(1-())-(1-())
ln(/)(/2)
-
c
c
rT
rT
c
BlackScholesMerton
cSNdXeNd
pXeNdSNd
where
SXrT
d
T
ddT
-'012-'201'20121--()-()(1-())-(1-())ln(/)(/2)-ccrTrTcBlackScholesMertoncSNdXeNdpXeNdSNdwhereSXrTdTddT
