Trial and Error? – Cash flow matching

When I was a kid, I spent a whole summer trying to solve magic squares. The first one (3 x 3) was easy; it only took me couple hours.

clip_image001

The next one (4 x 4) was much harder; it took me the whole week to solve it.

clip_image002

I was never able to solve the 5 x 5 one in the whole summer until I learned the trick.

clip_image003

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.

clip_image005

I spent quite a bit of time on trial and error 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.

clip_image007

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.

clip_image009

In the real world, there can be thousand 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.

Can you can do trial and error faster than a computer?

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: