Loan Modification Waterfall

This post describes how to use an Excel-based score to execute loan modification waterfall calculations in QBO.

Or, "the wonders of VLookup".

Briefly, the goal of a loan modification waterfall is to adjust the terms of a mortgage to get a delinquent borrower to a point where they can keep up with their mortgage. Rules vary widely, but boil down to something like this:
  • Extend the term (months remaining) to reach lower payments over a longer period of time
  • Reduce the interest rate to lower the payments
  • Forgive part of the balance owed, particularly if the borrower is "underwater" (owes more than the house is worth)
The first consideration is, "what can the borrower afford"? This is usually measured in a debt-to-income ratio (DTI), defined as:

Monthly Payment/Monthly Net Income

A typical target considered "affordable" is a DTI of 43%.  That is, given a monthly income of $10,000, the borrower can be expected to afford a monthly payment of $4,300.

When considering a term extension, the rule might be, "extend the term of the loan until you reach an affordable payment, or a maximum of 40 years".  A programmer might code this easily:

var balance = 370000; // remaining balance on the loan
var rate = 7.5%;
var netIncome = 10,000;
var remainingMonths = 360;
var payment = calculatePayment(balance, rate, remainingMonths);
var dti = payment / netIncome;
while ((dti > 43%) and (remainingMonths < 480)) {
  remainingMonths = remainingMonths + 6; // half-year adjustements
  payment = calculatePayment(balance, rate, remainingMonths);
  dti = payment / netIncome;
}
 
This could be programmed in any language. The downside is, if the logic changes, you need to have a programmer adjust the code.

The goal of QBO is to enable non-programmers to maintain such logic. If we can model this easily in Excel, then an Excel guru can maintain the logic, without going back to IT to adjust it every time a client tweaks their requirements.  (No VBA macros -- that's just programming by another name!)

Enter VLOOKUP. Here's the gist:
  • Create a Term column containing each of the possible terms (months) you want to consider
  • Add a Payment column, calculating the monthly payment given the term next to it
  • Add DTI range columns: 
    • first column: ={Previous rows}:{DTI second column}
    • second column: ={Payment cell}/NetIncome
Now we're ready to ask Excel which row is the "best match" for our target DTI of 43%: 

VLOOKUP(43%, A3:D23, 4, TRUE)

Specifically:
  • 43% is our lookup value: the value to find the best match of in our lookup table
  • A3:D23 is our table array: all the values of our lookup table (excluding the header rows)
  • 4 is our column to pull a value from. 4 would be Payment, 3 would be Term.
  • TRUE indicates we're doing a range lookup, which means our lookup value must fall between the values of column 1 and column 2.
That last bit is important: your lookup table must have it's range columns as the first two columns in your table array. That's why we couldn't list the Term column first. Moreover, the values must go in ascending order. The higher the term, the lower the DTI, which means we must start with our largest term (to get our smallest DTI).

These tables may be as big as you'd like. For example, you could:
  • Adjust the term month-by-month, instead of 6 month increments
  • Continue the table to terms shorter than 360 months (heck - go to a 1 month mortgage if you wish!)
  • Extract our Terms with a slightly different formula: VLOOKUP(43%, A3:D23, 3, TRUE)
    • The Term column is column 3 in our table, as opposed to Payment which was column 4
Below is a working spreadsheet model with several variants of VLOOKUP usage.

LossMit.Waterfall






Comments