Up

Software environment
 

Pricing & payment options
 

Buy now
 

Download
 

On-line demos
 

Latest version
 

FAQs

Hoadley Finance Add-in for Excel

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.
 

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

	Options (or warrants) on equities, currencies (FOREX), 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.  The function also optionally provides for a term structure of risky rates, which are used for discounting the option payoff, to be specified separately from the risk free rates -- for instance, to model counterparty risk for OTC options -- and allows borrowing and other costs to be specified together with discrete dividends. 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).
	SABR stochastic volatility model:  Three functions which implement the SABR stochastic volatility model for European spot and futures options.  HoadleySABRBlackVol returns the Black-Scholes/Black-76 equivalent volatility; HoadleySABROption calculates option values and Greeks using the SABR model; HoadleySABRCalibrate calibrates the SABR parameters with market data: the volatility smile (skew) from the strikes and implied volatilities of traded 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.

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  (the HoadleyBarrier1 function)  and double barrier options (HoadleyBarrier2). Handles discrete barrier monitoring and rebates. Dividends can be specified as an annual yield or as an unlimited number of discrete payments.
	Parisian option barrier adjustment:  HoadleyParisianBarrier to enable the valuation of single barrier options or employee stock options with a single performance target where the underlying must remain above (or below) the barrier for a specified number of consecutive days.
	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.
	Delayed start options (DSOs):  HoadleyDelayedStart calculates the the value and Greeks for European and American delayed start (forward start) options -- options which are valued and paid for 'today' but issued at some time in the future. The strike of the option is set at the future issue date. The function can be used to value options on stocks (with discrete dividends or dividend yields), indices, futures and currencies. The options are often used for volatility trading/volatility hedging.
	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.
	Compound options: HoadleyCompoundOption for valuing European and American options-on-options using a binomial tree. Options can be European on European, European on American, American on European or American on American.  The function handles dividends expressed as a yield or as a schedule of discrete payments.
	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.
	Accumulator pricing: HoadleyAccumulator calculates the fair value and Greeks (Delta, Gamma, Vega) of an accumulator or decumulator contract (also knows as forward accumulator or "I kill you later" contracts).  Increasingly popular in commodity markets, the accumulator pricing function handles both accumulator (for buyers) and decumulator (for sellers) contracts. Features include delayed settlement, discrete barrier monitoring, guarantee periods, guaranteed pricing levels on knock-out, and gearing ratios ("double-up"). The function can also estimate the accumulation level required for an accumulator or decumulator with a zero cost structure.

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

	Bond valuation:  HoadleyBond for the valuation (clean & dirty), yield to maturity, duration, modified duration, and convexity of coupon bonds. HoadleyBond accepts a term structure of interest rates ("zero coupon yield curve") as input. Handles exchange-traded bonds 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.
	Bond portfolio target-date immunization: The HoadleyBondImmunize function immunizes a portfolio of bonds against interest rate fluctuations while maximizing the portfolio yield to maturity.  Weight constraints can be specified for individual bonds, and for groups of bonds. For example, group constraints can be used to set a maximum holding for junk bonds in a portfolio, and/or a minimum holding for government bonds.
	Interest rate swaps:  HoadleySwapIR for the valuation of standard and forward start (delayed start) interest rate swaps. The function will calculate the value of the swap, and the value of the fixed and floating legs, for a given swap rate, or will calculate the swap rate for a fairly valued swap. Valuation can be at inception or at any time after commencement of the swap.
	Cross-currency swaps:  HoadleySwapIFX for the valuation of standard and forward start (delayed start) cross-currency swaps. All combinations of receive/pay and fixed/floating legs handled.  Valuation can be at inception or at any time after commencement of the swap. Exchange of principal amounts at the start of the swap can be included or excluded from the valuation.
	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.
	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 swaption 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.

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, and to value Market-Leveraged Stock Units (MSUs).  A corporate/commercial license is required to use the ESO functions. See ESO functions for more details.
 

Functions for analyzing volatility, correlation, price distributions, testing for cointegration etc. --:

	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,  high-low-open-close, or GKYZ (Garman-Klass-Yang-Zhang). 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 or returns data, or from the exposures of assets to one or more underlying factors (eg beta from the CAPM, or beta, LMS, and HML from the Fama-French three-factor model).. 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, HoadleyPricesStambaugh, and HoadleyReturnsStambaugh functions use the Stambaugh (1997) method to calculate the volatilities, correlation matrix, and geometric returns, or a set of complete synthetic historical prices, for assets where price histories have the same end date but different start dates. Instead of the common but unsatisfactory approach of truncating price histories to match the asset with the shortest history -- and therefore discarding valuable information --  the full history of all assets is utilized with the Stambaugh methodology.
	Rank correlation models:  Two functions for creating correlation matrices based on the Spearman Rho correlation coefficient (HoadleyCorrelSpearman) or the Kendall Tau correlation coefficient (HoadleyCorrelKendall).
	Shrinkage estimators for returns, volatility, correlation: Three functions for improving the estimation of expected returns, and correlations and volatilities (covariance) from historical data using bayesian  shrinkage estimators. The Jorion Bayes-Stein shrinkage model (returns and covariance) or the Ledoit-Wolf shrinkage model (covariance) can be used. The Jorion estimators adjust returns and covariances using the minimum variance portfolio as the shrinkage target. The Ledoit-Wolf estimator uses a target based on the average correlations across asset pairs.
	Orthogonal EWMA (OEWMA) and Orthogonal GARCH (OGARCH):  Two functions to enable the preparation of covariance and 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, among other things, 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.
	Multiple linear regression and polynomial linear regression: HoadleyMLR, performs multiple (or single) linear regression. Returns coefficients, standard errors, t-stats, and R-Squared.  Similar to Excel's LINEST function but output is more logically arranged and function is much faster.  HoadleyPLR performs polynomial linear regressions.
	Principal Component Regression (PCR):.  PCR is a method of dealing with multicollinearity -- highly correlated explanatory variables in a regression. Multicollinearity, which is a common problem with fundamental factor models, may cause the results from regressions to be highly unstable. The HoadleyMLRCheck function checks for multicolloinearity in data using two standard indicators (The Variance Inflation Factor (VIF) and condition numbers).  The HoadleyPCR function performs a multiple linear regression using principal component analysis to minimize the impact of Multicollinearity.
	Cointegration testing: HoadleyEngleGranger tests multivariate time series for cointegration using the Engle-Granger methodology. Includes optional automatic lag length estimation for the Augmented Dickey-Fuller (ADF) test. Returns comprehensive statistics to aid interpretation of results.

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

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.
 

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.
	Option Based Portfolio Insurance (OBPI): HoadleyOBPI will calculate the strike and number of index put options required to insure an investment 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.  Beta weighting is therefore used to ensure that these differences are taken into account.
	Constant Proportion Portfolio Insurance (CPPI):  Two functions (HoadleyCPPI and HoadleyCPPIOnePath) for the valuation, payoff at expiry, and analysis of investment portfolios insured using the CPPI methodology. Handles rebalancing at discrete time periods, proportional transaction costs, and evaluation of "gap risk". An additional samples spreadsheet compares CPPI with Option Based Portfolio Insurance (OBPI).
	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: 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.
 

Copulas and simulation:  Copulas provide a powerful and flexible model for simulating financial returns for multiple assets where individual asset return distributions are specified independently of the dependence structure which exist between the assets.

A key feature of copulas is that they obviate the need to assume normally distributed returns and a relationship between assets based on simple linear correlation.

The Finance Add-in for Excel includes a comprehensive set of functions for the calibration of copulas using historical data and for simulating financial data.  See copulas and simulation for more information.

Correlated Monte Carlo simulation (not using copulas): The HoadleyCorrelSim and HoadleySimSingleIndex functions can be used to undertake correlated Monte Carlo simulations of lognormally distributed prices for two or more assets. The HoadleyCorrelSim function uses a full correlation matrix; the HoadleySimSingleIndex function is based on the "single Index" model where asset betas are used to infer cross-correlations instead of directly using a correlation matrix. It is suitable for the simulation of very large portfolios.

Whilst the capabilities of these functions can be exactly replicated using a Gaussian copula together with lognormally distributed marginals (asset prices), in cases where a normal distribution of returns and simple linear correlation can be assumed these function will achieve the same result as a Gaussian copula but more efficiently.  Can be used, for example, to value options where the payoff depends on the correlation between multiple assets or to simulate future return distributions for investment portfolios.

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

VaR and CVaR (also known as Expected Shortfall) 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.

A set of fundamental, macroeconomic and statistical factors analysis functions for analysing returns, portfolio risk, performing asset allocation etc.   Functions include:

	Correlation matrices from factors: HoadleyCorrSingleFactor estimates a correlation matrix from asset exposures to a single risk factor (eg market beta); HoadleyCorrMultiFactors estimates a correlation matrix from asset exposures to multiple factors (eg Fama-French; macroeconomic factors).
	Portfolio systematic risk: HoadleyRiskSingleFactor calculates the systematic risk of a portfolio of assets exposed to a single risk factor.  HoadleyRiskMultiFactor calculates the systematic risk of a portfolio of assets exposed to multiple risk factors..

Factor risk attribution: The HoadleyFactorRiskAttribution function will analyze the contribution of risk for multiple factors to portfolio systematic risk.  Uses include the calculation of the modified Treynor ratio for each factor and for the overall portfolio for the anlysis of portfolio performance on a risk-adjusted basis. See also HoadleyRiskAttribution for asset risk attribution.

Principal Component Analysis (PCA): A set of functions for undertaking Principal Component Analysis on a portfolio or yield curve, using either price/returns/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 estimate covariance and correlation matrices using all or a reduced number of the principal components, and to calculate the factor scores of the principal components themselves.

	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 risk diversification with principal components: HoadleyPCARisk will calculate the degree to which a portfolio is diversified across underlying principal component risk factors -- the "diversification index" as describe by Attilio Meucci -- and will estimate the weights of the lowest volatility Diversified Risk Parity (DRP) portfolio, where risks are spread evenly across underlying risk factors rather than across assets. HoadleyMinTorsionRisk implements the Meucci Minimum-Linear Torsion (MLT) model which, like the principal component model, measures diversification using a set of uncorrelated (orthogonal) risk factors.  However the orthogonal factors are designed to retain a more direct relationship to the original assets or factors than under the principal component model. The HoadleyMinTorsionParity function will calculate the weights of a long-only portfolio which spreads risk evenly across MLT factors. An additional sample spreadsheet covering risk based asset analysis and allocation using PCA is available for download.
	Portfolio optimization with factor targets and constraints: Functions and and Mean Variance Optimizer application (Portfolio Optimizer) which allow the construction of portfolios targeting exposure to specific combinations of risk factors. More details.
	PCA-based portfolio aggregation:  A function to  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 HoadleyPCAPortAggregate aggregates a portfolio using 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 analytics for analysing the structure, risk, style and performance of investment portfolios. 

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 portfolio analytics 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.
	Tracking error & active correlation: HoadleyTracking Error will calculate the tracking error (TE) of a portfolio using a history of asset closing prices.  HoadleyActiveCorrel will calculate the correlation of active returns.  Both can be calculated using the equally weighted model or the EWMA model.
	Downside risk measures:  HoadleyDownsideDeviation will calculate downside deviation, and HoadleyDownsideCorrel will calculate the downside correlation matrix for two or more assets.  The methodology used is based on the paper by Javier Estrada  Mean-Semivariance Optimization: A Heuristic Approach.  Using this methodology, the combination of downside deviation and correlation -- ie semicovariance -- can be used in standard Markowitz mean variance optimizers, like the Hoadley Portfolio Optimizer, to perform portfolio Downside Risk Optimzation (DRO) without the need for specialized software
	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.
	Portfolio return: HoadleyPortfolioReturnFX will calculate the expected return for a portfolio consisting of a combination of domestic and foreign assets with foreign currency exposures, some of which may be hedged.
	Active Portfolio Management Statistics: HoadleyPortfolioStats decomposes return and risk/tracking error into residual (alpha), active, and market; calculates beta, R-Squared, the Sharpe ratio, information ratios (residual and active) and M-Squared (a measure of risk adjusted return).  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.
	Risk adjusted portfolio analysis using the M3 (M-cubed) methodology:  HoadleyM3 calculates the proportions of an active portfolio, a passive portfolio (benchmark) and a riskless asset (cash) required to achieve a combined portfolio volatility equal to the benchmark volatility and a tracking error (TE) equal to a user-specified target TE. The objective is to enable all active portfolio management statistics to be compared on a risk-adjusted basis, and to provide some guidance for portfolio construction.
	Risk attribution:  The HoadleyRiskAttribution  function (domestic assets only) and the HoadleyRiskAttributionFX function (mixture of domestic assets assets with foreign currency exposures) will analyze the contribution of assets, or groups of assets (eg industry, country, style...) to overall portfolio volatility.  The risk attribution is expressed in terms of contribution, marginal contribution, and percent contribution to portfolio risk. See also HoadleyFactorRiskAttribution for factor risk attribution.
	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), and ten assets.  To handle an unlimited number of returns and assets a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for approved 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.
	Performance measures based on Lower Partial Moments (LPM): HoadleyPerformanceLPM will calculate a number of LPM measures (Omega, Downside Deviation, Sortino Ratio, Kappa 3 and Upside Potential Ratio) from the past returns of a fund or portfolio. LPM performance measures consider only negative deviations from a reference point when calculating risk, unlike variance which treats positive and negative deviations equally. LPM measures therefore reflect the common view of risk as something undesirable.
	Correlation clustering: HoadleyCorrelCluster performs a hierarchical cluster analysis on a correlation matrix, and re-arranges the matrix into clusters.  The function also returns a step-by-step breakdown of the hierarchical clustering process to help with interpretation.  Cluster analysis can be used to help identify groupings of assets or funds such as those based on sectors, growth/value, instrument type, manager style etc. and to identify groups of assets which provide a required level of portfolio diversification. See also:  Hoadley Correlation Analyzer application.  This application, which uses the HoadleyCorrelCluster function, simplifies the analysis of correlation matrices.
	Correlation matrix asset/sector merging:  HoadleyCorrelMergeAssets will physically reduce the dimensions of a correlation matrix by merging assets, or sectors, into clusters. The clustered correlation matrix correctly reflects the correlation of each cluster to all other clusters and non-clustered assets.  Returns/price data is not required for this function. This powerful utility can be used to support a sub-portfolio approach to portfolio optimization where asset weights are, firstly, allocated within clusters using, eg Risk-Parity, then secondly across clusters using the same or a different approach (eg MVO). Other sub-portfolio aggregation requirements can be similarly handled.  eg  aggregating Value at Risk (VaR).
	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 and in the Hoadley Factor Analyzer applications.
	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.

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

Black-Litterman asset allocation model: Six 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 a market equilibrium portfolio. The functions will back-out implied asset class or sector returns from market cap or portfolio weights by reverse optimization.  Two methods are provided: the returns for all asset classes can be inferred from an estimate of the overall market return (either with or without foreign currency exposures), or, alternatively from an estimate of the return for a single asset class in the portfolio. The implied returns represent a market-neutral reference point -- a set of expected equilibrium returns.  One or more returns may then be modified, to reflect user views which differ from those implied by the market, by applying absolute and relative views or tilts, with specified levels of confidence.  An optimal unconstrained portfolio based on these views is 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 four 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", plus two other papers)  is available for download with the full version of the Finance Add-in for Excel. To simplify using the Black-Litterman functions the Hoadley Black-Litterman Returns Estimator application is included with the full version of the Add-in.  This application, which uses the Black-Litterman functions as the calculation engine, provides a convenient way of implementing the Black-Litterman model without the need to use the functions directly. 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 approved private investors ("Pro" licensing enquiries).

Portfolio optimizer/efficient frontier:  Two functions for performing mean-variance optimization on a portfolio of assets.  The functions (HoadleyEfficientFrontier and HoadleyMVOTarget) implement the Markowitz critical line algorithm.  The HoadleyEfficientFrontier function produces 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. The HoadleyMVOTarget function also uses the critical line algorithm and is used to produce the lowest risk portfolio for a single target return, or the highest return portfolio for a single target volatility. 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., and/or to constrain the portfolio's exposure to one or more risk factors (eg to market beta).  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 functions will return the weights by constraint (eg by industry or risk factor) as well as by individual asset.  Portfolio optimization licensing: When purchased under a private/personal license the number of assets that can be optimized is capped at ten for both the HoadleyEfficientFrontier and the HoadleyMVOTarget functions. .  To optimize more assets using these functions, and using the Portfolio Optimizer Pro, a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for approved 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.
	Risk-based asset allocation:    Increasingly seen as an alternative to mean-variance optimization in some situations, the objective of risk-based portfolio construction is to maximise the diversification of risk.  The Hoadley Finance Add-in for Excel includes a number of functions for risk based allocation:  HoadleyRiskParity, will estimate the portfolio weights required to equalize the contribution of each asset or asset class to overall portfolio volatility (Equal Risk Contribution (ECR) portfolios); HoadleyMDP (Most Diversified Portfolio) will estimate the weights for the portfolio with the highest diversification ( where diversification is defined as the average weighted volatility across all assets divided by portfolio volatility);  HoadleyMVP will estimate the weights of a long-only minimum variance portfolio; HoadleyPCARisk will calculate the degree to which a portfolio is diversified across underlying principal component risk factors -- the "diversification index" as describe by Attilio Meucci -- and will estimate the weights of the lowest volatility Diversified Risk Parity (DRP) portfolio, where risks are spread evenly across underlying risk factors rather than across assets. HoadleyMinTorsionRisk implements the Meucci Minimum-LinearTorsion (MLT) model which, like the principal component model, measures diversification using a set of uncorrelated (orthogonal) risk factors.  However the orthogonal factors are designed to retain a more direct relationship to the original assets or factors than under the principal component model. The HoadleyMinTorsionParity function will calculate the weights of a long-only portfolio which spreads risk evenly across the MLT factors. An additional sample spreadsheet covering risk based asset allocation is available for download.
	Partial index replication ("Direct Indexing"):  The HoadleyIndexTrack function will find a subset of assets, from a larger candidate list, which closely tracks -- ie replicates the performance of --  an index or ETF as measured by portfolio tracking error.  Cardinality constraints can be specified to limit the number of assets in the portfolio to a subset of potential assets.  Minimum and maximum weights can also optionally be specified for assets to ensure specific assets are included in the tracking portfolio and/or to limit their weights.  An Index tracking portfolio builder application -- Partial Index Replicator-- is also available for download.  The application can simplify the construction of an optimized index tracking portfolio as the foundation of a "direct indexing" investment strategy.
	Tax-adjusted portfolio optimization:  The impact of tax on asset allocation is significant for personal investors.   Two functions are included to convert pre-tax expected returns, volatilities and market values to their after-tax equivalents for use with the HoadleyEfficientFrontier function or the Hoadley Portfolio Optimizer.  The HoadleyAfterTaxTaxable function is used for taxable investments/accounts;  The HoadleyAfterTaxTaxDef function returns after tax values for investments in a tax deferred account (such as a 401(k) US retirement account).  An additional sample spreadsheet is available which illustrates tax-adjusted portfolio optimization for a new portfolio, for an existing portfolio (with embedded tax liabilities), and for the allocation of assets across taxable, tax deferred, and tax-exempt accounts.
	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.

Functions for company valuation and allocation of equity value using "industry standard" valuation models: 

	Dividend discount (DD) models: Two functions for the valuation of equity using the dividend discounted cash flow model.  HoadleyValuationDD2 implements a two-stage model -- an initial stage of unusually high or low growth, followed by a long term "steady state" stable stage.  HoadleyValuationDD3 implements a three-stage model consisting of a relatively high or low growth initial stage, a transition stage, and a long term stable stage. During the transition stage growth rates, payout ratios and other measures from the initial stage transition in a linear fashion to their stable-stage equivalents.
	Free cash flow to equity (FCFE) discount models: Two functions for the valuation of equity using the FCFE model. This model is often used to value companies which have a relatively low dividend payout ratio and which are retaining more cash than they can afford to return to shareholders.  HoadleyValuationFCFE2 implements a two-stage version of this model; HoadleyValuationFCFE3 implements a three-stage version.
	Free cash flow to the firm (FCFF) discount models: Two functions for the valuation of a firm (debt + equity) using the FCFF model.  With the FCFF model future cash flows are discounted using the weighted average cost of capital (WACC), rather than the cost of equity which is used in the DD and FCFE models.  The FCFF model is particularly well suited to the valuation of companies with significant leverage, and/or leverage which is expected to change over time.  HoadleyValuationFCFF2 implements a two-stage version of this model; HoadleyValuationFCFF3 implements a three-stage version.
	Implied Equity Risk Premium (ERP): A function, HoadleyImpliedERPDD2, to calculate the Equity Risk Premium implied by a market index.  The underlying valuation model used is the two-stage dividend discount model.
	Option Pricing Method (OPM) for equity valuation in complex capital structures: A template application for the allocation of value for venture capital-backed and private equity-backed companies across all equity classes (preferred stock, convertible debt, common stock etc) using the OPM.  Includes a backsolve function to derive the implied equity value for a company from a recent transaction in the company's own securities (typically preferred stock), and will estimate discounts for lack of marketability (DLOM) using three methods: protective put, Asian protective put, and differential put.  The OPM template application, which is available on request to corporate users of the Hoadley Finance Add-in for Excel, is suitable for the preparation of US IRS section 409A valuations.
	Implied growth:  Given long-term (stable) estimates of growth, payout ratios and interest rates etc.  the HoadleyImpliedGrowthDD function will calculate the short term growth rate implied by current valuations. Useful for assessing the reasonableness of current market valuations and/or analysts' estimates of growth.  Can be applied to either individual firms or market indices. The underlying valuation model used is the two-stage dividend discount model.
	Adjusting betas for leverage: The HoadleyReleverBeta  function adjusts a firm's equity beta to take account of an expected change in debt levels (leverage) in the future. The relevered beta could then be used to calculate the future weighted average cost of capital (WACC) for firm valuation purposes. The function can also be used to calculate a firm's unlevered beta.

The advantages of using add-in functions for business valuation rather than template spreadsheets include ease of incorporating functions into user-designed spreadsheets, presenting valuations of multiple companies side-by-side on a single sheet for comparative purposes, and performing sensitivity analysis on the key inputs: generating multiple "what if" scenarios in tables and charts for companies using different sets of assumptions for growth, beta, interest rates, payout ratios, duration of each stage (growth, transition) etc.
 

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

	Streaming and snapshot quotes from Yahoo Finance (free):  HoadleyYahooQuotes (a function plus toolbar and design wizard) for on-line stock, ETF, US options, index, futures and currency quotes  The HoadleyYahooQuotes function will update quotes manually (by clicking a button on the  toolbar) or automatically at a refresh frequency which can be set by the user. The HoadleyYHStreamQuotes function provides streaming quotes for most 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 (instrument types, delayed, real-time, streaming or snapshot only) depends on Yahoo's support for each exchange.  eg US stocks are real-time streaming.  See Yahoo exchange list for a list of exchanges covered and the suffixes to use with stock symbols when requesting quotes.
	Streaming real-time quotes from brokers or by subscription: The Finance Add-in includes functions plus design wizards to deliver streaming (ie dynamic, without the need to manually refresh) into Excel spreadsheet 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):   BullSignal (Australian markets): Equity, index and option quotes.  Subscription to WebLink's BullSignal data service is required.  See More details.   Interactive Brokers (US and international markets):  A wide range of instruments and markets are supported by IB and are available to IB customers.  More details   Charles Schwab (US markets): Equity, futures, index, Forex, and option quotes are available.    This service, is currently free of charge for Schwab customers/account holders. Quotes are real-time streaming, or snapshot with optional auto-refresh.   More details.   Tradier Brokerage (US markets): Equity, index and option quotes.Free to Tradier Brokerage customers.  More detail.
	Option Chains:  A component for use in a VBA module for retrieving entire on-line equity option chain snapshots into spreadsheets. Exchanges currently supported include US Exchanges using Yahoo as the provider, Montreal Exchange, The Australian Securities Exchange (ASX), National Stock Exchange of India (NSE), Euronext (equity & index options), Eurex  (not futures options),  Free option chains are delayed except for Canadian options on the Montreal Exchange which are real-time.  Live (real-time) option chains can also be retrieved from  Charles Schwab : (US equity, and index option chains), Interactive Brokers (Equity and futures option chains for US and many International markets), Tradier Brokerage (US equity option chains), and  BullSignal (Australia). Real-time Crypto option chains on cryptocurrency futures traded on the Deribit Crypto Options and Futures Exchange, and spot option chains traded on Binance, Bit.com, OKX and Delta exchanges are also available free of charge for several of the most popular cryptocurrencies (Bitcoin, Ethereum, etc). 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 downloads 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. See Yahoo exchange list for a list of exchanges covered and the suffixes to use with stock symbols when requesting historical data
	Historical dataset downloads from Quandl: Quandl is a repository of free and subscription-based historical data covering a large number of subject areas:  futures (including continuous futures contracts), currencies, interest rates (including constant maturity time series data), commodities, world equity indices, Fama-French factors, and much more.  The add-in includes a toolbar and component for VBA modules to bring Quandl datasets into spreadsheets. Visit the Quandl web site for more information.

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, subscription, or broker price data, or option chain data, will continue to be provided in the future by any of the information providers currently used by the add-in. No guarantee can be given 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 though any of the data functions will continue to be available, and will  be of of high quality, from any of the currently supported information providers in the future.  No guarantee can be given that providers will not change the format of their data in the future, or that following any change to data formats the add-in will continue to provide an interface to these providers.
	Two data providers (BullSignal, and eSignal) require 32-bit Excel and cannot be used with 64-bit Excel.  This is because the application programming interface (API) software supplied by these providers is 32-bit.  These two providers can be used under 64-bit Windows, but Excel 32-bit must be installed.  All other streaming quotes and option chain providers will run under both 32-bit and 64-bit Excel. System requirement details.

The add-in contains a number utility functions to perform common tasks:

	Rate conversions:  HoadleyRateCon for conversion of rates expressed in one compounding frequency to another.  eg monthly compounding to continuous compounding.
	Real/nominal conversions: Five utility functions to convert nominal rates and risk premiums to their real equivalents and vice-versa, (eg for investment valuation) and to calculate the inflation rate implied by real and nominal rates.
	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.
	Interpolation:  HoadleyInterpolation to estimate missing numbers in a series (eg in a yield curve) using either linear or cubic spline interpolation.
	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.
	Correlation/covariance matrix integrity checking and correction: Two functions to check the integrity of correlation or covariance matrices (ie that they are positive definite or positive semi definite) and a function to make minor changes to invalid matrices to ensure they are internally consistent.
	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.
	Correlation matrix reordering:  Three functions to simplify changing the order in which assets/sectors appear in correlation matrices. eg to manually restructure a correlation matrix to group similar clusters of assets together.

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 Hoadley menu which is inserted into the main Excel menu or ribbon bar (Excel 2007 and above) 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 Hoadley menu.which will automatically launch the Excel function wizard.  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 Hoadley menu in Excel.

On-line demo of key features (5 minutes)
 

The Finance Add-in for Excel requires Microsoft Excel (32-bit or 64-bit) running under Microsoft Windows.  For detailed systems requirements, including supported versions of Windows and Excel see systems requirements.
 

Private use

Corporate/commercial use

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

Trial version: Free to download, includes full documentation and samples, but many of the functions are non-operational.  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:  $AU176.00 (Australian dollars, which is approximately $US120.00) per user. This is a one-time cost; there are no on-going costs.  Show estimate in your local currency. What's included? When you buy the Finance Add-in for Excel 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.  Use of this product for any business purpose when purchased under a private-use license is illegal. 

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

Does one license allow me to install the software on all my PCs?  Yes!

Download of either version requires a simple registration.
 

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 / commercial/business use of this product, purchased under a private-use license is illegal. 

Contact Peter Hoadley for corporate/commercial license enquiries.
 

Before you download the add-in file note that:

	By downloading the Excel add-in you signify your assent to these Terms of Use.  In particular note that the add-in purchased on-line is for your private/non-corporate, non-commercial use only. (Contact Peter Hoadley for corporate/commercial license enquiries.)
	By purchasing a private-use license you signify that you have read and understood the feature set covered by this license as defined on this Licensing Summary page.
	The add-in is made available to experienced Microsoft Excel users who have a good understanding of how to locate and install add-ins on their PCs running Microsoft Windows, and how to use add-in functions in Excel spreadsheets.
	No guarantee can be given that free on-line stock or other price data, or free on-line option chain data will be available in the future or that any of the information providers currently used will not change their data formats in the future without notice.

If you agree to these terms of use then download/purchase the add-in, and double click the setup  file to install.