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.

This comment has been removed by the author.

ReplyDeleteWell, using MarketXLS works for me. It's great.

ReplyDeleteDividend History in excel