Author: Pete Shannon

CLIENT

Manager of one of the world’s 10 largest derivatives portfolios

CHALLENGE

Derivatives have emerged as a prominent financial tool, employed by both financial and non-financial businesses to hedge risks, and some insurers, banks, and investment managers to increase risk.  The latter often takes the form of proprietary trading, where derivatives can magnify returns through leverage, enabling sometimes extreme counterparty and asset class risk concentrations.  In 2003, Warren Buffett predicted that derivatives would act as a “financial weapon of mass destruction.”  Investment in derivatives grew considerably after that warning and in 2010 total notional value in outstanding derivatives contracts exceeded $600 trillion, almost 5 times the total value of the world’s stock and debt markets.  To help mitigate latent downside risk, firms trading or making markets in derivatives, whether defensively as a hedge or speculatively for profit, need to closely track and actively manage the counterparty risk in their over-the-counter positions.

Our client, a large participant in FX markets, and holding risky derivatives across markets and trade types, needed a solution to comprehensively capture credit-related data on derivatives exposures across their enterprise.  They turned to eBIS to analyze their book of derivatives business, captured on a multitude of tracking systems, and devise a solution for risk modeling, quantification, and reporting.  The solution needed to address the following requirements:

Data Consolidation

• Consolidate trade-level data from multiple source systems into a consistent data model.
• Accommodate modeling of, and create reporting taxonomy for, all trade types and markets:

Trade Type	Market
Spot	Credit
Forward	Equity
Future	Currency
Option	Interest Rate
Swap	Commodity
Exotic

Credit Limit Monitoring

• Associate credit facility limit and usage calculations to individual trades.

Accounting

• Report fair value and hedge accounting as required by FASB statement 133.
• Accommodate counterparty deal accounting for structured positions.  Report dominant trade notional, net exposure, and dimensionality.
• Accurately report position information to the general ledger.  Some derivatives product systems lack a mechanism to accurately post a marked-to-market and notional balance.

Exposure Quantification

• Facilitate capture of credit risk mitigants (netting, collateral), offset permissions and their associations to exposures.
• Report positive marks after netting and potential future exposure calculations for internal analysis of downside credit risk and profit contribution.
• Provide trade-level details for calculation of a Basel II-compliant current exposure value.

Trade Management

• Provide a method to effectively eliminate reporting of trades between internal desks.
• Identify hedge trades and the portfolio exposures being hedged.
• Capture all derivatives data, identifying OTC positions separate and distinct from those trades executed on, and guaranteed by, exchanges.

IDEAS

We analyzed each requirement and formulated design ideas to address each need.

Data Consolidation

• Create a single, custom-built data store for integration of all product systems.
• Use 3 trade attributes: family, type, and sub-type to drive selection criteria for reporting and analysis of other data elements

 Credit Limit Monitoring

• Use facility identifier on trades to enable calculation of usage and remaining available credit

Accounting

• Initiate a back office accounting project to calculate FAS 133 fair value for requisite trade types, including cash flow and foreign currency hedges.
• Establish a back office process to properly identify a dominant trade notional within a structured deal and consolidate its mark-to-market accounting.  Deliver dimensional information, including product, with accounting balances to categorize the position.
• Initiate a back office reengineering project, centering on a product system upgrade, to improve the GL posting process for revalued OTC mark-to-market calculations and FX trade notional values.

Exposure Quantification

• Create a mitigation data model to capture rules for netting trades and assigning collateral to netting agreements.
• Calculate a positive marks after netting balance and potential future exposure value per counterparty credit facility for internal credit reporting.
• Use trade-level attributes for Basel II current exposure method calculation, leveraging mitigation relationships and notional and MTM balances assigned to trades.

Trade Management

• Use trade-level attributes of customer and department to determine if a given trade originated between desks within the same business unit.  If so, exclude it from credit analysis.
• Instruct traders to flag trades for hedge accounting.  Use a reference customer, facility or bond ticker to identify the exposures being hedged.
• Comprehensively consolidate derivatives trades for FFIEC reporting, and use the customer and facility identifiers to segregate those positions traded over-the-counter and thus relevant for counterparty risk analysis.

SOLUTION

eBIS delivered the derivatives analysis solution that our client required.  It provided a centralized derivatives model and reporting platform, incorporating requirements across all stakeholders: trading desks, the derivatives back office, credit portfolio management, regulatory reporting, finance quality assurance, and management accounting.  We carefully analyzed reporting needs, developed ideas, and then delivered a derivatives monitoring and reporting solution embraced by all of these internal clients at the bank for the first time.

Of particular note, the solution enabled:

1. Accurate counterparty credit risk monitoring, including concentration risk analysis
2. Comprehensive FFIEC reporting of all derivatives positions
3. Calculation of economic credit capital
4. Calculation of Basel II regulatory capital through the Current Exposure Method
5. Quantitative Impact Study (QIS) scenario analysis
6. Capital relief through identification of swap hedge positions

RESULTS

Derivatives were cast as a central character in the Global Credit Crisis. AIG issued credit derivatives in excess of $2 trillion, requiring a taxpayer bailout. Merrill Lynch created off balance sheet SIVs for selling CDOs that it then guaranteed with total return swaps, eventually leading to its forced merger. At the time of its collapse, Lehman Brothers had derivatives contracts in place with over 8,000 counterparties, many of whom were not fully repaid pledged collateral or compensated for a positive marks position. As the crisis spread, it became clear that some firms were not monitoring and managing the counterparty risk in their derivatives exposures effectively.

Post-crisis, financial firms continue to deal extensively in derivatives, with JP Morgan, the U.S.’s largest dealer, holding contracts worth over $75 trillion in notional value on 30 June 2010.

In this context, eBIS helped our client understand crucial components of their derivatives risks through the credit crisis and beyond. The net effect? For our client, the financial weapon of mass destruction appears much more manageable.

CLIENT

A top 3 US custodian bank

CHALLENGE

Short sale coverage and the need for low-cost avenues to increase securities inventory for many broker-dealers has created a robust market for securities lending. Custodians with large portfolios of securities often indemnify their customers from loss and earn a split fee for lending those securities to other financial institutions, a low risk and high margin business. However, indemnified securities lending creates extremely large due from balances in the banking book, representing the risk of financial counterparties failing to return the borrowed assets. In practice, pledged collateral normally eclipses the value of securities lent and is re-margined daily, greatly reducing or negating the tangible exposure to the custodian.

A challenge lies in quantifying the economic reality of this risk in a manner that considers the potential down-side price movements of both sides of the transaction: the security lent and the collateral received. Moreover, one must implement the calculation such that it reflects the true risks of this business for internal capital modeling: the ability to quickly liquidate collateral and replace securities lent in liquid markets.

The Federal Reserve issued a guidance letter under the Basel I capital rules permitting capital relief for certain securities lending portfolios: double indemnified transactions (re-hypothecation), and those secured by non-cash collateral. Capital could be reduced to a net exposure (securities lent – collateral received) plus Value at Risk (VaR), accurate to 99% confidence, using a market risk methodology to approximate an equivalent unsecured loan balance. The VaR must represent a stressed 5-day simulation of downside price movements against both the short and long positions by security type. Although reported quarterly, the calculation must execute daily for all reportable portfolios and prove reliable to a 97% confidence interval. Without adherence to this guidance letter, custodians are relegated to regulatory capital at 1.8% of aggregate gross nominal balance, punitive and potentially prohibitive for business line viability.

Any solution must also adapt to changing requirements for subsequent capital regimes (Basel II, etc.). Specifically, Basel II permits VaR treatment for a much broader range of repo-style portfolios, significantly expanding the opportunity for capital relief. In addition, it specifies criteria for collateral eligibility, including exclusion of sub investment grade and unrated debt, not present under Basel I.

On the technical side, we faced client-specific challenges from internal systems, which affected the ability to meet the Fed’s reliability benchmark. Some upstream systems compiled data manually, without systematic logic and validity checks, and many failed to meet service level agreements for timeliness of data delivery.

IDEAS

We developed ideas in three areas to help our client address the complex challenges of risk analysis and capital computation in securities lending:

Data Environment

Build a database environment that can model all data from the bank’s securities lending businesses, and use it to create portfolios of securities defined by counterparty collateral agreements. Model these agreements discretely within this data framework for ease of validation.

Run stressed VaR calculations, using historical securities prices as model factors, against both the securities lent and collateral components of the portfolio, and model risk based on the residual exposure plus VaR, as permitted under the Federal Reserve guidance letter.  Use the central environment for data capture and definition to coordinate all calculations.

Reliability

To address the requirement of 97% reliability, and combat source system issues with data quality and timeliness, build a parallel processing architecture that allows for re-processing of dirty data or delinquent extracts delivered outside of a normal processing window.  Allow the iterative data to flow through normal processing, delineated by a time dimension, facilitating analysis of sequential improvement in data quality and adherence to service level agreements.  The most recent time stamp would alert users to the cleanest, most comprehensive data iteration.

Adaptability

Leverage the architecture established for the subset of securities lending portfolios calculated under Basel I, and expand it to include all of the repo-style portfolios eligible under Basel II.  Tag each portfolio as applicable to a specific capital regime: Basel I, Basel II, economic capital, etc.

Include stream-based processing during the portfolio creation process.  For instance, if the data is intended for Basel II reporting, include a filter to exclude non-compliant collateral.  If relevant for Basel I, exclude the filter, potentially allowing the same set of data to be treated differently according to the intended reporting audience, easing data classification and ensuring accurate VaR calculations.

SOLUTION

The solution that we helped engineer created a central repository of securities lending data across lines of business and source systems, enabling comprehensive downstream processing.  Out of this environment grew:

1. A process to catalogue the details of counterparty collateral agreements.
2. A process to construct portfolios of exposure and collateral based on these agreements.
3. A stressed VaR calculation over a five day window of both exposure and collateral components, meeting the regulatory requirements for capital reduction.

In addition, we were instrumental in delivering a solution to comply with data reliability requirements.  When unreliable source data became a critical inhibitor, eBIS stepped forward to address the problem with a proposal for an alternative processing path.  We translated our ideas into a robust architecture for data re-processing that satisfied the regulatory reliability benchmarks.   And we did so within a compressed timeframe, juggling resources and other project initiatives to ensure successful solution delivery.

RESULTS

Over $1 billion in regulatory capital savings. Regulatory capital reduction under Basel I from $1.41 billion to $6.8 million upon disclosure of VaR results in September 2007, with similar results ongoing. The liberated capital is available to the custodian for stock buy-backs, retirement of debt, and acquisitions, all of which contribute to shareholder value.

The solution provides a sensible, risk-sensitive capital calculation and reporting vehicle for an otherwise low risk, profitable line of business.

CLIENT

A Top 10 (by assets) internationally active US bank

CHALLENGE

With advances in portfolio risk and profitability modeling, and the need for consolidated regulatory reporting, large financial institutions have increasingly turned to enterprise data warehouses to satisfy their requirements for data analysis and reporting.  Data warehouses provide numerous benefits in these scenarios: conformance of multiple sources of data, consistency in data modeling, and data transformation and calculation algorithms that provide data for analytic consumption.  In short, a “single source of the truth” no matter the reporting view or end user.

Our client faced the challenge of a diversified business model, with risk-producing lines of business in retail, commercial credit, global markets, securities processing, custody, asset management, and treasury management, yet no consolidated data environment in which to analyze their risks and report their positions.  They collected credit data from a subset of systems and supplemented with manual data feeds, introducing data quality problems.  They had never undertaken an initiative to comprehensively identify all of their risk and financial reporting data. With Basel II compliance looming on the horizon and divergent internal architectures for economic capital and profitability reporting, they acknowledged the value of data consolidation, and engaged eBIS to help engineer a solution.

It was clear the bank needed to upgrade its data collection and management architecture.  Our engineering challenges were many:

1. Design a conformed dimensional data model to integrate over 45 source product systems, representing the 7 lines of business producing risk for the bank
2. Determine the requisite data points to satisfy regulatory, corporate finance, and portfolio risk management reporting
3. Reconcile source product system data to the general ledger, such that reportable data at a granular level reflects the bank’s official books and records
4. Design an approach to identify and exclude transactions between closely held entities
5. Accommodate the effects of merger and acquisition activity seamlessly
6. Create a solution for retail modeling, pooling similar product exposures and assigning calculated credit attributes
7. Devise a modeling approach for the Basel II concept of repo-style portfolios: pools of exposures that are collateralized and re-margined on a daily basis
8. Create a consistent definition of credit exposure across the enterprise to feed both regulatory and economic capital calculations
9. Integrate with external systems to calculate: a) value at risk for portfolios margined daily; b) a probability of default (PD) for every commercial customer; c) a loss given default (LGD) for each credit facility; d) economic credit risk capital and e) loan reserve
10. Devise a process scheduling approach to manage technical execution across technologies and platforms
11. Establish expectations with providing data systems and enable a process to identify and remediate source system data errors

Notwithstanding the immediate modeling challenges, our underlying objective was to create a scalable environment comprehensive of reportable risk and profitability data points from all lines of business, with the flexibility to absorb changing analytic requirements through time.  If banking regulations evolve or new portfolio modeling techniques emerge, our client would have the tool to not just adapt, but react quickly: an intelligently crafted enterprise data warehouse.

IDEAS

The bank had an existing profitability environment, with data input limited to two product systems, that provided a flexible development platform and established back end infrastructure.  With an eye toward the long-term benefits of integrating risk and profitability data, and potentially creating an enterprise warehouse environment that could eventually serve all data consumers, we supported the idea of leveraging the profitability environment for their initiative.

In contemplating design for such a large construction effort, we concentrated on developing ideas around each of the initiative’s architectural challenges.

1. Data Model: Identify the complete list of exposure types, such as letters of credit and derivatives, and, where possible, construct a single data entity per exposure type, modeling discrete data attributes.  Conform data from multiple sources into each data entity, creating a consistent functional representation.  Capture data at the lowest possible grain, facilitating detailed analysis where necessary with the potential for aggregations and summary reporting. Create a robust dimensional model that includes the dimensions of time, customer, department, organization unit, asset category and product. Design a multivariate time dimension for measures that would allow for analysis of three factors: the date of valuation, the date of data extraction in source, and the date of data processing in our warehouse.   Of particular importance for our primary audience of credit risk and portfolio managers were the credit attributes of any risk position.  We generated the idea of using a bespoke product dimension to model these attributes, discretely identifying the maturity band, priority of claim, business context, form of credit extension, and mitigation offsets associated to each risk position.
2. Secondary Users: Interview and request documentation from regulatory reporting, financial accounting, and profitability managers outlining their data needs.  Expand the data model and collect data points from feeding systems to meet their requirements.
3. General Ledger Reconciliation: Create a custom program to aggregate granular positions at a level consistent with the dimensional reporting of the corporate general ledger: department, organization unit, account, and currency.  Use a robust time dimension to ensure that, in the case of revaluation, back valuation, or lagged data, the most current data is used for analysis. Where net positions are relevant, such as with deposits and derivatives, create a process that replicates the netting logic, and reconcile the netted balance to gl.  Check for balance differences between the granular and ledger levels.  Where differences exist, represent them at the granular level such that, when summed, the granular amount equals the ledger amount.  As such, any granular analysis of risk would tie to the bank’s official books and records.  Build associations between the balance differences and the system(s) that provided the granular data.  Identify problem systems and use the reconciliation output as a tool to improve source inputs.
4. Closely Held Entities: At a granular data level, build a relationship between the dimensions of customer and department.  A customer attribute could identify subsidiaries and affiliates, which could then be associated to the department that originated the position with the closely held entity.  If both parties rolled up to the same legal entity, exclude the position from any analysis.  For those positions with customers that represent subsidiaries or certain affiliates in a different legal entity, flag them for analysis depending on the reporting need.
5. Mergers and Acquisitions:  Use a generic, surrogate key for all data entities, including dimensions.  If the bank were to merge with another company, the additive data could flow in seamlessly.  For existing data that then transfers to an acquired system, the surrogate assignment process could identify the old data in the new system, preserving its original identifier.
6. Retail Modeling: Using empirical studies of the portfolio of retail exposures, members of the bank’s portfolio management division identified multiple strata of data to which probability of default (PD) and loss given default (LGD) ratings and usage given default (UGD) percentages could be assigned.  Using a combination of product identifiers (for PD, LGD) and funded status (for UGD), we were able to translate the functional study into a mechanism for rule maintenance.  Each retail position would receive product tags, and periodic analysis of retail data within the data warehouse would provide the means to update the credit ratings and percentages.  A workflow process would allow expert judgment override capability, providing a flexible tool to reflect both evidence and discretion, as well as a decision trail for regulatory review.
7. Repo-Style Modeling: Design an application that would pool like sets of exposure by business line and associate collateral to each pool, ensuring that securities on either side of the transaction are clearly identified.  Once the pools of exposure and mitigation were defined, they could be sent to an external vendor for simulation of stressed downside price movements in the form of value at risk (VaR).  The VaR calculation could return to the data warehouse for inclusion in an exposure at default (EAD) balance for calculation of regulatory and economic capital downstream.
8. Credit Exposure Definition: With analysis of exposure occurring at varying levels across systems and business lines, we recommended the creation of a central data mart to consistently define a unit of exposure.  The data mart would build a relationship between the lowest grain of exposure, instrument, and the modeled grain of exposure, exposure set, establishing either a one-to-one or many-to-one relationship.  Perhaps most importantly, the data mart would include logic to ensure every defined exposure, including on and off balance sheet positions, had a PD and LGD rating assigned to it, and would calculate a remaining maturity, derive a rule-based modeled maturity, and calculate EAD.  It would define the universe of exposure types and tag each exposure, creating the first ever enterprise classification for reporting and analysis. The effects of guarantees received would be estimated through a double-default algorithm, and guarantor exposure would be included for economic capital and customer concentration analysis.  For instances where regulatory and economic capital balance requirements diverge, model EAD in both ways as separate balances.  We recommended the inclusion of secondary balances, such as accruals, receivables and assessments, in the definition of a loan equivalent exposure, which would avail them to preferable capital treatment.  In one central place, the bank could define exposure and all its associated attributes and balances, and deliver it consistently to regulatory and economic calculation engines.
9. Mitigation Definition: EAD and VaR calculations and LGD ratings all rely on mitigation information to derive their values.  We recommended the creation of a consolidated mitigation model, defining the exposure, type and magnitude of mitigation, and the context in which it could be used, such as jurisdiction, governing law and permissions.  The mitigation model would cover collateral, 3rd party guarantees and indemnities, credit derivatives, participations, syndications, and netting.
10. External Integration: Create a flexible inbound and outbound data transfer platform with firewall security protection.  Define inbound and outbound layers of data within the warehouse: the inbound layer representing the data format of the sending system and the outbound layer representing the data format required by the receiving system, pre-processed into a data mart with analytic cacluations as required.  Both would include validation logic to cleanse data before it entered or left the warehouse.  In this way, the data acquisition paradigm could be blind to the system providing data.  Whether it is an internal legacy system or an external analytic calculation vendor, the treatment is the same: acquire the data in its native format and then validate and transform it into data warehouse dimensional standards.  Finally, build with tools flexible enough to adjust to messaging and a service-oriented architecture when the bank becomes ready for that strategic infrastructure change.
11. Process Scheduling: A data warehouse normally employs a multitude of technologies to manage data extraction, transfer, load, analytic processing, replication and reporting.  Sequencing of process execution is of high importance, and often crosses technologies. The project team needed to formulate an approach for managing the amalgam of technologies, with the ability to trigger process execution and scale to the level of thousands of daily jobs with varying frequencies, controlled through a central software program.  We recommended investigating, in order of priority: 1) a corporate standard scheduler used in a similar capacity elsewhere at the bank; 2) a best of breed scheduling tool; 3) a custom built interface, using metadata and APIs.  Flexibility to integrate new technologies in the future should be a top priority.
12. Source System Management: A data warehouse is only as good as the data it receives.  In that vein, it’s vitally important to establish agreements with providing systems as to format, content, timing and frequency of data delivery.  We recommended formalizing this relationship through Service Level Agreements (SLAs), which would document these data expectations and act as a reference point for adherence.  In addition, lines of communication, supported by data reports from the warehouse, would need to be established with source system owners for data quality remediation.  As a best practice, we stressed the importance of identifying data problems in a central warehouse, but managing them in providing systems.  The quality of today’s data is far less important than confidence in the quality of future data; only remedies at the source could ensure that confidence.

Our ideas provided guidance on best practices in data warehousing and functional modeling.  Taken together, they established a foundation on which to build a robust solution.

SOLUTION

Acting in strategic advisory, architecture design and implementation roles, we staffed an eBIS team of 15 financial services professionals, part of a project team of over 75 client and consulting resources. Collaborating with a Big 4 firm to construct the solution, we worked together to translate a package of good ideas into a robust data environment.

The solution embraced all of the ideas outlined: a dimensional data environment addressing the needs of risk, finance, profitability, and regulatory users that ties to the bank’s reported ledger and calculates credit risk measures for Basel and economic reporting. The data warehouse, sized to almost 3 terabytes, orchestrates 1,700+ jobs from close to 50 providing and numerous receiving systems on a daily basis through a dependency-based scheduler.

The solution embodies numerous data warehouse best practices, ones that we documented as project standards, providing an architecture flexible enough to accept new systems and modeling requirements as they emerge. It handles identification and reprocessing of dirty data in a way that preserves the iterative improvements in data through time, while maintaining the locus of responsibility for data quality in originating systems.

Our contribution also included training sessions on the solution architecture for all interested parties within the bank. Our presentations focused on identifying business interpretations of the information captured in the warehouse, so that secondary users, outside of credit portfolio management, in the profitability, regulatory, and finance sectors, could recognize the potential benefits for their areas.

RESULTS

Our initiative was mandated out of a need for a comprehensive credit risk environment to meet regulatory and economic capital reporting requirements. The client committed significant time, monetary and human capital to that end through a project that spanned seven years. The result met the need: a comprehensive financial warehouse from which other reporting tools could extract data for Basel, Federal Reserve, economic credit risk capital and corporate profitability calculations and FFIEC reports.

As Basel III, trading book liquidity, and stress testing requirements unfold, and U.S. regulators set the parameters for adherence to the Dodd-Frank Act, our client has a flexible, scalable data environment in which to adapt. The solution was the first at the bank to consolidate all risk systems and dimensional data in a central warehouse, providing a substantive analytic and reporting tool at a critical time in financial services regulation.

In addition, the solution acts as the important first step on the path to creating a true enterprise data warehouse, from which all bank employees consume analytic data for custom analysis and standardized internal and regulatory reporting. We helped create a data environment that enables the “one source of the truth” that enterprises yearn for. A customer, department, and product are viewed consistently, regardless of the report. A unit of risk has a definition every user can understand. Regulatory, risk, finance, and profitability users can refer to the same intersection of data and get the same result, outcomes that are not easily quantifiable, but unquestionably valuable.

The mandate that set our initiative in motion was clear, but the unintended benefits resulted from a cadre of good ideas and the steadfast belief in designing solutions that can adjust to changing needs.

CLIENT

A Top 10 (by assets) US bank

CHALLENGE

Traditional savings and loan banks are in the business of taking deposits to then lend out, earning a spread between the interest paid and received. On the surface, loans are cash cows, generating all of the revenue, while deposits appear as dogs, producing the interest expense necessary to fund the loan book.

Some mechanism is necessary to risk-adjust the spread a bank earns, by product, as administered and managed through a central treasury function. Deposits earn a credit for their worth in funding assets. Loans incur a cost for the expense involved in securing capital to lend. This concept can extend to every position on the balance sheet: every liability has some benefit operationally, while every asset has a cost.

On the income statement, non-interest expenses, associated to servicing, marketing, and administration overhead, exist that don’t easily associate to the products, departments and customers that they support. Organizations must devise a method to allocate these overhead costs back to the areas that generate revenue.

Once the balance sheet and income statement are normalized for profitability, data consumers can analyze any slice of data, down to the lowest grain of instrument (customer account), and understand its fully expense-loaded, risk-adjusted contribution.

With a mandate for improved corporate profitability reporting, our client engaged us to build a comprehensive solution for risk-adjusted profitability. Calculate instrument-level profitability in the commercial loan and deposit books. Transfer price the entire balance sheet. Allocate service overhead costs dimensionally, providing a fully cost-loaded product profitability view. Consolidate all profitability data into a central data store for enterprise consumption. In short, paint a picture of granular, risk-adjusted profitability, accounting for interest rate, liquidity, prepayment and credit risks, as well as costs to market, service, and operate the revenue-generating product lines.

IDEAS

In collaboration with an enterprise software vendor, our ideas centered on four conceptual areas: 1) Transfer price the commercial loan and deposit businesses at the lowest grain of detail to leverage pricing and cash flow information; 2) allocate overhead costs to dimensions not available on the corporate ledger; 3) compile all profitability calculations in a performance ledger; 4) report dimensionalized net interest margin and net profitability contribution.

The details of these ideas are as follows:

1) Instrument Profitability

• Model the commercial loan and deposit books of business at the instrument level (customer account), leveraging a robust, granular data model to fully describe their pricing and cash flow characteristics and dimensional attributes of product, customer and department.
• Transfer price the indeterminate term deposit products according to their “core” stable balance levels using the yield of an assumed term, averaged over that same period, e.g., 3 month moving average of the 3 month LIBID.
• Transfer price the term loans and deposits using a yield curve that splices fed funds, LIBOR/BID, and SWAP rates, with appropriate bid/ask spread adjustments.  Use linear interpolation to bridge observed quotation points, reflecting the convexity of the interest rate environment at the time and the bank’s pricing policies.
• Based on empirical evidence, assume minimal or no adjustments for prepayment risk.
• Transfer price the repricing loans according to a repricing schedule, allowing for irregular periodic schedules by instrument.
• Include a liquidity adjustment cost for the reprice method loans, given that the full term of funding is longer than the reprice term.
• Use currency-specific yield curves, using the transaction currency to drive pricing, reflecting the liquidity profile of each currency.
• Choose AA rated swap curves, reflecting the client’s credit costs as a counterparty in swap transactions.

 

2) Cost Allocation

• Identify non-interest expense accounts relating to overhead costs.  Use cost drivers to dimensionalize the expense numbers, driving them back to the products and customers that necessitated their expenditure.
• Use the official general ledger as the input source and the performance ledger as the target, creating a single source of dimensionalized profitability data.

 

3) Performance Ledger Modeling

• Import the full balance sheet and income statement ledgers from the source of 10K and 10Q reporting, lagging month-end to allow for adjustments during the GL close process.
• For those GL dimensions that match the available instrument detail, perform a GL reconciliation.  Plug any difference between the instrument and GL balances such that the GL amount dominates, allowing a profitability perspective that reflects the official books and records.
• Leverage the instrument detail to aggregate to the ledger level, dimensionalizing the ledger data by product, customer and channel.  Use the rule: instrument detail + reconciliation plugs = ledger amount.
• For GL positions without instrument detail, assume a term and yield curve for pricing, or a stated basis point charge or credit.
• Base processing off of a tree that logically depicts groupings of GL accounts in hierarchies, associating transfer pricing rules to clusters with identical functional treatment.
• Recursively process each account with the lowest level rule associated to it until all accounts are processed, ensuring transfer pricing of the entire balance sheet.
• Identify inter-company accounts (positions between internal departments) and transfer price them at a 0 rate, effectively eliminating a charge or credit for these balances.
• Some bank branches have standing agreements with the treasury group to receive pricing based on a pre-arranged spread against a defined index.  For the accounts under these branches, calculate a transfer price cost or credit as a basis point spread against the stated index.
• Post the transfer pricing output to income statement profitability accounts, representing the cost or worth of funds for each balance sheet position.

 

4) Reporting

• With transfer pricing and cost allocations compiled in a central performance ledger, generate dimensionalized profitability reports that detail the components of net contribution for customer-facing products:

 

             ASSETS                                                                LIABILITIES

 

   Interest Income                                                    Transfer Pricing Credit

+Fee Income                                                            +Liquidity Credit

-Transfer Pricing Cost                                        +Fee Income

-Liquidity Costs                                                     -Interest Expense

-Overhead Costs                                                  -Overhead Costs

  NET CONTRIBUTION                                        NET CONTRIBUTION

 

• Generate net interest margin reports, isolating the raw spread between external interest rate and the cost or worth of funds.
• Make reports flexible to include the cost of allocated capital, representing external credit, market, and operational risks, to be calculated in a future initiative.

SOLUTION

eBIS led a team consisting of client and partner resources and delivered a robust data capture, processing architecture and reporting framework for profitability analysis. We overcame a number of obstacles during this engagement: a change in business strategy partner, functional gaps in vendor software, and a significant customization to facilitate index spread processing, to name a few. Throughout, our client trusted us to lead the way, leaning on our ability to bridge gaps and deliver the right business solution.

In the end, our profitability solution was the first at this client to consolidate profitability calculations on a single platform, enabling a central definition of business rules and consistent analysis of dimensionalized, risk adjusted profitability.

RESULTS

Is an 18 month CD more profitable than an 18 month unsecured commercial loan? Does it depend on the customers involved? On the surface, counter-intuitive questions, but ones that, if answered effectively, provide great insight into how to manage customers and products, adjust operational incentives, and efficiently allocate future capital.

eBIS delivered a solution to help answer these questions. It’s great tasting pie, one that can help your business not just grow, but grow in the right direction.

CLIENT

The largest deposit institution in Scandinavia

CHALLENGE

Our challenge lay in modeling the economic market risk of contracting spreads on administered rate loan products in low interest rate environments: the effect of deposit interest rates near or at 0%, with loan interest rates steadily declining, compressing net interest margin and profitability. Our client endeavored to allocate market risk economic capital to cover lost profitability, represented by this contracting net interest margin.

It is in these low interest rate environments that demand for capital is often highest, making market risk a key factor in managing aggregate credit extension, product pricing, and fees.

IDEAS

eBIS analyzed the business requirements and internal processes of the client’s market risk group, and recommended a solution model that was parameter-driven, flexible, scalable, and integrated with a larger initiative to build a data warehouse for all capital and profitability reporting.  We categorized various functional requirements, analyzed alternatives, and then submitted our ideas, creating the foundation for a robust technical solution.

We categorized our ideas into 5 areas for ease of reference and management:

1) ARCHITECTURE

• Translate existing spreadsheet analysis to a scalable relational database environment with a flexible development platform, enabling user-defined input parameters on the front-end and the ability to reactively change modeling architecture on the back end.
• Minimize data redundancy and maintenance by centralizing configuration, input and output data in a central data warehouse, common to all economic capital engines.  Benefits:

a) A common data model
b) Consistent processing architecture
c) Reliable data inputs, which are validated and cleansed prior to processing
d) Modeled dimensions that provide depth and meaning to fact data
e) Scenario capabilities, allowing comparison of varying business assumptions

 2) STOCHASTIC INTEREST RATE SIMULATION

• Using prevailing interest rate levels as a starting point, stochastically simulate interest paths through time using the Cox, Ingersoll, Ross monte carlo method.  Calibrate a trend line relative to the interest rate starting point.
• Simulate interest rates to a user-defined horizon, e.g., 10 years, with income losses discounted on an NPV basis back to the end of year 1 using a user-defined discount factor, or one inferred from the stochastic equation.
• Set the number of interest path iterations based on user input.  Enable scalability of simulation paths to the hundreds of thousands.
• Set stochastic generation for capital allocation to 50,000 paths, a level that allows modeling at a high downside confidence interval, 99.97%, representative of a desired S&P credit rating of AA.
• Provide a random seed input to control reproduction of simulations across models and within variables, e.g., currency.
• Allow definition of a simulation index separate from product pricing indicies.  A delta in the simulation index would apply to the forecast periods of a product pricing index.
• Truncate simulated price movements at 0, but track internally the cumulative price level when negative.  In instances where the simulation index has a starting point higher than the product pricing index, the deltas could trend to positive territory later in the simulation.
• Make the simulation index currency specific, associated to either the transaction or base currency.

3) RELATIVE INTEREST RATES & VOLATILITY

• Tie the magnitude of volatility to relative interest rates; high interest rate environments will produce larger simulated interest rate movements than low ones.  Use a volatility algorithm that prevents simulated interest rates from dropping below 0.
• In stochastic modeling, force the interest rate path back to an assumed long-term rate based on two user-defined variables: a) mean reversion speed and b) term of volatility.
• Provide the ability to model an expected path of interest rates, modeled without the volatility component, for expected loss calculations.
• Start the simulation at any point (maturity) on a user-specified historical yield curve, allowing for currency-specific simulations.
• Assume parallel shifts in the asset and liability rates.  If empirical evidence suggests interest rates shift non-congruently by product, provide the ability to adjust the indices representing the asset and liability sides of the spread equation discretely.
• Calibrate model parameters to account for lower volatility at low interest rate levels, yet theoretically greater risk of loss events (contracting margin), and hence higher economic capital.

4) CAPITAL CALCULATION

• Employ VaR as the analytic measure, with downside loss levels measured as the difference in net interest margins through time, translating directly to allocated capital levels through a loss function.
• Make the loss function calculation dependent on the simulation index interest rate starting point and its position relative to the assumed long-term interest rate.  If the starting rate is < the long term rate, the unexpected loss is the path of rate simulation below the starting rate.  If the starting rate > the long term rate, the unexpected loss is the spreads between the expected long term rates and unexpected rates.
• Focus the loss function on simulated interest income and expense cash flows, which are not easily hedged centrally through transfer pricing.  Exclude from analysis fee or non-interest income that may be administered as a reaction to interest rate levels.
• Make capital allocation a function of the loss distribution, specifically the loss at the desired confidence interval in a tailed test, given the user-specified number of iterations.  In a simulation of 50,000 interest rate paths in a one-tailed test, capital is allocated against the 15th largest portfolio income loss.
• Adjust portfolio loss for VaR correlation and diversification factors to arrive at a final capital number.

5) DIMENSIONAL MODELING

• Use product groups to pool positions with similar attributes for processing and analysis.  Use a weighted average formula for calculation of variables that differ within product group.
• Allow for scenario-based simulation parameters to override standard product attributes.  For instance, if an administrative rate product pricing schedule calls for repricing upon a change in the fed funds rate, during simulation change the reprice trigger to any change in the stochastic rate path.
• Multi-dimensionalize the lost margin spread according to the input grain: currency, product group and department.

SOLUTION

We delivered a detailed architectural model for estimation of extreme downside interest rate market risk, calibrated to allocate capital relative to expected interest rates, and integrated with an enterprise risk management software suite.

Through tight collaboration with client stakeholders and an enterprise software vendor, we generated value-added ideas, and then applied them to an application roadmap. It was through our bridge-building process that our client received critical business solution architecture on an enterprise risk management platform where none previously existed.

A win for our client through improved functional modeling, solution flexibility, and data integration. A potential new product offering for an enterprise vendor partner. And eBIS helping in between doing what we do best: facilitating solutions for our clients.

RESULTS

In times of activist monetary policies, instituted by central banks to increase economic activity, commercial and retail banks feel pressure to increase capital supply. In these environments, interest rate market risk assumes heightened significance. Deposit interest rates can approach zero. Customers clamor to secure capital. Net interest margin compression can materially affect bank profitability. Case in point: JP Morgan Chase saw its net interest margin fall 31 basis points from March 31 to September 30, 2010, decreasing its net interest income by $1.2 billion. Financial institutions need a tool to assess the risk of market variables derailing their revenue model, influencing tangential areas such as product pricing, fees and credit extension.

eBIS provided this mechanism, enabling our client to understand the potential effect of market interest rate variables on product profitability. The result? Much more informed loan book management.