Records Excluded when processing Payment Files - Check Underpayment

Created by Ted Lucas, Modified on Wed, 27 Mar 2019 at 10:11 AM by Ted Lucas

A common reason for a Mortgage Tape File to have records excluded is when the Check Amount is not large enough to cover the Payments for all of the accounts in the file.  When this happens, when it gets to the last rows of the Mortgage Tape file, all of the money has already been used.  

The reason column will say "Check Underpayment" for the accounts in the bottom rows of the Mortgage Tape, but it may simply say "Check" if the Reason column isn't wide enough for the entire error.

In the above example, the entire Check Amount has been applied, but there are 2 records that were excluded because there was not enough left in the Check Amount.

Rounding Errors

One possible reason for the Check Amount not covering all of the checks is when rounding is handled differently for each individual account when determining the Check Amount than the rounding that appears in the Mortgage Tape.  In the Mortgage Tape, each account is rounded to the penny.  Sometimes in Excel there may be additional numbers after the penny that are not obviously visible.  

In the above example, the Due column is displaying 75 cents, and this is what is going to appear in the Mortgage tape.  In Excel, the 75 cents is really 75 1/4 cents.  Some numbers may be rounded up, and some may be rounded down, but if the Check Amount is determined from these values in Excel, those fractions of cents added together may cause the total to not match.

One of the simplest ways to get the correct total is to use Notepad to remove the hidden digits.  Open Notepad in Windows.  Copy the entire column from Excel into Notepad, and it will ignore the hidden digits.  


Then copy from Notepad into Excel (you can copy over the existing amounts, or into a different Column, Sheet or Workbook, depending on your process), and then re-total it there.  This will give you the correct total for the Mortgage Tape File.