Finance Add-in for Excel for Options & other Derivatives Analysis, Investment Portfolio Construction, and more by Peter Hoadley

www.hoadley.net/options

|||||

Software environment

Pricing & payment options

Hoadley Finance Add-in for Excel

Function Categories
Vanilla options	Exotic options
Interest rate derivatives & convertible bonds	Employee stock option valuation
Historical & forecast volatility; correlation	Probabilities
Portfolio hedging & insurance; profitability	Futures & variance swaps
Value at Risk (VaR)	Portfolio analysis
Asset allocation, optimization & simulation
On-line data	Utilities

View On-line demos of key features

Most of the functions can be used directly from a worksheet cell, or called from a VBA module.

Vanilla options

Option valuation, implied volatility, and analysis functions will handle:

	Options (or warrants) on equities, currencies, indices and futures. (See the Options Strategy Evaluation Tool FAQ for how these option types are handled)
	Black-Scholes (for European options) and Cox, Ross, & Rubinstein binomial pricing models (for European and American options).
	American and European exercise.
	Dividends specified either as an unlimited number of discrete payments, each consisting of an ex-dividend date and an amount, or as a continuous yield.

The pricing, implied volatility, and analysis functions include:

	Option pricing and "Greeks": Calculation of option prices and "Greeks" for American and European options. The HoadleyOptions1 function uses absolute dates for deal, expiration and ex-dividend dates; HoadleyOptions2 lets you specify these in days.
	Implied volatility: Calculation of implied volatility for American and European options. The HoadleyImpliedVolatility1 function uses absolute dates for deal, expiration and ex-dividend dates; HoadleyImpliedVolatility2 lets you use days. Both use the Newton-Raphson method. An Implied Volatility Calculator which will retrieve complete option chains from a number of on-line data providers is included with the add-in.
	Percent-to-target: Calculation of the percentage change in the price of the underlying that would be required to increase the option price by a specified percentage. The "percent-to-double" metric is one example of its use. HoadleyPercentToTarget1 uses absolute dates; HoadleyPercentToTarget2 uses days.
	Implied values: Calculation of values (implied strike, implied spot, implied term, implied volatility and implied risk free rate) implied from either an option price or an option delta. Can be used to identify options to meet specific hedging or other requirements. eg "what strike would I need for a put with a delta of - 0.75?". The HoadleyImply1 function uses absolute dates; HoadleyImply2 uses days.
	Pricing with time-varying interest rates: The HoadleyBinomialTS calculates prices, Greeks, and implied volatility taking into account a term structure of interest rates (ie yield curve). Handles European & American options with discrete dividends or dividends expressed as a yield (which can also vary by time). This function is useful during times of steepening yield curves where the pricing of American options using the usual assumption of constant rates may not be satisfactory. The function also handles Bermudan-style options which cannot be exercised prior to a specific date.
	Pricing with time-varying volatilities and time-varying interest rates: The HoadleyTrinomialTS includes all the functionality contained in the HoadleyBinomialTS function, and in addition it handles a term structure of volatilities -- ie volatilities that vary over the term of the option -- using a flexible recombining trinomial lattice. Being able to capture the volatility term structure is particularly important for longer term options with American exercise. This function is only available under a commercial license.
	Pricing with volatility smiles/skews: The HoadleyOptionsNLN (Non-LogNormal) function prices American and European options when underlying asset prices depart from a lognormal distribution in terms of skewness and excess kurtosis. Also calculates the volatility implied by the underlying asset distribution, the results of which can be used to graph the volatility smile for a range of strike prices. Uses the Gram-Charlier (similar to Edgeworth) expansion method together with an extended Black-Scholes formula (for European options) and Rubinstein implied binomial trees (for American options).
	Early exercise analysis: A component (VBA class) which will analyze an American option specification and report on any optimal early exercise thresholds: the underlying asset price/date combinations where it may be optimal to exercise an option before maturity. See early exercise for more details on the conditions under which early exercise may be optimal.

Exotic options

Functions for the valuation of the most common types of "exotic" options. Both European and American exercise styles are handled for each type of option:

	Barrier options (single and double): Calculates prices, "Greeks" and implied volatility for American (using trinomial trees) and European (analytic and trinomial trees) single barrier options and barrier warrants (the HoadleyBarrier1 function) and double barrier options and barrier warrants (HoadleyBarrier2). Handles discrete dividends, discrete barrier monitoring and rebates. Dividends can be specified as an annual yield or as an unlimited number of discrete payments.
	Basket options: HoadleyBasketOption calculates the value of a European basket option on a portfolio ("basket") of underlying assets using an analytic moment matching approximation. HoadleyBasketSim calculates the value of both European and American basket options using correlated Monte Carlo simulation. The Longstaff and Schwartz simulation model (LSMC) is used for the American pricing.
	Spread options: HoadleySpreadOption calculates the price, hedge parameters and implied correlation for options on the price differential between two assets. eg the heating oil crack spread futures options which are traded on NYMEX. Both European and American exercise handled. Uses a modified Black model for European options and Rubinstein's three dimensional binomial trees for American options.
	Asian options: Two functions for valuing European and American-style arithmetic average price options (Asian options). HoadleyAsianA calculates the value and "greeks" of an European Asian option using an analytic model. Handles averaging using either continuously or discretely observed underlying prices, and averaging which occurs over only a part of the option's life. HoadleyAsianB uses a binomial tree to value both European and American-style Asian options.
	Quanto (cross-currency) options: Four functions for valuing regular, single barrier and Asian Quanto options: options on assets denominated in a foreign currency with settlement in the domestic currency at a pre-defined exchange rate. The functions handle both European and American exercise. An example of an exchanged traded quanto option is the CME Nikkei 225 US dollar based futures option (American style) which is on a Yen-denominated asset but settled in US dollars.
	Binary (digital) options: Nine functions for cash or nothing and asset or nothing, single barrier cash or nothing and asset or nothing, and double barrier cash or nothing, binary options. Fair value and "Greeks" are calculated for all options.

Interest rate derivatives and convertible bonds

Functions available for valuing interest rate instruments (bonds, floating rate notes, convertible bonds, & derivatives) include:

	Bond valuation: HoadleyBond for the valuation (clean & dirty), yield to maturity, duration and modified duration of coupon bonds. HoadleyBond accepts a term structure of interest rates ("zero coupon yield curve") as input. As well as valuing bonds, this function can be used, together with equity option pricing functions, to value some hybrid securities, such as converting and reset preference shares. Handles exchange-traded bonds and hybrids with ex-coupon dates.
	Floating rate notes (FRNs): HoadleyFRNote for the valuation, and effective margin (spread) over swap, of FRNs ("floaters"). FRNs have defined maturity dates and coupon rates which are periodically reset based on a contract margin over a reference rate. The function accepts a term structure of interest rates (Swap curve, LIBOR). Exchange traded floating rate notes with ex-interest dates are handled as well as the grossing up of yields to take account of the impact of tax imputation (franking) credits. This function is available in the full version of the add-in only.
	Convertible bonds: HoadleyConvBond for the valuation of convertible bonds, convertible notes and other similar hybrid securities with optional hard call, soft call (triggers) and put features. Valuation is by trinomial tree using the methodology by Tsiveriotis and Fernandes which takes into account issuer credit risk and the impact of embedded calls and puts -- none of which are correctly handled by the simplistic but widely used bond-plus-option approaches to valuation. Function available in full version of add-in only.
	Bond options: (valuation, Greeks, implied volatility) HoadleyBondOptBlk for European coupon bond options using Black-76; HoadleyBondHW for European and American coupon bond options using the Hull-White analytic, and trinomial interest rate tree short-rate models. Both functions accept the zero curve as input.
	Caps and floors: (valuation, Greeks, implied volatility) HoadleyCapFloorBlk using Black-76, or HoadleyCapFloorHW using the Hull-White short-rate model. Both functions accept the zero curve as input.
	Swaptions: (valuation, Greeks, implied volatility) HoadleySwaptionBlk for European options on interest rate swaps (swap options) using Black-76; HoadleySwaptionHW for European and American swaptions using the Hull-White analytic and trinomial interest rate tree short-rate models. Both functions accept the zero curve as input.
	Eurodollar futures options: HoadleyEurodollar for the valuation, "Greeks" and implied volatility of American and European options on Eurodollar time deposit futures contracts.

Employee stock option (ESO) valuation functions

Standard option pricing models cannot be used to determine the value of employee stock options. Vesting requirements, forfeiture of unvested and OTM options when employees leave the company, non-tradability of ESOs, and other considerations make ESO valuation more complex than standard option valuation.

The add-in contains a number of functions designed specifically for IFRS 2 and FASB 123R-compliant ESO valuation. A corporate/commercial license is required to use the ESO functions. See ESO functions for more details.

Historical volatility, correlation/covariance, and price distribution functions

Four functions for analyzing volatility and price distributions -- the most important components for option valuation:

	Un-weighted and exponentially weighted volatility: The HoadleyHistoricVolatility function calculates the volatility of an asset based on a sample of historical prices (close-close, high-low, high-low-close, or high-low-open-close). Includes both equally weighted volatility calculations and calculations using the exponentially weighted moving average (EWMA) model. When using EWMA the smoothing constant can be optionally calculated automatically using the maximum likelihood method.
	GARCH(1,1): HoadleyGARCH uses the GARCH (generalized autoregressive conditional heteroscedasticity) model to calculate the volatility of an asset based on a sample of historical closing prices. The function can also be used to forecast future volatilities and volatility term structures (how volatility can be expected to change over time). All GARCH parameters (including standard errors and confidence interval information) are automatically estimated using the maximum likelihood method. See accuracy of HoadleyGARCH for further details. The GARCH function is used extensively in the Historic Volatility Calculator which is included with the full version of the add-in. The HoadleyGARCH function itself is only available in the full version of the add-in.
	Volatility cones: The HoadleyVolatilityCone function produces information which can be used to plot volatility cones using historical prices. Information produced includes average, maximum, and minimum volatilities calculated using a rolling windows of a specified size, current volatility with confidence interval bounds, and percentile bands. Volatility cones can help determine whether current implied volatility (eg from the Implied Volatility Calculator) is high or low compared with historical volatility measured over the same period.
	Correlation and covariance matrices: Functions for creating correlation and covariance matrices from historic price data. Unlike the simple Excel corr and covar functions these functions create an entire matrix with one function call and without the need to calculate asset returns from prices. Both correlation and covariance matrices can be produced using either the equally weighted model, or the EWMA model (as per the RiskMetrics datasets). Functions are included to convert correlation matrices to covariance matrices, and vice versa.
	Correlation and volatility where price histories differ in length: The HoadleyCorrelStambaugh function uses the Stambaugh (1997) method to calculate the volatilities and correlation matrix for assets where price histories have the same end date but different start dates. Note: this function is currently in beta testing, awaiting independent verification of results.
	Orthogonal EWMA (OEWMA) and Orthogonal GARCH (OGARCH): Two functions to enable the preparation of covariance & correlation matrices using EWMA or GARCH without the need to use the same decay factor (EWMA) or GARCH parameters across all assets. Uses the methodology, based on principal component analysis, developed by Carol Alexander and described in the paper "Orthogonal methods for generating large positive semi-definite covariance matrices". Two functions are also included enable the charting of daily direct and orthogonal volatilities for a given asset, which can be useful in assessing the suitability of the orthogonal models.
	Asset price distribution: HoadleyPriceDist measures the extent to which a sample of historical prices (intra-day, daily, weekly...) diverges from a lognormal distribution (in terms of skewness and excess kurtosis).
	Auto-correlation: HoadleyAutoCorrel calculates autocorrelation in returns and in squared returns using a sample of historic prices. Can be used to determine the extent to which volatility clustering is present and hence the suitability of using GARCH to estimate volatility. Includes the Ljung-Box test for statistical significance.

For more information see the FAQ on volatility, and the historical volatility calculator. Also view the historical volatility demo and the GARCH demo.

Probabilities

Functions to help quantify the likelihood of trading success (or failure) and to help manage risk:

	End of period probabilities: The HoadleyProbAtEnd function will calculate the probability that the underlying asset will be above or below a target price at the end of a specified number of days. Dividends on the underlying stock can be a continuous yield, unlimited discrete payments, or none.
	Any time probabilities - analytic model: HoadleyProbAnyTime1 will calculate the probability that the underlying asset will be above or below a target price at any time during the period. HoadleyProbAnyTime2 will calculate the probability that an asset will be outside either of two prices (ie a target range, like the breakeven points of a straddle) at any time during the period. It will also calculate the probability of moving outside both of the targets, and of being between the target prices at some time during the period. These powerful functions, which provide valuable information for evaluating American options, and options and warrants with barriers and triggers, are only available in the full version of the add-in.
	Any time probabilities - trinomial tree model: HoadleyProbAnyTime1T and HoadleyProbAnyTime2T perform the same functions as the above analytic models, but in addition, because they use a trinomial tree methodology, can be used to calculate probabilities on stocks with discrete dividend payouts during the period (which have no analytic solution). Any-time probabilities are very sensitive to discrete dividends (both the amount and timing). When there are no discrete dividend payments, the results converge to the corresponding analytic models.
	Future price estimation: The HoadleySpotAtEnd function will calculate the spot (or futures) price at the end of a period, given a probability (up or down). This function can be used to plot probability cones showing expected stock or futures price distributions (probability bands) between "time now" and a future period. The samples worksheet contains an example of how to plot probability cones. The HoadleySpotAnyTime function will calculate the spot price that has a specified probability of occurring at any time during a specified period of time. .
	Probability distribution analysis: HoadleyProbDist calculates the probability distribution for lognormal and non-lognormal price distributions. The output from the function can be used to plot probability distribution curves to enable the impact of changing input variables such as volatility, skewness, and kurtosis to be observed. The HoadleyProbDist function is only available in the full version of the add-in.
	Monte Carlo simulation: A class for use from a VBA module for running Monte Carlo simulations. Can be used to generate log-normally distributed prices with any number of discrete dividend payouts. Methods include calculation of probabilities which can be used to verify the accuracy of the above analytic and trinomial tree models. Available only in the full version of the add-in.

All probabilities (apart from Monte Carlo simulation) are calculated analytically or by trinomial tree so their fast calculation times make them suitable for applications which need to calculate and manipulate a large number of probabilities (eg a probability matrix by spot and strike prices).

See FAQs for more on of probabilities and also view the probability analysis demo.

Portfolio hedging, portfolio insurance & profitability

Functions for hedging, portfolio insurance, risk control, and for calculating trade profitability:

	Position hedging -- adjusting the "Greeks": The HoadleyHedge function will calculate the adjustments to current portfolio positions (consisting of options and their underlying assets) required to achieve specific hedge ratio targets with respect to one or more of a portfolio's position delta, position gamma, or position vega. For instance, dynamic hedging by simultaneously making a portfolio delta and vega neutral, or delta, gamma and vega neutral. You can nominate whether the hedging adjustments should be made using options alone, or by using the underlying plus options.
	Position hedging optimization: The HoadleyHedgeOptimize function will scan a list of available options trades (eg from an options chain) and identify the optimal combination of trades in the underlying and/or options required to meet specific hedge ratio targets. Several scenarios are returned by the function, each of which, in addition to meeting the specified hedge ratio targets with the minimum possible number of trades, will also meet supplementary targets. For example, maximizing the position theta of a delta and vega neutral portfolio (so the hedger can derive maximum benefit from time decay), or minimizing the outlay required to make a portfolio delta and gamma neutral. The Implied Volatility Calculator contains a hedging optimization component which uses this function.
	Portfolio insurance: HoadleyPortInsure will calculate the strike and number of index put options required to insure a portfolio of equities against falling below a specified value. Usually the beta of the portfolio to be insured will not equal one, meaning the expected portfolio returns will not mirror index returns. The Capital Asset Pricing Model (CAPM) is therefore used to ensure that these differences are taken into account.
	Correlation and covariance functions: Functions for calculating the correlation (or covariance) between assets using historical prices. These measures can be used for hedging (eg for cross hedging commodities) and for Value at Risk (VaR) analysis. See VaRtools for more details.
	Profit at option expiry: The HoadleyPLExpiry function calculates the profit or loss of an option trade at option expiry. Along with the other two profitability functions below, this function can be used to produce pay-off diagrams, and to analyze the profitability of individual options trades, multi-trade positions, and entire portfolios.
	Profit if close prior to expiry: HoadleyPLIfClose calculates the profit or loss of an option trade if closed at any time prior to expiry.
	Underlying asset profitability: HoadleyPLUnderlying calculates the profit or loss of a position in an underlying asset as at a specified date and spot price The function takes into account dividends (discrete or continuous) which are received or paid during the period. This function would generally be used in association with the option profit and loss functions to, for instance, calculate the profit or loss from a covered call, or a zero cost collar.

Futures & variance swaps

Futures: Two functions for the pricing and valuation of forward and futures contracts. Both functions handle futures on investment assets paying income as discrete payments (like individual dividend-paying stocks or coupon bonds) or paying income expressed as an annual yield (like stock indices and foreign currencies):

	Futures pricing: The HoadleyFuturesPrice function calculates either the futures price, or the risk free rate implied by a given futures price.
	Futures contract valuation: HoadleyFuturesConVal calculates the value of a futures contract that was entered into some time in the past. This represents the profit or loss of closing out a contract prior to maturity.

Variance swaps: HoadleyVarianceSwap1 calculates the fair variance and hedging portfolio weights for a variance swap. HoadleyVarianceSwap2 will calculate the value of a variance swap some time after inception taking into account realized volatility to date and forecast implied volatility.

Value at Risk (VaR) -- "VaRtools"

A set of functions and components for calculating Value at Risk (VaR) on multi-asset portfolios containing both linear (eg stocks, futures, FOREX exposures) and non-linear instruments (eg options, bonds).

VaR can be calculated using the covariance/analytic model (for linear portfolios) or by correlated Monte Carlo simulation for portfolios containing non-linear instruments like options.

Other features and tools include cash flow mapping for bonds and other interest rate assets, multi-asset portfolio volatility calculation, functions for calculating the Beta and R-Squared for individual assets and for portfolios, functions for the preparation of correlation and covariance matrices from historical prices using either equally weighted or EWMA models, and more.

See VaRtools for more information on features.

VaRtools is only available in the full version of the Finance Add-in for Excel.

Portfolio analysis

A set of tools for analysing the structure of portfolios of linear assets (eg stocks) and yields, and for designing portfolios based on optimal asset allocation.

The functions can be used to calculate a number of the key indicators required by Modern Portfolio Theory (MPT), the Capital Asset Pricing Model (CAPM), and the Arbitrage Pricing Theory (APT). These include individual security and portfolio Beta and R-Squared statistics, overall portfolio volatility, and Principal Component Analysis (PCA).

Available functions include:

	Correlation and covariance matrix functions using historic price data. Unlike the simple Excel corr and covar functions the these functions create an entire matrix with one function call and without the need to calculate asset returns from prices. Both correlation and covariance matrices can be produced using either the equally weighted model, or the EWMA model. Functions are included to convert correlation matrices to covariance matrices, and vice versa.
	Beta and R-Squared: HoadleyBeta and HoadleyRSquared will calculate beta and r-squared for individual assets and and for portfolios of assets using a history of asset closing prices. Both beta and r-squared can be calculated using the equally weighted model, or the EWMA model which gives more weight to recent data. These functions are useful for portfolio hedging or for portfolio "engineering". For example, using index futures to remove beta (market risk) from an equity portfolio so it can earn an "absolute return" equal to the risk free rate plus alpha.
	Portfolio volatility: HoadleyPortfolioVol will calculate the net volatility for a portfolio of assets taking into account the correlation between the assets and their individual volatilities. HoadleyPortfolioVolFX will calculate the net volatility for a portfolio containing a mixture of domestic and foreign assets.
	Active Portfolio Management Statistics: HoadleyPortfolioStats decomposes return and risk/tracking error into residual (alpha), active, and market; calculates beta, R-Squared, the Sharpe ratio and information ratios (residual and active). All measures are shown for individual assets and for the portfolio. The residual correlation matrix is also produced by the function. An additional samples spreadsheet is available for download which illustrates how this function can be used to assess the impact on key active management statistics of changing the portfolio beta (eg to 0 or 1) using index futures contracts.
	Style Analysis: HoadleyStyleAnalysis, will analyze the style of a mutual fund or portfolio using returns-based methodology originally developed by William F Sharpe. This approach uses quadratic programming to determine the combination of positions in passive indices, style benchmarks, or asset classes that would best replicate the performance of a fund or portfolio over a specified time frame. Licensing: When purchased under a private/personal license the number of returns that can be handled is capped at 12 returns, or one year of returns. To handle an unlimited number of returns a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for private investors ("Pro" licensing enquiries).
	Hoadley Portfolio Style Analyzer: An Excel-based application that uses the HoadleyStyleAnalysis function to analyze the style of a portfolio or mutual fund. More details.
	Drawdown Analysis: HoadleyDrawdown will analyze the drawdown history of a fund or portfolio using a history of returns. One or more drawdowns within the analysis period can be highlighted (eg the maximum drawdown and recovery dates, and the second largest drawdown and recovery dates), and information is returned to enable the plotting of drawdown "under water" charts. The drawdown function is used to produce the drawdown analysis in the Hoadley Portfolio Style Analyzer.
	Principal Component Analysis (PCA): A set of functions for undertaking Principal Component Analysis on a portfolio or yield curve, using either price/yield data as input or a covariance matrix. Options include several different types of data normalization, a choice of using equally weighted or EWMA models for covariance, and two options in the way returns are calculated. Functions are included to reconstruct original covariance matrices using all or some of the principal components, and to calculate the factor scores of the principal components themselves. A spreadsheet can be downloaded which illustrates how Principal Component Analysis can be used in Arbitrage Pricing Theory (APT) applications in constructing factor (basis) portfolios, and asset-mimicking portfolios from the factor portfolios (requires full version of add-in).

Risk decomposition with statistical factor model: Two functions which use principal component analysis to decompose portfolio and individual asset risk into market factor-specific volatility, and "specific" or "residual" volatility.

Portfolio aggregation tools: Two functions to simplify the data management and computation issues associated with calculating value at risk( VaR), preparing correlation matrices etc by providing a simple means of aggregating the individual assets of sub-portfolios into higher level portfolios.

HoadleyPortAggregate aggregates a portfolio of any number of individual assets into a single weighted price series which can be used to represent the sub-portfolio in any functions which accept historical prices as input. HoadleyPCAPortAggregate also aggregates a portfolio but uses principal component analysis (PCA) to limit the number of principal components used. Representing portfolios by a limited number of factors rather than by individual assets can lead to more stable/robust results when preparing covariance matrices, VaR etc.

Portfolio asset/sector allocation, optimization, & simulation

Functions and components for portfolio design and construction, optimization and simulation. Functions include:

Black-Litterman asset allocation model: Four functions providing a full implementation of the Black-Litterman Bayesian asset allocation model for portfolio design. The starting point for the Black-Litterman model is either a market equilibrium portfolio (eg an index), or your strategic asset allocation target, to which user views or "tilts" are applied. The functions will back-out implied market/strategic portfolio returns and implied betas from market cap weights by reverse optimization, and will allow the user to modify the implied returns by applying absolute and relative views, with specified levels of confidence. An optimal unconstrained portfolio based on these views can then be produced.

Alternatively the modified returns from Black-Litterman can be fed into the Hoadley Portfolio Optimizer if the portfolio designer wishes to impose asset weight and/or higher level constraints. See Estimating Returns.

An additional samples spreadsheet which replicates the results from examples contained in two of the key papers on the Black-Litterman model (Idzorek's "A Step-By-Step Guide to the Black-Litterman model" (2004), and He and Litterman's "The Intuition Behind Black-Litterman Model Portfolios") is available for download with the full version of the Finance Add-in for Excel.

View the Black-Litterman tutorial

Black-Litterman licensing: When purchased under a private/personal license the number of assets that can be handled by the Black-Litterman functions is capped at ten. To handle more than ten assets a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for private investors ("Pro" licensing enquiries).

Portfolio optimizer/efficient frontier: A function for performing mean-variance optimization on a portfolio of assets. The function (HoadleyEfficientFrontier) implements the Markowitz critical line algorithm to produce a user specified number of efficient portfolios providing a range of returns starting at the minimum variance portfolio and ending at the maximum return portfolio. The optimal (tangency) portfolio (which maximizes the Sharpe ratio) is also returned. The function can be used in a worksheet, or in a VBA module.

In addition to individual asset minimum and maximum weights, additional linear constraints can be specified to group assets one or more higher level classifications, such as industry, large cap/small cap, asset class etc., Minimum and/or maximum weights can be specified for these higher level groupings -- for example, technology stocks must be at least 5% and nor more than 15% of the total portfolio. The function will return the weights by constraint (eg by industry) as well as by individual asset.

Efficient frontier licensing: When purchased under a private/personal license the number of assets that can be optimized is capped at ten. To optimize more assets using Portfolio Optimizer Pro a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for private investors ("Pro" licensing enquiries).

	Portfolio Optimizer: An Excel-based application which Analyzes a portfolio of stocks and produces the efficient frontier and capital market line using mean variance optimization. The "Pro" version of the portfolio optimizer uses the HoadleyEfficientFrontier function as the optimization engine. See Portfolio Optimizer for details.
	Portfolio Monte Carlo Simulation: A class for simulating future portfolio risks and returns -- including the likely spread of returns and the probabilities of occurrence -- for portfolios consisting of mutual funds, individual assets, or asset classes. A key feature is the ability to model the impact of periodically rebalancing the portfolio back to an optimal (eg from the Hoadley Portfolio Optimizer) or strategic asset allocation. The simulation class is used in the Portfolio Monte Carlo Simulator.
	Retirement Planning using Monte Carlo Simulation: A class for preparing retirement plans using Monte Carlo simulation. The retirement planning class is used in the Hoadley Retirement Planner.

On-line data

The add-in includes several components for retrieving on-line quotes and option chains:

Quotes from Yahoo (free): HoadleyYahooQuotes (a function plus design wizard for Excel 2002 or above) and HoadleyGetQuotes (a subroutine for Excel 2000 or above) for on-line stock and index price quotes (option prices are also available on some exchanges). The quotes, which are free of charge, are supplied by Yahoo finance. Most exchanges around the world are supported. The type of information available (stocks, options, delayed, real-time) depends on Yahoo's support for each exchange. See Yahoo exchange list for a list of exchanges covered. No browsing of the Yahoo site is required as the component retrieves all information directly into Excel spreadsheet cells.

Streaming (dynamic) real-time quotes: The Finance Add-in includes functions plus design wizards to deliver streaming (ie dynamic, without the need to manually refresh) into Excel spreadsheet cells (Excel 2002 or above required). Unlike DDE solutions, stock codes and field names do not need to be hard-coded in formula cells. The following quote sources are currently explicitly supported by the add-in (but note that the add-in can use data from any source that can be brought into an Excel spreadsheet -- see notes below):

	Marketfeed (US markets): Equity, index and option quotes. Subscription to the Marketfeed quotes service is required. More details.
	OptionsXpress (US markets): Equity, index and option quotes. This service, which utilizes the OptionsXpress streamer data feed, is currently free of charge for OptionsXpress customers/account holders. More details.
	eSignal (US and international markets): A wide range of instruments (including futures options) and markets are supported by eSignal. Subscription to eSignal required. More details on instruments and markets.
	Stockwatch (Canadian and US markets): Equity, index and option quotes. Subscription to Stockwatch required. More details.
	BullSignal (Australian markets): Equity, index and option quotes. Subscription to WebLink's BullSignal data service is required. See More details.
	Netquote (Australian markets): Equity, and index quotes. Subscription to Netquote Information Services data service is required. More details.
	MoneyAM (UK markets): Equity and index quotes. Free registration with MoneyAM required for limited streaming quotes service; subscription required for unlimited service.
	TD AMERITRADE (US markets): Equity, index and option quotes. Free to TD AMERITRADE customers. More details.

Option Chains: A component for use in a VBA module for retrieving entire on-line option chains into spreadsheets. Exchanges currently supported include US Exchanges using MSN and CBOE (manual entry of ticker symbols), The Australian Stock Exchange, Eurex, Euronext.LIFFE (London, Paris and Brussels), and the National Stock Exchange (NSE) of India. This data is free and delayed 15 minutes. Live (real-time) option chains can also be retrieved from eSignal (US and international), Marketfeed (US), OptionsXpress (US and Australia), TD AMERITRADE (US), Stockwatch (Canada and US), and BullSignal (Australia).

A comma delimited text file containing option chain data can also be used by the add-in. The format of the text file is described in the option chain help file documentation, and a sample application which illustrates how to create a compatible text file is available for download.

Included with the add-in is an Implied Volatility Calculator which uses the option chain component to retrieve option chains. Implied volatility, implied volatility surface, Greeks, and theoretical vs market pricing comparisons are then automatically calculated for the entire chain. The Options Strategy Evaluation Tool will also utilize the option chain component if the full version of the add-in is installed.

Historical price download from Yahoo:

Toolbar and wizard for downloading price history for multiple symbols into a worksheet with one button click. Prices are aligned by date even when prices for some dates are missing from Yahoo (eg because of trading suspensions, public holidays etc) and several simple options are provided for handling missing prices.

Component for use in a VBA module for downloading history under program control.

Notes:

None of the pricing, volatility, probability or any other functions in the add-in depend on being able to retrieve on-line data using the above data components. All add-in functions, like Excel's own functions, are completely independent of any data source. The main purpose of the on-line data components in the add-in is to provide simple-to-use data sources for position valuation, scenario evaluation, option chain analysis etc. in your own spreadsheets or in the various applications available from this web site (Options Strategy Evaluation Tool, Open Positions Manager, Implied Volatility Calculator). Data from any other sources can be used in place of data retrieved by using the above on-line data components.

No guarantee can be given that free price data, or option chain data, will be provided by any of the information providers currently used by the add-in, that all types of options available on an exchange will be available through the option chain interface, or that the data which are currently available will continue to be available from these information providers in the future. No guarantee can be given that the data will remain free of charge, or that the providers will not change the format of their data without notice in the future.

Utilities

The add-in contains several utilities to perform commonly required functions:

	Rate conversions: HoadleyRateCon for conversion of rates expressed in one compounding frequency to another. eg monthly compounding to continuous compounding.
	Forward rates: HoadleyForwardRate to calculate the forward rate applying between two future periods.
	Zero rates: HoadleyZeroRate to calculate the zero coupon rate implied by a forward rate and a zero rate.
	Discrete dividends to yield: HoadleyDivYield to convert a schedule of dividend payments to an equivalent yield taking into account the term of the option and ex-dividend dates.
	Volatility adjustment functions for discrete dividends: Two functions to improve the accuracy of option valuation with discrete dividends when using historical, as opposed to implied, data for volatility estimates.
	Price matrix conversion: HoadleyPriceMatrix to convert a matrix of historical prices by date by ticker symbol, which may contain missing prices and non-trading days, to a form suitable for the calculation of beta, r-squared, correlation and covariance matrices etc., all of which require that prices be aligned by date and that there be no missing prices. Several simple methods are available to handle missing prices.
	Correlated Monte Carlo simulation: HoadleyCorrelSim to undertake correlated Monte Carlo simulation of lognormally distributed prices for two or more assets. Can be used to value options where the payoff depends on the correlation between multiple assets.

Context Sensitive Help & Working Examples

The add-in includes a standard Windows help file containing comprehensive documentation of all functions and classes. The help file can be accessed either from the Windows program group or from the HoadleyOptions menu which is inserted into the main Excel menu when the add-in is installed (this can be removed if not wanted).

The help system is also integrated with the Excel function wizard. Functions can be inserted into your spreadsheet cells by selecting the required function from the HoadleyOptions menu. You can then launch the Excel function wizard for the selected function (by pressing the "=" symbol (Excel 2000) or "fx" symbol (Excel 2002 & above) on the Excel formula bar. Context sensitive help is available on the selected function by selecting "help on this function" from the function wizard.

The add-in also comes with a sample spreadsheet containing working examples of all functions and classes. The spreadsheet can be opened either from the Windows program group, or from the HoadleyOptions menu in Excel.

On-line demo of key features (5 minutes)

Software Environment

Microsoft Windows 2000 or later, including Vista. Microsoft Excel 2002, 2003 or 2007. Microsoft Excel 2000 may run the add-in and associated applications but new features are no longer tested on Excel 2000 and support will not be provided for users of Excel 2000.

Versions, Pricing & Payment Options

Private-use (non-corporate, non-commercial license)

There are two versions of the Finance Add-in for Excel for private (ie non-corporate/non-commercial or ) use:

Trial version: Free to download but does not contain many of the functions included in the full version. The trial version has a 50 second start up delay and will work for a maximum of two minutes per session, before returning zero results for all functions. As such, it is not a usable version; it is provided as an evaluation tool for those who would like more detail on what the add-in does and how it works prior to making a purchase decision.

Full version: Private-use/non-commercial license: $AU110.00 (Australian dollars, which is approximately $US104.00) per user. This is a one-time cost; there are no on-going costs. Show estimate in your local currency.

When you buy the add-in you also get the options applications summarized here and the portfolio analysis and design tools summarized here. Everything is included in the one price.

Purchase is on-line using credit card (Visa or MasterCard), or PayPal. PayPal accepts credit cards (Visa, MasterCard, American Express, and Discover), and most debit cards. You can download immediately after payment with both payment methods. Buy now.

The above purchase price is a one time payment -- there are no on-going charges. Purchase entitles you to download the product free of charge for a period of one year (365 days) from date of purchase to take advantage of any version upgrades during the free download period. After the one year free download period has expired, you can continue using the product -- there are no additional charges.

Note: this price is for one private-use/non-corporate license as defined by Terms of Use. This means it can only be used by the individual who purchased it for his/her private trading, personal investment, personal education, or other similar private purposes undertaken under the individual's own name. Corporate or commercial use of this product, purchased under a private-use license is illegal.

Why is the private-use license so inexpensive compared with other products? Here's why.

Download of either version requires a simple registration.

Corporate/commercial license

A Corporate/commercial license is required if the add-in is to be used in any organization or business (commercial or government) or by any consultant, for any business, commercial or government-related purposes. Examples include trading, building financial models for internal or customer use, providing advisory, training, consulting or other services to clients.

Also the employee stock option functions, the HoadleyTrinomialTS function, and the HoadleyEfficientFrontier function (for greater than ten assets) are only available under a commercial license.

Corporate or commercial use of this product, purchased under a private-use license is illegal.