power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Calculating Implied Volatility for the Zymogenetics Option Trade

Now that I knew the historical volatility of ZGEN stock, I needed to calculate the volatility implied by the current price of options in the open market.

Quote services provide both the "bid" and the "ask" price.  Since I was not engaging in a mere theoretical exercise but was actually planning to make a trade, the relevant price for my analysis was the bid price, since I was planning to short-sell the options.

So again I developed an Excel spreadsheet template that had a placeholder range of cells for importing the data.  Since Yahoo! doesn't provide for downloading options quotes into spreadsheet format, I simply go to the web page on the Yahoo! financial site, select the relevant block of HTML with my mouse, then copy and paste the HTML directly into my spreadsheet.  I had already set up functions in the spreadsheet to pull the data from the correct pasted cells for the analysis.

Since my tipster had predicted a near-term drop in ZGEN's stock price, I wanted to evaluate the options with a short life.  September options were expiring in ten days; October expiration was almost a month and a half away.  I analyzed both.  As I had suspected, the implied volatility of all around-the-money options, both puts and calls, was well above the historical volatility.  This looked like a potential overpriced scenario, where money could be made by selling rather than buying.

Screenshot of my spreadsheet template after pasting the HTML from Yahoo's option quote page

Efficient markets:  No free lunch

This wouldn't be a slam-dunk, however.  One thing they drilled into our heads in finance courses at Harvard Business School was that markets are efficient and there is no "free lunch".  True arbitrage seldom occurs in the marketplace, or at least not in a way that is accessible to the average investor.  So instead of assuming that the market-makers were asleep at the wheel, I had to consider the fact that there may be expected short-term volatility in the stock price which exceeded the stock's typical volatility.  Sometimes this occurs just before earnings announcements, as stocks tend to make large moves one directly or the other more frequently in connection with this predictable event.  In the case of ZGEN, no earnings announcement was anticipated in the near-term.

However, a little more scratching around in the days leading up to my trade revealed that the company was expected soon to announce the results of a clinical trial.  This was a likely explanation for the high implied volatility.  So if I could develop a reasonable viewpoint on which way the stock was likely to move following the announcement, I'd have a basis for deciding whether to bet on the upside or the downside.  Short calls, or short puts?

I looked further into the reasons my broker had recommended the short sale.  He talked about the company's short supply of cash; the company would need to be raising money soon, the market new it, and this would depress the stock price.  It appeared the market had already priced in the expected positive report on the clinical trial and was just waiting for confirmation before taking profits and dumping the stock.  So there was a believable story behind the downside bet.

Next: Comparing Historical versus Implied Volatility -->

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.