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!)