Portfolio Analysis & Design
|Additional Sample Spreadsheets
The Hoadley Finance Add-in for Excel includes sample sheets for each of the functions in the Add-in. Applications available from this web site, such as the Hoadley Portfolio Optimizer, also provide examples of how the functions can be used to build applications.
To illustrate some of the functions or portfolio investment/asset management subject areas in greater detail a number of additional sample spreadsheets are available for download.
These additional samples, which have been zipped into one file, can be downloaded free of charge by users of the full version of the Hoadley Finance Add-in for Excel, who are within their one year free download period.
The following additional samples are available for download:
|Risk-based portfolio asset allocation functions
diversification with respect to underlying risk factors rather than
assets. Also compares and contrasts key risk-based allocation models
(Risk Parity, Diversified Risk Parity, Most Diversified Portfolio,
Minimum Linear Torsion, Minimum Variance).
|Constant Proportion Portfolio Insurance (CPPI)
the valuation and payoff estimation for investment portfolios insured using
a CPPI strategy.
Includes a comparison of CPPI with Option Based Portfolio Insurance (OBPI), sensitivity analysis of key inputs (eg the impact of proportional transaction cost rates), and graphical analysis of individual simulation paths.
|Arithmetic mean return vs geometric mean return efficient frontiers
and reconciles standard arithmetic Mean Variance
Optimization (MVO) with geometric MVO
using an adaptation of the Hoadley Portfolio Optimizer.
|Tax-adjusted portfolio optimization
|Asset allocation & portfolio optimization functions
three common after-tax portfolio optimization scenarios: optimizing a new
taxable portfolio; optimizing an existing portfolio with embedded capital
gains tax liabilities; allocating assets efficiently across taxable, tax deferred, and tax exempt accounts.
|Tracking error efficient frontier
Tracking error and active correlation functions
Includes examples of two methods for
producing tracking error (TE) efficient frontiers: portfolio
optimization against a benchmark. Illustrates
how to gauge the efficiency of a
TE frontier by comparing it with a standard efficient
frontier on one chart, using the MSCI global sector index as an
Management: adjusting portfolio beta
|Active Management Statistics function
Illustrates how the impact on all key active management statistics
(eg tracking error, information ratio) can
be easily assessed when the beta of a portfolio is changed to a
target value using stock
index futures contracts.
For example isolating residual risk and return (alpha) as part of an "absolute return strategy" by reducing portfolio beta to zero ("portable alpha").
Black-Litterman Returns Estimator application
|Three examples which
replicate the results published in three key papers on the
Black-Litterman asset allocation model, plus an example which
compares and reconciles regression betas (and CAPM returns) with
implied betas and implied returns from reverse optimization.
|Orthogonal EWMA &
|Orthogonal EWMA & GARCH functions
|Six examples which
illustrate how volatilities and correlation matrices can be calculated with
EWMA and GARCH using the orthogonal principal component analysis
Results compared graphically to "direct" EWMA & GARCH.
|PCA and APT
|Principal Component Analysis functions
|Illustrates how Principal Component Analysis
(PCA) can be used in Arbitrage Pricing
Theory (APT) applications to construct factor (basis) portfolios, and to
construct asset-mimicking portfolios from the factor portfolios.
Value at Risk (VaR)
|Value at risk functions
illustrating: correlated simulation; analytic vs partial simulation vs full
revaluation simulation; Monte Carlo simulation vs Filtered
Historical Simulation (FHS); use of copulas for VaR; handling foreign currency exposures; cash flow
mapping; stress testing and VaR aggregation (both PCA and non-PCA
|Copula functions; VaR
illustrating the use of copulas in worksheets and in VBA modules.
Examples cover calibration, generic simulation, multi-asset option
valuation, and VaR.
|Historical Data Scrubbing
illustrating cleaning historical price data
containing gaps and price histories differing in length, without
truncating asset price histories to match the asset with the