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

Call Premium

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


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

CallPremium
(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)
  • 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.
  • 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.
  • 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.  If omitted, zero is assumed.
  • DividendYield:  The annualized dividend yield of the underlying security, expressed in continuous compounding terms.  If omitted, zero is assumed.

 

 

Example

 

Notes
  • American call options generally have the same value as European call options.
  • 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.
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.