

Hoadley Finance Addin for Excel
View Online 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)


BlackScholes (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 exdividend
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 exdividend 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 exdividend dates; HoadleyImpliedVolatility2 lets you
use days. Both use the NewtonRaphson method. An Implied Volatility
Calculator which will retrieve complete option chains from a number of
online data providers is included with the addin.


Percenttotarget:
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
"percenttodouble" 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 timevarying
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 Bermudanstyle options
which cannot be exercised prior to a specific date.


Pricing with timevarying
volatilities and timevarying 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
(NonLogNormal) 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 GramCharlier (similar to Edgeworth) expansion method together with an
extended BlackScholes 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 BlackScholes/Black76 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
optionsonoptions 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 Americanstyle 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 Americanstyle Asian options.


Quanto (crosscurrency)
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 predefined 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 Yendenominated 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 knockout, and gearing ratios
("doubleup"). The function can also estimate the accumulation
level required
for an accumulator or decumulator with a zero cost structure.

Interest rate derivatives
and convertible bonds 
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 exchangetraded bonds with
excoupon 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 exinterest dates are handled as well as the
grossing up of yields to take account of the impact of tax
imputation (franking) credits.


Bond portfolio targetdate 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. 

Crosscurrency swaps:
HoadleySwapIFX for the valuation of standard and forward
start (delayed start) crosscurrency 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 bondplusoption approaches to
valuation.


Bond options:
(valuation, Greeks, implied volatility) HoadleyBondOptBlk for
European coupon bond options using Black76; HoadleyBondHW
for European and American coupon bond options using the HullWhite
analytic, and trinomial interest rate tree shortrate models. Both
functions accept the zero curve as input.


Caps and floors:
(valuation, Greeks, implied volatility) HoadleyCapFloorBlk
using Black76, or
HoadleyCapFloorHW using the HullWhite shortrate 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 Black76; HoadleySwaptionHW
for European and American swaptions using the HullWhite analytic
and trinomial interest rate tree shortrate 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.

Employee stock option (ESO)
valuation functions 
Standard option pricing models cannot be used to determine the value of
employee stock options. Vesting requirements, forfeiture of unvested and OTM
options when employees leave the company, nontradability of ESOs, and other
considerations make ESO valuation more complex than standard option
valuation.
The addin contains a number of functions designed specifically for IFRS 2
and FASB 123Rcompliant ESO valuation, and to value MarketLeveraged
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. :

Unweighted and exponentially
weighted volatility: The HoadleyHistoricVolatility function
calculates the volatility of an asset based on a sample of historical prices
(closeclose, highlow, highlowclose, highlowopenclose,
or GKYZ (GarmanKlassYangZhang).
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 addin. The HoadleyGARCH function
itself is only available in the full version of the
addin.


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 FamaFrench threefactor 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 BayesStein
shrinkage model (returns and covariance) or the LedoitWolf
shrinkage model (covariance) can be used. The Jorion estimators adjust returns and
covariances using the minimum variance portfolio as the shrinkage
target. The LedoitWolf 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 semidefinite 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 (intraday, daily, weekly...) diverges from a
lognormal distribution (in terms of skewness and excess kurtosis).


Autocorrelation:
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 LjungBox test for
statistical significance.


Multiple linear
regression and polynomial linear regression: HoadleyMLR, performs multiple (or
single) linear regression. Returns coefficients, standard errors,
tstats, and RSquared. 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 EngleGranger methodology. Includes optional
automatic lag length estimation for the Augmented DickeyFuller (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 addin.


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). Anytime 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 nonlognormal 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 addin.


Monte Carlo simulation:
A class for use from a VBA module for running Monte Carlo simulations.
Can be used to generate lognormally 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 addin. 
All probabilities (apart
from Monte Carlo simulation) are calculated analytically or by trinomial tree so
their fast calculation times make them suitable for applications which need to
calculate and manipulate a large number of probabilities (eg a probability matrix
by spot and strike prices).
See FAQs for more on of probabilities
and also view the probability analysis
demo.
Portfolio hedging,
portfolio insurance & profitability 
Functions for hedging, portfolio insurance, risk control, and for calculating
trade profitability:

Position hedging  adjusting the
"Greeks": The HoadleyHedge function will calculate the adjustments
to current portfolio positions (consisting of options and their underlying
assets) required to achieve specific hedge ratio targets with respect to one
or more of a portfolio's position delta, position gamma, or position
vega. For instance, dynamic hedging by simultaneously making a
portfolio delta and vega neutral, or delta, gamma and vega neutral. You
can nominate whether the hedging adjustments should be made using options
alone, or by using the underlying plus options.


Position hedging
optimization: The HoadleyHedgeOptimize function will scan a list
of available options trades (eg from an options chain) and identify the
optimal combination of trades in the underlying and/or options required to
meet specific hedge ratio targets. Several scenarios are returned by
the function, each of which, in addition to meeting the specified hedge ratio
targets with the minimum possible number of trades, will also meet
supplementary targets. For example, maximizing the position theta of a delta
and vega neutral portfolio (so the hedger can derive maximum benefit from
time decay), or minimizing the outlay required to make a portfolio delta and
gamma neutral. The Implied Volatility Calculator contains a
hedging optimization component which uses this function.


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 payoff
diagrams, and to analyze the profitability of individual options trades,
multitrade 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 dividendpaying 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 Addin 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 crosscorrelations 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.
Value at Risk (VaR) 
"VaRtools" 
A
set of functions and components for calculating Value at Risk (VaR) and
Conditional Value at Risk (CVaR) on multiasset portfolios containing both linear
(eg stocks, futures, FOREX exposures) and nonlinear 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 nonlinear instruments like
options.
Other features and tools include cash flow mapping for bonds and other
interest rate assets, multiasset portfolio volatility calculation, functions for
calculating the Beta and RSquared 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 FamaFrench;
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 riskadjusted 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 factorspecific 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
MinimumLinear 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 longonly 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. 

PCAbased 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 subportfolios
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 RSquared 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 RSquared:
HoadleyBeta and HoadleyRSquared will calculate beta and
rsquared for individual assets and and for portfolios of assets using
a history of asset closing prices. Both beta and rsquared 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 MeanSemivariance
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, RSquared, the Sharpe ratio, information ratios (residual and active)
and MSquared (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 (Mcubed) 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 userspecified
target TE. The objective is to enable all active portfolio
management statistics to be compared on a riskadjusted 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 returnsbased 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 Excelbased 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 rearranges the matrix into
clusters. The function also returns a stepbystep 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 nonclustered assets.
Returns/price data is not required for this function.
This powerful utility can be used to support a subportfolio
approach to portfolio optimization where asset weights are, firstly,
allocated within clusters using, eg RiskParity, then secondly across
clusters using the same or a different approach (eg MVO).
Other subportfolio 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 subportfolios 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
subportfolio 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:

BlackLitterman asset allocation
model: Six functions providing a full
implementation of the BlackLitterman Bayesian asset
allocation model for portfolio design.
The starting point for the BlackLitterman model is a market equilibrium
portfolio. The functions will backout 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 marketneutral 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
BlackLitterman 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 BlackLitterman model (Idzorek's "A StepByStep Guide to the
BlackLitterman model" (2004), and He and Litterman's "The Intuition
Behind BlackLitterman Model Portfolios", plus two other papers) is
available for download with the full version of the Finance Addin for
Excel.
To simplify using the BlackLitterman functions the
Hoadley BlackLitterman
Returns Estimator application is included with the full version
of the Addin. This application, which uses the BlackLitterman
functions as the calculation engine, provides a convenient way of
implementing the BlackLitterman model without the need to use the
functions directly.
View the BlackLitterman tutorial
BlackLitterman licensing: When purchased under a
private/personal license the number of assets that can be handled by the
BlackLitterman 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 meanvariance
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
Excelbased 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.


Riskbased asset allocation:
Increasingly seen as an alternative to
meanvariance optimization in some situations,
the objective of riskbased
portfolio construction is to maximise the
diversification of risk.
The Hoadley Finance Addin 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 longonly 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
MinimumLinearTorsion (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 longonly 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:
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 index tracking portfolio.


Taxadjusted portfolio optimization: The impact
of tax on asset allocation is significant for personal investors.
Two functions are included to convert pretax expected returns, volatilities
and market values to their aftertax 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 taxadjusted 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 taxexempt 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
twostage model  an initial stage of unusually high or low
growth, followed by a long term "steady state" stable stage.
HoadleyValuationDD3 implements a threestage 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 stablestage 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 twostage version
of this model; HoadleyValuationFCFE3 implements a threestage 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 twostage version of this model;
HoadleyValuationFCFF3 implements a threestage 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 twostage dividend discount model. 

Option Pricing Method (OPM) for equity valuation in complex capital
structures: A template application for the
allocation of value for venture capitalbacked and
private equitybacked 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
Addin for Excel, is suitable for the
preparation of US IRS section 409A valuations. 

Implied growth:
Given longterm (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 twostage 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 addin functions
for business valuation rather than template spreadsheets include ease of incorporating functions into userdesigned
spreadsheets, presenting valuations of multiple companies
sidebyside 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 addin includes several components for retrieving online
price histories, quotes and
option chains:

Streaming and snapshot quotes from Yahoo
Finance (free): HoadleyYahooQuotes (a function
plus toolbar and design wizard) for online 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, realtime, streaming or snapshot only) depends on Yahoo's support for each
exchange. eg US stocks are realtime streaming. See
Yahoo exchange list
for a list of exchanges covered and the suffixes to use with stock
symbols when requesting quotes.


Streaming
realtime quotes from brokers or by subscription: The Finance Addin 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 addin (but note that the addin 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.


eSignal (US and
international markets): A wide range of instruments
(including futures options) and markets are supported by
eSignal. Subscription to eSignal required. More details on instruments and
markets.


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 and option (equity, index
and futures options) quotes are available (currently only for
customers who migrated from OptionsXpress). This service, is currently free of charge
for Schwab customers/account holders. Quotes are realtime
snapshot with optional autorefresh.
More details.


TD Ameritrade (US
markets): Equity, index and option quotes. Free to TD
Ameritrade customers. 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 online option chain
snapshots into spreadsheets. Exchanges currently supported include US
Exchanges using Yahoo as the provider, The Australian
Securities Exchange (ASX), National Stock Exchange of India (NSE), and Eurex
(not futures options), Free option chains are delayed.
Live (realtime) option chains can also be retrieved from eSignal (US and international equity and
futures option chains, including crypto futures option chains traded
on CME), Charles
Schwab (for ex OptionsXpress customers only: US equity, futures, and index
option chains), TD Ameritrade (US equity), Interactive Brokers (Equity and
futures option chains for US and
many International markets), Tradier
Brokerage (US equity option chains), and BullSignal (Australia).
Realtime 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 addin. 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 addin 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 addin 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 subscriptionbased 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, FamaFrench factors, and much more. The addin 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 addin depend on being able to
retrieve online data using the above data components. All addin
functions, like Excel's own functions, are completely independent of any data
source. The main purpose of the online data
components in the addin is to provide simpletouse 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 online 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 addin. 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 addin will continue to provide an
interface to these providers.


Two data providers (BullSignal,
and eSignal) require
32bit Excel and cannot be used with 64bit Excel. This is
because the application programming interface (API) software
supplied by these providers is 32bit.
These two providers can be used under 64bit Windows, but Excel 32bit
must be installed. All other streaming quotes and option chain
providers will run under both 32bit and 64bit Excel.
System requirement details.

The addin 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 viceversa,
(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 exdividend 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 nontrading days, to a form suitable for the calculation of beta,
rsquared, 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.

Context Sensitive Help
& Working Examples 
The addin 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 addin 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 addin 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.
Online demo of key features (5 minutes)
The Finance Addin for Excel requires Microsoft Excel (32bit
or 64bit) running under Microsoft Windows. For detailed
systems requirements, including supported versions of Windows and Excel
see systems requirements.
Versions, Pricing &
Payment Options 
Private use
Corporate/commercial use
Privateuse (noncorporate,
noncommercial license) 
There are two versions of
the Finance Addin for Excel for private (ie noncorporate/noncommercial/ nonbusinessrelated)
use:

Trial version: Free to
download, includes full documentation and samples, but many of the functions
are nonoperational. 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 addin does and how it works prior to making a purchase
decision.


Full version:
Privateuse/noncommercial license: $AU176.00 (Australian
dollars, which is approximately $US116.00) per user. This is a
onetime cost; there are no ongoing costs. Show estimate in your local currency.
What's included? When you buy the Finance Addin
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 online 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 ongoing 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 privateuse/noncorporate 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
privateuse license is illegal.
Why is the
privateuse 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.
Corporate/commercial
license 
A Corporate/commercial
license is required if the addin is to be used in any organization or business
(commercial or government) or by any consultant, for any business, commercial or
governmentrelated 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 privateuse license is
illegal.
Contact Peter Hoadley for
corporate/commercial license enquiries.
Before you download the
addin file note that:

By downloading the Excel addin you
signify your assent to these Terms of
Use. In particular note that the addin purchased online is for
your private/noncorporate, noncommercial use only. (Contact Peter
Hoadley for corporate/commercial license
enquiries.)


By purchasing a privateuse license
you signify that you have read and understood the feature set covered by this
license as defined on this Licensing Summary page.


The addin is made available to
experienced Microsoft Excel users who have a good understanding of how
to locate and install addins on their PCs running Microsoft Windows, and how
to use addin functions in Excel spreadsheets.


No guarantee can be given that free
online stock or other price data, or free online 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 addin,
and double click the setup file to install.
