

Portfolio Analysis & Design

Additional Sample Spreadsheets 
The Hoadley Finance Addin for Excel includes sample sheets for each of the functions in the Addin. 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 Addin for Excel, who are within their one year free download period.
The following additional samples are available for download:
Sample  Functions Illustrated  Comments 
Risk Diversification  Riskbased portfolio asset allocation functions  Analysing risk
diversification with respect to underlying risk factors rather than
assets. 
Constant Proportion Portfolio Insurance (CPPI)  CPPI functions  Examples illustrating
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  Portfolio Optimizer  Compares, contrasts
and reconciles standard arithmetic Mean Variance
Optimization (MVO) with geometric MVO
using an adaptation of the Hoadley Portfolio Optimizer. 
Taxadjusted portfolio optimization  Asset allocation & portfolio optimization functions  Examples illustrating
three common aftertax 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 
Efficient frontier
function 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
example. 
Active Portfolio
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"). 
BlackLitterman 
BlackLitterman
functions BlackLitterman Returns Estimator application 
Three examples which
replicate the results published in three key papers on the
BlackLitterman 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 GARCH 
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
methodology. 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 assetmimicking portfolios from the factor portfolios. 
Value at Risk (VaR) 
Value at risk functions  Nine examples
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 nonPCA
methods). 
Copulas  Copula functions; VaR  Five examples
illustrating the use of copulas in worksheets and in VBA modules.
Examples cover calibration, generic simulation, multiasset option
valuation, and VaR. 
Historical Data Scrubbing 
HoadleyCorrelStambaugh
HoadleyPricesStambaugh HoadleyPriceMatrix 
An example
illustrating cleaning historical price data
containing gaps and price histories differing in length, without
truncating asset price histories to match the asset with the
shortest history. 