Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins &
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.
(Values in boldface are required; others are
StartDate, EndDate, OptionPremium,
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
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
The current value or price of the
- 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
- 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
For this function
to work, you must also have the
"Premium" function installed. Get
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
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
- "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).
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
If you have difficulties with the purchase,
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.
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.