

Portfolio Analysis & Design

Utility Applications 
Three utility applications  application tools of limited scope which are
complementary to the other portfolio analysis and design applications
 are available for download.
Partial Index Replicator: Index Tracking Portfolio Builder ("Direct Indexing") 
The Partial Index Replicator application selects a subset of assets, from a candidate list, which most closely tracks an index or ETF. Historical data can be downloaded from Yahoo Finance or imported from a file.
For example using historical data for 100 candidate assets, the application could construct a portfolio consisting of 15 assets which closely replicates the performance of an index. Specifying cardinality constraints (ie the maximum number of assets to include in a tracking portfolio) can produce portfolios which are practical to implement without having to arbitrarily decide which assets to include or exclude from the tracking portfolio.
Minimum and maximum weight constraints can also optionally be specified to ensure that specific assets will always be included in the tracking portfolio and/or that their weights do not exceed a specified maximum.
The combination of cardinality, and max/min weight constraints provides an effective method of implementing a "direct indexing" investment strategy.
Statistics produced for the tracking portfolio, include
tracking error, portfolio volatility, systematic risk, specific
risk, portfolio beta, and
RSquared. Tracking performance over time is also shown
graphically.
Volatility, Beta and Correlation Calculator 
This application downloads data for multiple assets from Yahoo Finance (or imports price histories from a file). Assets with incomplete/short price histories are highlighted, as are other potential issues with the data (eg missing prices; abnormally large jumps or falls from one day to the next).
Volatilities, betas and correlations are then calculated using the sophisticated Stambaugh model, which utilizes all historical data without truncating price histories to match the asset with the shortest price history.
This utility greatly simplifies the identification of potential data
problems, and enables the preparation of inputs to other applications such
as the Portfolio Optimizer, Portfolio Simulator, and the VaR
Simulator, without discarding valuable data when price histories differ in length
.
S&P500 Returns Analyzer 
This application downloads (from the Robert Shiller web site) and analyses US S&P500 returns data from 1871 to the present. Two sets of S&P500 performance analyses are produced: one for the entire dataset, and the other for selectable subperiods.
The analysis includes returns (dividends reinvested, nominal and real, arithmetic and geometric), dividend yields, volatilities, standard deviations, and equity risk premiums (both arithmetic and geometric). Results are shown for all 30 year, 20 year and 10 year periods since 1871 and for every combination of 1, 2, 5, 10, 20, and 30 year holding periods. Measures include maximum/minimum, average, percentile rankings of returns, and more.
A drawdown analysis, showing the top 15 drawdowns, is produced for the entire period and for any user selected period using either nominal or real returns.
The results from the analyzer using the full dataset broadly lend support to the key themes in the book "Stocks for the Long Run" by Jeremy J Siegel, namely:
Over the long run the real (ie
after adjustment for inflation) geometric (ie compounding) return
for US equities has been 6.7%  6.9%pa. 

As the holding period for equities increases the standard deviation of returns declines significantly faster than predicted by the random walk hypothesis. This provides fairly strong support for mean reversion in equity returns  that equities are less risky than expected for longer holding periods. 
These broad findings, however, conceal considerable variations in US equity performance over time and for different holding periods and the S&P500 Returns Analyzer can provide an insight into this.
CAPE & P/E ratio returns prediction analysis
The S&P500 Returns Analyzer also includes a tool to analyse how well the Shiller Cyclically Adjusted Price to Earnings (CAPE) ratio can predict subsequent returns. Users can select any period since 1880 for the analysis, can specify the number of years over which subsequent returns are measured (eg 1, 2, 5, 10, 15 years), and select whether nominal or real returns are to be used.
The results, which are presented in tabular and graphical form show how well
CAPE has performed as a predictor of future returns, and how the
prediction accuracy has varied over time. The analyses can also be
undertaken using the regular P/E ratio to highlight the value CAPE adds
to returns prediction.
Software Environment 
For detailed systems requirements, including supported versions of Windows and Excel see systems requirements.
Download the Complementary Utility Applications 
The Utility Applications are available for download (they are included with the Portfolio Optimizer and other portfolio analysis and design applications) to users who have purchased the Hoadley Finance Addin for Excel and are still within their one year free download period.