A review of portfolio applications with EXCEL Solver including portfolio construction, allocation and optimization challenges. Challenges include applications from trading fixed income bonds, equities, options and index matching portfolios.
Over the years we have used Excel Solver across a range of portfolio applications . Each problem and challenge required a specific formulation and approach. We walk through these problems at a high level to give you a sense of patterns we can use with Excel Solver so that the next time you see a challenge, you can use an existing Solver optimization design to crack it.
Solver application in portfolio management follows a standard framework. We begin by identifying an objective function. This could be risk, return, a sum or a difference. The objective function can be maximized or minimized. The objective function is what Solver solves for.
In addition to the objective function, there are additional constraints that the solution needs to comply with. These could be the size of a single position relative to the size of the full portfolio, non-negative exposures (no short selling allowed), additional risk metrics (such as value at risk or VaR for short), liquidity and concentrations limits (not more than a certain amount of daily traded volume) and capital allocation limits (could be a function of value at risk above, regulatory or compliance constraints).
A large part of the work for a solver challenge is figuring out the right objective function and the right set of constraints. If the model is defined and set up correctly solver can solve for it.
Sometimes the original configuration turns out to be unsolvable. It’s not the end of the world and if you are limited to the MS Excel version of Solver, quite common. The resolution to this setback requires a visit back to the drawing board to figure out a structure that Solver can actually solve. You may have to turn the problem around on its head with the objective function becoming a constraint and a constraint becoming an objective function.
With larger more complex problems sometimes it makes sense to break the problem into smaller sub problems, solve them in sequence and then plug the solution together. It is a sub-optimal approach but in the absence of a formal solution, it can help us get close enough to the optimal solution.
Here is a set of problems and applications we have solved in the past with Excel solver. We hope that these design patterns will give you a leg up the next time you come across a challenging optimization question.
1. Fixed Income Portfolio Optimization using EXCEL Solver
For a given portfolio of bonds, the general challenge is optimizing the duration yield trade off. This can be handled by creating a unit of yield for a unit of duration (divide YTM by duration) score for each bond. Duration or interest rate sensitivity serves as a proxy for risk.
The score is then multiplied by the exposure (size of position) for each bond and summed across the entire allocation band (vector or row). The sum represents the score for the portfolio. The portfolio score becomes the objective function.
Typical limits involve risk, duration, position size, liquidity, concentration and capital allocation. From an Asset Liability point of view, we can also specify a target duration or convexity threshold that needs to be matched or exceeded.
Given the usage of duration and convexity, this is a non-linear problem. For most markets we also check the non-negative option to ensure no short sales are allowed by the model. A more detailed and step by step implementation can be viewed at the link that follows. Optimizing Fixed Income Portfolios for yield per unit of duration using Excel Solver.
2. Optimizing Equity portfolio allocation using EXCEL Solver
Similar to the fixed income portfolio allocation challenge, the equity portfolio allocation challenge also uses a return per unit of risk framework. We play with two primary choices. Daily expected return or historical holding period return for yield and standard deviation for risk. The score calculation used for the objective function is return per unit of risk. Which translates into holding period return divided by the standard deviation for the same period. The score is tabulated for each individual security and then calculated for the entire portfolio weighted by the size of individual positions.
Additional limits and constraints include a target portfolio beta and an optimal alpha. Designed in this form, the problem tends to be unsolvable. An alternate approach is to seek a maximum alpha manually by finding a target threshold rather than asking solver to optimize for it.
Given the usage of standard deviation in the objective function this is a non-linear problem. Short sales are controlled by turning on or off the non-negative check. A full implementation can be found at the following link. Building the Excel Solver portfolio management sheet.
3. Hedging higher Order Greeks for option trading using EXCEL Solver
If you buy and sell options for a living you want to offset your higher order Option Greeks (Gamma, Vega and Volga) on the sell (short) side with your hedge (long) positions. The Solver configuration for this problem is quite interesting. Since there are multiple Option Greeks, rather than using any one for the objective function we calculate the difference between all the Greeks and sum them up. The objective function is asked to minimize the sum of all the differences. In case of a perfect offset the difference between the buy and sell side Option Greeks is optimized to zero, leaving us with perfectly offset higher order Greeks. We can facilitate the convergence by specifying and limiting each individual difference to zero. A full implementation is available at the following link. Hedging Higher Order Greeks using Excel Solver.
4. Calibrating arbitrage free interest rate models using EXCEL Solver
When we use an arbitrage free interest rate model such as Black Derman and Toy (BDT) or Heath Jarrow and Merton (HJM) we face a unique challenge. Bond prices produced by the model have to match market prices of the same bonds on calibration dates. We use the same formulation used above for Hedging higher order Option Greeks. We tabulate the difference between bond market prices and model bond prices, sum the difference and ask solver to minimize the difference. Additional constraints are defined to ensure individual differences also get minimized to zero. The one limitation with this approach is when one is trying to calibrate the model using quarterly repayment periods for a 20 or 30 year bond. This may prove to be a challenge for an earlier version of Solver and on less powerful machines. The approach to calibrating BDT using treasury bond prices can be found at the link that follows. Calibrating BDT model using treasury bond prices with Excel Solver.
5. Index Matching and Surplus Immunization using EXCEL Solver
By now you should have the idea for the correct formulation for this last challenge. As fund managers we are often measured against the performance of a benchmark index. How do we ensure that we craft a portfolio that beats that index. In the insurance and banking industry a different version of this problem exists. We are asked to create portfolios such that weighted average duration of assets is equal to the weighted average duration of liabilities but weighted average convexity of assets is greater than weighted average convexity of liabilities. These are called the surplus immunization conditions and are commonly used to keep the Asset Liability Mismatch risk within control in the insurance industry.
Rather than showing you the solution like we have done above for all other challenges, we ask you to take a minute to think about your formulation and put it down on paper. When you are ready, you can take a look at the two separate formulations for this challenge.
a. Beat a benchmark portfolio index using Excel Solver.
b. The Life Insurance Portfolio and Surplus Immunization Challenge.
The original inspiration for each of these optimization challenges and their solutions were my students in the Portfolio Optimization and management courses offered at the SP Jain Global, SP Jain IMR Executive MBA program in Dubai and at the Weekend Executive MBA Program at Institute of Business Administration, IBA, Karachi.
Notes from three years of teaching the course at the two cities lead to Portfolio Optimization Models in Excel, a textbook which is now in its 4th Edition. The textbook covers detailed solutions for 3 of the 5 challenges above. Hedging higher order Greeks and Calibrating arbitrage free interest rate models require two additional textbooks to solve.
Free lecture notes from the book previously hosted on this page can now be found at the new Portfolio Optimization and Management page.