 |
|
|
|
|
|
|
Hoadley Portfolio Optimizer
The Hoadley Portfolio Optimizer applies the principles of Modern Portfolio Theory (MPT) and the Capital Asset Pricing Model (CAPM) to analyzing and optimizing a portfolio of stocks and/or other asset classes. The Excel-based application, which is not password protected, performs two key functions:
The data download and
analysis function (which is optional) will automatically retrieve historic stock,
fund, and index prices from Yahoo Finance (most exchanges supported) for a
complete portfolio and will calculate key risk measures such as volatility
(decomposed into active risk, residual risk and market risk), Beta, and
R-Squared: for individual securities, the specified index, and for the
portfolio as a whole.
Note:
The retrieval of data from Yahoo is optional and the portfolio
optimization process does not in any way depend on Yahoo data. The
optimizer can be used with data from any source.
The portfolio optimization component uses mean-variance optimization (MVO), originally developed by Harry Markowitz, to determine the weightings of each asset required to produce a range of returns for the portfolio at the lowest possible risk (the efficient frontier). The "assets" to be optimized can be individual assets (eg individual stocks, bonds, funds) or asset classes (eg equity and other indices, funds of a particular investment style, an industry sector). Minimum and maximum weights can be specified for each asset, including negative weights if short positions are permitted. Assets can also optionally be assigned to user-defined asset groups. The groups could represent industry groupings, investment style (value, growth, small cap...), asset class (domestic equities, foreign equities, fixed interest...) or anything. Minimum and maximum weight constraints can be specified by asset group as well as by asset. So, for example, you could specify that no more than 15% of a portfolio should be in technology stocks. An optimal portfolio -- the portfolio which will provide the maximum return for the lowest unit of risk -- is then estimated by maximizing the Sharpe ratio. A minimum variance portfolio is also produced.
The portfolio to be optimized can either be a portfolio analyzed by the portfolio analysis component using data downloaded from Yahoo (as described in the portfolio analysis section above), or by using user-supplied historic data (not from Yahoo). The portfolio can consist of individual assets or multiple asset classes rather than individual assets. Following optimization the efficient frontier, and associated capital allocation line (or capital market line), are automatically charted to provide a graphical representation of the risk/reward trade-off options available. If a previously analyzed "current" portfolio is used, then the Sharpe ratio is also calculated for this portfolio and its position relative to the efficient frontier is shown on the efficient frontier graph (the yellow diamond in the example shown above). This lets you easily compare your current portfolio with an efficient portfolio in terms of return per unit of risk. All results can be viewed graphically (as shown above) or in tabular form showing the individual optimal asset weights for each point on the efficient frontier. Individual assets can also optionally be shown on the the chart so their risk and return can be viewed in in relation to the efficient frontier.
If assets have been assigned to user-defined groups, an analysis is produced
showing the actual allocations by asset group (eg industry, investment style...)
for each point on the efficient frontier and for the optimal portfolio. The
current portfolio is also summarised by asset group to enable easy comparison
with the optimal portfolio.
Optimization scenarios -- sets of assumptions on returns, volatilities, minimum and maximum weights, correlations and asset groupings -- can be saved and later retrieved for further analysis and fine tuning.
The optimization results -- optimal and minimum risk portfolios, and efficient frontiers -- from saved scenarios can also be retrieved and compared, two at a time, both in tabular and in chart form. The above picture illustrates how the efficient frontiers from two scenarios can be plotted on one chart for comparative analysis.
Saving optimization scenario datasets and scenario comparisons is available
in the Portfolio Optimizer Pro version of the Portfolio
Optimizer.
Using the results from the optimization process the Portfolio Optimizer produces a comprehensive set of active portfolio management and risk statistics. Active management statistics: The statistics include excess returns and risks decomposed into residual (alpha), active (active returns and tracking error), and market (benchmark); Beta and R-Squared; the Sharpe ratio; residual and active information ratios, and M-Squared (a measure of risk adjusted return). These statistics are produced for each of 20 portfolios on the efficient frontier, the optimal portfolio (tangency portfolio), the minimum variance portfolio, and for ten combinations of optimal portfolio and cash holdings to show the effects of leveraging/de-leveraging the optimal portfolio along the capital allocation (or capital market) line. Value at Risk (VaR) and Conditional Value at Risk (CVaR) are calculated for each leveraged/de-leveraged portfolio. Active portfolio management statistics are also shown for a portfolio with a beta of one.
Risk-adjusted measures: Active management statistics are also shown on a risk adjusted basis using the M3 (M-cubed) methodology. The M3 methodology uses a combination of the active portfolio, the passive benchmark and the risk free asset to produce a combined portfolio with a volatility equal to the benchmark volatility and with a tracking error equal to a user-specified tracking error target. The M3 analysis enables "normalized" comparisons of expected portfolio performance, and provides guidance for portfolio construction with tracking error targets. Risk attribution: A risk attribution analysis is produced for the optimal portfolio showing the contribution of each asset and, optionally, each asset grouping (eg asset class, industry, country) to overall portfolio volatility. The analysis compares contribution to risk, marginal contribution to risk and percent contribution to risk with the allocated weights of assets and asset groups to facilitate the active management of portfolio risk.
Active management statistics and the risk attribution analysis are available
in the Portfolio Optimizer Pro version of the Portfolio
Optimizer.
One of the fundamental insights from the Capital Asset Pricing Model (CAPM) is that all investors should hold the same "risky" (optimal) portfolio, differing only in the amount of leverage applied. When investors can borrow and lend at the risk free rate then the capital allocation line (CAL) -- shown in red on the picture below -- will be a straight line tangent to the efficient frontier. By combining risk free borrowing and lending with the optimal portfolio an investor can select a point on the CAL -- the risk/reward trade-off -- commensurate with his/her tolerance for risk. However, usually investors cannot borrow at the risk free rate. When borrowing is more expensive than the risk free rate the CAL will be kinked, with the slope of the line to the right of the optimal portfolio (the yellow triangle in the picture below) being flatter than the slope of the line to the left of the optimal portfolio.
In this case the CAL will dip below the efficient frontier for a range of volatilities (as shown on the above picture). This indicates that, to achieve returns in excess of the optimal portfolio return, there are more efficient asset allocation options available than simply leveraging the optimal portfolio.
The Hoadley Portfolio Optimizer
produces a leverage analysis showing total portfolio returns and
volatilities for a range of cash/optimal portfolio combinations, from 0%
invested in the optimal portfolio (ie 100% in cash) to 200% invested in
the optimal portfolio. A premium over the risk free rate for
borrowing can be specified, to clearly highlight the real risk/reward
tradeoffs available to the investor or portfolio manager.
The Hoadley Finance Add-in for Excel includes tools to help estimate the key inputs to the portfolio optimizer: asset (or sector) volatilities, correlations, and expected returns. Volatilities and correlations can be easily estimated from historical data using the volatility and correlation functions in the Finance Add-in for Excel, or by using the portfolio analysis sheet in the portfolio optimizer to automatically download data and calculate these measures, as described above. The Finance Add-in for Excel includes a number of tools to improve estimation accuracy of volatilities and correlations. These include Bayesian shrinkage estimators using the Jorion or Ledoit-Wolf models, and principal component analysis (PCA). In situations where price histories differ in length, as may be the case when some of the assets have only recently been listed or when some funds are relatively new, then the Stambaugh methodology can be used to estimate volatilities and correlations without truncating data to match the asset with the shortest price history. See the HoadleyCorrelStambaugh function for more information on this sophisticated estimator. Downside deviation and correlation ( ie semicovariance) calculated using the Hoadley downside risk functions can be used instead of standard volatility and correlation, to perform Downside Risk Optimization (DRO) if required. In this case the Sortino ratio, rather than the Sharpe ratio, would be maximised to produce the optimal portfolio. Expected returns are more difficult to estimate than volatilities and correlations. Historical returns can provide quite robust estimates of future volatility and correlation but are usually very poor predictors of future returns. Models such as Bayesian shrinkage estimators can help improve estimates based solely on historical sample mean returns, and the Finance Add-in for Excel includes a Bayesian estimator function using the Jorion model. But usually more advanced techniques must be used. One of the most sophisticated and widely used of these is the Black-Litterman Bayesian asset allocation model. The Black-Litterman model was developed by Fischer Black (of Black-Scholes fame) and Robert Litterman at Goldman Sachs in the early 1990s. The Black-Litterman model starts with market asset class weightings and backs-out, using reverse optimization, the returns implied by the market. These equilibrium expected returns -- the market neutral reference point -- are combined with user views about the absolute or relative performance of selected asset classes/sectors to produce revised estimates of returns. The revised estimated returns are then used in the Hoadley Portfolio Optimizer to produce the optimal portfolio and efficient frontier. Using the Black-Litterman model leads to well diversified portfolios which avoid extreme long/short positions, and the wild swings in asset allocation weights (and hence portfolios which are impractical to implement) which you normally get from MVO when asset returns are changed, even slightly, in isolation. The Black-Litterman model achieves this diversification without the need to impose tight arbitrary constraints on asset weights during optimization. The Hoadley Finance Add-in for Excel includes a full implementation of the Black-Litterman model. View:
Black-Litterman
tutorial which demonstrates, by way of example, how freely
available S&P Global Industry Classification Standard (GICS) data can be used
to estimate expected returns for equity markets.
The impact of taxes on efficient asset allocation for private investors is significant -- taxes really do matter. Incorporation of tax impacts into an efficient allocation framework, however, can be challenging without a clear methodology to follow and taxation is therefore often ignored. The result is the construction of portfolios which fail to achieve their prime objective: the most efficient maximization of after-tax wealth. The Hoadley Finance Add-in for Excel includes two functions for converting pre-tax returns, volatilities and market values into their after tax equivalents, for both taxable investments and investments held in a tax-deferred account, such as a US 401(k) retirement account. The functions take into account the multiple tax rates that can apply during the life of each asset (taxes on annual income and short term capital gains, taxes on dividends, taxes on long term capital gains realized at the end of the investment horizon etc) and make adjustments for embedded tax liabilities from unrealized capital gains at the start of the investment period. The adjusted returns, volatilities, and values from the functions can then be used with the Hoadley Portfolio Optimizer to allocate assets on an after-tax basis. The after tax asset weights from the portfolio optimizer would then be converted, again using the tax adjustment functions, back to pre-tax (market) weights for implementation.
A sample
spreadsheet is available for download with the full version of the Finance
Add-in for Excel which provides working examples for three common situations:
allocation of assets for a new taxable portfolio (ie without existing unrealized
capital gains); allocation of assets in an existing taxable portfolio (ie
with embedded tax liabilities attached to some assets at the start of the
investment period); and allocation of assets or asset classes efficiently across taxable, tax-deferred
and tax-exempt accounts taking into account legislative or other constraints on
the value that can be held in the tax-advantaged accounts ("location
planning").
There are two versions of the Hoadley Portfolio Optimizer: Portfolio Optimizer Personal and Portfolio Optimizer Pro. Both versions are included in the download file which is available to users of the full version of the add-in who are within their one year free download period. The two versions of the Portfolio Optimizer are identical to use. However they are based on different technologies. Portfolio Optimizer Personal uses Microsoft Excel Solver to find each portfolio on the efficient frontier and the optimal (tangency) portfolio. It is suitable for optimizing up to fifty assets. Portfolio Optimizer Pro uses the HoadleyEfficientFrontier function which implements the Markowitz critical line algorithm. Excel solver is not used and it is much faster than the the Portfolio Optimizer Personal version. "What if" analysis -- rapidly testing the impact on portfolio composition of changing input assumptions -- is therefore more practical using Portfolio Optimizer Pro. Active portfolio management statistics, risk attribution analysis, scenario comparisons, and leverage analysis are only available in the Pro version. Users of the Personal version can still produce these analyses using functions in the Finance Add-in for Excel. Licensing: Portfolio Optimizer Pro included in the full version of the Finance Add-in for Excel bought under a private use/personal license uses the HoadleyEfficientFrontier function and is limited to optimizing a maximum of ten assets. To optimize more than ten assets using Portfolio Optimizer Pro a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro upgrade" license for approved private investors ("Pro upgrade" licensing enquiries). Portfolio Optimizer Personal has no licensing limitations on the number of assets, but there are practical limitations due to the slower speed of the Personal version compared with the Pro version. The "personal" version is used mainly by private investors. The "pro" version is used mainly by professional asset managers and very active private investors. Licensing
summary
Portfolio Optimizer Personal has been tested with English versions of Microsoft Excel for Windows 2002 - 2010 (32-bit) on English versions of Microsoft Windows XP or later with English language regional settings. The Optimizer is not supported on non-English versions of Excel, on non-English Windows or on Windows with non-English regional settings. The Optimizer may well run on these un-tested platforms but no support can be provided where any issues are judged by Hoadley Trading & Investment Tools to be related to these non-English platforms. Portfolio Optimizer Pro will run on versions of Microsoft Excel for Windows 2002 -2010 (32-bit) on Windows XP or later, including Windows 7, 32-bit and 64 bit editions, with any regional settings. The Hoadley Finance Add-in for Excel must be installed
before using the Portfolio Optimizer.
Both versions of the Portfolio Optimizer are included with the full version of the Hoadley Finance Add-in for Excel. Pricing and licensing information for the Finance Add-in for Excel can be found here. Before you download the add-in or the Portfolio Optimizer note that:
View: Portfolio Optimizer tutorial. View: Black-Litterman tutorial. Download and purchase Hoadley Finance Add-in for Excel . The Finance add-in for Excel is required to run the Portfolio Optimizer. Download the Hoadley Portfolio Optimizer (both Portfolio Optimizer Personal and Portfolio Optimizer Pro included). If you have not purchased the Finance Add-in in the last 12 months you will be redirected to the secure purchase page.
|
