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.

• 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%.

• 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).