FRTB in Excel

  • FRTB Calculations directly from Excel.
  • What-If analysis of new trades via quick trade entry.
  • We use the CRIF format to make entry of trade portfolios simple.
  • FREE 14-day trials available here.

Background

FRTB in Excel from Clarus calculates the capital requirements for your portfolio in Excel. We also check whether risk factors are modellable.

The Fundamental Review of the Trading Book (FRTB) by the Basel committee is the work conducted “to ensure that standardised and internal model approaches to market risk deliver credible capital outcomes“. We’ve written plenty about this work in the past, including:

  1. FRTB – What you Need to Know
  2. Internal Models or Standardised Approach?
  3. FRTB Excel Calculator

FRTB in Excel is available to try on a 14-day FREE trial. Download the workbook today to get started. It couldn’t be more simple.

Read on to find out more.

Excel Workbook

Let’s take a look at how this works.

After downloading and opening the workbook, a Get Started sheet is shown.

This sheet provides a tutorial on how to get started. We find the best way to become familiar with the Excel sheet is to have a play around with the examples already there – changing cell values etc. Be warned that the results are returned very quickly, so you might not notice that a calculation has even occurred! We provide links to all of the features for you to try out.

FRTB Standardised Approach

Switching to the “FRTB” worksheet, we see an area to upload our trades (via CRIF files), plus a panel to enter what-if trades. We provide the results split into old trades (under “Account”) and new trades (under “What-If”) so that you can see your capital contribution for each at a glance.

Where a CRIF file might not be detailed enough, we have provided extended fields – which are all optional. These are Notional, Issuer, CreditRating and Addon.

What could be easier?

It sure beats trying to build all of the calculations from scratch, as we did in our original blog on FRTB SA in Excel.

Remember that these calculations cover the Standardised Approach (SA). This is a calculation that all banks, even those on Internal Model Approach (IMA) will have to calculate, due to the capital floors that were recently agreed.

Under the revised output floor, banks’ risk-weighted assets must be calculated as the higher of: (i) total risk-weighted assets calculated using the approaches that the bank has supervisory approval to use in accordance with the Basel capital framework (including both standardised and internal model-based approaches); and (ii) 72.5% of the total risk-weighted assets calculated using only the standardised approaches.

BCBS, December 2017

New, what-if trades can be easily entered into the same sheet using any of our Clarus quick trade formats. This can either be in natural language (“pay USD 10y 100m”) or Bloomberg tickers (“USSW10 100m”).

FRTB Modellable Risk Factors

For those who are looking at IMA for FRTB, we also use our Clarus data to test for continuously available real prices. The Excel sheet “ModellableRiskFactors” provides this key input for FRTB. For further background, please see FRTB – Modellable Risk Factors and Non-Modellable.

Showing;

  • We simply ask for the currency, index and tenor – e.g. AUD BBSW 6M.
  • The sheet returns a list of all swaps (and their Bloomberg ticker) associated with that definition.
  • We then show in green and red whether an individual swap is deemed modellable (green) or non-modellable (red).
  • We should aim to use only modellable factors in an IMA model, to avoid expensive capital add-ons.

FRTB Modellable Tickers

The final Excel sheet, “ModellableTickers”, flips the inputs above on their head. We now test individual tickers to see if they are modellable risk factors:

Meaning that you can test all individual tickers that you may use in an IMA model to make sure that they are consistently considered “modellable” under the FRTB framework.

For added peace of mind, this check could be automated either via Excel or using our Microservices API.

Simple.

Easy to use.

Accessible.

Invitation to Try

Are you working on an FRTB project?

Or curious about the FRTB calculations?

Or do you need a what-if tool to check capital consumption of new trades?

If so, why not contact us for a FREE trial of FRTB in Excel?

It could not be simpler – just download our Excel workbook and you are good to go.

Who would not prefer to use Excel given the choice?

Stay informed with our FREE newsletter, subscribe here.

Leave a Reply

Your email address will not be published. Required fields are marked *