Chapter 5 – Loan arrears audit

This module is used to check the calculation of loan arrears balances on every loan account and highlights exceptional loans, such as loans which will not be paid off in the specified repayment period.

0. Introduction

0.1. Description of audit tests

0.2. Suggested sequence and frequency of using this module

1. Default Arrears Parameters

2. Arrears Dump

3. Arrears Audit

4 Print Arrears Audit

5. Infrequent Repayments Test

6. Print Infrequent Repayments Test

7. Clear Arrears Audit Files

0. INTRODUCTION

0.1. Description of Audit Tests

This audit module examines every loan on file and re-calculates its loan arrears position. CAPS then lets you print ten different reports highlighting loan accounts which may need investigating for different reasons.

This audit is slightly complicated because the calculation of loan arrears needs a 'starting point'. That is why you must first run a Data Capture of the loan accounts.

The 'loan arrears' is a kind of single entry 'memorandum account' attached to every loan account which works in the following manner:

Whenever a loan repayment falls due, the Financial Package automatically adds the amount to the 'loan arrears' figure.

Whenever the member makes a repayment, the amount is subtracted from the 'loan arrears' figure.

The effect of this is that at any time the Financial Institution can print their 'loan arrears' report and see which members are behind in their repayments and by how much.

Note, however, that the actual arrears figure is not updated until the Financial Institution runs their programs.

Now, to re-calculate the 'loan arrears' on a loan, CAPS needs details of all the transactions on the account since the loan started. This is usually impossible, since most Financial Institutions regularly purge transactions off the system.

To allow for this, you must execute a Data Capture of the loan arrears position on all loan accounts. The Data Capture holds a record of the loan arrears position at that point in time. Later, when you are ready to do the audit, CAPS uses the Data Capture as a starting point, and uses the transactions since the date of the Data Capture to re-calculate the up-to-date loan arrears. Because CAPS calculates the arrears by taking the loan arrears position at the time of the Data Capture and adding subsequent transactions, you must wait at least a month (preferably longer) after running the Data Capture before running the audit. This is so there is time for transactions to actually occur in the member's account. See Figure 1 below.

Figure 1 shows how CAPS works out the Calculated Repayment Date at the time of the audit using the actual repayments made in the audit period. CAPS can then use this information to calculate the arrears position of the loan. See more detailed information in the Arrears Data Capture and Arrears Audit section of this chapter.

After re-calculating the loan arrears for each loan, CAPS compares its calculated figures with those shown in the Financial Institutions loan records. Any discrepancies are then printed.

CAPS also carries out a number of other checks and prints the results on the following reports:

Report 1 - New Loan with unreasonable effective date

One way to stop loans appearing on the 'loan arrears' report is to enter the first repayment date (also called the 'next due date') a long way into the future. This report highlights those loans which have an unusual 'next due'.

Report 2 - Loans with no, or incorrect repayment frequency

In the Financial Package, if you don't enter a loan repayment frequency code (i.e. F = fortnightly, W = weekly etc.) when setting up the loan details, then loan arrears are never calculated for that loan. This report prints those loans without a repayment frequency, or with a code the Financial Package does not recognise.

Report 3 - Loans with no security

The Financial Package includes a field to enter details of the security over the loan. Loans that are not secured and are over a specified balance will be reported.

Report 4 - Loans with unreasonable arrears in advance

One successful fraud has been to get a loan for (say) $30,000 over five years, and then make an advance repayment of (say) $20,000 which covers three years repayments. Then skip the country! It is very difficult to trace someone after three years. This report highlights those loans with 'arrears in advance' more than a nominated period.

Report 5 - Loans with unreasonable repayment period

In many cases the law limits the period over which a loan may be made. Increases in interest rate, and accumulated loan arrears may combine to make the final repayment date longer than that allowed by law.

In some cases CAPS may even highlight loans which will never be repaid. The interest charges could be bigger than the repayments.

Report 6 - Loans with some records missing

If you are employed by the Financial Institution and you have a fraudulent loan, a potential way to avoid detection by CAPS is to simply wipe all details of the member record and transactions record from the computer file. However this report will highlight those loans which exist on the 'Data Capture' but which have some vital information on the member or transaction records missing. Note that this is one reason for doing a Data Capture as soon as possible after getting CAPS installed.

Report 7 - Loans with unequal book and calculated balances

This report highlights those loans where the loan arrears figure calculated by CAPS does not agree with the loan arrears figure currently stored by the Financial Package.

Report 8 - Summary aging report

This report summarises the loan arrears position. It gives you a good overview of the arrears situation and gives you a basis for a provision for doubtful loans.

Report 9 - Detail aging report

This report lists those loans calculated by CAPS to be in arrears. They are listed according to how many months they are in arrears.

Report 10 - Purpose Code Change report

This report lists all loan accounts for which the Purpose Code has changed since the Data Capture.

The other useful function of the Arrears Module is the Infrequent Repayments Test. This test allows you to locate loan accounts that either have been repaid with inadequate frequency or inadequate amount, within a specified time frame. The test will also locate any new exceptions if requested.

0.2. Suggested sequence and frequency of using this module

For the internal auditor, the main use of this Loan Arrears Audit Module is to check for possible fraud, or errors in recording a loan which could cause difficulties if not detected early. The internal auditor will probably want to run the module every three months.

For the external auditor its main use is to test the accuracy of the client arrears calculations as the first step in evaluating the provision for doubtful loans. However you will need all transactions on file since the last time you ran a Data Capture for this module to work.

Therefore you'll have to run the audit and generate a new 'Data Capture' if the Financial Institution decides to purge any transactions.

This is a simple audit module to use, but it is essential to have done the Data Capture first. The sequence is as follows:

Step  
1 Enter default parameters for all future arrears audit runs using step 1 - DEFAULT ARREARS PARAMETERS. You don't have to use this step every time. These parameters should remain static for a considerable time.
2 Run a Data Capture of the Financial Institutions loan files using menu option 2 - ARREARS DATA CAPTURE. This is usually done overnight.
3 After a suitable period, from perhaps one to four months later, use menu option 3 - ARREARS AUDIT to run an audit of the loan arrears. This is usually ran overnight.
4 As soon as possible or the next morning use menu option 4 - PRINT ARREARS AUDIT to print out the results of the audit.
5 As soon as you are certain that the results of the arrears audit are satisfactory (meaning that you won't need to run menu option 3 - ARREARS AUDIT again) you should run another Data Capture using menu option 2 - ARREARS DATA CAPTURE. You should do this the same day. The Data Capture has then been run in preparation for your next arrears audit. You may want to use menu option 1 - DEFAULT ARREARS PARAMETERS first to fine tune your Data Capture process.
6 When you are satisfied the arrears audit is complete, and you have no further use for any of the CAPS files generated during the audit, you can select Menu option 5 - CLEAR ARREARS AUDIT FILES to release the space taken up on the Financial Institutions computer system. Note that the Arrears Data Capture file and default parameters will not be cleared using this menu option.

1. DEFAULT ARREARS PARAMETERS

This step gives you control over the preset Default Arrears Parameters. Normally you don't have to change this after the initial set up. There are two screens of Default Arrears Parameters.

Screen 1 appears as follows:

<1> New Disbursement Option

If a new disbursement is made to an existing loan, the Financial Institution has the option to either clear the old arrears or keep the old arrears. Enter [C] for clearing old arrears, or [K] for keeping old arrears.

<2> Maximum Repayment Period

Enter the normal maximum repayment period for loans in years. This is used only as the default. You can define the maximum period for individual account levels when you run the Arrears Audit. If you enter 0 (zero) for this field, all loans will be reported. This is useful when you want to import this report into other software packages for manipulation.

<3> Minimum Disbursement Amount

Sometimes, the Financial Institution needs to post charges to loan accounts, using the same transaction codes that are used for loan disbursements. Since most of these charges are small compared to disbursements, you can define a minimum disbursement to let CAPS distinguish between the two types of transactions.

<4> New Disbursement Code

Enter the transaction codes that are used to disburse loans.

<5> Credit Code

Normally most credit transactions affect the arrears position of a loan, enter those credit transaction codes that DO NOT affect the arrears.

<6> Debit Code

Normally most debit transactions do not affect the arrears position of a loan. Enter those debit transaction codes that DO affect the arrears, e.g. debit arrears adjustment. If the code for a Loan Redraw is different than that for a New Disbursement, enter that code here.

<7> Visa Level

Enter account levels that are used for visa accounts. CAPS will not attempt to calculate arrears for these accounts.

<8> LOC Level

Enter account levels that are used for line of credit accounts. CAPS will not attempt to calculate arrears for these accounts.

<9> Security Code

Enter security codes that indicate a loan with security. All loans that have no security code or have a security code that does not match one listed in this field, will be subject to the 'Maximum Loan Balance for Unsecured Loans' test. Normally, secured loan codes are: M – Motor vehicle; R – Real Estate; F – Funds; L – Life insurance; G – Guarantee; O – Other.

<10> Off Prl Code

Enter those payroll deduction codes that indicate that the client does not have a payroll deduction. Many clients will have no payroll deduction code set on their client record. Some will have one set. For those that do, most codes indicate an actual deduction, but some don't. Input here those few codes that do not indicate a deduction. An '@' symbol will be displayed next to those arrears discrepancies that do not have a payroll deduction code or have one that indicates no deduction.

<11> Field Verification (window)

This set of tests is optional. It allows you to tell CAPS the mandatory fields in various files that must have a value for a loan account. Note that CAPS does not check the content of the field, it simply checks that data exists. For instance you might want to check that every loan account has a security code. You would need to check that the thirteenth attribute of the ACCOUNT file record has a value by entering the information as on line one of field eleven on the screen above.

To obtain information on where certain data is recorded and an explanation of what attributes, values and sub-values are, either consult your EDP department or contact BDO CAPS.

File

Enter ‘TRAN’ for the Transaction file, ‘CLIENT’ for the Client file, ‘ACCOUNT’ for the Account file, or ‘APPLIC’ for the Application file.

Attribute

Enter the attribute number of the field you want to check.

Value

Enter the value number of the field within the attribute you want to check. If the whole attribute needs to be checked, enter [1].

Sub-value

Enter the sub-value number of the field with the value you want to check. If the whole value needs to be checked, enter [1].

Description

Enter a short description for the field to be checked. This description will appear on the audit report.

<12> Arrears Audit Date

In most cases this field should be left blank. You can enter a date in this field if you would like to backdate your arrears audit. However if you choose to do this CAPS will only back date the aging reports to the date you request, i.e. CAPS will age the accounts in the summary and detail aging reports to this date.

NOTE: If you decide to set a date in this field remember to remove it before the next audit. If your audit has not produced the results you expected, the most likely cause is that a date has been set incorrectly in this field.  

<13>Arrears Next Due Date Method

Enter [Y] to use the ‘next due date’ method or [N] to use the ‘arrears balance’ method for calculating the aging of arrears. The Balance method takes the arrears balance (Dollar amount in arrears), divides it by the repayment amount and multiplies it by the number of days in the repayment period (eg Fortnightly repayment = 14 days). The ‘Next Due Date’ method simply subtracts the calculated Next Due Date from the date of the audit run (or the Arrears Audit Date above) to get the number of days in arrears.

Result

CAPS will file the parameters for future Arrears Audits.

2. ARREARS Data Capture

This menu option generates a 'Data Capture' containing details of all loan accounts and the current state of their arrears. You must do this menu option at the start of every audit period. It gives CAPS a 'starting point' to use for the audit.

Under no circumstances begin the Arrears Data Capture before all the transactions for the day have been processed (ie. Do not run the Data Capture during the day. Any transactions for the day that occur after the process may cause an arrears discrepancy for that account). The process should be started between the “Finish” process  and midnight.

It is not critical when you run the Arrears Data Capture. The process just creates a starting point from which CAPS can determine the current arrears position of a loan and then after repayments have been made in subsequent months a calculated arrears position can be determined in the Arrears Audit.

CAPS must assume that the arrears position as recorded by the Financial Institution at the time of the Data Capture is accurate. A typical process of running a regular Arrears Data Capture schedule would be: run the process, wait 3 months, run the audit, run the process, wait 3 months, run the audit.

Note - Do not run the Data Capture in Test Mode. You achieve nothing, and you run the risk of overwriting an essential Data Capture with useless 'test' data.

Program 'Arrears Data Capture' requires running overnight.

Confirm (Y/N)

Enter [Y] to add it to the overnight queue.
Enter [N] to cancel the request, and return to the Arrears menu.

Result

CAPS sets up an instruction for 'Arrears Data Capture' to be run overnight under 'NIGHT AUDIT'. (See CAPS UTILITIES, Section 2 - NIGHT AUDIT for more details).

NOTE: The results of the arrears audit will be meaningless unless you have waited at least a month between this process and running the audit. The Data Capture records the next due date and partial payment position for each loan account. The audit, at a later stage, takes these figures, adjusts them with payments to arrive at a new arrears figure which is then compared with the arrears position currently recorded on file. 

3. ARREARS AUDIT

This menu option instructs CAPS to independently re-calculate the arrears position for loan accounts and lets you enter some parameters it needs to do this.

CAPS will first prompt you with a screen as follows :

<1> Latest 1st repayment after funding date (days)

The Financial Institution should have a policy stating the maximum number of days allowed between the time the member takes out a loan and when they have to make their first repayment.

Enter the number of days here.

<2> Max Reasonable Arrears in Advance

Enter the maximum allowable arrears in advance in months. If the member’s payments exceed this amount they will be reported.

<3> Last Repayment Before

In order to further filter the ‘Maximum Reasonable arrears in advance’ test you can enter a date to restrict this report to show only accounts that have not been paid recently. Enter a last repayment date cut-off limit. All accounts with a last repayment date before this date (i.e. they are not being paid regularly) but are in advance beyond the limit in field <2> will show up on the report. If you leave this field blank then CAPS will report all accounts as per field <2>.

For example, if you were running the audit on 25/05/98 then you may choose to enter a date here of 01/04/98. This means CAPS will report all accounts which are in advance as per field<2> but which have not made a repayment since 01/04/98. Any accounts in advance (as per field<2>) which have made repayments since 01/04/98 will not appear on the report. Only those in advance and made repayments prior to 01/04/98 will appear on the report.

<4> Max Loan Balance for unsecured loans

Enter the maximum loan amount allowable for unsecured loans. If their balance exceeds this amount they will be reported. Unsecured loans are those that either do not have a security code or have one other than the ones listed in your default parameters. 

<5> Report Sorting Method

Enter [L] to sort the reports in account level order.
Enter [M] to sort the reports in member number order.

<6> Level to exclude (window)

Enter the accounts levels that you wish to exclude from testing.

<7> Other Maximum Repayment Periods (window)

The default maximum repayment period was set by you in your default arrears parameters. If there are loans that have a maximum different to the default, list them here. Enter loan levels (i.e. 2 for L2) and their corresponding loan repayment periods in years. If a loan level is not entered here the maximum repayment period will default to the Default Maximum Repayment Period.

<8> Purpose Code Test

Indicate [Y] if you would like CAPS to check for Purpose Code changes since the Data Capture was run. CAPS will report any accounts which have a Purpose Code which is different to the Purpose Code previously recorded. Enter [N] if you do not wish CAPS to run this test.

- If you are in Test Mode, CAPS asks:

You are in test mode, do you want to start ‘Arrears Audit’ now? (Y/N)

Enter [Y] if you want to conduct a quick test run by auditing only a small number of loan arrears account balances.

Enter [N] if you don't want a test run, but want to audit all members loan account arrears balances.

- If you answer [Y], CAPS asks:

Do you want to process individual accounts?

-If you answer [Y], CAPS prompts with:

Account to Process

Enter the loan account number you would like to process.

-If you answer [N] , CAPS asks:

Records to process

Enter the number of members loan accounts you want CAPS to use as a test sample.
CAPS displays messages to let you know what it is doing. When finished it returns to the Arrears menu.

- If you answer [N], or if you are not in Test Mode, CAPS asks:

Do you want ‘Arrears Audit’ submitted for overnight running?

Confirm (Y/N)
Enter [Y] to add to overnight queue.
Enter [N] to cancel the request, and return to the Arrears menu.

Result

If you chose a test sample only, CAPS immediately runs 'Arrears Audit'. If you chose a full live sample, CAPS sets up an instruction for 'Arrears Audit' to be run overnight under 'NIGHT AUDIT'. (See CAPS UTILITIES, Section 2 - NIGHT AUDIT for more details).

CAPS uses details of the repayment transactions posted to each members loan account to independently re-calculate the arrears position as at the audit date. It uses the saved arrears position as a starting point.

Working with the Transaction file CAPS tries to read the loan account from the Data Capture file. If the account is on the file then CAPS obtains the arrears position recorded by the Financial Institution at the time of the Data Capture. If the account is not on the file and the account has been opened since the process date, then CAPS obtains the information it can to perform some audit tests. In these cases CAPS will not calculate arrears for these accounts due to insufficient information. If an account is not on the Data Capture file and it was opened prior to the process date, then an error is reported as CAPS expects these accounts to be on the Data Capture file.

The results of the following tests are stored on file for use by the Print Loans Arrears Audit program (Menu option 4).

  • No or illegal repayment frequencies
  • Calculated and book arrears unmatched
  • Summary of aging loan arrears
  • Some record data missing
  • Accounts with unreasonable effective dates
  • Detail results of aging arrears
  • Loans with no security
  • Arrears in advance
  • Loans with long repayment period
  • Loans with purpose code change

4. PRINT ARREARS AUDIT

This option lets you print the results of the arrears audit.

CAPS shows you a list of 10 reports you can print out. Enter [Y] if you do want to print the report, (N) if you don't. The reports are as follows :

Reports 1 to 6

Enter [Y] to print the report or [N] if you do not want it printed.

Report 7

Loans with unequal book and calculated balances
- if you answer [Y] for this report, CAPS then prompts:

New Disbursements (Y/N)

Different Financial Institutions have different policies for loan repayments at the beginning of a loan. Because of this, the CAPS calculated loan arrears position for new loans often differs from the Financial Institutions figures. You might not want these new loans printed out.

Enter [N] if you want the report to exclude new loans.

Advance

Enter [N] if you want the report to exclude those loans which have had repayments made in advance. You may only be interested in loans that are actually in arrears and therefore which could affect the provision for doubtful loans.

Minimum Discrepancy

Enter the minimum discrepancy required for an unequal book and calculated arrears report to be made.

If this report is unusually large, and the CAPS audit figures are consistently larger than the client (book) figures by the same number of periods for accounts with the same repayment frequency, investigate the possibility that transactions have been purged after the Data Capture date. If the transactions after the Data Capture date have been purged, the Data Capture and audit will have to be rerun.

Adjustment to Report 7

Enter [Y] to only report accounts where CAPS has calculated an arrears discrepancy (unequal book and calculated arrears balance) where the loan is in advance and the book arrears figure is less than the CAPS calculated arrears figure.

Report 8

Summary aging report

Report 9

Detail aging report - this report ages the arrears on individual loan accounts. CAPS will print loans with arrears balances older than the time you choose here:

  • Up to 1 month (Y/N)
  • 1 to 2 months (Y/N)
  • 2 to 3 months (Y/N)
  • 3 to 6 months (Y/N)
  • 6 to 9 months (Y/N)
  • 9 to 12 months (Y/N)
  • Over 12 months (Y/N)

Enter [Y] if you want the loans listed.
Enter [N] if you do not want loans listed.

NOTE: it is advisable to check the Summary aging report before printing out the detailed aging reports. There are often a large number of loans of little significance which will appear in the first two categories.

Exclude Interest Stopped Accounts in Detail Aging

- Indicate [Y] if you want to exclude interest stopped accounts from the detail aging reports. Enter [N] if you wish to include them.

Report 10

Purpose Code Change report.

Print Password

Enter your print password. No user can print a report unless they have their print password. The user will be given two opportunities to enter the correct password or CAPS will automatically shut down. This feature provides security to reduce the likelihood of unauthorised users gaining access to CAPS reports.

You must collect the print out immediately. Do not leave at the printer.

Result

CAPS uses the following files to print the reports you selected:

  • No or illegal repayment frequencies
  • Calc and book arrears unmatched
  • Summary of aging loan arrears
  • Some records missing
  • Accounts with unreasonable effective dates
  • Detail results of aging arrears
  • Loans with no security
  • Arrears in advance
  • Loans with long repayment period
  • Purpose Code changes

Please refer to the example at the end of the chapter.

5. INFREQUENT REPAYMENTS TEST

The Infrequent Repayments Test allows you to locate loan accounts that have been repaid with inadequate frequency or inadequate amount, over a specified time. It can locate all infrequent or only "newly" infrequent.

If you require only new infrequently repaid accounts, input a start date for period one, else leave it blank and it will list all infrequently repaid accounts. If Start Date is given, CAPS will use that period to calculate the account’s previous status. An account that was infrequent in the first period will not be selected regardless of it’s status in the second period because it is assumed it has been reported in a previous run.

An account need only fail one of the two tests to be selected as infrequent.

1. Start Date

Input the start date of the first period (ie [01/10/89]). If this field is left blank, CAPS will automatically assume you want all infrequently repaid loan accounts (as judged in the second period). If you enter a date here CAPS will assume you only want newly infrequent repaid loan accounts, ie. Accounts which have only become infrequent in the second period.

2. Middle Date

Input the first date of period two. The last day of period one will default to the day before this. Last Disbursement Date must be before this date, or the account will be ignored.

Period 1 will be from Start Date to one day before Middle Date.

Period 2 will be from Middle Date to End Date.

3. End Date

Input the last date of period two.

4. Critical Number

Input the minimum required number of repayments as a percentage for an account to be considered a 'frequent payer'. For instance if you set this to 100% an account will be deemed infrequent if CAPS calculates that there should be 10 repayments in the specified period but there actually were 9, (i.e. 9/10 = 90% < 100%). However if you set this to 85%, the same account would not be selected as the required frequency of repayment (90%) is satisfied.

5. Critical Amount

Input the minimum required total repayment as a percentage for an account to be considered a 'adequate payer'. For instance if you set this to 100% an account will be deemed infrequent if CAPS calculates that there should be $4500 repaid in the specified period but actually only $3980 was paid (i.e. 3980/4500 = 89% < 100%). However if the Critical Amount was set to 87%, the same account would satisfy the requirement.

6. Transaction Codes (window)

Input all of the transaction codes relevant to repayments and reversals of loan accounts.

7. Minimum Balance

Enter the minimum balance required of the loan account before it is reported. This allows you to restrict the report to larger loans most at risk.

8. Minimum Next Due Date

Enter the minimum next repayment due date required of the loan account before it is reported. The NDD on the account must be greater than or equal to this date to meet this criterion. This accurately targets those loans that are not in arrears (and thus not reported in the arrears audit) but are still a potential risk in the future due to inadequate or infrequent repayments.

- If you are in Test Mode, CAPS prompts:

You are in test mode, do you want to start ‘Infrequent Repayments’ now. (Y/N)

Enter [Y] if you want to conduct a quick test run by auditing only a small number of loan accounts.

Enter [N] if you don't want a test run, but want to audit all members loan accounts.
- If you answer [Y], CAPS prompts:

How many records do you want to process?

Enter the number of members loan accounts you want CAPS to use as a test sample.

CAPS displays messages indicating current activity. When completed it returns to the Arrears menu.

- If you answer [N], or if you are not in Test Mode, CAPS prompts:

Do you want ‘Infrequent Repayments‘ submitted for overnight running?

Enter [Y] to add to overnight queue.

Enter [N] to cancel the request, and return to the Arrears menu.

Result

CAPS will produce the report placing the data on file ready to be printed by Print Infrequent Repayments Test.

6. PRINT INFREQUENT REPAYMENTS TEST

This step simply prints the details of the accounts selected in the Infrequent Repayments Test. If only new infrequently repaid accounts were looked for, CAPS will print the reasons for first passing and then (In the second period) failing the test. Otherwise CAPS will only print the reason for failing the test.

Print Password

Enter your print password. No user can print a report unless they have their print password. The user will be given two opportunities to enter the correct password or CAPS will automatically shut down. This feature provides security to reduce the likelihood of unauthorised users gaining access to CAPS reports.

You must collect the print out immediately. Do not leave at the printer.

Result

CAPS will have printed the results on the printer. Please refer to the example at the end of the chapter.

7. CLEAR ARREARS AUDIT FILES

This allows you to clear all files relating to the arrears audit.

CAPS prompts:

Are you sure you want to clear all arrears audit files?

This is just a warning and a check to make sure you really know what you are doing. If you answer [Y], CAPS immediately clears all arrears audit files. They cannot be recovered. If you answer [N] CAPS returns you to the Arrears menu without clearing anything.

Result

CAPS clears all of the Arrears files ready for re-use.