This post implements a simple asset-liability cash flow matching using Excel and R. This example is a linear programming (LP) model but will serve as a foundation for the advanced ALM modeling techniques such as two-stage or multi-stage stochastic linear programming (2SSLP, MSSLP).


We borrow a basic asset-liability cash flow matching model as an example from the next book(pdf).

LP model : Asset/Liability Cash Flow Matching

The page 47 of their book states a problem of financing short term cash commitments as follows.


During the 6 months, we should be funding short term capital to fulfill the cash flow requirements. Given that sources of funds have different interests and terms, we can set up a mathematical optimization problem for this work. We will use the company’s wealth \(v\) in June as a objective function and the following decision variables:

• \(x_i\) : the line of credit in month i(=1,2,3,4,5)
• \(y_i\) : CP issued in month i(=1,2,3)
• \(z_i\) : the excess funds in month i(=1,2,3,4,5,6)
• \(z_6 = \nu\)

\[\begin{align} &\max_{x_i, y_i, z_i} \nu \\ s.t. &\\ &x_1 + y_1 \quad\quad\quad\quad\quad\quad\quad\quad\quad\quad – z_1 = 150 \\ &x_2 + y_2 \quad\quad – 1.01x_1 + 1.003z_1 – z_2 = 100 \\ &x_3 + y_3 \quad\quad – 1.01x_2 + 1.003z_2 – z_3 = -200 \\ &x_4 – 1.02y_1 – 1.01x_3 + 1.003z_3 – z_4 = 200 \\ &x_5 – 1.02y_2 – 1.01x_4 + 1.003z_4 – z_5 = -50 \\ & \quad – 1.02y_3 – 1.01x_5 + 1.003z_5 – \nu = -300 \\ \\ &x_i, y_i, z_i \ge 0, \quad x_i \le 100 \end{align}\]
We can solve this problem by using either Excel or R.

Excel

Making a table for given problem is followed by setting Excel Solver with initial guesses (yellow range of F4:J4, F5:H5).


The above table consists of cash flow requirements, funding actions or instruments (the line of credit and CP), the associated cost and proceeds, and finally wealth or excess funds. We add cost and proceeds from funding and excess funds. Excel formula will be explained later.

Given a table for optimization problem, Excel Solver is set as follows.



The answer for this problem is also presented in their book as follows. Output from Excel Solver delivers the same result as their book’s answer in the following way.

R code

Using ROI, ROI.plugin.neos R packages, we can solve the above linear optimization problem using NEOS server.

Refer the following post for more details of ROI, ROI.plugin.neos R package.

The following R code implements the same asset-liability cash flow matching problem with the help of ROI, ROI.plugin.neos.



We can easily find that results from R code is the same as the results of Excel and Cornuejols & Tütüncü’s book.

Concluding Remarks

From this post, we solve the simple ALM model but we think that it is not easy because this problem has many important aspects. Among them are flow and stock relationship, timing convention of cash flows, set-up for linear optimization problem and so on. Although this problem seems so simple, it has 14 decision variables which it is not small number.

Of course, using NEOS server for this problem may not be efficient because locally installed other R packages might deliver the fast and better performance. But when we deal with stochastic version of this kind of problem, the number of decision variables grows tremendously and the size of equations increases exponentially. In this case, the NEOS server is a good choice when we do not have an expensive commercial solver. \(\blacksquare\)