Trading Blotter and Book Risk Management of USD Interest Rate Swaps in Excel: Example of a Book with 10,000 Trades.
In my previous post about USD Interest Rate Swaps in Excel, I explained how to calculate the price and risk of a single USD interest rate swap using actual market data from Bloomberg as of 22 May 2019.
In the current post, I will make use of the same market data to calculate the prices and DV01s (both flat and bucket) of two different swap collections.
The first collection will be comprised of 30 swaps traded on the same date, the 22 May 2019. The swaps will be arranged in an Excel table of 30 rows that functions as a trade blotter in a real time environment under automatic calculation mode, where the trader may change the features of any swap and see the corresponding NPV and flat DV01 output immediately.
The second collection will be comprised of 10,000 swaps that were traded (entered into) during the trading days of the past year. These swaps are represented by a text file, which is typically produced by the backend system designed to hold the book positions. This file will be then processed by Deriscope and produce the NPVs and DV01s of all constituent swaps.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Preliminaries
The first tab in my workbook is called Curves and is dedicated in the creation of the two yield curves that are needed for the pricing of the swaps.
Below are the sections allocated for the creation of the OIS-based discounting and Libor-based forecasting yield curve respectively. You may visit my previous post noted above for more details.
The next tab is called Swap and contains the definition of a Swap object – named &BaseSwap.1 in the screenshot below - that supplies the conventions shared by all swaps in my portfolio.
In effect, every swap will be constructed by first cloning the &BaseSwap.1 and then modifying selected properties, such as the End Date or the Fixed Rate.
This approach is far more efficient than constructing every swap from scratch.
The next tab is called Model and contains the definition of the &Model.1 object shown below.
This object is of type Model[FixedFloat IRS] and is required as input to the swap pricing routine because it specifies which of the two curves should be used for forecasting purposes.
Without this input, the pricing routine would resort to its default strategy of picking the riskless curve for both the discounting and forecasting task. This curve would be the USD-OIS.1 due to its setting Issuer= %Riskless Issuer that can be seen in the screenshot with the respective curve above and would lead to erroneous results.
With the currently chosen input, the USD-OIS.1 curve will be used only for discounting and the USD-3M.1 curve will be used only for forecasting, i.e. determining the forward Libor rates and their corresponding floating cash flow amounts.
Trade Blotter
Below is how the trade blotter in my spreadsheet looks like.
All blue-color data are values that can be entered/edited by the user in the respective cells.
The red-color data are the output of the Deriscope spreadsheet formulas. They display the NPVs and flat DV01s of my 30 swaps with respect to both curves.
This basic setup is fast enough to support Excel automatic calculation mode without a noticeable slow down. Changing any of the blue-color entries thus causes an immediate change of the red-color output.
I could easily add more rows to handle a greater number of swaps and more columns to display additional output, such as fair rates for given target NPVs.
You can see below the array formula used in column J that produces the NPV values.
The shown formula is {=ds("ObjectTools::Show","Reference=",$R$4,"Key=","Extra Data","Prices","2,31,2,2")} and appears in curly brackets because it is pasted as an array formula over the one-column range J4:J33.
More details on using the Show function to display some subset of an object's data are available in my introductory post.
The main object here is the &FlatDV01.1 created in cell R4 by the formula =ds(R5:S9,R10:T12) as shown below. The object's contents can be seen in the wizard because the cell R4 is selected.
The various objects are created in the following dependency order:
First the object MyBlotter.1 of type Day Blotter is created in cell X12, which references the blue-color swap data at the far left of the spreadsheet.
Then the object MyPortfolio.1 of type Portfolio is created in cell X4 by reading the MyBlotter.1 object. It contains the collection of fully constructed swap objects that correspond to the blotter rows.
Finally, the object FlatDV01.1 of type Variant is created in cell R4 by running the Price function on the MyPortfolio.1 object.
One of the inputs to the Price function is the object &FlatMdl.1 created in cell R17. This is the object that instructs Deriscope through its key-value pair Delta Mode= Flat to calculate the flat DV01s.
The contents of the &FlatDV01.1 object can be seen in the wizard as soon as the cell R4 is selected:
The red-colored data in columns J, K and L stem from this object here.
For example, I can see where the NPV data of column J come from, by clicking on the lens sign to the right of the key "Extra Data=", which takes me to the contents of its associated object $Variant#2:
If I then click on the lens sign to the right of the key "Prices=", I see the contents of its associated object $Set#5, which corresponds to the data in column J:
Full Risk Report
Changing the key-value pair Delta Mode= Flat of the object &FlatMdl.1 to Delta Mode= By Bucket is all it takes to generate a table with the swap deltas with respect to all curve nodes.
So, I set up a sheet where a run the same Price function as before, but with the Model object &FlatMdl.1 replaced by the &ByBucketMdl.1 shown below:
Then the Price function returns an object that contains several different arrays that correspond to the DV01 sensitivities of the swaps with respect to the various curve nodes.
The final result with respect to the forecasting Libor curve is shown below. A similar table is also produced with respect to the discounting OIS curve.
Full Book Analysis
Calculating the P&L and risk of a whole book containing open positions on thousands of trades presents a certain set of challenges.
I will show you how I can use Deriscope to analyze a book consisting of 10,000 interest rate swaps traded in a period of 12 months from 22 May 2018 to 22 May 2019.
In particular, I will set up a spreadsheet where the NPVs and bucket DV01s of all 10,000 swaps can be calculated in about 20 minutes, utilizing only one logical CPU of an average speed computer.
Typically, the data that define the swaps held in the book are maintained by the bank's back end system and can be exported to either Excel or a text file in various formats.
Deriscope is capable of reading a text file that conforms to a specific XML format where keys and their associated values are designated with the <k> and <v> tag respectively.
More details on the XML interface to Deriscope can be found at my post about FX Option Portfolio Pricing with and without Excel.
In the currently treated case, the text file represents an object of type Period Blotter, which differs from the already encountered object MyBlotter.1 of type Day Blotter only in having an additional column titled #TradeDate that supplies the trade dates of the recorded swaps.
The text file is called BigBlotter.txt and is available in compressed form for download here:
This is how the file's contents that define the top row with the titles and the first two rows with swap data look like:
The text file is imported in Excel with the Import function that reads the file's contents and creates the corresponding object of type Period Blotter, as shown below:
The next step is to create the corresponding portfolio, i.e. an object of type Portfolio, on which I can run the Price function in order to calculate the NPVs and DV01s as I did earlier with the much smaller portfolio of 30 swaps.
The key-value pair Virtual= TRUE is important here.
It instructs Deriscope to create the object &MyBook.1 in a special way so that the Tradable objects corresponding to the 10,000 swaps are not created in memory. This is necessary if the Excel is 32 bit because otherwise the allocated memory size would push Excel to its limits and make it hang or crash. The Tradable objects will be created dynamically later only if and when needed during a particular process.
Parenthetically, the Get Portfolio function accepts additional optional parameters that allow the construction of sub-portfolios consisting of trades that fulfill certain criteria.
For example, the following formula creates a portfolio that contains only one swap, the one of which the Trade ID matches the given value of 1314:
The following formula creates a portfolio that contains all swaps traded on 13 Jun 2018:
Final Formula and Result
The formula that produces the book results is the one shown below in cell AE4:
The formula is "=ds($AF$25, AE5:AF9, AE10:AH10, AE11:AF12)", of which the first argument $AF$25 is a Boolean that controls whether the function is run or not. It is currently set to FALSE, which instructs Deriscope to NOT run the function and only return whatever value is currently shown on that cell.
FALSE is the standard entry to protect the user from unwittingly starting a calculation that may take a very long time to complete.
The new element here is the insertion of the object &Hist-Libor.1 in cell AH11 as an additional value associated with the key Markets=. It is created in cell AL4, is of type Historical Values and contains the historical 3-month USD Libor fixings over the past 3 months, i.e. from 21 Feb 2019 to 21 May 2019. The shown rates have been acquired from macrotrends.net.
Contrary to what I had done wrt the risk of my 30-trade portfolio, I have set here Risk Ref= &USD-3M.1, i.e. decided to run the risk against only one curve – the forecasting Libor curve – for efficiency reasons because this is the most important risk output.
After setting the special first argument to TRUE and pressing SHIFT-F9 to recalculate the formula in cell AE4, the results appear after about 50 minutes on my pc that has an Intel Core I7 920 chip. I would expect a shorter time of about 20 minutes in a high-end pc. Note that the whole process runs on one thread, which means that several books could be processed in parallel by utilizing all available logical CPU cores. A further significant speed improvement could be achieved by using Excel 64 bit that can accommodate the creation of non-virtual portfolios, i.e. portfolios created with the setting Virtual= FALSE.
Below is the top section of the range where the output numbers are displayed.
Each row corresponds to one of the 10,000 processed swaps, with the exception of the top numerical row that contains the totals, i.e. the sums over all the swaps.
The left column shows the Trade ID and the second column shows the flat DV01.
The remaining columns show the DV01s wrt the curve market instruments. They are partitioned in three different colored groups: DV01s against the deposit rate, DV01s against the Futures prices and DV01s against the swap rates.
Below is the bottom section of this range:
The table below shows the consolidated results corresponding to the top row in a transposed, vertically oriented form:
Click on IntRateSwapPfolio.xlsx to download the spreadsheet produced with the above steps.
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
