Pricing and Risk Management in Excel of Inhomogeneous Trading Book containing Different Types of USD Interest Rate Swaps

In an earlier post with the title Trading Blotter and Book Risk Management of USD Interest Rate Swaps in Excel: Example of a Book with 10,000 Trades, I explained how to calculate the price and risk of a fictitious portfolio of up to 10,000 vanilla fixed-to-floating USD interest rate swaps that all involved the 3-month USD LIBOR index.

Apart from the restriction of all swaps being of the same type, a major shortcoming of that analysis was the fact that the 10,000-strong portfolio was constructed out of data that were expected to abide by the formatting rules set by Deriscope.

In the real world, the portfolio data are delivered by the company's IT system in any possible format and the challenge is to translate these data to the final Excel object that can be processed by Deriscope for pricing and risk management purposes.

In this post I will make use of a small sample of slightly altered real-world swap data that I came across, courtesy of TPG Software, a company that serves the technology needs of over 100 banks and financial institutions over the last 30 years in the areas of Regulatory Reporting (DFAST, CCAR), Investment Accounting ( FAS 115-2, FAS 157, Other-Than-Temporarily Impaired Assets Accounting ), Hedge Accounting, Derivatives Accounting, CounterParty Risk etc. I would expect that most financial institutions around the world would use a more-or-less similar formatting for storing their swap portfolios.

I am also concerned about an additional constraint affecting the ability of small to medium houses to build their own curves out of market rates. Often, several financial reports are created based on assumed curves represented by long arrays of discount factors delivered by third parties or in-house facilities. This is usually the case in model validation and product control projects. For this reason, I choose to rely on curves constructed out of discount factors rather than market rates. 

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Overall Structure 

The aim is to calculate the price and risk of a portfolio consisting of 13 USD interest rate swaps that bear exposure to the following four indices:

USD LIBOR 6M, USD LIBOR 3M, SOFR, FED FUNDS

Not all the swaps are vanilla LIBOR vs Fixed. Many are basis swaps that pay OIS type coupons referencing the overnight SOFR or FED FUNDS index.

The 13 swaps are described in the report sent by the financial institution that hosts these swaps, as follows:

3yr FF/3ML basis swap $25mm 6/29/2024

15mo pay fixed swap $100mm 0.172% 8/13/2024

15mo 3ml/6ml basis swap $25mm 8/13/2024

1yr 6ML/3ML basis swap $50mm 6/4/2024

2yr MTN Swap 6ML $25mm 5/26/2024

10yr fixed swap CF hedge $10mm 5/22/2030

7yr FF/6ML Basis swap $15mm 5/20/2027

$15mm pay fixed amort swap FV hedge 0.76% 5/20/2050

30yr pay fixed FV hedge 0.76% $11mm 5/20/2050

30yr FV pay fixed 11.5mm 10/15/2045

5yr 6ML swap $10mm 6/16/2025

18mo basis swap $100mm 3ML/6ML 12/22/2024

1yr 6ML/3ML basis swap 7/1/24

The project will be accomplished be means of the following three downloadable Excel workbooks:

IntRateSwapPfolioInhom-Trades.xlsx

Contains only the raw data that define the 13 swaps in the format in which they are delivered by the IT system of the hosting financial institution.

IntRateSwapPfolioInhom-Market.xlsx

Contains only the discount factors of all relevant curves for all the calendar days between the valuation date and the maturity of the longest swap.

IntRateSwapPfolioInhom.xlsx

Contains the Deriscope formulas that read the data supplied in the two workbooks above and return the price, cash flows and risk of the portfolio.

The Trade Data 

The booked swap trades are defined by a table in a single sheet in the IntRateSwapPfolioInhom-Trades.xlsx workbook.

The table consists of 58 columns and 14 rows. Each row defines one swap. The top row contains text labels that allude to the meanings of the respective columns.

The 58 columns can be logically divided in three groups:

The first group consists of the leftmost 13 columns and contains information that affect both swap legs. They are shown below: 

Not all these columns are processed by Deriscope. For example, the descriptions in column F quite obviously can bear no significance to the Deriscope formulas, since no textual analysis has been yet implemented! The few columns that are processed by Deriscope appear shaded.

The second group consists of the next 21 columns and contains information that affects only one of the swap legs, the one designated as "Leg1". They are shown below in blue background, broken in two parts for better viewing resolution: 

The third group consists of the remaining 21 columns and contains information that affects only the other leg, the one designated as "Leg2". They are shown below in pink background, broken in two parts for better viewing resolution:

The Market Data 

The market data consist of the discount factors of all relevant curves as of today's valuation date of 21 Oct 2021 for all calendar dates up to a maximum horizon and of the necessary past fixings of all involved interest rate indices. They are supplied in two sheets in the IntRateSwapPfolioInhom-Market.xlsx workbook.

The first sheet contains all discount factors in the form of a four-column table that spans 18,262 rows, enough to represent all dates for the next 50 years. Its top section is shown below: 

These are the discount factors capable of forecasting the interest rates shown in the top row, as observed on the valuation date of Oct 21, 2021.

The left column is particularly important because it contains the SOFR discount factors, which play a special role in swap pricing by being used to discount all cash flows. The discount factors in the remaining columns are only used to forecast the future values of the corresponding interest rates.

The second sheet contains all historical fixings of these four indices on the applicable business dates. For simplicity, I have set them all equal to the respective spot rates. The top section is shown below:

Create a Deriscope Set Object by Parsing the Trade Data 

The first job is to transform the raw trade data to something that Deriscope can understand. This "something" is an Excel object of the Deriscope type Set because – as I have explained in my earlier post mentioned above – the Deriscope Blotter object can only be created out of an input object of type Set, which contains the trade data in a specific Deriscope-compatible format.

The transformation is done with the help of the function Parse that is available within the Deriscope type Set. It is shown below: 

I am not going to go over the technical details of how the Parse function works, since these can be read off the wizard as soon as one selects certain cells. For example, by selecting the cell H8 containing the key Filter Cols= I can see the following description in the task pane: 

The output of the Parse function is the object that appears with the handle name &Trades.1 in cell H4. I can see its internal structure inside the wizard by simply selecting the cell:

If I expand the viewing area of the grid in the task pane, I will see a table consisting of 48 columns and 14 rows, which corresponds to the 58-column raw data table.

The important feature of this table is two-prone: First, it boasts column titles that exactly match those expected by Deriscope during the Blotter creation step that will be undertaken next. Second, its contained data have all valid types. For example, the date columns contain proper dates, while the numerical columns contain proper numbers.

Similar to how I divided the raw data table in three sections, this Deriscope table may be also divided in three sections.

Below is the leftmost section that comprises the columns that pertain to both swap legs: 

Then follows the first half of the second section that comprises the columns that pertain to Leg1:  

Then follows the second half of that section: 

Then follows the first half of the third section that comprises the columns that pertain to Leg2:  

Then follows the second half of that section:  

Create the Deriscope Blotter Object ​ 

It is important to understand that every Set object is just a collection of stand-alone data that are not automatically interpreted as being part of the representation of concrete financial instruments, such as interest rates swaps.

To proceed, I will need to create an object of the special Deriscope type Blotter that enhances the Set type by adding the missing financial information.

Below is the formula that creates a Blotter object in cell AK8 out of the existing Set object. It makes use of a Base Swap Object created in cell AA4 that serves to define the financial information that is common to all swaps. The Set object is supplied as input in cell AL13. 

Create the Deriscope Portfolio Object 

The Blotter object is a lightweight object that acts as a sparse data collection of all 13 swaps and can be easily stored as a text file. But it is still not what in Deriscope is referred as Tradable and therefore cannot run any of the Tradable functions, such as the function Price.

Before I can therefore calculate the price or risk of my swap collection, I need to repackage the data into a Tradable object. This will be an object of the Deriscope type Portfolio, which inherits from the Deriscope type Tradable and is therefore a Tradable object as well. This repackaging is trivial and shown below: 

The Portfolio object has been created in cell AP4 using as input the existing Blotter object &TrdBlotter.1 in cell AQ5. It is just a wrapped Blotter object. As mentioned above, its only difference from the Blotter object &TrdBlotter.1 is that it is of type Tradable and therefore its price can be calculated.

The entry Virtual= TRUE is important if one deals with several thousands of swaps. It instructs Deriscope to construct the individual swap objects on the fly, only when they are needed in the course of pricing so that memory utilization stays low. 

Create the Deriscope Market Objects 

One required input to the Price function is the collection of curves, represented as objects of the Deriscope type Yield Curve. For the current purposes, I will need four such objects that correspond to the given discount factors associated with the USD SOFR, FedFunds, LIBOR 3M and LIBOR 6M.

Below is the section of my spreadsheet that contains the formulas that create these objects: 

Create the Deriscope Model Objects 

Another optional input to the Price function is a collection of Model objects that define my various assumptions that govern the risk and cash flow generation.

Below is the section of my spreadsheet that contains the formulas that create these objects: 

Portfolio Pricing 

The hard work of assembling all the required inputs to the final pricing formula has been accomplished. Now I am ready to run the function Price on my Portfolio object. Below is the result:  

The formula that kicks of the pricing routine is in cell C4 and appears in the formula bar as =ds(C5:D10). The result is the object &Variant_C4:4.1. It is an object rather than a number because I have specified the input Output= Full, which forces Deriscope to pack all output data inside an object of type Variant. Because the cell C4 is currently selected, the object's contents can be seen inside the wizard. These contents consist of the following key-value pairs:

Breakdown=$Set#4

CashFlows=$Set#5

Value= 19527019.52

Proc Time=0.339

The pair Value= 19527019.52 means the portfolio NPV equals 19,527,019.52 USD.

The pair CashFlows= $Set#5 also appears, but the object $Set#5 contains nothing due to the fact that I have not requested the generation of cash flows. I will do so in the next section.

Finally, the pair Proc Time= 0.339 means the pricing routine took 0.339 seconds to complete. 

Portfolio Cash Flow Generation 

Generating and displaying the cash flows of a portfolio is not different than doing so with respect to any single tradable. In principle, I would only need to run the Cash Flows function. But in practice, generating the cash flows of a very large portfolio could pose a challenge to the hardware and software constraints of any computer system. For that reason, Deriscope provides an elaborate way of specifying various filters that reduce the volume of generated data. These filters are part of a special Model object of type CF Model that is fed as input to the Price function. I have shown you above how this object looks like.

In the current case, all cash flows can be generated easily due to the fact the portfolio size is very small. Below is the formula that generates the cash flows: 

The formula that generates the cash flows is in cell C9 and appears in the formula bar as =ds(C10:D15). The result is the object &Set_C9:5.1, which is of the Deriscope type Set. Because the cell C9 is currently selected, the object's contents can be seen inside the wizard.

As I have explained in this section of an earlier post about custom USD fixed-to-floating swaps, the cash flows can be also displayed in the regular spreadsheet with the help of the Show function, as shown below. Unfortunately, there exist over 1,000 rows and many more columns that cannot be included in the screenshot here. 

Single Trade Cash Flow Generation 

One is often interested in a targeted portion of all portfolio cash flows.

One may, for example, want to investigate the cash flows of the single trade with trade ID 1686.

This can be easily accomplished by means of the Filter Values function, as shown below: 

As you see, the Show function now returns only 23 rows that represent the cash flows of the single swap with trade ID 1686.

Portfolio Risk Generation 

Generating and displaying the flat DV01 of a portfolio is also not different than doing so with respect to any single tradable. I will only show you below the results. If you are interested in the details of the generating formula, visit this section of my mentioned earlier post. 

Click on IntRateSwapPfolioInhom.xlsx to download the spreadsheet produced with the above steps.

You would also need the following two supporting workbooks:

IntRateSwapPfolioInhom-Trades.xlsx

and

IntRateSwapPfolioInhom-Market.xlsx

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com