Financial Excel Add-In

You can download an experimental Black Scholes Excel Add-in here:

Black Scholes Add-In v0.1

The add-in is a ZIP file with three documents:
(1) the add-in.
(2) a spreadsheet with examples on how to use it.
(3) a txt document with some descriptions.

Functions

The Add-In currently has two functions.

BlackScholes

BlackScholes(Put/call, Stock Price, Strike Price, Volatility, Rate, Time, Dividend Yield (optional), Output Type (optional)).

This will calculate the value and theoretical Greeks of an European option with a continuous dividend yield.

Put/call: will take upper or lower case text of 'p' or 'c' for put and call respectively.
Stock Price: The current market price of the stock.
Strike Price: The strike price of the option.
Volatility: The expected volatility of the stock.
Rate: The annual cash rate of interest.
Time: Time until expiration in years (e.g. 6 months = 0.5).
Dividend Yield: The current dividend yield of the stock (optional parameter which defaults to 0).
Output Type: (Optional parameter)
     0 - Option Value (default value)
     1 - Delta
     2 - Gamma
     3 - Vega
     4 - Theta
     5 - Rho

ImpliedVolatility

ImpliedVolatility(Put/call, Stock Price, Strike Price, Option Price, Rate, Time, Dividend Yield (optional), Output Type (optional)).

This will calculate the implied volatility and theoretical Greeks of an European option with continuous dividend yield.

Put/call: will take upper or lower case text of 'p' or 'c' for put and call respectively.
Stock Price: The current market price of the stock.
Strike Price: The strike price of the option.
Option Price: The expected volatility of the stock.
Rate: The annual cash rate of interest.
Time: Time until expiration in years (e.g. 6 months = 0.5).
Dividend Yield: The current dividend yield of the stock (optional parameter which defaults to 0).
Output Type: (Optional parameter)
     0 - Option Value (default value)
     1 - Delta
     2 - Gamma
     3 - Vega
     4 - Theta
     5 - Rho

Notes: The value is estimated using the bisection method. It will estimate the value as precise as 10^-10 which should be adequate for most situations. If the exercise value is greater than the option value, it will produce an error.

Macros

The Add-in comes with two macros. They add the functions to the Financial functions list in Excel.

DescribeBS
DescribeIV

Run these macros if you want them to appear in the formulas drop-down menu.

Also, if you hit CTRL+SHIFT+A (after typing "=blackscholes" or "=impliedvolatility" into a cell) it will fill in the parameter descriptions.

Feel free to leave any comments regarding any problems with the add-in.