Easily calculate the Black-Scholes 'Greek' values Rho, Delta, Gamma, Vega, Theta, d1 and d2 using free open-source Excel macros - functions - formulas. Some of our many free Power4XL Black-Scholes stock options analysis tools for Excel. Calculate put & call premium, historical volatility, implied volatility, greeks, & more. VB open-source formulas work just like native Excel functions.

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

Home

Stock Options

The "Greeks"
(Delta, Rho, Theta, Gamma, Vega, d1, d2)

The "Greeks" are a set of Black-Scholes values which measure the sensitivity of an option's price to changes in the option's parameters.

General Syntax

For each of the Greeks, the function arguments have the following meanings:

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's 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.

Individual Functions

CallDelta

Returns the Black-Scholes value "Delta" for a call option.

CallDelta(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

CallRho

Returns the Black-Scholes value "Rho" for a call option.

CallRho(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

CallTheta

Returns the Black-Scholes value "Theta" for a call option.

CallTheta(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

PutDelta

Returns the Black-Scholes value "Delta" for a put option.

PutDelta(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

PutRho

Returns the Black-Scholes value "Rho" for a put option.

PutRho(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

PutTheta

Returns the Black-Scholes value "Theta" for a put option.

PutTheta(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

OptionGamma

Returns the Black-Scholes value "Gamma" for a call or put option.

OptionGamma(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

OptionVega

Returns the Black-Scholes value "Vega" for a call or put option.

OptionVega(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

Option_d1

Returns the Black-Scholes intermediate value "d1".

Option_d1(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

Option_d2

Returns the Black-Scholes intermediate value "d2".

Option_d2(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

Notes

Many of the functions in this set are interdependent, therefore the entire code block should be installed in tact.

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.

Public Function CallDelta( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) CallDelta = Exp(-DividendYield * Years) * (Application.WorksheetFunction.NormSDist(My_d1)) End Function Public Function PutDelta( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) PutDelta = Exp(-DividendYield * Years) * (Application.WorksheetFunction.NormSDist(My_d1) - 1) End Function Public Function CallRho( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. My_d2 = Option_d2( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ DividendYield _ ) CallRho = StrikePrice * Years * Exp(-RiskFreeRate * Years) * Application.WorksheetFunction.NormSDist(My_d2) End Function Public Function PutRho( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. My_d2 = Option_d2( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ DividendYield _ ) PutRho = -StrikePrice * Years * Exp(-RiskFreeRate * Years) * Application.WorksheetFunction.NormSDist(-My_d2) End Function Public Function CallTheta( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) My_d2 = My_d1 - Volatility * (Years ^ 0.5) Pi = Application.WorksheetFunction.Pi() My_Nd1 = 1 / ((2 * Pi) ^ 0.5) * Exp(-0.5 * My_d1 ^ 2) CallTheta = -(UnadjustedPrice * My_Nd1 * Volatility * Exp(-DividendYield * Years)) / (2 * (Years ^ 0.5)) + DividendYield * UnadjustedPrice * Application.WorksheetFunction.NormSDist(My_d1) * Exp(-DividendYield * Years) - RiskFreeRate * StrikePrice * Exp(-RiskFreeRate * Years) * Application.WorksheetFunction.NormSDist(My_d2) End Function Public Function PutTheta( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) My_d2 = My_d1 - Volatility * (Years ^ 0.5) Pi = Application.WorksheetFunction.Pi() My_Nd1 = 1 / ((2 * Pi) ^ 0.5) * Exp(-0.5 * My_d1 ^ 2) PutTheta = -(UnadjustedPrice * My_Nd1 * Volatility * Exp(-DividendYield * Years)) / (2 * (Years ^ 0.5)) - DividendYield * UnadjustedPrice * Application.WorksheetFunction.NormSDist(-My_d1) * Exp(-DividendYield * Years) + RiskFreeRate * StrikePrice * Exp(-RiskFreeRate * Years) * Application.WorksheetFunction.NormSDist(-My_d2) End Function Public Function OptionGamma( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) Pi = Application.WorksheetFunction.Pi() My_Nd1 = 1 / ((2 * Pi) ^ 0.5) * Exp(-0.5 * My_d1 ^ 2) OptionGamma = My_Nd1 * Exp(-DividendYield * Years) / (UnadjustedPrice * Volatility * (Years ^ 0.5)) End Function Public Function OptionVega( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If My_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) Pi = Application.WorksheetFunction.Pi() My_Nd1 = 1 / ((2 * Pi) ^ 0.5) * Exp(-0.5 * My_d1 ^ 2) OptionVega = UnadjustedPrice * (Years ^ 0.5) * My_Nd1 * Exp(-DividendYield * Years) End Function Public Function Option_d1( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If DividendYield Then MyAdjustedPrice = UnadjustedPrice * Exp(-DividendYield * Years) Else MyAdjustedPrice = UnadjustedPrice End If Option_d1 = (Application.WorksheetFunction.Ln(MyAdjustedPrice / StrikePrice) + (RiskFreeRate + 0.5 * Volatility ^ 2) * Years) / (Volatility * Years ^ 0.5) End Function Public Function Option_d2( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ Optional DividendYield = 0 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. Option_d2 = Option_d1( _ UnadjustedPrice, _ StrikePrice, _ Years, _ Volatility, _ RiskFreeRate, _ DividendYield _ ) _ - Volatility * Years ^ 0.5 End Function

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.