Estimating Implicit Volatilities (H20)


Goal

By completing this homework you will learn to estimate stock volatilities using the implied (aka 'implicit') method.


Context

source:sg

"Stockton Bonds is a financial GENIUS!"
"Did he win the Nobel Price in economics, Ms. Tickertracker?"
"Not quite.  Maybe he will... I think he should! He showed me how to solve a most VEXING problem we have building the Spartan Trader.  As you know, the calculation of delta requires knowledge of certain parameters: the spot price, the strike, the time to expiration..."
"Sure, we have all those."
"BUT we do not have the volatilities of the stocks. Without that, we cannot compute the deltas! Stockton taught me an estimation method called implied volatility. MOST PERSPICACIOUS!   He even gave me a workbook that computes these estimates."
"It is a good day" think you "For once, I do not have to code new functionality".

"I am on it, Boss."


Task

Wait until the 12 tickers chosen in class will be loaded on Athens on ALPHA and BETA. This will happen on Wednesday night. You can check that on your own Spartan trader. After grading h19 we will load them also on GAMMA.

Using the tool demonstrated in class, compute the volatilities for the 12 stocks that you picked for use in the Hedge Tournament.  These volatilities will be later incorporated in your Spartan Trader and will be one of the elements to improve the performance of your robotrader over other robotraders.


Requirements

  • To submit the homework: download this spreadsheet and fill it up with your point estimates as in the example below.
  • Make sure that the ticker are sorted alphabetically.
  • Submit the spreadsheet as your homework.  No need to zip. The usual rules on writing a note to the TA to get the grade from the team submission apply.
IVol


Notes

  • There is no coding, hence no vLab for this homework.
  • If you see an error stating that you need to enable the macros, go to Excel > Developer tab > Macro security and enable them.
  • You might have SOLVER turned off in your EXCEL.  Easy fix:  start an Excel workbook.  File > Options > Add-Ins > Excel add-ins > Activate Solver
  • If VSTO can't find Solver.xlam, just copy and paste (do not cut and paste) Solver.xlam and Solver.dll from C:\Program Files\Microsoft Office... (or wherever it is) into the folder called "My Documents".
  • Generally speaking, the more you sample (using different days and different databases), the better your results. However, the improvements from additional estimations decrease fairly rapidly.  It is up to you to decide when to stop and what to do with outliers, and with the results of multiple samples (e.g., averaging them out).  Also, sometimes the methods will not converge. That is normal. Try with a different day, or different dataset.