Portfolio Optimization Models in EXCEL

5 mins read

Portfolio Optimization Models in Excel 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?

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

  1. Show us how to calculate Holding Period Returns (HPR) for a given security and a given portfolio.
  2. Simplify Beta and Alpha estimation process against a given index for a given security and a given portfolio.
  3. 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.
  4. Test and evaluate allocation models using real world data set with multiple asset classes including equities, currencies, fixed income bonds, precious metals and commodities.
  5. Solve complex allocation problems such as index matching, risk/return tradeoff, alpha cyclicality and multi-dimension optimization challenges using Excel Solver.

Your own list depends on who you are. An academic or a practitioner? A student or a professional? There is some overlap between the needs of the four groups but also divergence based on who you are.

As a practitioner and a professional, you want higher and stable returns. If not that, at least better than market returns. You want scalable frameworks that evolve as markets around you evolve. As a student of the subject, you want some theory and enough examples and exercises. The right balance – getting comfortable enough with applications to put your own money at risk. As an academic, you want to ensure that your apprentices, your padawans, understand the philosophical foundations of the subject and can critically analyze models for strengths, weaknesses and breaking points. How do you decide which model is better?

Ideally, you want all of this in a real world setting with real world data and a non-trivial implementation within a real world filtered security universe.  Not a portfolio with two securities A and B, but a pre-selected 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 as well as tickers that are household names.  You want this done with as few equations as possible to make your book palatable to large audiences but provide enough annexures with math to avoid being labeled as one focused on trivial mechanical processes.

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 classroom 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 textbook 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.

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.

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. Why could be? Why not, is? Could be, because we don’t have all the answers.

Irrespective of our status as professionals, practitioners, teachers and padawans we still remain students of art of allocation. The market is a great teacher and sometimes it teaches with an iron hand. Models need to evolve to keep pace with market moods. More than any one thing, the Portfolio Optimization Models in EXCEL book focuses on the mindset required to critically evaluate allocation models. While markets and models may change, that mindset will serve you well.

Acknowledgments, gratitude and changes in the 3rd edition.

This will be short and sweet since we have already done this twice. A few lines and I will get out of your way.  

Another year, a new chapter, a few corrections, new test data set to go with a brand new edition and solved solutions. New editions owe a debt of gratitude to readers and reviewers. Much more than authors, it is your commentary that points out flaws in our work and motivates us to fix issues of comprehension and understanding. My heartfelt thank you to all who took out time to write a few words or shared ideas and topics that would make this a better and more useful book. This edition wouldn’t be possible without you. Keep those comments coming.

Compared to where we were when we started this adventure, we have come a long way. From the original class notes that formed the first edition, we have crossed three hundred pages.  And yet this still remains just a book on portfolio optimization. A small bite out of the field that includes security selection, construction, management and optimization. There is a lot more to the subject that we haven’t touched.

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.  A set of solved solutions for the six sample exams we have used with the book have been included.

A big shout out to Nana, my maternal grandfather, who finally gets a book dedication to his name. I should have done this earlier but couldn’t write anything worthy of all that he did for his children and grandchildren, the community and the city he cared for.

Portfolio Optimization Models in Excel. Dedication

Thank you for your love, your gifts, your understanding, your humor and the endless supply of aamdoodh (mango milk) and hafuz (a premium variety South Asian mango unique to the Indo-Pak Sub-continent). You would have had a good laugh on how the story you started writing all those years ago in Bombay, finally turned out. And your foresight on the link between politics, market returns and the shape of things to come.  I had to write three hundred pages to understand only a small part of the magic you brought into this world with you. 

The mistakes as always, remain mine.

Jawwad Farid.


21st May 2019.