ICAAP (Internal Adequacy Assessment Process) Reporting Excel Template now available for download

3 mins read

Internal Capital Adequacy Assessment Process was introduced a few years earlier with mandatory regulatory reporting deadlines that started kicking in 2009, 2010.  While we first introduced the sample ICAAP report a few weeks earlier a number of customers asked for an ICAAP excel sheet.  Here is the excel template that was used to generate the detailed credit portfolio portion of the ICAAP report. From transition matrices to provision and reserves stress testing, from profitability analysis to segment by segment breakdown of the credit portfolio.

What follows is the table of content for the excel templates on a tab by tab basis.

You can download a copy of the report from the Finance Training Courses as well as the new excel template for the credit part of ICAAP reporting. And if you need any assistance in downloading the course material, please see our buyers download guide.

ICAAP- Credit Worksheet

Table of Contents

Tab 1: Ratings

  • Master Table- Sample customer and ratings data set
  • Consolidated Tables
    • Customer-wise scores (month on month)
    • Downgrades? – Indicator table (1 if downgraded; 0 if not)
    • Customer-wise outstanding amounts (month on month)
    • Industry identifier
    •  Classification identifier (Current or blank)
    • Rating grade distribution
      • By number
      • By amount outstanding
    • Graphs – Monthly rating grade distribution
      • By number
      • By amount outstanding
  • Downgrade Statistics
    • Number of downgrades by industry (month on month)
    • Amount of downgrades by industry (month on month)
    • Total loan outstanding in previous month by industry (month on month)
    • Percentage of exposure downgraded by industry (month on month)
    • Graph- Percentage of exposure downgraded by industry (month on month)
  • Transition Matrices
    • Month on Month
      • By number
      • By amount at the beginning of the period
      • By amount at the end of the period
    • Industry wise
      • By number
      • By amount at the beginning of the period
      • By amount at the end of the period
      • By amount at the end of the period (adjusted)
  • Expected Classification Calculation
  • Summary Statistics
    • Number of accounts
    • Amount at the beginning of the period
    • Amount at the end of the period

Tab 2: Advances Portfolio

  • Transaction level sample data set
  • Consolidated Table by customer/ facility level
  • Data statistics
    • Facility versus Rating Frequency
      • By number
      • By amount outstanding
    • Graphs – Facility versus Rating Frequency
      • By number
      • By amount outstanding
    • Facility versus Industry
      • By number
      • By amount outstanding
    • Graphs- Facility versus Industry
      • By number
      • By amount outstanding
    • Facility versus Sector (SME/Non-SME)
      • By number
      • By amount outstanding
    • Graphs – Facility versus Sector (SME/Non-SME)
      • By number
      • By amount outstanding
    • Facility versus Residual Maturity
      • By number
      • By amount outstanding
    • Graphs – Facility versus Residual Maturity
      • By amount outstanding
    • Rating versus Sector (SME/Non-SME)
      • By number
      • By amount outstanding
  • Infection Ratio
    • Calculation table
    • Graphs
      • By number
      • By amount outstanding

Tab 3: Provisioning

  • Classified customer-wise consolidated table
    • Industry and Facility Identifiers
    • Days overdue
    • Provisioning requirement/ amount according to days overdue
    • Classification according to Rating
  • Classification-wise consolidated table
  • Projected Days past due (DPD) for future months (under assuming of worsening credit quality)
  • Projected Classification for future months
  • Projected Provisioning requirement for future months
  • Projected Provisioning amount for future months
  • Industry wise projected provisioning amount for future months
  • Graph –Total and incremental projected provisioning amount for future months
  • Classification wise projected provisioning amount for future months
  • Graph – Classification wise projected provisioning amount for future months
  • Industry wise Weighted Time to Maturity
    • By substandard classification
    • By doubtful classification
    • By loss classification
    • By overall classified customers
  • Graphs – Industry wise Principal and Outstanding Amounts
    • By substandard classification
    • By doubtful classification
    • By loss classification
  • Graphs –Weighted DPD
    • By substandard classification
    • By doubtful classification
    • By loss classification
    • By overall classified customers

Tab 4: Profitability Analysis

  • Expected Classification- Transition based
  • Expected Provisioning Requirement – Stress test based
  • Best Case and Worst Case Provisioning
  • Cost of Funds
  • Best Case and Worst Case Earnings