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

Functions in the Black-Scholes Stock Options Toolkit

Functions
Call Premium The value of a "call" option as determined by the Black-Scholes formula
Put Premium The value of a "put" option as determined by the Black-Scholes formula
Premium Calculate Put or Call premium (as above), but using only one function.  Adds flexibility for worksheet implementation and enables calculation based on number of trading days rather than calendar days prior to option expiration.
Historical Volatility One of the key inputs to the Black-Scholes formula for determining the value of an option.  Calculate volatility using daily, weekly, monthly closing price data, either adjusted for dividends or unadjusted.
Implied Volatility Returns the annualized volatility of a security implied by the market price of a put or call option on that security.
Adjusted Price Calculate the adjusted current stock price used as Black-Scholes input, based on actual current price and anticipated future dividend yield.
Continuous Compounding Rate Conversion Converts any periodic interest rate into the continuous compounded rate required by Black-Scholes.
Greeks A set of Black-Scholes values which measure the sensitivity of an option's price to changes in the option's parameters.

  • Delta (Call, Put)
  • Rho (Call, Put)
  • Theta (Call, Put)
  • Gamma
  • Vega
  • d1
  • d2

 

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.