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:
- Portfolio Management – Dataset 1
- Portfolio Optimization Challenge – Dataset 2
- Holding Period Return & Index Matching
- Portfolio Optimization Challenge – Solution 1 – without short sales
- Portfolio Optimization Challenge – Solution 2 – with short sales
After taking this course you will be able to:
- List some of the motivations behind portfolio management
- Assess market volatility over time
- Calculate returns and standard deviations for individual securities & portfolio
- Build a portfolio management EXCEL sheet
- Set up Solver for different portfolio optimization objective functions
- Calculate and interpret correlations between two variation using different tools
- Compute the key statistical measures used by the Markotwitz Portfolio Theory, Capital Market Theory and Capital Asset Pricing Model (CAPM)
- Explain and determine the efficient frontier of risky asset portfolio
- Graph the capital allocation line
- Adjust the capital allocation line for a non-government entity
- Determine investor utility indifference curve
- List the assumptions made for the Capital Market Theory & CAPM
- Explain why the Capital Market Line cannot be used to assess individual securities
- Discuss CAPM & the Security Market Line (SML)
- Evaluate the fairness of the market price of assets for individual securities using the SML
- Setup & use Solver to for different portfolio optimization strategies
- Compare strategies over a given observation period and evaluation effectiveness of selected strategy’s allocation over a separate period
- Calculate Alpha and Beta for the portfolio
- Explain the difference of Alpha and Beta in terms of market dependency
- Utilize this difference to set up revised portfolio optimization strategies
- Index match a portfolio
- Determine an optimal portfolio allocation for a life insurance companies investment portfolio
- Calculate a suite of portfolio risk metrics
- Compute the duration and convexity of a Treasury Bill and fixed income instrument
- Optimize the portfolio allocation of a fixed income bond portfolio using their duration & convexity measures
- Discuss stop loss limits
The student should also be comfortable with basic mathematics, statistics and EXCEL.
Intermediate level finance and business school students who require a quick reference and study guide containing basic principles and real world applications of investment management and portfolio optimization.
Portfolio Optimization Models in EXCEL
- 1 PDF & 5 EXCEL worksheets