ftp (fund transfer price) - info.cs.pub.roinfo.cs.pub.ro/scoaladevara/pdf/cfo dwh ftp -...
TRANSCRIPT
UBIS CFO DWH & FTP (Fund Transfer Price)
Bucharest, June 2016
Overview
2
1. DWH Concepts
2. CFO – DWH data loading process overview
3. CFO Data Warehouse- technology platform Architecture
3.1 Mainframe platform
3.2 Unix Data Stage Server
3.3 Teradata DBMS Platform
4. FTP – Introduction
5. FTP – Overall Architecture
6. FTP Components
7. FTP – Phase 1
8. FTP – Phase 2
9. Related systems
1. DWH Concepts
3
• A data warehouse is a system used for reporting and data analysis, and is considered as a
core component of Business Intelligence environment.
• DWH are central repositories of integrated data from one or more disparate sources.
• They store current and historical data and are used for creating analytical reports for knowledge
workers throughout the enterprise.
• The data stored in the warehouse is uploaded from the operational systems (such as marketing,
sales, etc., shown in the figure to the right).
• The data may pass through an operational data store for additional operations before it is used
in the DW for reporting.
• A data mart is a simple form of a data warehouse that is focused on a single subject (or
functional area). The sources could be internal operational systems, a central data warehouse,
or external data .
• IN UBIS each asset has its own data mart (FTP also);
4
• The most important approaches are the dimensional approach (introduced by Ralph Kimball )
and the normalized approach (also called the 3NF model, introduced by Bill Inmon );
• In Ralph Kimball’s approach is stated that the data warehouse should be modeled using a
Dimensional Model/star schema , while in Bill Inmon's approach is stated that the data
warehouse should be modeled using an E-R model/normalized model.
• In a dimensional approach, transaction data are partitioned into "facts", which are generally
numeric transaction data, and "dimensions", which are the reference information that gives
context to the facts.
• In the normalized approach, the data in the data warehouse are stored following, to a
degree, database normalization rules. Tables are grouped together by subject areas that
reflect general data categories (e.g., data on customers, products, finance, etc.).
• The normalized structure divides data into entities, which creates several tables in a relational
database. When applied in large enterprises the result is dozens of tables that are linked together
by a web of joins
5
Kimball’s bottom-up approach:
• Data marts are created first.
• These provide a thin view into the organizational data, and as and when required these can be
combined into a larger data warehouse.
• Dimensional modeling focuses on ease of end user accessibility and provides a high level of
performance to the data warehouse.
6
Inmon’s top-down approach:
• Inmon defines data warehouse as a centralized repository for the entire enterprise.
• Data warehouse stores the ‘atomic’ data at the lowest level of detail.
• Dimensional data marts are created only after the complete data warehouse has been created.
Thus, data warehouse is at the center of the Corporate Information Factory (CIF), which provides a
logical framework for delivering business intelligence.
7
Inmon vs. Kimball:
8
Pros and cons of both the approaches:
2. CFO – DWH data loading process overview
9
• The CFO DWH stores data relevant for the Unicredit financial officer that contains
information for many products of the bank (mortgages, accounts, bonds, deposits etc..)
generated by other procedure.
• For each product that is integrated in the CFO Core Layer there is a standard workflow, with
some customization, that tracing how a row from a file received is included in the
datawarehouse.
• The general procedure consists of following steps:
File is received on the server
Datastage ETL extract and load a stage
Data quality checks
Surrogated Keys
The information are stored in the tables 3NF
Create history
10
2. CFO – DWH data loading process overview
3. CFO Data Warehouse- technology platform Architecture
11
• The technology platform of the CFO DWH is composed of 3 main layer, each one of
which is an area in which different tasks and processes are accomplished:
1. Mainframe platform
2. Unix DataStage Server : DataStage Engine and File System
3. Teradata DBMS Platform
12
JCL jobit contains a unix shell call that executes a DS command for running DS Job Sequences
Unix shellIt executes a DS command that runs the instance of DS Sequences dedicated to the DS parallel job for the upload on DB
DS job SequnceL’istanza della sequenze lancia, il job ETL Parallel di caricamento/Estrazione verso/da Teradata DB
DS job Parallel •Job Staging•Job Core•JobExtractor It executes the connection to Teradata DB for the process of data download/upload
Example of a CFO data flow:
3.1 Mainframe platform
13
• Is the environment from which the 90% of the source data that have to be loaded in the
datawarehouse come.
• Each data loading process start from the Mainframe platform through the OPC applications.
• Each application is composed of multiple JCLs that through the Beta92 scheduler
communicate with the DataStage environment through a set of shell scripts to execute in the
correct established order all the ETLs programs needed to complete the data loading process.
3.2 Unix Data Stage Server
14
Composed of:
• File System : A portion of the DS server file system is structured and dedicated to acquire
input data file that has to be loaded in the DWH.
• In the file system is also developed a software layer (composed of shell scripts) used to
let communicate the mainframe OPC schedule application with the datastage engine.
• DataStage Engine : The repository and the engine of the ETL tool adopted to manage
the data loading in the DWH (DataStage) are installed on that server.
15
Main Unix Shells
UNIX SHELL DATASTAGE SEQUENCE
Load_stg.sh It triggers the sequence Seq_Load_Staging
ScriptGenerator.sh It triggers the sequence Seq_Load_Primary
Extract_from_DWH.sh It triggers the sequence Seq_Extracting_File
In regards to the activities of implementation of load/extraction processes that use job ETL Datastage,
there are 3 main Unix shells, handled centrally:
•Load_stg.sh
•ScriptGenerator.sh
•Extract_from_DWH.sh
Each corresponds to a DS launch Sequence of load/extract Data Parallel Job .
16
Load_stg.sh
• The trigger of a Datastage sequence that runs Loading Staging ETL is done by surveying
the names of the ETL job in a single shell that contains the sequence Seq_Load_Staging.
17
ScriptGenerator.sh
• Shell generation process for the Primary is a semi-automatic process and consists in filling
out a shared document, named SCRIPT_GEN.csv, with the informations that characterize
the calls to ETLs .
• Once the modified file SCRIPT_GEN.csv is available on the server, from the
same folder you will run the script ScriptGenerator.sh that regenerates and
creates all the shells related to CFO-DWH core layer-
18
Data Stage Engine - ETL programs
The data loading process of the CFO DWH is composed of two specific standard typologies of
ETL programs:
Staging loading ETLs
Core loading ETLs
19
Staging loading ETLs
• The Staging loading usually read data from a file format source to load them in the Teradata
staging table. The process is represented bellow:
20
1) Enrich the record by adding the technical key (record counter).
2) Create an output DataStage dataset containing the copy of the input source data with the technical
key added.
3) Retrieve from the F-table the records discarded by the DQ checks during the previous runs and that
have been eventually corrected in order to try to reprocess them.
4) Performs the Syntactic DQ checks for the input fields that have to be put in a date/number format
output field.
5) Performs the input to output data conversion to load data in the staging table.
6) Fill a table called Q-Table with the details of the results of the syntactic DQ checks.
7) Load data in the staging target table.
21
Core loading ETLs
The core layer data loading ETLs respects a more simple structure (see picture below) and are
used to read data from the staging table to load them in the core layer table .
22
This kind of jobs will performs the following processes:
1) Read data from the staging table, by using also complex SQL if is needed.
2) Performs data transformations.
3) Performs the data normalization to store different information related to a given key (that usually
in the staging are contained in different attributes fields related to a single record) in a way that
respects the structure of the data model (3NF).
4) Create the target [N] table and load it.
3.2 Teradata DBMS Platform
23
• Some important parts of the data elaboration process, that make part of the common data loading
and treatment data workflow are performed by executing Teradata stored procedure.
• Each procedure performs a different data transformation or elaboration task, but all these SPs are
executed by using a main SP called CALLER_SP that dispatch the request to the specific SP
in function of a specific parameter that identify the typology of the process that has to be
executed.
• The execution of these steps (Stored procedures) are performed by using a UNIX shell defined on
the DataStage server called Call_master_sp.sh.
eg. Call_master_sp.sh P C0 DMUTUI X D MD 20140515 1
The last parameter (PROCEDURE_ID) is used to let the CALLER_SP identify the “slave” procedure
that has to be executed
24
The main procedures of a scheduling workflow are:
- Call Master n°1 Update of portfolio with new values using the parameters sets on table
TRS_PARAM (Used only for particular data)
- Call Master n°2 Performs data cleaning on the F_% and S_% table by comparing the
content of both in order to avoid the possibility to have duplicate records between the S_% table
content and the record stored in the F_% that can be reprocessed (in this case delete the oldest
record)
- Call Master n°3 Performs the surrogated keys generation and the data enrichment process.
The technical table in which SK and ENRICHMMENT rules are stored is the SP_KEYS_PARAM
(see par 3.4)
- Call Master n°4 Performs the semantic Data quality process by executing the DQ rules
stored in the DQ_PARAM_SIR related to referential integrity check, key uniqueness check and
value range validation check.
25
FTP – Introduction
• Fund Transfer Price (FTP) definition: an “arm's length” methodology of internal
remuneration/charge of deposits and loans at market prices, between two correlated parties as if
they were not correlated.
• FTP is a process to:
• Facilitate fair pricing of financial risk components and support business activity (pricing ex -ante)
• Constitute the basis to analyze business performance profitability and work out segmentreporting (performance measurement ex - post)
• Facilitate balance sheet steering
• FTP system should be defined FOR EACH PRODUCT (or by typology of products)
• FTP should reach as much as possible the final client (or products in terms of segmentreporting)
5. FTP – Overall Architecture
26
26
ET
L CFO
DWH
Legacy
Ext. Sources
DM
FTP
Phase 2FTP
Engine
Historicizing
DM
(Market data)
FTP Environment
Phase 2
Phase 1
FTP Data Mart loading
27
CFO Environment
Group CFO DWH (GCD)
Core Layer
Schedule
FTP Engine
1
Staging
Tables
5
FTP Data Mart
FTP Application Layer
2
4
6
7 8
93
Sources
EU
RO
SIG
OP
C/B
eta
48
Sources
3
Source procedures
integration with GCD via
ETL tasks
Data loading into the core
layer
Market Data DB is loaded
via scheduled ETL
Scheduled calculation are
started on FTP
FTP requests data in the
FTP DataMart via
application layer
Data loading from core
layer to data mart (via SP)
During calculation, FTP
Engine ask for Market Data
The output data is written in
the FTP Data mart
The output data is
historized into the core
layer
Calculation steps
1
2
3
4
5
6
7
8
9
28
FTP components
Equity
Rebate
SBA
Pricing
Liquidity
risk
Country
risk
Interest
Rate risk
Model
risk
= + + ++
Hurdle
Rate
(MIS) + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
e.g. tax effects
FTP structure shared (EMC)
FTP components are further broken down in the following slides, in order to deepen
■ Definition and rationale
■ Calculation methodology
■ Perimeter of application
■ Engine rules
Liquidity
Spread
Basis
Risk
Loan
Prepayment
Adjustment
Eligible
Assets
Adjustment
Embedded
Option
Spread
Loan
Prepayment
Swaption
Spread
6. FTP Components
29
6. FTP Components
Base Rate
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
The Base Rate is, in principle, the contractual rate
Negative impacts on SBAs for assets, and positive
for liabilities
It depends on product time bucket (Sight, Short
Term, M/L Term), rate typology (Fixed vs. Floating),
fixing
Methodology
Perimeter
* Work in progress – part of UCIFIN was priced out of TIF procedure before One4C
Engine Rules (CFO CE)
All contracts under TIF
procedure (Retail
Network Italy, UCCB,
UPB)
All contracts
(UCIFIN)*
TIF (same rules as
FTP Engine)
All new contracts
calculated via CFO
CE
Repricing of True Sale
securitizations (CFO
CE)
Stock From September 2010 Sight Items: 1 month Euribor (365 basis) (monthly average to CdG, daily fixing
to Tmis, ALM, Murex)
Fixed rate: Euribor amortizing curve with the same tenor of the maturity date of
the contract
Floating rate: Euribor bullet curve with the same tenor of the index rate of the
installment date
According to the product typlogy, tenor may be equal to the maturity date of the
contract
Frequency: at each interest rate refixing and at each change in interest rate
typology and/or tenor of the underlying index
Sight: 1 month Euribor monthly average
Bullet curve*: Euribor (all bucket available on market data) + SwapEur
Amortizing curve*: built on the bullet curve, by applying a 3-month French
amortizing plan
Model
Engine
30
6. FTP Components
Maturity Premium - Liquidity Spread
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
Liquidity Spread is the premium for a specific
contractual maturity relevant to UCG cost of funding
UCG Senior Curve that is the cost, in terms of basis
points, for benchmark senior issues in the market; it
identifies the UCG medium long term risk on
wholesale public cash market.
Negative impacts on SBAs for assets, and positive
for liabilities
Curve built as follows, according to maturity
― Below 1 year: Euribor vs. Market Place UCGMI Bid/Ask quotation
― Over 1 year: UCG Senior curve
UCG Senior Curve horizon could be extended from current 10 years to whether 20
or 30 years
Input frequency: weekly
Always EUR-based
To be evaluated UCG Senior Curve calculation for different currencies
Perimeter
Sight items: n.a.
Short Term Items: Delta UCGMI Bid/Ask vs. Euribor
― Bid quotation for liabilities
― Ask quotation for assets
Medium Long Term Items: for each cash-flow of the contract, the
interpolation of the curve at the installment date (cash-flow maturity) is
required, and Liquidity Spreads referred to each bucket are summed
Frequency: at origination
All contracts new contracts calculated via CFO CE
(see Annex – “FTP AS-IS analysis” for details)
Temporal differences due to the starting time of
FTP application
Perimeter differences due to the starting time of
FTP application
FTP AS IS methodology for deals originated before 01/01/2005: an average UCG Senior curve of year 2005 is applied, since no specific curve is available
for deals originated from 01/01/2005 to 01/01/2007: Planning Retail provided a Balance Sheet Optimization curve
for deals originated after 01/01/2007: UCG Senior curve
Maturity
premiumLiquidity
Spread
Basis
Risk
Methodology
Engine Rules (CFO CE)
Model
Engine
31
6. FTP Components
Maturity Premium - Basis Risk
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description Methodology
Engine Rules (CFO CE)Perimeter
Premium (positive/negative) for the usage of an
index different from 3 month Euribor (reference
index in the FTP logic) for floating rate contract
It applies to floating rate contract linked to
parameters as Euribor 1/6/12 months
Positive/negative impacts on SBAs, depending on
the index parameter
ALM provides the spread to be applied, vs Eur3M, according to the tenor of the
index and the maturity of the floating rate contract
Input Frequency: weekly
Basis risk grants the aforementioned re-parametrizations for floating rate items
― Tenor: all Euribor rates with tenor different from 3 months (e.g. 6-month Euribor-based
deals) shall be adjusted via Basis Swaps, in order to equal 3-month Euribor rate
For all floating rate based contracts this methodology is applied
― Frequency: has to be computed at the same date for which the liquidity
spread is defined (at the origination)
― Interpolation on ALM curve
― Remark – mortgages with fixed interest rate “tecnico”, liquidity spread is
priced when the floating rate is effective, but under conditions of deal
origination
New FTP component,
priced by FTP Engine
starting from 1/11 and
applied to the contract
originated after 1/11
Stock New Positions
Maturity
premiumLiquidity
Spread
Basis
Risk
Model
Engine
32
6. FTP Components
Sight Stickiness
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
It represents the liquidity spread for the core
amount of sight items
Positive impacts on SBAs for sight liabilities
(e.g. Deposits) and negative for sight assets
(e.g. Loans)
Sticky profile based on behavioral model that
estimate the persistence over time of sight
items
Methodology
Engine Rules (CFO CE)Perimeter
ALM provides a matrix for the stickiness of Assets/Liabilities of each SBAs
Input frequency: monthly
Sight stickiness below 1 year ― Amount: core below 1Y
― Spread: difference between Euribor and Swap vs 1 M with a tenor equal to the Weighted
Average Life (WAL) of the Core amount < 1 year
― Reference Period: the last N months where N is the Weighted Average Life (WAL) of the
Core amount < 1 year― for partial maturities the interpolation is needed
Sight stickiness over 1 year― Amount: core above 1Y
― Spread: the average value of senior curve with a tenor equal to the WAL of the Core amount
> 1 year + basis risk Euribor 3m vs Euribor 1M (since the index for the senior curve is
Euribor 3M)
― Reference Period: the last N months where N is the Weighted Average Life (WAL) of the
Core amount > 1 year
― for partial maturities the interpolation is needed
CFO DWH smoothes the overall stickiness amount modelled by ALM, according
to the product typology (Asset vs. Liabilities)
Frequency: every day
― Daily: to TMIS and MXG
― Monthly: to TX
Not applicable for stock
― Sight Items review their remuneration
every month
All Sight Items of all SBAs
― Products in scope are, for example:
current accounts, libretti liberi
Model
Engine
SBF (Salvo Buon Fine) are Short Term positions treated like sight items with a temporary maturity of 45 days, depending on their weighted average life estimation
33
6. FTP Components - Prepayment and other adj.
Loan Prepayment Adjustment
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
Base rate / maturity premium adjustment connected
to the modeling of loans prepayment
the prepayment reduces the duration of the loan with
a positive effect on maturity premium and base rate
Methodology
ALM provides the expected prepayment curves for the different types of loan
(mortgages, personal loan, CQS)
Input frequency: quarterly
Prepayment curve calculated using an econometrical function that defines the
survival probability of the loan based on historical data
Engine Rules (CFO CE)Perimeter
Sight items: n.a.
Short Term Items: n.a.
Medium Long Term Items: same calculation methodology of liquidity spread,
but under an accelerated amortizing plan
Frequency: at the origination
ProductsMortgages and Personal Loans
Consumer financing
Stock New Positions
Pricing by FTP Engine for
UCCB only (equal to 0.18)
on constant monthly
probability
New FTP component, priced
by FTP Engine starting from
1/11 and applied to the
contract originated after 1/11
Deals of division Retail
Sub-division Family
Prepayment
and other
adjs.
Loan
Prepayment
Adj.
Eligible
Assets
Adj.
Model
Engine
34
6. FTP Components - Prepayment and other adj.
Eligible Assets Adjustment
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
Funding cost adjustment connected to the treatment
of eligible assets (e.g. ABACO products,..)
Methodology
Engine Rules (CFO CE)Perimeter
ABACO assets
― ALM Matrix
― CRO Transition Matrix
BEI curve
― Spread now equal to null
OBG
― ALM Matrix (starting from 01 January 2011)
“cassa depositi e prestiti”
― Spread As Of Communication from CDP
Input frequency: weekly
* See Annex – “FTP AS-IS analysis” for further details
Sight items: n.a.
Short Term Items: n.a.
Medium Long Term Items:
― Eligible Assets Adjustment = Cost of Funding – Liquidity Spread
― Adjustments for Eligibility equal the ABACO curve weighted for the survival rate
of the deal as eligible, then accounting for the residual life of the deal itself
Frequency:at the origination
All eligible contracts
(on the basis of CRO
Transition Matrix)*
Stock New Positions
All eligible contracts
All new contracts
calculated via CFO
CE
Prepayment
and other
adjs.
Loan
Prepayment
Adj.
Eligible
Assets
Adj.
Model
Engine
35
6. FTP Components
Country risk premium
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
The Country Risk Premium represents the
compensation against risk of counterparty default due
not to specific factors related to the performance/trends
in the institutional business, but rather to macro-events
of a political nature (nationalization, impossibility of
conversion, violence)
It is proportional to the premium of the Credit Default
Swaps related to the specific country on the
expiry/maturity of the transactions
Methodology
The first step envisages the estimation of an econometric model that relates
the market CDS with the rating of each country, in order to extrapolate the
short term component – more subject to fluctuations especially during phases
of high market volatility.
Then we use the model output to compute the CDS indicator of each country,
that is the CDS long term component determined by the model estimation
The CRP will be simply the difference between:
― the CEE CDS Indicator (that prices the macro fundamentals of the
country, avoiding the CDS market volatility)
― the Eurozone CDS Indicator (weighted average of CDS indicator of
Austria, Germany and Italy)
Input frequency: weekly
Engine Rules (CFO CE)
Same calculation methodology of liquidity spread, by applying the Country Risk
Spread curve.
Frequency:at the origination
Perimeter
All contracts that fulfills the both conditions
― Residential currency of the counterpart is not
EUR;
― Contractual currency is different from the
residential currency of the counterpart
All new contracts
calculated via CFO
CE
Stock New Positions
Model
Engine
36
6. FTP Components - Interest Rate Risk Premium
Embedded Option Spread
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
Premium for the financial option embedded in the
contract (e.g. cap, floor)
Premium based on the market quotation of similar
option
FTP must be increased/reduced by the underlying
option cost/income sold to the customer
Methodology
Perimeter Engine Rules (CFO CE)
ALM provides the premium of the embedded option according to
― the option strike
― the maturity of the option
― the maturity of the contract
Input frequency: weekly
Option spread is given by the fair value of the option expressed in basis points
running to be applied up to option maturity (Option Fair Value) / (Notional *
Option duration)
The allocation of the spread is product driven
Frequency: starting date of the option
The spread is given by the Fair value of the option on the market expressed in
basis points running and it’s equal to: (Fair Value Option)/(value of a basis point
spread increase)*0,01%
It has to be charged to the client for the time period where the option is alive
New FTP component,
priced by FTP Engine
starting from 1/11 and
applied to the contracts
originated after 1/11
All mortgages with
cap/floor embedded and
within typology “salvarata”
Stock New Positions
n.a.
Embedded
Option
Spread
Loan
Prepayment
Swaption
Spread
Interest
rate risk
premium
Model
Engine
37
6. FTP Components - Interest Rate Risk Premium
Loan Prepayment Swaption Spread
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description Methodology
MethodologyPerimeter Engine Rules (CFO CE)
Premium to be paid for the early redemption of a
fixed rate loan without penalty
It is currently applied for fixed rate residential
mortgages only, where an early termination is not
applicable by law
It is the price of the embedded option under an interest
rate perspective
FTP must be increased by the underlying option cost
sold to the customer
ALM provides the premium of the prepayment option according to
― the tenor of the fixed rate period
― the maturity of the contract
Input frequency: weekly
Prepayment Swaption spread is given by the Fair Value of the option expressed
in basis points running to be applied during the fixed rate period
Fair Value cost is calculated using toghether a market and statistical model to
estimate a potential loss generated by early repayment in different interest rate
enviroment
The allocation of the spread is product driven
Frequency: starting date of the fixed rate
The spread is given by the Fair value of the option on the market expressed in
basis points running and it’s equal to: (Fair Value Option)/(value of a basis point
spread increase)*0,01%
It has to be charged to the client for the time period where the loan rate is fixed
Stock New Positions
New FTP component,
priced by FTP Engine
from 1/11
Applied to Fixed rate
MLT Mortgages
originated after 1/11
(EUR)
Deals of division Retail
Sub-division Family
n.a.
Embedded
Option
Spread
Loan
Prepayment
Swaption
Spread
Interest
rate risk
premium
Model
Engine
38
6. FTP Components
Replicating Portfolio
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description Methodology
Perimeter Engine Rules (CFO CE)
Premium/cost over Euribor of the replicating strategy
for the core insensitive amount of sight & saving
Replicating Portfolio, through cash or derivative
products reduces the volatility of Net Interest Income /
Economic Value
In a low interest rate scenario, it is positive for sight
liabilities (e.g. Deposits)
No stock is available
― Sight Items review their remuneration every
month
All Sight Liabilities of all SBAs
― Products in scope are, for example: current
accounts, libretti liberi
CFO DWH smoothes the overall stickiness amount on base rate modelled by
ALM, according to the product typology
Frequency: every day
― Daily: to TMIS and MXG
― Monthly: to TX
Model
Engine
Replicating spreads rebated depend on the effective P&L result generated, i.e.
actual swaps traded on the market (as of current situation) or internal deals
closed with the Treasury
Starting from Jan 2011 effective margin of replicating portfolios is allocated on a
gross approach in terms of assets\liabilities and SBAs (Retail, Private, Corporate)
according to the amount of core insensitive* and the its Weighted Average Life
ALM provides a matrix for the Replicating Spread of Assets/Liabilities of each
SBAs
Input frequency: monthly
* Core Insensitive: stable base of sight & saving deposits that is insensitive to interest rate changes
39
6. FTP Components
Managerial Alpha
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description Methodology
On stock positions Managerial Alpha adjustment is currently set up for
Self securitizations, via Managerial Adjustment Metric = [-Current costs/(1+ Actual Overcollateralization Rate)] = -11 bps
Covered Bonds (OBG), via Eligible Adjustment Metric = {[Floating spread current/(1+ Actual Overcollateralization Rate)] - Liquidity spread} = NONE for funded
Covered Bonds vs. -13 bps for retained Covered Bonds
Perimeter Engine Rules (CFO CE)
At Group level, allocation of diversification and
dimension benefits or mispricing of specific products
can be addressed through a managerial a
component (ex-ante / ex-post) in order to properly
support balance sheet steering/specific product
pricing
Corporate Center P&L affected
Spreads parameterized and applied to the new deals Covered bonds, Self-
securitizations
Stock New Positions
Not yet applied
Model
Engine
40
6. FTP Components
Commercial Adjustments
Equity
Rebate+ + ++ + + Managerial
α
Base
rate
Replicating
Ptf +
Cost of
Equity ,
Credit
Risk,
Other
Commercial
adjustment
Prepayment
and other
adjs.
Maturity
premium
Sight
stickiness
Country
risk
premium++
Interest
rate risk
premium
Description
Commercial Adjustments
At SBA/commercial level, it allows for specific
incentives to support commercial flexibility on
products/customers satisfying marketing and global
commercial targets
Decided by SBA (Commercial Network)
Business P&L affected (SBAs)
Methodology
MethodologyPerimeter Engine Rules (CFO CE)
Both in LMIS and TX, Commercial Adjustments are reported via specific
Value Types / flagged items
Stock New Positions
n.a. n.a.
Commercial Adjustment
― SBAs may quantify Commercial Adjustments for each FTP component, in
order to rebate the overall Managerial Adjustment on single products /
Relationship Managers (MBO purposes)
Model
Engine
7. FTP – Phase 1
41
FTP is composed by 2 phases:
• Phase 1 – The data mart loading from Core (done thought the feeding process specific for every
product);
8. FTP – Phase 2
42
• Phase 2 – metric calculation done inside FTP engine (developed in java).
9. Related systems
43
Pricing Tool *
MUREXTreasury Position
Keeping
Selection of deals eligible to be
calculated (new deals, floating rate refixing…)
Preparation of input data for
calculation (deal data, market data, parameters)
FTP Calculation
Set of default values for errors
and data incoherencies
FTP
values
storage
On Line applicationsReal Time
EndOfDay Daily File to Mx
ALM PROBanking Book Position
Keeping
EndOfDay Daily File to AlmPro
TXNetwork P&L
EndOfDay
Monthly File to
Performances Meas.
System- PMS
TMISTreasury P&L
EndOfDay
EndOfMonth
Daily File to Treasury
MIS
Supported Systems Computed at Exploited by
Defaulted
values storage
Data Flows of FTP Engine based on CFO DWH
• Fund Transfer Prices (FTPs) are the value transfer drivers, since they build the rationale for
the internal remuneration of the deals among units involved in the Group.
• Down streams asstets are represented bellow: