# Portfolio Optimization Models in EXCEL

#### About the Course

**What do you want from a book on portfolio management and optimization?**

We have been asking this question for three years. Here is the wish list that customers like you came up with. A good text book on Portfolio Optimization models should:

a) Show us how to calculate Holding Period Returns (HPR) for a given security and a given portfolio.

b) Simplify Beta and Alpha estimation process against a given index for a given security and a given portfolio.

c) Link security allocation criteria to risk adjusted returns and performance benchmarks. Work with portfolio construction models geared toward beating a specific index, commodity or sector returns.

d) Test and evaluate allocation models using real world data set with multiple asset classes including equities, currencies, fixed income bonds, precious metals and commodities.

e) Solve complex allocation problems such as index matching, risk/return tradeoff, alpha cyclicality and multi-dimension optimization challenges using Excel Solver.

Ideally all of this in a real world setting with real world data and a non-trivial implementation within a filtered security universe. Not a portfolio with two securities A and B, but a preselected universe that asks you to choose between Disney (DIS) and Amazon (AMZN), Home Depot (HD) and Netflix (NFLX). Exercises that require you to look back and evaluate your allocations against market benchmarks and tickers that are household names. As few equations as possible but with enough annexes with math for those who want to validate it for themselves.

We couldn’t find a book that did all of the above so we wrote one. In its 3rd edition now, Portfolio Optimization Models in Excel, owes its origin to a week long Executive MBA workshop in Dubai in 2016. In a small class room filled with bankers, traders, treasurers and CFOs, we opted for the practical versus theoretical route. Since then the book has been used to teach business school students, market practitioners and treasury professionals. Using hands on practical application questions from the field – variations on how do I put this **“Thing / Model / Equation / Framework”** to work in Excel, the 305 page text book with 290 illustrations comes with an Excel data set, supplementary materials, practice questions and insights driven by US equities market data from August 2008 to December 2018.

The book presents the foundations of a scalable framework that evolves as markets around us evolve. It includes just enough theory to ensure that we understand philosophical foundations of the subject and critically analyze optimization models for strengths, weaknesses and breaking points. So that we can answer the question - Which allocation model is better and leads to stable robust returns? The book is far from being perfect but it’s come a long way in three editions. All driven by feedback from students and practitioners.

____________

This is the prescribed text book for Advance Portfolio Management Crash course at SP Jain IMR Campus in Dubai as well as the full semester Portfolio Optimization Models course at IBA Karachi. Three editions in three years. 6 chapters, 5 end of book Annexures, 6 open book sample practice exams with real world application questions. 305 pages, 64,209 words, 290 illustrations.

Our strength has always been our appeal to practitioners. In the computational finance space there is no shortage of books on a given subject. No shortage of equations and long drawn proofs. Proofs that are important yet remain incomprehensible to most professionals working in the field. How do you take that thing (a list or formulation of Greek symbols) and turn it into an Excel model? An Excel model that works.

________________________

In its 3rd edition Portfolio Optimization Models in Excel does one thing well. Using a step by step approach it clearly shows how to turn that thing into an Excel model that works. If you want to teach and test students on the theoretical foundations of the field, this is not a good book. If you want to play with allocation models, debate performance attribution, detect patterns in a ten-year data set, solve abstract problems using real world prices and talk intelligently about real world implementations, this could be the book for you.

**What new in the 3rd Edition?**

The new chapter is on performance evaluation as well as a detailed step by step guide on portfolio construction has been added. This was the one piece we had been missing since the beginning. While the discussion on holding period returns addressed a part of this topic, there was a need to introduce a framework that could be applied across strategies and securities universe. This has been done. Our big question when we used the framework was to evaluate possible play between risk return trade off and higher order optimization models. The answer may surprise you. It certainly surprised us.

There are also some new sections and rewrites across the book and our only theoretical chapter on CAPM has been moved to the annexure. There is an updated optional data set with a larger universe of securities for you to play with when you are done with the original set the book uses. Most sample practice questions also work with the extended data set.

**Complimentary materials**

Better Excel Charts + Data Analysis in Excel - A free 41 page guide to getting more from your Excel graphs and charts and Excel data tables. Required reading if you haven't used Excel Data Tables before.

**Detailed overview and Table of content - Portfolio Optimization Models in EXCEL**

The book 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, a step by step process for building a portfolio management worksheet in EXCEL. This includes the calculation of security wise and portfolio wise daily return series average returns and standard deviations, betas and 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 tested 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. The stability and robustness of portfolio Alphas - the excess return metric - and the implication for portfolio allocation and optimization models are discussed. 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 book includes a number of annexes:

- 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.
- Variance Covariance and Correlation Matrices - Including the calculation of portfolio volatility and VaR using the variance covariance and correlation matrices as well as the short cut approach, and stress tests of portfolio volatility/ VaR under perfect and zero correlations
- Sample Papers and Practice Questions - including 6 sample exam papers and a set of review questions

The package includes the following 4 EXCEL files:

- Holding Period Return & Index Matching Excel Solver Model
- Portfolio Optimization Challenge - with & without short sales
- Portfolio Risk Metrics
- Markowitz Portfolio Theory & CAPM
- 2 practice price data set including prices from a number of US equities selected from NYSE and NASDAQ are also included and are used as the basis for practice questions and examples.

**Learning Objectives**

After reading this book you will be able to:

- Understand the primary model used for portfolio allocation, construction, optimization and management
- Assess market volatility over time and relate it to returns
- Calculate returns and standard deviations for individual securities and portfolio
- Build a portfolio management EXCEL sheet that can be optimized using Excel Solver
- Set up Solver for different portfolio optimization objective functions include maximize risk, return and risk return tradeoff
- 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
- Setup and 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 between 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 an institutional life insurance 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

**Target Audience**

Active investors who want to take a deep dive through the investment allocation and optimization process without delving deep into mathematical equations and proofs.

Auditors and accountants who need to evaluate portfolio performances against benchmarks and prepare audit disclosures for holding period returns, fund investment income.

Consultants and professionals required to comment on adequacy of portfolio return and robustness of allocation models.

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.

**Prerequisites**

The student should also be comfortable with basic mathematics, statistics and EXCEL. Some finance and market knowledge would also be useful.