NUMERICAL AND SIMULATION TECHNIQUES IN FINANCE
PROJECT I
Edward D. Weinberger, Ph.D., F.R.M
Adjunct Assoc. Professor
Dept. of Finance and Risk Engineering
Code and debug aVBA function that computes the implied volatility of an American equity put option that does not pay dividends. Because there is no analytical formula for this calculation, you must use one of the numerical root finding method such as one of those presented in an earlier class.
You can’t use Newton’s method, because there is no known analytical formula for the derivative of price with respect to volatility. The next fastest method, the secant method, requires an upper and lower bound for the required implied volatility. An obvious lower bound on this volatility is that it has to be greater than zero. A not-so-obvious upper bound on the volatility of an American option is that it has to be smaller than the volatility of an otherwise identical European option selling at the same price (Why?).
Both the bisection and the second methods involve pricing the American option for a given volatility, but, as you know, there is no analytical formula for that either. You have probably implemented at least one binary tree in previous courses, so that’s no fun. Instead, use the Crank Nicholson method presented in class.
I would suggest implementing your option pricer in a function that is called by your VBA function. Although option pricers that are actually used in the industry should “break gracefully,” in the event they detect problems, such as a lack of convergence in a reasonable time or a negative “volatility,” I do not expect that of you. I do, however, expect right answers over the wide range of implied volatilities (less than 10% to, say, 200%) that I have observed in practice. When grading these functions, we will compare the answers to the results we obtain with our version of the function (which has been tested against several generations of students.). You should do the same, i.e., write a MatLab Function to compute the volatility, compare the results with textbook answers, etc.
Please note that I have created the following named ranges in the project template:
“OptPrice”, the current market price of the option
“Price”, the current market price of the underlying, in the same currency units as “OptPrice”. It is assumed that “OptPrice” will buy an option that exercises into the amount of the underlying that can be purchased by “Price”. In other words, “OptPrice” would be the “per share” price of an exchange traded equity option, as opposed to the price per contract, which would exercise into 100 shares.
“Strike”, the per-unit strike price of the option. Note that the option is at the money when market conditions are such that “Strike” equals “Price”.
“TMat”, the time to maturity of the option in years and fractional years.
“RFRate”, the continuously compounded risk free rate, expressed as a decimal fraction. Note that the template will store it that way, even though it is displayed as a percentage.
“ImpVol”, for the implied volatility that you will return. You will return it as a decimal, correct to the nearest basis point, but I have set up the template to display it as a percentage.
Incorporate your function in the spreadsheet template “Project I Template.xls” that is posted in the “Project I” folder on NYU Classes, making sure that it reads the requisite input data from and writes the computed implied volatility to the pre-defined named range “ImpVol” in the template. Email the modified version of the spreadsheet template, with your function, to my TA. PLEASE BE SURE TO INCLUDE YOUR NAME AND YOUR POLY ID IN THE EMAIL.