Financial Asset Data Modelling - Excel

Document Sample
Financial Asset Data Modelling - Excel Powered By Docstoc
					Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                          Sheet: Instructions




                             CRD Pillar II Data Return Template 2009: Instructions for Institutions

           1) Complete as much detail as possible in all Templates
              Data submitted should be relevant to the licence entity where possible
              Provide information as of 30-June-2009 where possible or use the most recent information available where not
              Please follow the instructions provided for each individual data return sheet
              All data quality issues should be clearly marked and should include an explanation for the unreliability
              This data will be extracted for central storage - do not merge cells or apply custom formats.

           2) Return the completed workbook in electronic form to Risk Analytics Unit, Financial Regulator
              Submissions can be made via email (RiskAnalytics@FinancialRegulator.ie) or via delivery on CD/DVD/USB to
              Alternative data layouts or file formats will not be accepted

           3) Please direct all queries to the Risk Analytics Unit only (not your examination team)
              RiskAnalytics@FinancialRegulator.ie




Page 1 of 25                                                                                          Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                                                                     Sheet: P2(1). (Instructions)



                                                    General Guidance for Template: P2(1) Sectoral_Overview


Please provide as much detail as possible, completing the 19 headline and 2 memorandum sectors shown in emboldened text at a minimum
Please provide details for the performing loan book only (i.e. assets that are currently not in default according to the CRD definition)
Standardised institutions are not expected to provide information for fields (k)-(m), but should provide values if they are available (e.g. used for internal risk management purposes)
IRBA institutions should populate fields (k)-(m) where information is available (including on a partial coverage basis), irrespective of whether credit rating models are used for regulatory and/or risk management purposes.

 Column                    Sub-Section                                Item                                                                            Field Description
       (a)    Industry Sector                       Code                                    Sector code
       (b)                                          ID                                      Sector ID
       (c)                                          Sector                                  Sector Description
       (d)    Book Details                          Net Exposure                            The net exposure to this sector as calculated by internal systems in €m.
       (e)                                          EAD                                     The exposure used for the purposes of calculating regulatory capital in €m
        (f)                                         Number of Obligors                      The number of unique credit obligors in this sector
       (g)    Pillar 1 Capital                      Risk Weighted Assets                    Sector Risk Weighted Assets in €m
       (h)                                          Capital Amount                          Sector Capital requirement in €m (i.e. RWAs * minimum_solvency_ratio)
        (i)   Exposure Weighted Averages            Maturity                                Sector exposure weighted average residual maturity in years
        (j)                                         Maturity Definition                     The maturity type used above (for example, and in order of preference: duration; average life; cashflow maturity; legal maturity)
       (k)                                          PD                                      Sector exposure weighted average Probability of Default of performing assets in percentage terms
        (l)                                         LGD                                     Sector exposure weighted average Loss Given Default of performing assets in percentage terms
      (m)                                           DLGD                                    Sector exposure weighted average Downturn LGD of performing assets in percentage terms




Page 2 of 25                                                                                                                                                                                            Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                             Sheet: P2(1) Sectoral_Overview




                     (a)    (b)                                         (c)



                                                           Industry Sector



                   Code     ID                                        Sector


                 AAGRIC      10 Agriculture and Forestry




Page 3 of 25                                                                         Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                                                                                                Sheet: P2(2). (Instructions)




                                                       General Guidance for Template: P2(2) Top_50_Exposures

              Please provide details of the institutions 50 largest counterparty exposures
              Exposures provided should be the net (aggregate) exposure to a given counterparty
              Netting should take account of credit enhancements (such as credit guarantees and overcollateralisation) where possible
              On and off balance sheet exposures should be included
              The list should include ALL exposures, regardless of whether exempted from Large Exposure reports or not (under 2006/48/EC Article 113(3)(a) to (d) and (f) to (h)) - NO EXEMPTIONS APPLY
              Standardised institutions are not expected to provide information for fields (m)-(o), but should provide values if they are available (e.g. used for internal risk management purposes)
              IRBA institutions should populate fields (m)-(o) where information is available (including on a partial coverage basis), irrespective of whether credit rating models are used for regulatory and/or risk management purposes.

 Column                     Sub-Section                           Item                                                                                      Field Description
       (a)    Borrower                                 Obligor/Connection         The Client/Bank/Government name
       (b)                                             Sector                     The dominant industry sector that the borrower/connection is exposed to
       (c)                                             Country                    The dominant country that the borrower/connecction is exposed to - provide the two-letter ISO 3166-1 alpha-2 code of the borrower; e.g. "IE" for Ireland.
       (d)                                             Internal Obligor           Yes, if this an affiliate exposure (i.e. the borrower is a wholly owned subsidiary or belongs to the same financial group); otherwise No
       (e)                                             In Default                 Yes, if the borrower is currently in default according to the CRD definition (i.e. 90 days past due or unlikely to pay); otherwise No
        (f)   Exposure Details                         Net Exposure               The net exposure to this obligor as calculated by internal systems in €m
       (g)                                             EAD                        The exposure used for the purposes of calculating regulatory capital in €m
       (h)                                             Asset Class                The borrower's dominant asset class (sovereign / corporate / financial / retail / specialised lending)
        (i)   Pillar 1 Capital                         Risk Weighted Assets       Borrower total Risk Weighted Assets in €m
        (j)                                            Capital Amount             The obligor/connection Capital requirement in €m (i.e. RWAs * minimum_solvency_ratio)
       (k)    Exposure Weighted Averages               Maturity                   Exposure weighted average residual maturity in years
        (l)                                            Maturity Definition        The maturity type used above (for example and in order of preference: duration; average life; cashflow maturity; legal maturity)
      (m)                                              PD                         Probability of default of performing assets in percentage terms (use a blended PD if appropriate)




Page 4 of 25                                                                                                                                                                                                                       Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                             Sheet: P2(2) Top_50_Exposures




                                                     (a)          (b)               (c)



                                                               Borrower



                                          Obligor/Connection     Sector        Country
                                                                          ISO 3166-1 alpha-2 Code




Page 5 of 25                                                                                         Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                                                                                             Sheet: P2(3). (Instructions)




                                                              General Guidance for Template: P2(3) AFS_Assets


          Provide a breakdown of all licenced entity assets treated as Available-for-Sale for the purposes of financial reporting on a consolidated group basis using the below fields.
          The returned information should be grouped by asset class and credit quality

           Column                    Sub-Section                               Item                                                                                    Field Description
                 (a)    Asset Type                            Asset Class                            The Asset class (Sovereign/Subsovereign, Financials, Corporate, Retail, Specialised Lending)
                 (b)    Credit Quality                        Rating Grade                           The internal/external credit rating grade assigned
                 (c)                                          PD                                     The probability of default associated with this credit rating grade, if available
                 (d)    Exposure Details                      Nominal Exposure                       Nominal exposure €m (this will usually be the drawn exposure or the book value)
                 (e)                                          Number of Obligors                     The number of unique credit obligors in this asset class
                  (f)                                         Maturity                               Exposure weighted average residual maturity in years
                 (g)                                          Maturity Definition                    The maturity type used above (for example and in order of preference: duration; average life; cashflow maturity; legal maturity)
                 (h)    Pillar 1 Capital                      Risk Weighted Assets                   The total risk weighted assets for this asset class and rating grade in €m
                  (i)                                         Capital Amount                         The total regulatory capital for this asset class and rating grade in €m (i.e. RWAs * minimum_solvency_ratio)
                  (j)   Comments                              Comments                               Provide any additional comments/information relevant to the data furnished as appropriate




Page 6 of 25                                                                                                                                                                                                                    Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                            Sheet: P2(3). AFS_Assets




               (a)           (b)           (c)          (d)        (e)           (f)                  (g)                    (h)            (i)    (j)


      Asset Type               Credit Quality                       Exposure Details                                         Pillar 1 Capital      Comments


                           Rating                    Nominal    Number of                      Maturity                 Risk Weighted    Capital
       Asset Class         Grade           PD        Exposure    Obligors    Maturity          Definition                   Assets       Amount    Comments
                                                                                           (duration / average life /
                                           (%)         (€m)                   (in years)       cashflow / legal)             (€m)          (€m)




Page 7 of 25                                                                                                                                                  Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                            Sheet: P2(3). AFS_Assets




               (a)           (b)           (c)          (d)        (e)           (f)                  (g)                    (h)            (i)    (j)


      Asset Type               Credit Quality                       Exposure Details                                         Pillar 1 Capital      Comments


                           Rating                    Nominal    Number of                      Maturity                 Risk Weighted    Capital
       Asset Class         Grade           PD        Exposure    Obligors    Maturity          Definition                   Assets       Amount    Comments
                                                                                           (duration / average life /
                                           (%)         (€m)                   (in years)       cashflow / legal)             (€m)          (€m)




Page 8 of 25                                                                                                                                                  Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                                                                                   Sheet: P2(4). (Instructions)




          General Guidance for Template: P2(4) Fixed Income Profile

          Please provide a breakdown of Assets, Liabilities and off-balance sheet instruments according to the below instructions



                                                                                        Instructions - Exposure and Funding Profiles by Cashflow Maturity
          Provide a breakdown for all interest sensitive assets, liabilities and off-balance sheet instruments by maturity in the tables provided
          Exposure details should be given in foreign currency (e.g. EUR, GBP, USD, JPY etc.) in units of millions - extend each table to accommodate additional currencies as appropriate
          Time band allocation should be according to the average maturity of cashflows for both fixed rate instruments and floating rate instruments. Use undiscounted cashflows where possible.
          Instruments lacking a definitive maturity (e.g. revolving loans, deposits etc.) should be assigned to a time band according to the judgement and past experience of the bank
          Details provided should be based on active and drawn exposure at the survey date - inactive forward start securities and committed but undrawn exposure at the survey date should be excluded where possible



                                                                                         Instructions - Exposure and Funding Profiles by Repricing Maturity
          Provide a breakdown for all interest sensitive assets, liabilities and off-balance sheet instruments by maturity/repricing schedule in the tables provided. For example a €100m, 10-year loan that is re-indexed to 3M
          Euribor on a fortnightly basis - this exposure has a 2-week repricing maturity and so should be entered into the asset table in the cell corresponding to the 'up to 1 month' time interval and EUR currency as €100m.
          Exposure details should be given in foreign currency (e.g. EUR, GBP, USD, JPY etc.) in units of millions - extend each table to accommodate additional currencies as appropriate
          Time band allocation should be according to maturity for fixed rate instruments and repricing interval for floating rate instruments
          Instruments lacking definitive repricing intervals, or actual maturities varying from contractual, should be assigned to repricing time bands according to the judgement and past experience of the bank
          Derivatives should be converted into positions in the relevant underlying. The amounts considered are the principal amount of the underlying or of the notional underlying.
          Futures and forward contracts, including forward rate agreements (FRA), should be treated as a combination of a long and a short position. The maturity of a future or a FRA will be the period until delivery or exercise
          of the contract, plus - where applicable - the life of the underlying instrument. For example, a long position in a June three month interest rate future (taken in April) is to be reported as a long position with a maturity of
          five months and a short position with a maturity of two months.
          Swaps should be treated as two notional positions with relevant maturities. For example, an interest rate swap under which a bank is receiving floating-rate interest and paying fixed-rate interest will be treated as a long
          floating-rate position of maturity equivalent to the period until the next interest fixing and a short fixed-rate position of maturity equivalent to the residual life of the swap. The separate legs of cross-currency swaps are to
          be treated in the relevant maturity ladders for the currencies concerned.
          Options should be considered according to the delta equivalent amount of the underlying or of the notional underlying.
          Details provided should be based on active and drawn exposure at the survey date - inactive forward start securities and committed but undrawn exposure at the survey date should be excluded where possible
          Exposures which create practical processing problems because of their large number and relatively small individual amount (e.g. instalment or mortgage loans) may be allocated on the basis of statistically supported
          assessment methods.




                                                                                                                                                                                                                                            38




Page 9 of 25                                                                                                                                                                                                           Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                         Sheet: P2(4). Fixed_Income_Profile




        Exposure and Funding Profiles by Cashflow Maturity

                Exposure profile of interest rate sensitive assets (on and off-balance sheet) in millions of local currency by cashflow maturity
                 Currency      Up to 1 month 1-3 months 3-6 months 6-12 months 1-2 years 2-3 years 3-4 years 4-5 years 5-7 years 7-10 years 10-15 years 15-20 years    Over 20 years
                   EUR
                   GBP
                   USD
                   JPY
                   etc..

                Exposure profile of interest rate sensitive liabilities (on and off-balance sheet) in millions of local currency by cashflow maturity
                 Currency      Up to 1 month 1-3 months 3-6 months 6-12 months 1-2 years 2-3 years 3-4 years 4-5 years 5-7 years 7-10 years 10-15 years 15-20 years    Over 20 years
                   EUR
                   GBP
                   USD
                   JPY
                   etc..




        Exposure and Funding Profiles by Repricing Maturity

                Exposure profile of interest rate sensitive assets (on and off-balance sheet) in millions of local currency by repricing maturity
                 Currency      Up to 1 month 1-3 months 3-6 months 6-12 months 1-2 years 2-3 years 3-4 years 4-5 years 5-7 years 7-10 years 10-15 years 15-20 years    Over 20 years
                   EUR
                   GBP
                   USD
                   JPY
                   etc..

                Exposure profile of interest rate sensitive liabilities (on and off-balance sheet) in millions of local currency by repricing maturity
                 Currency      Up to 1 month 1-3 months 3-6 months 6-12 months 1-2 years 2-3 years 3-4 years 4-5 years 5-7 years 7-10 years 10-15 years 15-20 years    Over 20 years
                   EUR
                   GBP
                   USD
                   JPY
                   etc..




Page 10 of 25                                                                                                                                                       Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                               Sheet: P2(5). (Instructions)




                   General Guidance for Template: P2(5) Pension Profiles


                                                                                              Instructions
                   Provide details for all defined-benefit pension scheme obligations of the licenced entity
                   All returns relate to defined-benefit pension scheme obligations only, please do not populate the tables for any other forms of pension scheme
                   Institutions contributing to foreign group schemes should provide details based on their contribution to the group scheme
                   If the institution has no exposure to defined-benefit pension schemes, please state this at the top of the sheet
                   If specific information is not available, please type 'Not Available' in the relevant field




Page 11 of 25                                                                                                                                     Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                   Sheet: P2(5). Pension_Profiles




                              Defined Benefit Pension Plan Totals
          Value of unfunded pension obligations
          Value of fully or partly funded pension obligations
          Fair value of assets under the pension plans
          Net pension obligation at December 31
          Actuarial gains/losses not recognised in the net pension obligation

          Pension assets on Balance Sheet recognised under Other assets
          Pension provisions on Balance Sheet recognised under Other liabilities



                       Defined Benefit Pension Assets at 31-Dec-2008
          Cash
          Bond
          Equity
          Real Estate
          Alternative Investments
          Other


                                  Defined Benefit Pension Funds
          Pension fund name
          Juristiction (e.g. IE, UK, US, etc..)
          Value of assets (€m)
          Number of retired members
          Number of active members
          Defined benefit type
          Predominant asset valuation method
          Funding Cost method


                                       Sensitivity Analysis
          Equity prices
          Interest rates
          Life expectancy


                            Pillar II Capital Held for Pension Risks
          Capital held to cover current funding deficit
          Equity writedown risk
          Credit migration & default risk
          Capital held to cover potential risks identified in sensitivity analysis
          Other risks




Page 12 of 25                                                                             Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                  Sheet: P2(5). Pension_Profiles




      2008 (€m)                    2007 (€m)




      2008 (€m)                    2007 (€m)




        Fund 1                       Fund 2                Fund 3




   Perturbation (%)    Impact on Funding Obligation (€m)




      2008 (€m)                    2007 (€m)




Page 13 of 25                                                            Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls          Sheet: P2(5). Pension_Profiles




   Average Modelling Assumptions        2008         2007
Discount rate
Expected long-term return on assets
Inflation rate
Salary adjustment rate
Pension adjustment rate
Retirement age
Withdrawal or termination assumption
Mortality assumption
Disability assumption
Estimated wind-up expenses




                Fund 4                 Fund 5




Page 14 of 25                                                    Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                                                  Sheet: P2(6). (Instructions)




                                General Guidance for Template: P2(6). Other_Risks


                                Ten Most Significant Equity Holdings by Euro value (i.e. equity stakes in listed and unlisted non-affiliate companies)
                                             Item                                              Description
                                Company Name                 The full name of the company
                                Sector                       Financial or Non-Financial
                                Country                      The location of the exposure
                                Value (€m)                   Estimated value of holding in €m
                                Valuation Date               Date when stake was last valued


                                Summary of Real Estate Holdings by Country and Property Type (i.e fixed assets such as bank branches, headquarters, etc.)
                                             Item                                                Description
                                Country                     Location of the properties
                                Property Type               Office,Retail,Residential,Industrial,Other
                                # Properties                Number of individual properties in this country
                                Value (€m)                  Estimated combined market value in €m
                                Valuation Date              The most recent revaluation date of all properties


                                Summary of Asset Credit Risk Mitigation (including synthetic securitisations)
                                "Any credit risk mitigation activities" refers to the reduction of credit risk based on collateral, netting, guarantees, insurance, cds protection, etc.
                                Unfunded CRM refers to instances where the protection provider steps in when the borrower is unable to pay (e.g. guarantee, insurance, cds protection, etc.)
                                Calculation of physical collateral revaluation frequency should include residential mortgages and commerical property loans


                                Summary of Collateral used for Asset Credit Risk Mitigation purposes
                                Provide a breakdown of collateral type (property,cash,equity,bonds,other) by value in €m



                                Summary of all Originated Asset Securitisations
                                               Item                                                Description
                                Vehicle/Entity Legal Name    The legal name of the vehicle or entity
                                Type                         The securitisation type - On or Off Balance Sheet
                                Assets (€m)                  Value of assets in €m
                                Funding Currency             The primary funding currency of the vehicle
                                Pillar 1 Capital (€m)        The Pillar 1 capital charge (if any)
                                Pillar 2 Capital (€m)        The Pillar 2 capital allocation (if any)
                                Credit Enhancement Type      Tranche Subordination,Monoline Wrap,Financial Guarantor, CDS
                                                             Protection,Excess Spread,Overcollateralisation, Cash Reserve,Standby Credit
                                                             Facility,Other
                                Credit enhancement provider The primary provider of credit enhancement (if any)




Page 15 of 25                                                                                                                                                        Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                      Sheet: P2(6). Other_Risks



       Ten Most Significant Equity Holdings by Euro value (i.e. equity stakes in listed and unlisted non-affiliate companies
                                          Company Name




       Summary of Real Estate Holdings by Country and Property Type (i.e fixed assets such as bank branches, headqua
                                            Country




       Summary of Asset Credit Risk Mitigation (including synthetic securitisations)
                                                    Item
       Balance Sheet Assets with any Credit Risk Mitigation applied (€m)
       Exposure weighted average maturity of underlying assets with unfunded protection (years)
       Exposure weighed average maturity of unfunded protection (years)
       Risk Weighted Assets for underlying credit exposure with unfunded protection (€m)
       Risk Weighted Assets for exposure with unfunded protection applied (€m)
       Exposure weighted average frequency of physical collateral valuations (years)
       Exposure weighed average haircut applied to financial collateral (%)


       Summary of Originated Asset Securitisations
                                    Vehicle/Entity Legal Name




Page 16 of 25                                                                            Printed 2/3/2011 8:29 PM
                  Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                     Sheet: P2(6). Other_Risks



in listed and unlisted non-affiliate companies)
                          Sector            Country           Value (€m)          Valuation Date




xed assets such as bank branches, headquarters, etc.)
                    Property Type     # Properties            Value (€m)          Valuation Date




                                                        Summary of Collateral used in Asset Credit Risk Mitigation
                        Amount                             Collateral Type          Value (€m)
                                                         Residential Property
                                                         Commercial Property
                                                                Cash
                                                               Equity
                                                               Bonds
                                                           Motor Vehicles
                                                                Other



                          Type           Assets (€m)       Funding Currency     Pillar 1 Capital (€m) Pillar 2 Capital (€m)




                  Page 17 of 25                                                                           Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls    Sheet: P2(6). Other_Risks




Credit Enhancement Type Credit enhancement provider




Page 18 of 25                                         Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls                                                                                            Sheet: P2(7). (Instructions)




                   General Guidance for Template: P2(7) Pillar II Development Plan


                                                                                             Instructions
                   Describe ALL scheduled activities that will result in improved Pilllar II tools or processes
                   The plan should include, at a minimum, all planned improvements during the next two years
                   All entries should be allocated to one of the following improvement categories using the drop-down list in the template:
                                Capital Adequacy Modelling
                                Capital Allocation/Planning
                                Governance Review
                                Management Reporting
                                Model/Tools Documentation
                                Model/Tools Validation
                                Performance Monitoring
                                Process Documentation
                                RAROC Analysis
                                Risk Tolerance/Limit Setting
                                Staff Training
                                Other




Page 19 of 25                                                                                                                                  Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls   Sheet: P2(7). Development_Plan




                   Category




Page 20 of 25                                              Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls   Sheet: P2(7). Development_Plan




Page 21 of 25                                              Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls           Sheet: P2(7). Development_Plan




                                     Scheduled Improvement




Page 22 of 25                                                      Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls   Sheet: P2(7). Development_Plan




Page 23 of 25                                              Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls   Sheet: P2(7). Development_Plan




 Delivery (month/year)




Page 24 of 25                                              Printed 2/3/2011 8:29 PM
Workbook: bf9f8d82-8fbe-4a71-8e65-67ae05241270.xls   Sheet: P2(7). Development_Plan




Page 25 of 25                                              Printed 2/3/2011 8:29 PM

				
DOCUMENT INFO
Description: Financial Asset Data Modelling document sample