Returns the value of a European put or call option as determined
by the Black-Scholes formula.
(Values in boldface are required; others are
StrikePrice, StartDate, EndDate, Volatility,
RiskfreeRate, DividendYield, WeekdaysOnlyMode)
Enter "C" or "Call" or 0 for call option; "P" or "Put"
or 1 for put option. If omitted, the function assumes
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.
- 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.
- 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.
(See ContCompRate function.)
If omitted, zero is assumed.
The annualized dividend yield of the underlying
security, expressed in continuous compounding terms.
(See ContCompRate function). If omitted, zero is
0 or omitted = OFF; 1 = ON. (See Notes).
- The inputs to this
function vary slightly from the inputs to the simple "CallPremium"
and "PutPremium" function, giving the "Premium" function
a slight edge in calculating dates and volatility.
This function also takes "OptionType" as an input,
which allows easier copying & pasting of the function
into different cells of your worksheet, such as from one
column headed by the word "CALL" and a second column
headed by the word "PUT". On the other hand, the "WeekdaysOnlyMode"
may be a bit confusing for beginners.
- The Ivol (Implied
Volatility) function calls this function, so you must
have this function installed in order for the Ivol
function to work.
Most securities markets are
closed on weekends and holidays. Therefore the
volatility which drives the value of the option does not
occur continuously, but only 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 twelve
trading or "volatility" days remaining in the option's
life. Setting WeekdaysOnlyMode to ON measures the value
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 the Black-Scholes formula provides a good
approximation of value under normal conditions.
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.