Data Warehousing and Data Science

27 September 2015

Credit Risk and Market Risk

Filed under: Business Knowledge — Vincent Rainardi @ 5:59 am

Broadly speaking when we talk about risk in investment banking IT, it’s about 2 things: Credit Risk and Market Risk. Other financial risks are liquidity risks, operational risks, legal risks, but they don’t usually require a large IT systems to manage them.

Credit Risk

As a bank, credit is about lending money to companies to get interest. The companies are called obligors or counterparty. These obligors have obligation to pay us certain amount at certain times. The risk here is if those companies cannot pay us the amount they need to pay, when it’s due. This is called a default.

Credit risk is about 2 things: a) to manage the credit portfolio, b) to manage credit transactions. For a), the goal is to maximise the risk-adjusted return by maintaining the credit exposure of the whole portfolio within certain parameters. This is done using economic capital, correlation and hedging. These subjects are explained in these articles:

Economic Capital:


Things that a credit risk business analyst (BA) is expected to understand are: counterparty risk, CVA, Basel II & III, credit portfolio management, PD, LGD, EAD, Expected Loss, VAR, KMV, PFE, volatility, Economic Capital, RWA, Monte Carlo, correlation, ratings, credit derivative.

A credit risk data warehouse has the following functionalities:

  • Calculate Value At Risk and volatility of the credit portfolio every single day.
  • Produce regulatory reports such as Risk Weighted Assets, capital requirements, stress tests, and Potential Future Exposure.
  • Calculate portfolio risk measures such as Exposure At Default, Expected Positive Exposure, Credit Valuation Adjustment, counterparty risk.

Market Risk

Banks, insurance companies, pension funds and hedge funds all invest their cash in various things: shares, bonds, derivative, commodity, property, or in other companies. You intend to keep them for years. This is called investment portfolio, e.g. if you have £1 million to invest, you put 20% in bond, 50% in shares, etc.

Sometimes you don’t keep it for a long time. But only a few days, or even a few hours. This is called trading portfolio. Shares, FX, commodity, derivative, etc.

The value of your portfolio (be it investment or trading) can go up or down depending on 4 factors: the share prices, FX rates, interest rate and commodity prices. These 4 factors is called market risk, because the prices of these 4 factors are determined by the market (the buyer and the seller).

23 September 2015

Investment Banking

Filed under: Business Knowledge — Vincent Rainardi @ 7:45 am

Traditionally, the core business of an investment bank (IB) was to help companies raise funds in the capital market  and doing merger & acquisition. In addition to these 2 core services, IBs also offer these services to clients: research, fund management, trading, market making and wealth management. IBs also do trading for themselves (using their own money, called prop desk).

Let’s take a look at these services one by one. But before that, let’s quickly describe sell side and buy sides, and private and public sides.

In the investment banking world there are 2 sides: the sell side and the buy side. The sell side (link) are companies that sell investment services, for example: an IB which does broking/dealing, raise funds in capital market, M&A/advisory, and research. Buy side (link) are companies that buy investment services, for example: private equity funds, mutual funds, life insurance company, hedge funds, and pension funds.

Within an investment bank, we have 2 sides: private side and public side. Private side is the part of the bank which have access to inside company information (i.e. their clients) which are not available to the public. For example: M&A division and capital market division (Debt Capital Market/DCM and Equity Capital Market/ECM). Public side is the part of the bank which only have access to public information. For example: trading and research. Between these 2 side we have a “chinese wall”, which separate the 2 parts of an investement bank. The 2 parts must not (by law) exchange information. Chinese wall is a fundamental principle that has to be considered very seriously when designing IT systems for an investment bank.

Now let’s take a look at the services of investment banking:

  1. Raising capital is basically issuing bonds or equity (IPO or secondary offering). The bank acts as underwriter, meaning that the bank (usually a syndicate) buys all the bonds or shares from the company, then sells it to the market with spread (for stock) or fee (for bond). This require a lot of corporate finance work.
  2. M&A is the original meaning of “investment banking”, i.e. to find the client a buyer, or to find the client a company to buy (takeover, acquisition). Or, to have an idea that if company A & B are merged there would be advantage for both companies, such as synergy, vertical integration, increased market share or economy of scale, then try to sell the merger idea to both companies.
    There is also spin off or de-merger, where some part of a company is detached (created as a new company), and then sold off to another company. M&A also involves a lot of corporate finance work. M&A is also called “advisory”.
  3. Research covers equity research, fixed income research, macro economic research, technical analysis, quantitative analysis. In addition to individual companies, equity research provides industry trends, market trends, sector weightings and geographical preferences. Technical analysis (link) studies the historical price to predict the future direction in a particular market or a single-name issue. Research also provides tools which enables clients to access forecasts and evaluate capital structure, and to search for a specific company/sector/year/asset class.
  4. Fund management manages clients’ money in mutual funds (open ended) or investment trusts (close ended). Covering many sectors, i.e. by asset class (equity, bond, cash, commodity), by geography (UK, US, European, Global), by type (growth, income, recovery, absolute return).
  5. Trading buys and sells securities in the capital markets, on behalf of the clients. Covers various asset classes including equity, credit, FX, commodity, securitized, prime, multi asset and tailored. Tailored brokerage offers tailored off-market transaction such as distressed situations, sale-and-leaseback and company expansions (link). Prime brokerage (link) offers services for clients to borrow securities and trade/invest on netted basis and leveraged basis (link).
  6. Market making: provide liquidity in the market by quoting both buy and sell price (simultaneously) in a share or a bond or a commodity, usually narrower than the market spread (link), hoping to make money from the bid-offer spread (link).
  7. Wealth Management: provide advisory on financials and investments to high net-worth individuals/families, as well as the work/execution. This includes retail banking, estate planning, will, tax and investment management (link). Aka private banking, which is misleading because wealth management is not only banking but also legal, tax, and investing services.

Some of the top investment banks are (link): Goldman Sachs, Morgan Stanley, JP Morgan, Bank of America Merrill Lynch, Deutsche Bank, Citigroup, Credit Suisse, Barclay, UBS, HSBC, Nomura, RBC, BNP Paribas, RBS.

6 July 2015

Securitising Cash Positions

Filed under: Business Knowledge — Vincent Rainardi @ 7:17 am

It is an old age issue in asset management industry, that not all positions consist of security. They are cash positions or cash-like positions, e.g. settled and unsettled cash balances, FX forward/swap positions, IRS positions, and transaction proceeds. One of the solutions is to securitise these positions into instruments.

So we’ll have instruments called USD Settled Cash, EUR Unsettled Cash, Buy USD Sell GBP 01/04/15, and Pay 3% GBP Receive LIBOR3m+1% JPY. And here is where the issue lies, outlined in the next 3 paragraphs. When does the cash become settled? It depends on when the settlement message from the broker is processed. Do we create a separate instrument for Repo cash? (Repurchase Agreement). Do we create a separate instrument for collateral cash? (margin requirements).

FX forward has 2 legs. In the above example, the Buy USD date is today/spot (say 26th March 2015) and the Sell GBP date is future/forward (1st April 2015). Do we create 2 instruments, one for each leg, or 1 instrument?

IRS (Interest Rate Swap) can be float-for-fix or float-for-float. It can be the same currency or different currency. To close out the exposure (but not the accounting values), an IRS usually have a contra. If in the IRS we pay fix, in the contra we pay float. So how do we securitise this? Do we create the contra as a separate instrument? An IRS has 2 legs (usually a fix leg and a float leg, but could be both float) – do we create separate instruments for each legs? Do we create a separate instrument for each of the rates? Do we create a separate instrument for each of the fixing dates?

Attributes of cash-like instruments

What is the country of risk of “USD Settled Cash” instrument? United States of course. What is its country of domicile? N/A. What is its currency? It’s obvious, USD. What’s the issuer? Also obvious, US Government.

Now try for Pay 3% GBP Receive LIBOR3m+1% JPY. What is the country of risk? Umm… UK? Japan? Well, the risk is on the fixed leg, so the country of risk is UK. What is the issuer? Hmm…. blank? What is the currency?

The most common attributes of an instrument are: country, currency, issuer, asset class/type, asset subclass/subtype, sector/industry/subsector, rating, (effective) maturity date, maturity bucket, coupon frequency. All these need to be populated. Oh and ID fields, e.g. Ticker, Sedol, ISIN, Cusip; most of which will be blank for cash or FX lines. Description however, is crucial. It is used for looking up to determine if that instrument already exists or not. So it is very important to have a consistency, e.g. “USD Settled Cash” or “USD Cash Balance”? “EUR Unsettled Cash” or “EUR Cash (Unsettled)”? “GBP Collateral Cash” or “Collateral Cash GBP”?

Analytics for cash-like instruments

Analytics are measures which are dependent on price, time to maturity, and interest rates. The most common analytics for fixed income are yield to maturity, current yield, modified duration, spread, spread duration, option adjusted spread, z-spread, convexity, gamma. Some of these will be zero for cash, but some of them have values (like yield for example).

These analytics will need to be calculated if we combine several positions into one instrument. Some of them are not simple additive, e.g. they need to be weighted with % contribution when summing up. Some of them doesn’t work with weighted sum.

The other solution: by not securitising them

The other option is not securitising cash and FX positions, and they become positions without instruments. If we take this route will need to store all security attributes in the holding table.

17 June 2012

Distribution Yield vs Underlying Yield

Filed under: Business Knowledge — Vincent Rainardi @ 2:03 pm

If we look at a fixed income investment such as high yield bond funds or high income equity funds on Fidelity, HL or Morningstar websites, sometimes we wonder what’s the difference between Distribution Yield, Historic Yield and Underlying Yield. One of the best people explaining this is NFU mutual (link), whereas the authoritative source in the UK is Investment Management Association (IMA, link, link)

  • Historic Yield is last 12 months, for equity funds
  • Distribution Yield is next 12 months, for bond funds
  • Underlying Yield is next 12 months, for bond funds, including the purchase price and expense.

Distribution Yield is useful for comparing between funds. This is what is planned to be given to the shareholder as dividend in the next 1 year, calculated based on the fixed interest payments of the bonds that the fund has.
Underlying Yield is better for measuring the income from the fund, because it takes into account the annual management charge.

Example1: a bond fund with distribution yield of 5.8% (this is the total dividend that should be given to shareholder in 1 year) has an annual management charge of 1.5%, so the underlying yield is 4.3%

Example2: an equity fund with historic yield of 3.5% (this is what has been given as dividend to the fund shareholders in the last 1 year) has an annual management charge of 1.7% so the “real yield” is 1.8%

Bond funds have another type of yield: yield to maturity, which the amount the fund receives from the bonds it holds. It is declared as 6 months rate.

For example: the fund has only 1 high yield bond, with 8% coupon. The yield to maturity is 3.923% because (1+3.923%)^2 = 1.08

15 June 2012

Credit Default Swap (CDS)

Filed under: Business Knowledge — Vincent Rainardi @ 7:15 pm

Credit Default Swap, or CDS, is an instrument which is used to insured an exposure against a default. When a bank lends credit to an obligor and this obligor is unable to make the payment on time, it is a credit event or default. When a default happens, then the CDS counterparty (the protection seller) pays the bank an agreed sum called notional amount.

For this service the bank pays the counterparty a fee, which is called CDS spread. This is usually paid upfront and annually. One company that publishes CDS prices is Markit, Markit also publishes the CDS Implied Rating, which is the S&P style of credit reliability of an entity, determined from their CDS price. The higher the price, the lower the rating, because it means that it is more likely that the company will default.

The CDS spreads is noted not as an absolute figure, but relative to the notation. For example, if the notation is USD 10 million, and the spread is 1%, then the fee that the lender needs to pay is $1m x 1% = $10,000. But a CDS spread is not noted in percentage. It is written in basis points, or bps, which is 1 percent divided by 100. So 1% is 100 bps, 2% is 200 bps, and so on. The notional amount is the maximum amount in which we are protected against. For example, if our exposure is $100m, and we buy a CDS with limit of $20m, then the counterparty will only pay us $20m in the event of obligor default.

There is a difference between the market spread and the traded spread. A market spread is the price of a CDS for that referenced entity that the publicly traded on that day. Of course the market spread will depend on the tenor. A tenor is the duration of coverage in which the CDS is active. For example, we can buy a CDS which lasts for 1 year, 3 years, 5 years, or 10 years. The longer the tenor, the higher the spread. Tenor is determined from the maturity date minus effective date. A traded spread is the price that we actually paid when we buy or sell the CDS, which can be above or below the market price on that day.

The obligor which the bank protected against is called the referenced entity. A referenced entity is usually a single name. And this single name is usually a legal entity, i.e. it is incorporated as company, a partnership (LLP), a government, an individual or a municipal which is a department in a local or national government.

In contrast to a single name CDS, we also have a CDS index, which is an amalgamation of several CDSes. Two famous indices are iTraxx and CDX. For example, we have an index for financial services company in Europe. Rather than buying several CDSes for individual companies, we buy a CDS Index for European banks which should lower the cost. If any of the constituent company in the index goes default, the seller pays the protection buyer. The pricing of a CDS Index could be tricky because different obligor has different credit rating, and different likelihood of going default.

ITraxx covers the following sectors: financials (senior and sub), non financials, TMT (telcom, media, tech), industrials, energy, consumers, automotive. Benchmark: top 123 companies, top 30 high volatility, 40 top crossover (between investment grades and junk/high yield).

CDX covers the following: CDX.NA.IG: investment grade, CDX.NA.IG.HVOL: Investment grade but high volatility, CDX.NA.HY: high yield, CDX.NA.HY.BB, B, XO (crossover), EM (Emerging Market), LCDX (Loan only)

CDS has seniority level. Senior Unsecured Debt (for corporate) or for a government it’s Foreign Currency Sovereign Debt is the most senior. Subordinated or Lower Tier 2 Debt has lower seniority. There is also Junior Subordinated or Lower Tier 1 which is even lower in seniority. CDS with higher seniority level has higher prices.

Recovery rate of a CDS reflects the amount that the lender might get back at the credit events. Recovery rate depends on the seniority. The more senior a CDS is, the higher the recovery rate. ISDA (the international body that governs the standards of CDS) assumes that the recovery rate for a senior unsecured (SNRFOR) is 40%, subordinate (SUBLT2) is 20%, and for emerging market (both senior and subordinate) is 25%. The final payment from the seller is the calculated as notional minus the amount recovered (which is recovery rate x notional). ISDA stands for International Swap and Derivative Association.

There two settlements: cash settlement and physical settlement. Physical settlement is where the buyer gives the seller the bonds that they are holding, and the seller pays the notional value (whole amount/par). Cash settlement is where the seller pays the buyer the notional minus the recovered amount (= recovery rate x notional). These days, most CDS are bought/sold not for hedging, but to take a position on credit, so cash settlement is preferred.

The recovery rate is determined in an auction, usually run by Markit or Creditex. A primer on CDS Auction is here, which is a must read. And the formal rules of CDS Auctions are determined by ISDA, here. There are 2 stages:
Stage 1. All dealers submit their bid and offer, at agreed spread and quotation size, and at agreed precision (usually 0.125%). They also submit their physical settlement requests. From these we calculate the Initial Market Midpoint (IMM), and the adjustment amount. And we calculate the open interest, which is the total of the physical settlement requests.

Stage 2: All dealers submit their new bids and offers, which is added to the bids and offers in stage 1. Any bids above IMM + ½ spread is set to IMM + ½ spread.
Check how many bids needed to cover open interest. Say we need four bids to cover the open interest. Then the final price is the lowest of these four. The final price is the recovery rate, the amount the investors get back from the defaulted bonds.

Restructuring. When an obligor defaulted, they restructure their debt. For the same entity, same tenor, same seniority, and same coupon (see below), we have different restructuring mechanism for which the CDS covers for. This is known as the document clause. We have NR (no restructuring, old restructuring, modified restructuring and modified-modified Restructuring). The market price of a CDS depends on: entity, date, tenor, coupon, seniority, document clause and currency. A CDS traded in EUR may have different market spread to JPY.

CDS big bang (read document from Markit, link): We have 2 elements of payments: fixed coupon and upfront payment. Coupon is the payment that the buyer pays every quarter, stated in bps. Coupon is usually either 100 or 500 bps. On top of the coupon, the buyer also pays an upfront payment, also stated in bps.

Conventional Spread is the CDS prices for CDS with coupon of 100 bps. Whereas CDS with coupon of 500 bps will be quoted (generally) with upfront payment.  Buyers pays the coupon in full on the first payment date. So we need to calculate the accrual rebate payment to the buyer at the time of the trade.

CDS Curve is a series of CDS spread across different tenors/years.

Traditionally, the purpose of buy or selling a CDS is to remove asset from balance sheet, to meet regulatory capital requirement/liquidity, improve risk of capital, ROE, ROEC. Basically to off load credit risk from balance sheet. But now a days banks trade CDS to take position on credit risk, not to hedge a portfolio.

DTCC is the clearing house for CDS. It stands for Depository Trust and Clearing Corporation. Their function is not just covering CDS, but also equity and fixed income.

CDO is collateralised debt obligation. It’s an asset backed security with multiple tranches (seniority / risk classes), collateralized by loans or bonds. Junior and equity tranches offer higher coupon and interest rates to compensate the default risk. There are 4 types: CLO (Loan), CBO) (Bonds), CSO (Synthetic/CDS), SFCDO (structured products, i.e. mortgages/MBS, ABS)

Value at Risk: is the risk of loss for a portfolio, for a time horizon and probability.

Interest rate risk: risk of decreasing revenue of bond, loan because of interest rate raise/fall. Basis risk: LIBOR and US prime rate moving in different direction. Yield curve risk: different between short term and long term interest rates. Repricing risk: when interest rate fall, loan with variable rate will generate lower interest income.

Main sources: Wikipedia, Markit web site, ISDA web site.

11 August 2010

Layering in Insurance

Filed under: Business Knowledge — Vincent Rainardi @ 4:21 pm

One of the most difficult things in data warehousing is actually not its technicalities, but understanding the business. There is no escape that we have to understand the business, in order for us to be able to model the data warehouse correctly. For example, I came across a case the other day where somebody asked me about the concept of layering in insurance, and specifically in Lloyd’s market. He needs to understand layering concept in order for him to be able to model the data warehouse correctly. He searched the web and found the following:

Layering: “The building of a program of insurance coverage using the excess of loss approach. Layered programs involve a series of insurers writing coverage, each one in excess of lower limits written by other insurers. Umbrella liability coverage is frequently structured in this manner, whereby a number of umbrella insurers write coverage at various levels, on an excess of loss basis, ultimately providing an insured with a high total limit of coverage”

After reading that definition he didn’t quite understand it so he asked me to explain. So I explained the following, which I’m sharing with you in this article/post.

In reinsurance industry nobody want to cover the whole thing. If a large US retail firm XYZ looks for coverage against fire, tornado, wind storm, hurricane, and flood for all of its 3500 stores in the US, the broker or managing agent will probably arrange it as follows:

Syndicate1 $100m x $0
Syndicate2 $400m x $100m
Syndicate3 $500m x $500m
Syndicate4 $800m x $1b

“x” means excess. Example: you took a house insurance with Churchill for £120k x 250 meaning that if the house burned down, and it costs £120k to rebuild, Churchill will give you £119,750 and you have to pay the 250 yourself. That’s excess.

In the above case Syndicate1 is taking $100 million excess zero. Meaning, no excess.
Syndicate2 is covering an insured amount of $400m from $100m.
Syndicate3 is covering an insured amount of $500m from $500m.

So if the XYZ store in Buffalo was burned and the loss estimate is $5m, then Syndicate1 will pay $5m.
But if a disaster like Hurricane Ike hits and XYZ lost 70 stores, which cost them $150m, then Syndicate1 will pay $100m and Syndicate2 will pay $50m.
So when a catastrophe like Katrina hits and XYZ lost 200 stores which cost them $700m, then Syndicate1 will pay $100m, Syndicate2 pays $400m and Syndicate3 pays $200m.

Obviously the premium is different. Syndicate1 bears the biggest risk, because the chance of a small event occurs is much bigger than a big event. Statistically speaking that is, and here’s where actuarial calculations and matrices comes in. Hence Syndicate1 gets the biggest premium.

Syndicate4 will get the smallest premium (percentage wise), but very profitable because it is very likely that the year will pass without a single event hitting $1 billion mark. Which means Syndicate4’s loss ratio will be very healthy.

Reinsurance companies like high layer business because of its profitability. Unfortunately the capital adequacy standard from FSA (and from next year Solvency II) require them to have large enough capital in order to be able to make good amount of profitable business in that market.

As I said in the beginning of this post, one of the most difficult things in data warehousing is the business knowledge. And insurance is one of the most difficult industry there is. As usual I welcome any corrections, discussions and questions on

Blog at