Portfolio Optimization Models

in EXCEL

The course begins with a discussion of the two of the principal drivers of portfolio management – risk and return. The objectives for portfolio management are reviewed.

Volatility in the market is assessed using various approaches to determine the change in volatility over time for different asset classes.

Next, the student is walked through a step by step process for building a portfolio management worksheet in EXCEL. This includes the calculation of security wise & portfolio wise daily return series average returns & standard deviations, betas & alphas, setting up Solver for optimization of the portfolio allocation so as to maximize return/ minimum risk. A comprehensive review of correlations is given, including interpreting price & volatility graphs, scatter plots, correlation coefficients, trailing correlations & relative price graphs.

A brief overview of the Markowitz Portfolio Theory, Capital Market Theory & Capital Asset Pricing Model is given. The efficient frontier, optimal risk portfolio, risk free asset, capital allocation line, capital market line and the security market and evaluation of individual securities is covered. Different portfolio strategies are testing and assessed using holding period returns evaluated over two observation periods.

The next chapter calculates Alpha and Beta measures. It elaborates on the difference between the two measures in terms of dependency on market returns and using this dependency (or lack thereof) to determine optimal portfolio allocation. Different index matching allocation strategies are tested. The book concludes with a life insurance portfolio optimization challenge. It poses the challenge for finding an optimal allocation for the investment portfolio, outlines the methodology to follow and provides one possible solution for the life insurer’s investment portfolio.

The course includes a number of annexures:

  • Portfolio Risk Metrics – step by step approaches for calculating Holding period return, Beta with respect to market indices, Jensen’s Alpha, Sharpe Ratio, Treynor Ratio, Value at Risk, Put Premium, Portfolio volatility, Volatility Trend Analysis
  • Bond Duration & Convexity calculation for US Treasury Bills, Notes & Bonds – this includes the calculation of Macaulay Duration, Modified Duration, Effective Duration & Convexity, & Approximate Price Change. For notes and bonds, price & duration are also calculated for settlement dates between coupon payment dates
  • Fixed Income Portfolio Management & Optimization Case Study – including two simplified and stylized of portfolio allocation optimization for maximization of risk & minimization of return objectives respectively
  • A Guide to Setting Stop Loss Limits – Including core principles of the limit setting process, best way of presenting odds & losses to the broad for risk limit setting, a case study for setting soft and hard limits for gold, silver and oil, reviewing limits and treasury compliance.

The package includes the following 5 EXCEL files:

  1. Portfolio Management – Dataset 1
  2. Portfolio Optimization Challenge – Dataset 2
  3. Holding Period Return & Index Matching
  4. Portfolio Optimization Challenge – Solution 1 – without short sales
  5. Portfolio Optimization Ch