Implicit Volatilities (H20)
Goal
By completing this homework you will learn to estimate stock volatilities using the implied method.
Context
"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."
Requirements
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 incorporated in your Spartan Trader and will be one of the elements that may improve the performance of your robotrader over other robotraders.
Wait until the 12 tickers chosen in class will be loaded on Athens. You can check on your own Spartan trader. Use the implicit volatility method and compute the volatilities for the 12 stocks.
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.
NOTES:
- There is no coding, hence no vLab for this homework.
- If VSTO can't find Solver.xlam, just copy 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.
- If you see an error stating that you need to enable the macros, go to Excel > Developer tab > Macro security and enable them.