Our Client
A growing European specialist bank offering lending and savings.
The Challenge
Every bank in the world needed to meet the LIBOR transition regulatory demands and our client had already developed a LIBOR transition framework for legal, accounting, product and risk management before starting with SoftServe. The existing DWH did not have scalability capabilities to absorb new interest rate types without additional efforts. Without action, this could lead to incorrect calculations of variables in consuming systems due to the inability of our client’s DWH to differentiate variable interest rate component types.
The impact to the IT ecosystem and continuity of back-office and regulatory reporting processes were defined as the highest risks for the entire LIBOR program.
Our Solution
SoftServe provided consultative discovery and interviews, followed by risk analysis and specific solutions for each risk identified. The implementation of the LIBOR roadmap was executed in five months.
Impact Benefits Delivered
- Guaranteed continuity of regulatory reporting and back offices processes following implementations
- Single data model for running new, middle, and back books of all bank products
- Enhanced capability of the multi-interest rate products introduction
- Retention of existing internal reporting flows
- Agnostic data warehouse data model that can support multi-reference rates products and became interest rate type agnostic
- Remastered up and down streams to satisfy needs of the LIBOR transition framework
- Connected and integrated new reference rates data sources into data flows
- End-to-end testing of the critical data flows and corresponding reporting, accounting, and servicing systems
Project Description
For context, The LIBOR transition initiative would have an impact on the DWH data workflows and on our client’s critical processes, including regulatory reporting, assets valuation, and hedging. The existing DWH did not have scalability capabilities to absorb new interest rate types without additional efforts. Without action, this could lead to incorrect calculations of the variables in the consuming systems due to the inability of the DWH to differentiate variable interest rate component type.
The main business challenges stem from the following circumstances:
- Variety of the origination and servicing systems
- Absence of the SSoT in the bank’s ecosystem
- Tight timelines dictated by the BoE LIBOR transition roadmap (project was initiated and started before the rescheduling of the BoE roadmap due to COVID-19), which required delivery of the project in Q3 2020.
- Existing central data warehouse was built on the principles – “LIBOR is the only reference rate” that required complex modification of some data flows and data structures
- The need to run new, middle, and back books in the same data model
- Limited IT and SME resources for the implementation of the required changes
The project began with a discovery phase. Using regulations analysis and interviews with business stakeholders, we executed an independent analysis of the potential business threats to the bank’s processes and corresponding technical threats to the upstream and downstream systems.
The results of our discovery allowed us to:
- Define business divisions and processes under critical threats
- Design requirements to the existing origination and servicing systems of the bank
- Execute a “As-Is/To-Be” gap analysis of the existing and new data model of the data warehouse and corresponding data flows
- Develop the implementation roadmap aligned with the BoE requirements and strategic goals of the LIBOR program
SoftServe also conducted a risk analysis to evaluate the data warehouse ability to support the existing workflows and data structure after the implementation of the LIBOR transition policy. Every defined risk was analyzed from a data source and data consumers perspective. The following risks were identified and evaluated, and a solution was suggested and tailored:
| Risk | Description | Solution |
|---|---|---|
| Capability of all source systems to provide interest rate component type | For the continuous work of the DWH and connected processes the main risk is to keep sufficient DWH in part of the product hierarchy. Interest rate component type should become a mandatory variable for all business systems. | Review capabilities of every source system and implement changes in the Stored Procedures and SSIS packages that will allow to consume interest rate component type in the corresponding tasks. |
| Absence of the direct definition of the component type in the existing structure of the data warehouse | In the existing data warehouse, there were no tables responsible for the identification of the interest rate type. In the existing tables we store only three types: “Fixed”, “Variable”, “Discounted”. The interest rate type definition is based on the logic that defines the difference between fixed period date and current date. | Product tables should include a variable that would allow to define interest rate component type. |
| Margin over reference interest rate calculations | In the existing structure of the data warehouse we were calculating margin over LIBOR only. In the case of implementing rates substitutions, it was required to implement a capability to calculate margin over the corresponding reference interest rate. | The solution to this risk will be highly dependent on the changes in the data structure that would be made in the business systems, as there could be different approaches to the raw data extraction. In any case, the creation and continuous, updating of the dictionary of the interest rate component type is required. |
| Hedging rate over reference interest rate calculations | In the existing structure of the data warehouse we were calculating margin over hedge instrument rate. Implementation of the substituting rates without corresponding mapping of the new rates with the new corresponding hedging instruments could lead to inaccuracies in data. | The addition of the corresponding feeding source (could be the same as used for the Rate and Rate Type DWH tables). The mapping of the product table interest rate component type variable with the corresponding hedging instruments should also be done in the first instance. |
| Purchased books product/rate type synchronization | Standardize raw data from the origination platform into the DWH format. Possible substitution of the reference interest rate in the purchased portfolio may lead to inaccuracies in the product table in the DWH. | Data providers should include interest rate component type in the CSV and XML files that will allow to keep raw data structure aligned with the DW structure. |
| Lack of data for the treasury team | Under the current process, the treasury team has to spend almost a full week to prepare portfolio information for the further hedging of the interest rate risk. The main problem is that the data warehouse does not include direct information about the type of the interest rate component. Implementation of the additional rates required additional manual work. | Could create view that will allow to breakdown portfolio by interest rate component types. |
SoftServe estimated the main impact of the LIBOR transition in part of the DWH being concentrated to the needs of the regulatory reporting and finance team. The absence of the interest rate component type in the main DWH could lead to the incorrect finance data, loans valuation, and regulatory reports.
The following remediations of the identified risks were implemented:
- Changes to the structure of the intermediate tables that consume interest rate data from the raw data extracted to the DWH
- Added a component identification variable to all product related objects
- Created stored procedures/SSiS packages that allow mapping of the interest rate component to the central IR component dictionary
- Implemented reference rates data sources including SWAP data sources
- Created a mapping mechanism between the IR component dictionary and reference rates data sources + SWAP data sources
- Implemented changes to the data extraction procedures and views that will allow to use new IR components for further data processing
- Defined the needed structure and request from 3rd party providers to provide data according to DWH needs (target, set of the purchased books systems)