Portfolio Analysis & Design
Additional Samples

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:

Sample Functions Illustrated Comments
Risk Diversification Risk-based 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 multi-period geometric MVO using an adaptation of the Hoadley Portfolio Optimizer.
Tax-adjusted portfolio optimization Asset allocation & portfolio optimization functions

Portfolio Optimizer - tax adjusted optimization

Examples illustrating 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 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").
Black-Litterman functions

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 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 asset-mimicking 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 non-PCA methods).
Copulas Copula functions; VaR Five examples 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 HoadleyCorrelStambaugh HoadleyPricesStambaugh
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.

White Dot