After the housing bubble crash, defaults on mortgage loans became a big problem for financial institutions. A large number of homeowners either could not or would not continue to make payments on homes that were “underwater” (home value less than the amount owed on mortgages and other liens).
After 90 days of no payments, a Notice of Default (NOD) was sent out to the homeowner. Also, regulators required that the financial institution estimate potential losses on all properties after 90 days of no payments, so a valuation of the property had to be made. This determination of potential losses got a little tricky if there were second loans (subordinate to the primary, first mortgage). For example, these are the inputs for a sample decision:
In this case we own a second loan of $380,000. There is a first mortgage for $200,000, but we do not own that loan. The property has been valued at $550,000, but we have estimated that it will cost 10% of the market value to make repairs and other expenses to sell the property. Since we are in a subordinated position, we would have to pay off the first mortgage to foreclose. To estimate our potential losses we have two options. We could walk away from our second and loss $380,000, or we could buy out the $200,000 first loan from the other institution, foreclose, and take only an $85,000 loss. For regulatory purposes, we only have to declare a potential loss of $85,000. Before I go on, here are the named ranges:
Before I could start the graphics for the decision tree, I determined that there were 11 possible outcomes, to the data entered and only the correct outcome can be TRUE:
The AND() function was used for all the outcomes above, as follows:
This way with a lookup function, searching for “TRUE”, I can give the person valuing the property the logic behind the dollar answer. I broke the profit/loss formula down into two separate formulas, depending upon if our initial loan is a First or Second. I could have put them together with and IF function for a very large formula, but breaking them out separately is easier to follow:
VBA shows the correct path. You can also hide all or show all. Try it.
Download workbook “DecisionTree” from:
Downloads Written in Excel 2013