This case provides a dramatic illustration of how Case Data Management and the Forensic Data Engine can cut costs, increase profitability and produce superior results in a situation that would normally call for extensive awkward effort with manual or electronic spreadsheets...

Case Scenario
An unexpectedly high number of non-performing loans at a financial institution prompted an investigation which revealed that many of the loans had been advanced with improper security or, in some other way, had contravened institutional or regulatory policy. The loans emanated from different loan officers over a period of years and although there was a question of fraud, the institution took the view that its auditors should have detected the contravening loans, however caused. Litigation ensued and our task was to quantify the damages i.e. calculate the principal and interest forgone on the non-performing loans. To do this we needed to create a financial model of the loan portfolio: define the requirements; develop alternative solutions; perform a cost/benefit analysis; and implement the selected solution.

Loan Portfolio Financial Model
The model would need to accommodate several key requirements:

• Liability Dates. A key issue for the trial (or settlement negotiation) was the length of time that would have been required to effect changes if the auditors had detected the contravening loans. Only after this time period had passed would the auditors be responsible for the financial damages. We refer to this as the “liability date”. Thus, any amounts advanced (or repaid) after the liability date would be added to ( or subtracted from) principal damages. Similarly, interest damages would be calculated on advances  (which includes interest charges posted to loan accounts) and payments after the liability date. The liability date could be different for different groups of loans because of the differing lengths of time that might be required to replace the different loan officers or to effect a policy change at the financial institution. The model would need to accommodate these variables in a manner that would allow rapid and easy recalculation of the damages.

• Settlement Date. This is the ending date for the calculation of interest damages which could change during settlement negotiations or a trial.

• Loan Types. The model would need to accommodate different types of loans: revolving lines of credit; fixed term loans; variable rate loans; and fixed rate loans.

• Transaction Types. All transactions fall into three categories: advances; payments; and interest charges. Recoveries were made on some of the loans. These amounts should be treated as payments while the cost of recovery should be treated as an advance.

• Interest Rates and Calculations. The model would need to accommodate the variation of interest rates over the different types of loans (e.g. variable vs. fixed rate) and over time.

• Data Volumes and Formats. Loan, transaction and interest rate information was stored in the financial institution's computer systems. To avoid transcription error and excessive data entry time the model would need to accept the data in electronic format and economically convert it to a form suitable for the calculations.

• Exclude/Include Loans. The gray area between contravening and non-contravening loans could lead to some loans being excluded from the calculations as a result of negotiations or at trial. The model would need to allow for rapid recalculation in these circumstances.

• Report. A report would need to be produced from the data listing each loan, the amount of forgone principal and the amount of forgone interest from the liability date(s) to the settlement date. The report should be ready for printing within a few minutes of changing any of the parameters or assumptions.

• Integration with a Document Management System. Loan and transaction documentary evidence was scanned into a document management system. It would be desirable if document indexes and types could be referenced by the financial information in the model. This would be useful when determining whether to exclude or include loans.

The manual solution was quickly ruled out because of the amount of labour required for initial data entry and calculations, the potential for human error, the difficulty of verifying the calculations without duplication of effort, and the inability to recalculate quickly based on changing liability dates. There was also no way to integrate the document management system.

Electronic spreadsheets were also ruled out for three main reasons: the use of different interest rate tables for different loans would require a complex macro (program) and/or slow inter-spreadsheet linkages for the interest calculation; the solutions involved duplicating the transaction data among several linked spreadsheets; and changing the liability dates could involve duplicating the entire set of spreadsheets. Though awkward, integration with the document management system was possible.

A relatively simple and reasonably accurate way of estimating the forgone interest was developed for spreadsheets but was rejected because 1) it lacked the flexibility to be quickly redone if the liability dates were different than those assumed in the spreadsheets, 2) the interest rate calculation could be done accurately (without estimation) using a relational database with less effort than the spreadsheet estimation method, and 3) the use of an approximation would necessitate conservative assumptions that would reduce the claimed damages.

The relational database approach seemed to be the best: all of the calculations seemed to depend on relationships between liability dates, loan parameters, interest rate tables and transactions. These relationships could easily be modelled using a standard relational database and the calculations could be implemented with single queries (vs. a repetitive program) in the international ANSII standard Structured Query Language (SQL). In addition, integration with a relational database was a standard feature of the document management system.

The  object-oriented database development environment offered many of the advantages of the relational database/SQL approach, but required much more programming for 1) the calculations, 2) integration with the document management system and 3) an interactive interface for editing the data and the model parameters.

Quantification of Damages
Accordingly, we used the Forensic Data Engine relational database to implement the loan portfolio financial model. (The Forensic Data Engine is a full featured, multi-user ANSI standard SQL relational database developed by ForenSys The Forensic Systems Group specifically for forensic and investigative case data management applications.) The design incorporated data structures to optimise the quantification calculations. This empty database structure was then created. The data were imported from the institution’s computer system and converted into this “normalised” data structure. (For example, in the plaintiff’s computer, the interest rate tables were stored as separate files with annual rates expressed as a percentage and an effective date. In order to simplify the calculations in the query, the annual interest rates needed to be converted to daily interest factors, the factors needed to be associated with an “effective until date” as well as an “effective date” and all of the rate tables needed to be combined into one table).

Next, the report quantifying the damages was designed simply as the standard output of two SQL queries: one for the Principal and one for the Interest. The reports list each loan, its approval date and the amount of principal or interest, sorted by approval date,  with a total of the amounts at the end. Prototypes of the two listings are provided in Appendices I and II. For the technically curious, the details of the database structure are provided in Appendix III, and the full text of the queries with an explanation is found in Appendix IV.

Lower Costs Using Case Data Management and the Forensic Data Engine
The principles of Case Data Management and the Forensic Data Engine cut costs at every stage of the case:

• Data Acquisition: Financial information and document information were quickly imported into the relational database in their native formats. The (time) cost of importing data into many spreadsheets and then integrating them was significantly higher.

• Data Conversion: Financial data were converted to the structure and format of the model with a few simple SQL commands. This step took about one half of the time that would have been required by the repetitive procedures required for multiple spreadsheets.

• Data Integrity: With totally electronic data transfer, no transcription data errors would be introduced. There was also no need to worry about simultaneous updates of multiple appearances of the data in different spreadsheets. In addition, the built-in relational and attribute (field) integrity of the Forensic Data Engine insured economical accuracy  without costly audit trails and procedures that would be required with spreadsheets or manual systems.

• Analysis: The costs of recalculation as well as the costs of developing and implementing the model were significantly lower than those of spreadsheets and manual systems.

• Report: The report could be printed within seconds of a change of parameters instead of requiring an adjournment for recalculation. This cut professional, technical and client hours required and reduced non-billable hours.

• Settlement: Rapid recalculation and presentation of the results led to a faster, more favourable settlement.

• Electronic retrieval, central management, no duplicate data to maintain, relational and entity integrity all combined to ensure easily maintained, error-free data.

• The FDE provided an exact analytical model as opposed to the approximation required using electronic spreadsheets.

• The FDE data and queries were delivered to the defendant for verification as one compact file over the Internet. A member of the IT staff of the defendant was able to verify the standard database structures and SQL queries of the model in two hours.

• The FDE provided a parametric model for real-time recalculation and results presentation. Parameters included interest rates, interest calculation methods, liability dates, the settlement date and the inclusion/exclusion of loans.

• The FDE provided a quick, clear and precise report in response to changing parameters. By contrast, the spreadsheet solution was slow, approximate and required justification, explanation and conservative assumptions.

• The fast, accurate and indisputable response of the model to changing parameters sped up the legal process from start to finish, resulting in a more favourable settlement and much lower costs to the client.

APPENDIX I: Principal Report

L.LoanID        L.ApprovalDate   SUM(T.TransactionAmount)
--------------- ---------------- ------------------------

ABC Co.         1992/01/01                      14,000.00
Smith,Joan      1992/01/05                       7,250.00
Some Corp.      1992/01/15                     107,135.73
Person,A.       1992/02/20                      11,000.00
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
                                    TOTALS   5,467,234.99
 
 

APPENDIX II: Interest Report

L.LoanID        L.ApprovalDate   SUM(T.TransactionAmo....
--------------- ---------------- ------------------------

ABC Co.         1992/01/01                       4,987.62
Smith,JA        1992/01/05                       2,325.22
Some Corp.      1992/01/15                      37,135.37
Person,A        1992/02/20                       6,128.42
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
   .                 .                                .
                                    TOTALS   1,027,234.15
 
 
 

APPENDIX III: Database Details
The institution provided transaction histories of each loan account, a loan information file and a series of interest rate tables. This information was extracted, edited and converted into a relational database with the following structure (only relevant data items are listed):

Loan table - LoanID, LoanType, RateType, ApprovalDate, GoodOrBad
Transaction table - LoanID,TransactionType,TransactionDate, TransactionAmount
Rate table - RateType,InterestRate,RateStartDate,RateEndDate
LoanType table - LoanType, LiabilityDate

Each loan is associated with a particular loan type and interest rate table. The interest rate tables provide interest rates and their effective dates. The loan type is used to refer to the “liability date” for that type of loan. GoodOrBad in the Loan table is either 'Good' indicating it is not contravening and should not be considered in the calculations, or 'Bad' indicating it is contravening and should be included in the calculation of damages. TransactionType in the Transaction table is 'Advance', 'Payment' or 'InterestCharge'. InterestRate in the Rate table is a daily interest factor. vSettlementDate is a variable, the date of settlement and the final day of the interest calculation. Compound interest is calculated i.e. interest is charged on outstanding principal and accrued interest. The “liability” dates and settlement date can be changed during the trial or settlement negotiation as needs be.

The following two Structured Query Language (SQL) commands will produced listings of the loans, sorted by approval date (ascending), with their principal balance owing at the settlement date (first command) and the amount of unpaid interest accrued up to the settlement date (second command).
 
 

APPENDIX IV: SQL Query Details
Principal Query
SELECT L.LoanID, L.ApprovalDate, SUM(T.TransactionAmount) = S FROM Transaction T, Loan L, LoanType LT GROUP BY L.LoanID, L.ApprovalDate WHERE T.LoanID = L.LoanID  AND L.LoanType = LT.LoanType AND L.GoodOrBad = 'Bad' AND T.TransactionDate > LT.LiabilityDate AND T.TransactionType IN ('Advance', 'Payment') ORDER BY L.ApprovalDate

Interest Query
SELECT L.LoanID, L.ApprovalDate, SUM(T.TransactionAmount * R.InterestRate * (LMIN(R.RateEndDate, .vSettlementDate) - LMAX(R.RateStartDate, T.TransactionDate)+1)) = S FROM Transaction T, Loan L, LoanType LT, Rate R GROUP BY L.LoanID, L.ApprovalDate WHERE T.LoanID = L.LoanID AND L.LoanType = LT.LoanType AND L.GoodOrBad = 'Bad' AND T.TransactionDate > LT.LiabilityDate AND R.RateType = L.RateType AND T.TransactionDate <= R.RateEndDate AND R.RateStartDate < .vSettlementDate ORDER BY L.ApprovalDate

Explanation of Principal SQL Query
SELECT L.LoanID, L.ApprovalDate, SUM(T.TransactionAmount)
Displays the loan ID, approval date and the sum of advances less payments for a particular loan.

= S
Produces a sum of all the principal amounts at the bottom of the listing - a grand total.

FROM Transaction T, Loan L, LoanType LT
Draws data from three of the four tables which are given short names (T, L, LT) for easy reference.

GROUP BY L.LoanID, L.ApprovalDate
Groups all the transactions for each loan. The SUM above adds up all of the advance and repayment transactions for each member of this GROUP.

WHERE T.LoanID = L.LoanID  AND L.LoanType = LT.LoanType
The WHERE clause specifies how to relate the three tables and which records to select for calculation. This part links the Transaction and Loan tables by the LoanID and links the Loan and the LoanType tables by the LoanType.

AND L.GoodOrBad = 'Bad'
This part of the WHERE clause restricts the calculation to the contravening loans.

AND T.TransactionDate > LT.LiabilityDate AND T.TransactionType IN ('Advance','Payment')
This part of the WHERE clause further restricts the command to those transactions  subsequent to the “liability” date and to ‘Advance’ and ‘Payment’ transactions only (‘Interest Charges’ are not part of forgone principal).

ORDER BY L.ApprovalDate
This sorts the loans by ascending approval date.
 

Explanation of Interest SQL Query
SELECT L.LoanID, L.ApprovalDate,SUM(T.TransactionAmount * R.InterestRate * (LMIN(R.RateEndDate, .vSettlementDate) - LMAX(R.RateStartDate, T.TransactionDate)+1))
Displays the loan ID, approval date and the sum of interest credited or debited on all transactions for a particular loan for all interest rates from the later of the time of the transaction or the effective date of the interest rate to the earlier of the settlement date or the last effective date of the interest rate.

= S
Produces a sum of all the interest amounts at the bottom of the listing - a grand total.

FROM Transaction T, Loan L, LoanType LT, Rate R
Draws the data from all four tables which are given short names (T, L, LT, R) for easy reference.

GROUP BY L.LoanID, L.ApprovalDate
Groups all the transactions and interest rates for each loan. The SUM above adds up the interest amount for each member of this GROUP.

WHERE T.LoanID = L.LoanID AND L.LoanType = LT.LoanType AND L.GoodOrBad = 'Bad' AND T.TransactionDate > LT.LiabilityDate AND R.RateType = L.RateType
The WHERE clause specifies how to relate the four tables and which records to select for calculation. This part of the WHERE clause links the Transaction and Loan tables by the LoanID, links the Loan and the LoanType tables by the LoanType, restricts the command to operate on only those loans which were contravening and on transactions that occurred subsequent to the “liability” date and links the Loan and the interest Rate tables by the RateType.

AND T.TransactionDate <= R.RateEndDate AND R.RateStartDate<.vSettlementDate
Transactions for the selected loans are then linked with the effective interest rate for all of the different interest rate periods between the transaction date and the settlement date. ‘Interest Charges’ are included in order to compound the interest.

ORDER BY L.ApprovalDate
This sorts the loans by ascending approval date.