Calculate Options Implied Volatility in Excel

Implied Volatility Function: 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.

=IVol(OptionType, UnadjustedPrice, StrikePrice, StartDate, EndDate, OptionPremium, RiskfreeRate, DividendYield, WeekdaysOnlyMode, Precision)

Description of parameters

For each Excel Function that calculates an Option Greek or other Options statistic, there are certain parameters required as shown in the formula(s) above. Not all functions use all parameters. Here is a description of each parameter:


WeekdaysOnlyMode: Whethere to include or not include weekends and holidays in your Implied Volatility calculation is a personal preference. Generally, markets are closed on weekends and holidays and some say for this reason, WeekdaysOnlyMode should be set to "ON" thereby calculating an Implied Volatility based on roughly a 252 Day Trading year. Personally, I prefer if off because it's a more accurate representation of a true year and because those that are short options over a weekend still get paid which means the market is really never closed!

StartDate and EndDate: These are not affected based on how WeekDaysOnly Mode is set. Using actual Calendar dates and times when entering the StartDate and EndDate values is the most accurate way to go.

European vs American call options have the same theoretical value. However American Puts are more difficult to value than European Puts. Most professionals agree that the Black-Scholes model provides a reasonably accurate estimation of value.