Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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.

 1234567891011121314151617 [Set Objective]    $K$9 [To]    Max [By Changing Variable Cells]    $F$4:$J$4,$F$5:$H$5 [Subject to the Constraints]    $F$4:$J$4 <= $D$4    $F$4:$J$4 >= $C$4    $F$9:$K$9 >= $C$9 [Make Unconstrained Variables Non–Negative]    Checked Colored by Color Scripter cs

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.

 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970 #========================================================## Quantitative ALM, Financial Econometrics & Derivatives # ML/DL using R, Python, Tensorflow by Sang-Heon Lee ## https://kiandlee.blogspot.com#——————————————————–## A simple Asset/Liability CF Matching problem#========================================================# graphics.off()  # clear all graphsrm(list = ls()) # remove all files from your workspace library(ROI)library(ROI.plugin.neos) # Gross Interest RatesRx = 1.01    # Xi : the line of creditRy = 1.02    # Yi : CP 90dRz = 1.003   # Zi : excess funds # decision variables# –> x1,2,3,4,5, y1,2,3, z1,2,3,4,5, v # Left hand side matrixv.LHS <– c(     1,0,0,0,0,    1,0,0,   –1,0,0,0,0,  0,   –Rx,1,0,0,0,    0,1,0,  Rz,–1,0,0,0,  0,    0,–Rx,1,0,0,    0,0,1,  0,Rz,–1,0,0,  0,    0,0,–Rx,1,0,  –Ry,0,0,  0,0,Rz,–1,0,  0,    0,0,0,–Rx,1,  0,–Ry,0,  0,0,0,Rz,–1,  0,    0,0,0,0,–Rx,  0,0,–Ry,  0,0,0,0,Rz,  –1)m.LHS <– matrix(v.LHS, nrow = 6, byrow = TRUE) # v (14th decision variable) is the objective functionlp_obj <– L_objective(c(rep(0,13),1)) # LHS * X = RHSlp_con <– L_constraint(    L   = m.LHS,     dir = rep(“==”, 6),    rhs = c(150,100,–200,200,–50,–300)) # Lower & Upper bounds for decision variableslp_bound <– V_bound(    li = 1:14, ui = 1:14, lb = rep(0,14),     ub = c(rep(100,5), rep(Inf,9))) # Set Problemlp <– OP(objective = lp_obj,          constraints = lp_con,          bounds = lp_bound, maximum = TRUE) # Solve Problemopt <– ROI_solve(    lp, solver = “neos”,     method = “mosek”,     email = “your email address”) # Printcat(“\nResult for simple A/L CF Matching \n\n”,    “Objective Function Value = “,     round(opt$objval,4), “\n”, “Decision Variable Xi = “, round(opt$solution[1:5],4), “\n”,    “Decision  Variable Yi = “,     round(opt$solution[6:9],4), “\n”, “Decision Variable Zi = “, round(opt$solution[10:14],4)) Colored by Color Scripter cs

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

 1234567 Result for simple A/L CF Matching   Objective Function Value =  92.4969  Decision  Variable Xi =  0 50.9804 0 0 0  Decision  Variable Yi =  150 49.0196 203.4344 0  Decision  Variable Zi =  0 351.9442 0 0 92.4969 Colored by Color Scripter cs

### 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$$