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
||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
||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
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.
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.
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
||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.
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
||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.
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.
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.
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
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
|Interest rate derivatives
and convertible bonds
Functions available for valuing interest rate instruments (bonds, floating
rate notes, swaps, convertible bonds, & derivatives) include:
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. The Add-in also includes
a function for bond portfolio immunization (see section on
Asset Allocation and Portfolio
||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.
||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.
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.
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.
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
| Employee stock option (ESO)
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
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.
| Historical volatility, correlation, time series
analysis (cointegration etc)
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, 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.
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
||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, or from the exposures of assets to one or more
underlying systematic 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
functions uses 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.
models: Two functions for creating correlation
on the Spearman Rho correlation coefficient (HoadleyCorrelSpearman)
or the Kendall Tau correlation coefficient (HoadleyCorrelKendall).
||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.
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).
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
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.
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
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
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. .
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
| Portfolio hedging,
portfolio insurance & profitability
Functions for hedging, portfolio insurance, risk control, and for calculating
||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.
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
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.
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
spreadsheet compares CPPI with Option Based Portfolio Insurance
||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.
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
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.
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.
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
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
Correlated Monte Carlo
simulation (not using copulas): The HoadleyCorrelSim
function can be used to
undertake correlated Monte Carlo simulation of lognormally distributed prices
for two or more assets. Whilst the capabilities of this function 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
this function will achieve the same result as a Gaussian copula but
slightly more efficiently. Can be used, for example, to value options where the payoff depends
on the correlation between multiple assets.
|Value at Risk (VaR) --
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,
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
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
See VaRtools for more information on
VaRtools is only available in the full version of the Finance Add-in for Excel.
Portfolio analytics for analysing the structure, risk, style and performance of
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
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
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
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.
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
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
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"
||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
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.
Hoadley Correlation Analyzer
application. This application, which uses the
HoadleyCorrelCluster function, simplifies the analysis of
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).
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
||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 estimatel 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.
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.
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.
|Asset allocation & portfolio optimization software
Functions and components
for investment portfolio design and construction, optimization and
simulation. Functions include:
||Shrinkage estimators: 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.
||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
To simplify using the Black-Litterman functions the
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
View the Black-Litterman
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
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
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
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
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 will
estimate the weights for the Most Diversified Portfolio (Maximum
Diversification Portfolio) as described by Choueifaty and Coignard (2008);
HoadleyMVP will estimate the weights of a long-only minimum
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 a Diversified Risk Parity (DRP) portfolio, where risks are spread
evenly across underlying risk factors rather than across assets.
An additional sample
spreadsheet covering risk based asset allocation is available
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.
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
Functions for the valuation of investments
(eg shares in a company) using "industry standard" valuation
||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.
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
||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 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
||Quotes from Yahoo
(free): HoadleyYahooQuotes (a function plus toolbar and
design wizard) and HoadleyGetQuotes (a subroutine for use in
a VBA module) for on-line stock and index price 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 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) depends on Yahoo's support for each
Yahoo exchange list for a list of exchanges
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 2003 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
markets): Equity, index and option quotes. Subscription
to WebLink's BullSignal data service is required. See 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
(US and international markets): A wide range of
instruments and markets are supported by IB. Free to IB
||MoneyAM (UK markets):
Equity and index quotes. Free registration with
MoneyAM required for limited streaming quotes service;
subscription required for unlimited service.
markets): Equity, futures, index and option (equity, index
and futures options) quotes. This service, which utilizes the
OptionsXpress streamer data feed, is currently free of charge
for OptionsXpress customers/account holders.
and US markets): Equity, index and option quotes.
Subscription to Stockwatch required.
||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 chain
snapshots into spreadsheets. Exchanges currently supported include US
Exchanges using CBOE,as the provder, The Australian
Stock Exchange (individual equities and the XJO index), and Eurex
(not futures options), This data is free and delayed 15 minutes.
Live (real-time) option chains can also be retrieved from eSignal (US and international equity and
futures option chains), OptionsXpress (US equity, futures, and index
option chains), TD Ameritrade (US equity), Interactive Brokers (Equity and
futures option chains for US and
many International markets), Stockwatch
(Canada and US equity option chains), 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
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
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
Component for use in a VBA
module for downloading history under program
dataset downloads from
Quandl is a
repository of free 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. Like for Yahoo, the add-in includes
a toolbar and component for VBA modules to bring Quandl
datasets into spreadsheets.
Quandl web site
for more information. Note that the Finance Add-in for Excel
and Historic Volatility Calculator are tightly integrated
with "Quandl favourites" which can be set up on the Quandl
web site to simplify dataset selection.
||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
continue to be provided in the future 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
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.
||Three data providers (BullSignal, eSignal,
and Interactive Brokers) 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 three 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:
HoadleyRateCon for conversion of rates expressed in one compounding
frequency to another. eg monthly compounding to continuous
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.
HoadleyForwardRate to calculate the forward rate applying between two
HoadleyZeroRate to calculate the zero coupon rate implied by a forward
rate and a zero rate.
HoadleyInterpolation to estimate missing numbers in a
series (eg in a yield curve) using either linear or cubic spline
||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.
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
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.
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.
|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 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.
You can then launch the Excel function wizard for the selected function (by
pressing the "fx" symbol 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 Hoadley menu in Excel.
On-line demo of key features (5 minutes)
for the Finance Add-in for Excel.
|Versions, Pricing &
There are two versions of
the Finance Add-in for Excel for private (ie non-corporate/non-commercial or )
||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
Private-use/non-commercial license: $AU154.00 (Australian
dollars, which is approximately $US127.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
Note: this price is
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?
Download of either version
requires a simple registration.
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
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
||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
and double click the setup file to install.