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

Zymogenetics, Inc. (ZGEN), September 2006 Calls

Trade Strategy

In order to evaluate and execute this trade, I took the following steps:

After the options expired ten days later, I was able to calculate my return expressed as an annualized IRR ("internal rate of return").

I have posted the spreadsheet template for you to download, but before you can use it you'll need to install the power4XL macros on your computer.

Calculating historical volatility

I wanted to calculate historical volatility in a robust way.  Sometimes volatility over the past month differs from volatility over a longer period - six months or a year - so I wanted to calculate the volatility over several time periods.  Also, sometimes stocks swing widely on a day-to-day basis, but on a week-to-week basis they are not quite as wild.  This is called "mean reversion" - the prices gravitate towards a midpoint.  Mean reversion skews the "volatility" input of the Black-Scholes formula.  In order to adjust for this, I wanted to measure volatility using both daily and weekly price data for comparison.

I constructed a spreadsheet template that allowed by to copy and paste historical price data downloaded directly from Yahoo!'s finance pages.  The cells that do the actual calculation are programmed into the template, so every time I copy & paste new data from Yahoo, the calculations are automatically updated.

Then I went to Yahoo and downloaded daily historical price data for ZGEN in .csv ("comma separated values") format.  The download automatically opens an Excel page within my MSIE browser.  I then copied & pasted the data directly into the correct range in my spreadsheet template.  I repeated the process with weekly price data.

I found actual historical volatility to be in a normal range.  Depending on the time period measured and the price data interval, the volatility ranged from 42% to 49%.  This was a tolerable amount of latitude within the range.  Since I was looking at a short-term (ten day) trade, I determined the most relevant measure for my analysis would be daily price data over a recent period - one to three months.  Volatility ranged from 44% - 48% during this period.  I used the average of 46% for my calculation.

Next: Calculating 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.