Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins &
Returns the value of a European put option as
determined by the Black-Scholes formula.
(Values in boldface are required; others are
StrikePrice, Years, Volatility,
The present price of the underlying security (i.e., the
stock price), before adjusting for future dividends.
Also known as "exercise price", the price at which the
underlying security may be bought or sold upon exercise
of the option.
- Years: The time
in years until the option expires. For example,
for an option that expires in one month you may enter
"1/12" or ".083". For an option that expires in
forty-five days, you may enter "45/365".
- Volatility: The
annualized estimated volatility of the underlying
security, expressed as a percentage greater than zero.
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.
zero is assumed.
The annualized dividend yield of the underlying
security, expressed in continuous compounding terms. If
omitted, zero is assumed.
- American put options
differ from European put options in that the American
variety can be exercised at any time up until the
expiration date, while European options can be exercised
only on the expiration date. No exact formula
exists for valuing an American put option precisely, but
the Black-Scholes value of its European cousin is
generally considered a good proxy.
- A good estimate for the
riskfree interest rate is the current yield on US
Treasury Bills having approximately the same duration as
the remaining life of the option.
- To estimate dividend
yield, multiply the most recent quarterly dividend by
four, then divide it by the stock price.
To install this function,
copy the entire contents of the window below into your
power4XL code module in Excel's Visual Basic editor.
For detailed instructions, see
Function Setup" page.
To copy the contents to your
clipboard, place your cursor inside the window below, then
NOTE: By copying the code below, you are agreeing to
the power4XL license terms. You agree to use
the code only on your personal and/or business computer(s)
for your own personal use. You agree not to
distribute, publish, alter, or edit the code in any way.
You may freely refer others to this website if you wish to
make the code available to them.
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.