Options pricing add-in

The options pricing add-in offers offers the possibility to the use extremely fast QuoteLink's options package pricing using cell worksheet functions. There are 2 groups of functions the Black Scholes based and Binomial method based.

These formulas are intended to be used as the building blocks of your spreadsheets. These functions are written in native code and they are much faster then any spreadsheet only based equivalents.


Parameter Description
option type 'Call' or 'Put' most of feeds provide it in the field FLAG example =P("CSCO", "FLAG")
stock price the underlying stock price used for the calculation
days to expiration you can get it easily by subtracting =P("CSCO", "EXPIRATION") - TODAY()
the strike price example =P("CSCO", "STRIKE")
risk free rate risk free rate used you have to input it manually.
historical volatility HV for the underlying stock. You have to input it manually
option price option price to be assumed in the calculation

greeks

=DELTA(option type, stock price, strike price, days to expiration, risk free rate, volatility)

=GAM( stock price, strike price, days to expiration, risk free rate, volatility)

=VEGA( stock price, strike price, days to expiration, risk free rate, volatility)

=THETA(option type, strike price, days to expiration, risk free rate, volatility)

Black & Scholes method

options pricing

=BS(option type, stock price,strike price, days to expiration, risk free rate, hist. volatility)

Binomial method

options pricing

=BN(option type, stock price, strike price, time days to expiration, risk free rate, hist. volatility, number of steps)

with devidends

=BND(option type, stock price, strike price, days to expiration, risk free rate, hist. volatility, dividend times range, dividend amounts range, number of steps)

greeks

=BNP(option type, stock price, strike price, days to expiration, risk free rate, hist. volatility, number of steps)


Unlike BNP formula result comes in an Excel array containing delta, gamma, theta, vega, rho. Please read MIcrosoft Excel documentation to understand how formula returning arrays are manipulated in a spreadsheet.

implied volatility

=IV(option type, stock price, strike price, days to expiration, risk free rate, option price)