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

Implied Volatility

Returns the annualized volatility of an underlying security implied by the market price of a European call or put option on that security, based on Black-Scholes valuation.


Syntax  (Values in boldface are required; others are optional)

IVol
(OptionType, UnadjustedPrice, StrikePrice, StartDate, EndDate, OptionPremium, RiskfreeRate, DividendYield, WeekdaysOnlyMode, Precision)
  • OptionType:  Enter "C" or "Call" or 0 for call option; "P" or "Put" or 1 for put option.  If omitted, the function assumes "Call". 
  • UndajustedPrice:  The present price of the underlying security (i.e., the stock price), before adjusting for future dividends.
  • StrikePrice:  Also known as "exercise price", the price at which the underlying security may be bought or sold upon exercise of the option.
  • StartDate:  The beginning day of the period measure.  If valuing an option as of today, you may enter "now()" for this argument.  If omitted, the function treats this argument as zero.  (Using zero as the start date is usually done in conjunction with a fixed time period such as "365," rather than an actual date, as the end date.)
  • EndDate:  The maturity, expiration, or exercise date of the option.  If 0 was entered as the StartDate, then enter as the EndDate the number of days remaining until expiration rather than the absolute date of expiration.
  • OptionPremium:  The current value or price of the option.
  • RiskfreeRate:  The annual risk-free rate of interest which corresponds to a time period roughly equal to the remaining life of the option, expressed in continuous compounding terms.  (See ContCompRate function.)  If omitted, zero is assumed.
  • DividendYield:  The annualized dividend yield of the underlying security, expressed in continuous compounding terms. (See ContCompRate function).  If omitted, zero is assumed.
  • WeekdaysOnlyMode:  0 or omitted = OFF; 1 = ON.  (See Notes).
  • Precision:  The acceptable error for the function's result.  By default this function will return a correct value 0.1% (0.001).  Since this function iterates to find the correct answer, setting a higher value may speed execution in a worksheet containing many (>100) instances of this function.  Lower values increase the precision.  Example:  If the exact implied volatility is 25%, setting "Precision" to .005 will cause the function to return a value between 24.95% and 25.05%.

 

 

Example

 

Notes
  • For this function to work, you must also have the "Premium" function installed.  Get it here.
  • WeekdaysOnlyMode: Most securities markets are closed on weekends and holidays.  Therefore the volatility which drives the value of the option does not occur continuously, but primarily on trading days.  This fact has little bearing on valuing options with a long remaining life, but for options expiring in the near-term, the number of weekend days remaining before expiration can have a material impact on valuation.  For example, if it is now Thursday evening and the option expires at market close on Friday two weeks from now, then there are fifteen calendar days but only eleven trading or "volatility" days remaining in the option's life.  Setting WeekdaysOnlyMode to ON measures the implied volatility of the option based on the number of remaining trading days, not calendar days, before expiration.
  • StartDate and EndDate are unaffected by WeekdaysOnlyMode.  Use the actual calendar dates or time period when entering the StartDate and EndDate, regardless of the value for WeekdaysOnlyMode.
  • "European" call options have the same theoretical value as "American" calls.  American puts are more difficult to value than European puts, but experts agree the Black-Scholes formula provides a good approximation of value.
  • Since the Black-Scholes formula cannot be deconstructed to solve for volatility mathematically, this function uses an iterative process to find the implied volatility.  It behaves similarly to Excel's "Goal-Seek" function, and other native Excel functions which iterate (such as IRR).

 

Code


 

Thanks for your interest in power4XL Black-Scholes macros.  I've made every other macro on this site available for free, but in order to provide a little bit of revenue to support ongoing site development I've decided to ask for a $6 contribution via PayPal for the Implied Volatility function.  (It's a bit more sophisticated of a function since there's no mathematical formula to calculate implied volatility.)

As a bonus to you for supporting the site in this way, you'll have the option to download ALL of my Black-Scholes functions in a single file so you won't have to copy-paste each of them one by one into your macro file.

By clicking the link below you'll be taken to a very fast PayPal check-out page.  In just one minute or so, for only US$6, you'll have the Implied Volatility function along with all my other Black-Scholes functions.

If you have difficulties with the purchase, just email me

Only $6.00!
Implied Volatility function code
+ All power4XL Black-Scholes Functions at Once!

IMPORTANT:  After completing your purchase, click the "Return to Merchant" button to retrieve your functions.

 

 
 

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.