power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Premium

Returns the value of a European put or call option as determined by the Black-Scholes formula.


Syntax  (Values in boldface are required; others are optional)

Premium
(OptionType, UnadjustedPrice, StrikePrice, StartDate, EndDate, Volatility, RiskfreeRate, DividendYield, WeekdaysOnlyMode)
  • 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.
  • Volatility:  The annualized estimated volatility of the underlying security, expressed as a percentage greater than zero.
     
  • 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).

 

 

Example

 

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.
  • 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 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 WeekdaysOnlyMode.
  • "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.

 

Code
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 the "Individual Function Setup" page.

To copy the contents to your clipboard, place your cursor inside the window below, then press "CTRL+C".

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.

 

Copyright notice:  This site 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.