Browse By

Portfolio Management with Excel Solver

Portfolio Management with Excel Solver

Learn how to allocate across sectors and specific securities based on risk, return, liquidity and other limits across multi asset portfolios using your friendly spreadsheet version of Excel solver. 

Despite my best efforts to not write another text book, the impossible has happened again. We have some how managed to put together two hundred pages on Portfolio Management with Excel Solver to address portfolio optimization problems in the investment management world.

You have wrapped up the sector weight and security selection filters. You now have a pool of 80 qualified investment securities that you would like to pick and chose from for your portfolio. Is there a tool out there that can handle and address these questions for you? How about a performance evaluation framework? Using Alpha and Beta? Matching and beating a industry benchmark or a market index? Calculating Holding period returns? On a simple interest basis? Compounded semi annually?

Portfolio Management with Excel Solver – Finding the perfect allocation from the qualified securities universe.

There are hundreds of awkward minor challenges that we did cover and review in business school but are locked inside some dusty shelf in the brain. Wouldn’t it be nice to have a handy electronic reference that we could pick up whenever we needn’t to cross check a calculation or a result?

Portfolio Management with Excel Solver – The origin

Professor Mark Broadie at Columbia first introduced us to using Excel Solver for single and multi period optimization problems. That material really came together in the first optimization workshop I ran for customers in the MENA region about a decade ago. We added local securities data, market eccentricities, treasury questions, issues around multi asset class optimization.  Over the next few years the content grew and evolved as we spent more time with treasury customers, first on the systems side, then pricing and then training.

In November I ran a week long EMBA course on Portfolio Optimization for 15 students in Dubai at the SP Jain Campus. Post the course, I felt there was a need to put together a manual for students that they could use to play and explore Solver. That realization lead to a few posts driven by questions asked in the class room that surprisingly grew into the textbook. While I had been teaching variations of this course for a while, this was the first 18 hour version.

This was quickly followed by a semester long edition at the Executive MBA program at the Institute of Business Administration (IBA) in January in Karachi. This time the class was blessed by a number of traders and the interaction and the exchange helped refine the material further. We dug deeper in investment styles, formulating investment thesis, marrying frameworks and challenges specific to a given asset class.

Originally put together as a class room study guide and a handy reference for their final exams, the book is now an intermediate to advance level text book that looks at real life investment management challenges through the lens of Excel Solver. We assume some familiarity with CAPM and APT, hands on expertise with data analysis and modeling in Excel and comfort with basic financial modeling.

If you are interested in exploring real world applications in the area of  investment and fund management and portfolio optimization, please take a look.

The Portfolio Optimization and Investment Management Course Lecture series. Sample lecture summaries from both the SP Jain and IBA versions of the course.

  1. Lecture 1a – Portfolio Management – Introducing risk and return.
  2. Lecture 1b – Building the Excel Portfolio management worksheet.
  3. Also see Market Risk – Portfolio volatility and Market risk metrics – volatility trend analysis.
  4. Lecture 3a – Calculating Beta and Alpha for portfolio management in Excel.
  5. Lecture 3b – The difference between Alpha and Beta
  6. Lecture 3c – Alpha dominant strategy and evaluation.
  7. Background and context – The Capital Asset Pricing Model – CAPM
  8. Lecture 4 – Index Matching Portfolio Optimization problem set.
  9. Lecture 5 – Evaluating Portfolio performance using Holding period return – a case study.
  10. Lecture 5a – Holding period returns, aggregate returns and annual returns
  11. Lecture 6 a Optimizing the investment portfolio allocation challenge for a life insurance company.
  12. Lecture 6 b Optimizing the investment portfolio allocation for a life insurance company – solution
  1. Portfolio management – Sample exam. Portfolio Management and Optimization assessment exam.

The audience is intermediate level finance, investment and portfolio management professionals and individuals who have an interest in actively managing their savings and wealth portfolios.  In wealth management and private banking roles relationship managers now need to be increasingly current with respect to both asset classes as well as allocation models. The book does a good job of covering both topics.

The book can also be used as a text book for a second more advance level course in investment management for business school students. I have now used it twice to teach the specialization course in Portfolio Optimization after the first five core finance courses have been covered.

The challenge with most business school courses, more so with specialization is the paucity of time challenge. So the book comes with and is taught with a data set that you can use easily to follow through the examples in the text book. Two text books later, the most awkward lesson I have learnt is to keep track of my modelling spread sheets so that I can give them away to audiences who are interested in building their own models from scratch.

Portfolio-Management-with Excel solver

Portfolio Management with Excel Solver

The package includes two practice data set, two problem sets, excel templates used to solve illustrative portfolio optimization challenges, some theory and a handful of thought experiments.  The first 90 pages cover five days of lectures with examples I recently delivered in Dubai as part of 6 day evening program. The remaining 110 include a number of detailed post chapter annexes and applications. The series of sample lectures have been shared above.

A revised edition is already in the works based on the feedback of students from SP Jain and IBA and should be out in time for the end of term final exam at IBA.

Key topics covered include risk and return, Capital Asset pricing model (CAPM), Alpha and Beta, holding period returns, volatility and correlations, the Index matching problem and a detailed review of portfolio risk metrics. The course ends with an end of course portfolio optimization challenge for the life insurance industry. If you would like to get a flavor of the content of the course that drove the book, please take a look at the course outline of the Portfolio Management training resource I put together for my EMBA students.

The text book with the Excel data set and solver templates is now available for sale and download on the store.  If you buy a copy you get the updated edition whenever it comes out. We have done that on a regular basis for our ALM Crash Course customers and plan to continue the practice.

If you are a current or previous MBA student of mine and would like to see a complimentary review copy, drop me a note and I will be happy to send you one.

Batch 36 EMBA students if you are reading this – I have just emailed your copies to Daisy and Julie. They should be up on Black Board if not today, then certainly by mid day tomorrow. And no, the end of chapter Excel Solver challenge is not based on the final exam questions I had spoken to you about.

If you are interested in attending a shorter two day instructor led power version of the 6 day course, drop me a line. We are scheduling two back to back sessions in mid July – in Dubai / Abu Dhabi followed by a second run in Bangkok, Singapore and Phuket. Bangkok will most likely be towards early August.


Comodo SSL