Portfolio Analysis & Design Additional Samples Spreadsheets

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. 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)	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.
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	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 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.