top of page

How to Perform a Credit File Review

A loan portfolio is like a box of chocolates: you never know what you're gonna get. Often, it's not even chocolate...

Over the past decade, the number of loan portfolio disposals has skyrocketed, becoming almost a craze. Common investors included private equity funds, specialized investors and credit servicing firms. M&A activity was particularly strong across Europe, where significant deleveraging programs were undertaken following the 2010 sovereign debt crisis.

Through the sale of non-performing assets, financial institutions ("FIs") are able to achieve the following:

  • Reduction in risk-weighted assets ("RWA") following the derecognition of sold portfolios

  • Free-up of capital absorbed in non-performing assets

  • Increase in the profitability of invested capital

  • Increase in liquidity

  • Positive impact on credit quality ratios and capital adequacy ratios

  • Improvement of the cost-income ratio from lower operating costs associated with managing non-performing assets

  • Greater focus of the management on performing assets

  • Send a positive message to shareholders and the market, and reassure on the institution's financial soundness.

The sale of non-core and underperforming assets as a way for banks to improve levels of profitability and capital adequacy was encouraged by the ECB, which in 2014 carried out a comprehensive assessment ("CA") of the Eurozone's top credit institutions that included an asset quality review ("AQR") and a stress test. As a result of the CA, financial institutions have, among other things:

  • increased provisions and, consequently, coverage ratios of non-performing loans,

  • adjusted downwards collateral valuations, and

  • recalculated PD and LGD parameters.

For those of you who are interested, you may access the full ECB report on the CA published in October 2014 at this link.

Common barriers to the successful disposal of non-performing assets, and key factors that contribute to the widening of the bid-ask spread (also known as price gap), include:

  1. Insufficient data and/or inadequate data quality of the loan portfolio

  2. Lengthy duration of the legal (or judicial) workout process

  3. Crisis in the Real Estate market (contraction in sales volumes and/or prices)

  4. High IRR yield required by private equity investors

  5. High sovereign risk.

While barriers 2-5 represent systemic, political and/or external factors, FIs have tackled barrier 1 by investing notable resources (both financial and human) to improve data systems, management and quality.

Big 4 firms have played a big part in assisting FIs throughout the entire CA, as well as in buy- and sell-side transactions. Some of the key services offered include:

  • Portfolio selection (sell-side)

  • Portfolio analysis and stratification

  • Credit file review for datatape population/completion and data quality checks

  • Dataroom preparation and management (sell-side), including Q&A and management presentations

  • Loan portfolio pricing

  • Credit servicing platform pricing, and assistance in its disposal or acquisition

  • Assistance in the creation of a bad-bank and/or a real estate owned company ("ReOCo").

In this article, we will look at how to perform an effective and meaningful credit file review.



A credit file review is essentially a due diligence on the loan documentation related to a FI's clients, carried out as part of a:

  • sell-side advisory mandate, as a means to cross-check the information included in the loan datatape with that found in the FI's credit files, in order to make sure that accurate and consistent files are provided to the potential investors;

  • buy-side advisory mandate, as a way to assess the quality of the information provided by the seller, and obtain further details on the largest exposures to better evaluate their recoverability.

Because of privacy issues, you will need to perform this activity at the client's offices, and you can be sure that they will reserve the smallest, most poorly lit room for you, probably without windows or up in the attic. Also, the credit files may be actual paper binders, which will have accumulated years of dust, and will cause you to have asthma attacks every time you open one.

But it could be worse! Your client could boast of having digital credit files, which you will be able to access on a centuries-old computer that grants you the sole permission of viewing (and neither downloading, nor printing) badly scanned documents.

On the plus side, you can always turn to the loan managers, who are usually very patient and helpful, other than knowing their files inside out.

It can easily be the most boring and mind-numbing activity you will ever have to perform. And yet, depending on the number of the credit files to be reviewed and their complexity, as well as the project timelines and the number of consultants required for the exercise, the clients pay good money for it.

The review generally targets the FI's largest exposures, e.g. the top 20 borrowers representing 75% of the gross book value ("GBV") of the loans portfolio. You will come to learn everything about these customers: address, family members, past and ongoing judicial troubles; for secured exposures, you will see photographs of the real estate asset(s) owned by the debtor; and their correspondence with the FI's dedicated loan manager can really be quite amusing.

With regards to non-performing loans ("NPLs"), it can be fascinating to read how creative some people can get when making up excuses to not pay! Many times, however, you will read about tragic, heartbreaking family stories, or about businesses filing for bankruptcy after being hit by yet another global financial crisis. And you realize that the customer IDs in your Excel file aren't just mere numbers, but they represent real people and real businesses that find themselves in trouble. On a positive note, it is in the best interest of FIs to avoid legal workout, which can be lengthy and expensive, and will usually offer their borrowers multiple options and opportunities to repay and/or restructure their debt exposures.


The Loantape

A loantape (also known as datatape) is an Excel file containing relevant information about a loans portfolio at a specific date (known as the cut-off date). A loantape containing a large number of secured and unsecured exposures would generally be structured as follows:

  • 'Loan' tab - the sheet is structured on a loan-by-loan basis, as customers may have more than one loan exposure towards the FI

  • 'Client' tab - the sheet contains information about individual borrowers (may be included directly in the 'Loan' tab for relatively small portfolios)

  • 'Asset' tab - the sheet contains information about real estate collateral linked to (ideally) all of the secured loans in the portfolio

  • 'Other collateral' tab - the sheet contains information about non-real estate collateral, e.g. promissory notes, shares, cars, heavy equipment and other personal guarantees.

A typical loantape would include the following fields:

  • Client ID - unique code assigned to each borrower

  • Client type - retail or corporate

  • Client birth/establishment date - important indicator for recoverability of retail exposures, as age also determines which workout options are viable

  • Country of client residence/headquarters - important indicator to estimate the recovery timings based on national statistics

  • Client occupation/business activity - important indicator to estimate the recoverability of retail exposures, as unemployment greatly limits which workout options are viable

  • Contract number - unique code assigned to each loan

  • Originator name/ID - name of credit institution that originated the loan

  • Loan type - mortgage loan, personal loan, etc.

  • Currency - EUR, USD, AED, etc.

  • Original financed amount

  • Contract start date

  • Contract maturity date

  • Instalment frequency - monthly, quarterly, semi-annual, etc.

  • Instalment amount

  • Contractual annual interest rate

  • Interest rate type - Fixed/Variable flag

  • Interest rate benchmark - EURIBOR, LIBOR, EIBOR, etc. (relevant for variable interest rate)

  • Last payment date - date at which the debtor completed the last payment

  • Last payment amount - amount paid on the last payment date

  • Loan status - whether the loan is performing or non-performing at the cut-off date

  • Default date - the number of years between the default date and the portfolio's cut-off date (known as the vintage) is an important indicator to estimate the recoverability of non-performing exposures

  • Gross Book Value - outstanding principal plus interest and expenses at the cut-off date

  • Total provisioning - provisions taken on a specific loan at the cut-off date

  • Net Book Value - GBV less provisions at the cut-off date

  • Secured loan - Yes/No flag, 'Yes' if the loan is secured by real estate collateral

  • Asset type - residential, office, land, etc.

  • Asset location - address of the real estate collateral

  • Asset valuation amount - latest available appraisal value

  • Asset valuation date - date of latest available appraisal

  • Asset valuator type - internal or third-party appraiser

  • Mortgage lien - first, second, etc.

  • Total claim amount of higher-priority liens - for non-first lien mortgages

  • Restructured loan - Yes/No flag

  • Legal workout activated - Yes/No flag

  • Relevant Court/Tribunal - name/location of the relevant Court (where relevant), an important indicator to estimate the recovery timings based on national statistics

  • Legal workout phase/status - status of the legal workout at the cut-off date, e.g. injunction, insolvency proceeding, collateral auction, etc.

  • Last auction starting price

  • Last auction date

  • Asset sold at auction - Yes/No flag

  • Next auction starting price

  • Next auction date.

The above information will be downloaded directly from the FI's data systems by the IT department. These data systems will have been compiled by the FI's loan managers.


Credit file review

Consultants assess the data quality of the loantape and verify the existence of important documentation through a credit file review. When cross-checking the loantape information with the documentation included in the credit files, the most important information to verify or corroborate includes the following:

  • Existence of the original loan contract(s) - Yes/No flag

  • Signatures of the client and the bank representative on the original loan contract(s) - Yes/No flag

  • Signing date of the original contract(s)

  • Maturity date of the original contract(s)

  • Loan type

  • Loan status at the cut-off date

  • Contractual annual interest rate

  • Gross Book Value at the cut-off date

  • Provisions at the cut-off date

  • Net Book Value at the cut-off date

  • Collateral information including asset type, address, latest collateral valuation amount and date (for secured loans)

  • Mortgage lien on collateral (for secured loans)

  • Total claim amount of higher-priority liens (for secured loans)

  • Other available collateral type and amount.

With regards to NPLs, the additional fields you will be checking include the following:

  • Default date

  • Last payment date

  • Restructured loan flag

  • Legal workout activated flag

  • Legal workout phase/status

  • If the legal workout has reached the collateral auction phase, specify the number of past auctions, the last auction price and date, whether the last auction was successful, or upcoming auction starting price and date.

Here's what you do:

1. Start from the client's datatape in Excel and delete all of the information related to loans/customers that are excluded from the review

2. Remove all fields that you do not need to check, and add new fields as required (e.g. Existence of original contract, Notes, etc.)

3. Color the reviewed cells:

  • in green if the field in the original datatape is populated correctly,

  • in yellow if you were unable to check/verify the data, e.g. because the relevant documentation was missing from the credit file, or

  • in red if the field in the original datatape is populated incorrectly and replace with the correct data.

4. Create the CountColor function in VBA, pasting the following code in a new module:

Function CountColor(xColor As Range, xRange As Range)

Dim xCell As Range

Dim wCol As Long

Dim yResult

wCol = xColor.Interior.ColorIndex

For Each xCell In xRange

If xCell.Interior.ColorIndex = wCol Then

yResult = yResult + 1

End If

Next xCell

CountColor = yResult

End Function

5. Make a summary table with the results of the credit file review, with the number and % total of correct (green), unavailable (yellow) and incorrect (red) cells.

NB: if members from your firm's legal team are also involved in the credit file review, it is absolutely necessary that you limit their ability to add notes to the Excel template (they love to write).

We hope you found this article useful. Make sure to download our Excel model template at the 'Members Area' of our website. Save the template as .xlsm and/or enable macros to activate the Count Color function.

For questions or clarifications, feel free to reach out. Thanks for reading, and good luck!

820 views0 comments

Recent Posts

See All


bottom of page