By Andrew Chan
When I was a kid, I spent a whole summer trying to solve magic squares. The first one (3x3) was easy; it only took me a couple hours.
The next one (4x4) was much harder; it took me the whole week to solve it.
All summer long I was not able to solve the 5x5 one, until I learned the trick.
I guess you know what I did the whole summer; I just kept doing trial and error!
I still often solve complex problems that involve many conflicting reactions by trial and error, e.g., business planning, resources allocation. However, the trial and error approach can be time consuming, and we cannot often guarantee if we have reached an optimized solution. Fortunately, there is a lot of commercial optimization software available.
Today, I would like to demonstrate how to use Excel Solver, a free optimization add-in that virtually every analyst has when Excel is installed on their PC.
I am going to solve a very simple financial problem—cash flow matching. Let’s say we have a series of fixed cash flow payments that we are committed to pay for the next 25 years. To cover the liability stream, we have a pool of 200 qualified assets that we can invest to generate the target cash flow. You may wonder why 200; I will explain it later on.
I spent quite a bit of time on trial and error for an optimized solution, i.e., the projected cash flow must be greater than the target cash flow. However, there are just too many assets (200) and each of them has its own projected cash flow, maturity date, coupon payment, expenses, etc. It is mission impossible to identify such a solution by trial and error.
Below is one of the scenarios that I tried. You can see how difficult it is to calibrate the model.
Let’s see what Excel Solver can do. The objective is to minimize the market value, and the projected cash flow must be always greater than the target cash flow.
In just over a minute, Excel Solver has identified an optimized portfolio that satisfied the above objective and constraints. You can see from the following graph that projected cash flow in most years is very close and always above target cash flow.
In the real world, there can be thousands or even millions of variables and many more constraints, e.g., asset types, credit rating, currency, industries, processing time. Excel Solver is easy to use, but it is not powerful enough, i.e., it can only handle 200 variables. But don’t worry, there is a vast array of optimization software that is more powerful and smarter.
But even with Excel Solver, a free add-in that comes with Excel, I am able to demonstrate how we can easily solve problems that we would never be able to solve by trial and error.
Do you think you can do trial and error faster than a computer?
Andrew Chan, ASA, is a financial model engineer with ALG Consulting. He can be contacted at chan_a@algconsultings.com.