Browse By

Portfolio Management with Excel Solver

Portfolio Management with 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 odd pages on Portfolio Management with Excel Solver to address portfolio optimization problems in the investment management world.

Think about it for a second. 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 these 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.

Last month 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.

Originally put together as a class room study guide and a handy reference for their final exams, the book is a basic entry level text. 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 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 four or 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 example 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.

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 with all the typos and numerical errors, 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 March – in Dubai / Abu Dhabi followed by a second run in Bangkok and Phuket. Dubai dates for the workshop have been finalized for 7th and 8th March. Bangkok will most likely be towards the mid or end of March.

Comodo SSL