11 minutes reading time (2226 words)

Libor Transition Impact on Portfolio Pricing: A Comparative Study with and without the published Bloomberg Spread Adjustments


Everybody these days seems to ask the same question: On D-Day when LIBOR will cease to exist and pricing will rely solely on risk-free reference rates like SOFR or SONIA and the Spread Adjustments published by Bloomberg, what will the impact on portfolio pricing be?

The mechanics of pricing a single USD interest rate swap with and without the ISDA-Bloomberg fallback rules has been described in utmost detail in my recent post titled Libor Cessation: Price and Risk of existing Vanilla Interest Rate Swaps by applying the ISDA Fallback Protocol.

The mechanics of calculating the price and risk of a portfolio consisting of various types of interest rate swaps was laid out in my previous post titled Pricing and Risk Management in Excel of Inhomogeneous Trading Book containing Different Types of USD Interest Rate Swaps.

Today, I will revisit the swap portfolio of the above post and reprice it using the exact same market data, but this time considering the fact that all USD LIBOR rates will be eventually replaced by fallback rates that are defined to equal the sum of corresponding SOFR term rates and appropriate spread adjustments.

To be specific, in terms of LIBOR dependence, the portfolio references only the 3M and 6M LIBOR tenors, which are known to last until Friday, 30 June 2023. Rather than pretending that my valuation date is the next business day of Monday, 3 July 2023 – which will be the first day when non-LIBOR-based portfolio valuation MUST take place (!) -, I will stick with the valuation date of 21 October 2021 I used in my previous post because I have the actual market data as of that date, including the Spread Adjustments as calculated and published by Bloomberg on that same date.

Doing so, I can arrive at a more realistic comparison than if I had assumed a future valuation date where the Bloomberg Spread Adjustments would not be known.

If you are not familiar with Deriscope, visit the Quick Guide for an introductory tour to the most important Deriscope features.  

Overall Structure 

The portfolio consists of 13 USD interest rate swaps that bear exposure to the following four indices:


The precise details of these swaps are discussed in my previous post mentioned above.

The project here will be accomplished be means of the following three downloadable Excel workbooks. The first two are the same as those used in my previous post.


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.


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.


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 with and without the Fallback assumption. 

Structure of the Pricing Workbook IntRateSwapPfolioInhomFallback.xlsx 

Most parts of this workbook are identical with the workbook IntRateSwapPfolioInhom.xlsx that I used to price the portfolio without the Fallback assumption in my previous post.

The first three Deri- prefixed sheets named Deri-Tradable, Deri-Market and Deri-Model are the same as in the previous workbook and build the corresponding Deriscope prerequisite objects.

Then follows a new sheet named Fallback, which contains all objects pertaining to Fallback calculations. This sheet will be described below in some detail, although its objects have been fully analyzed in my above-mentioned post that deals with the application of the ISDA Fallback Protocol.

The three old workbook sheets named Pricing, CF, Risk are here as well, but they now also include the results of the Fallback treatment.

Finally, an extra sheet named CF-Fallback has been added so that the cash flows of the portfolio under the Fallback assumption can be also viewed. 

Setup of the Fallback sheet 

The first and foremost object that needs to be created is the one shown below in cell C4:  

The red-colored cells contain the Deriscope formulas. Details on the meaning of the shown colors and the syntax of the Deriscope spreadsheet formulas can be found in this introductory post about the spreadsheet Deriscope function.

As you see, the &FbackModel.1 object is of type Fallback Model and is essentially just a package of two other objects, the &FbackSpreads.1 and &FbackConvs.1 created respectively in cells C14 and C26.

The latter two objects are of types Fallback Ibor Oi Mkt and Fallback Ibor Oi Table respectively. They are much smaller than their counterparts used in my earlier post because they are now made to include information pertaining exclusively to the two LIBOR rates encountered in my current portfolio, namely the USD LIBOR 1M and 3M. There is no reason to include information about other IBOR indices that are not referenced by any of the swaps in my portfolio.

The #Tenor value in cell D32 is left unspecified intentionally because I want the displayed conventions to apply to all LIBOR rates regardless of their tenor. This is ok because my portfolio does not reference the LIBOR tenors of 1W and 2M, which are the only rates that cease after the earlier date of 31 December 2021.

The values 0.26161 and 0.42826 in cells E20 and E21 respectively have been read from Bloomberg on 21 October 2021 using the Bloomberg codes YUS0003M and YUS0006M. They are the Spread Adjustments for the 3M and 6M LIBORs and represent the median spread between IBOR and adjusted reference rate over a five-year historical period as calculated by Bloomberg.

The job of the &FbackModel.1 object is to tell Deriscope to check all encountered interest rates and convert them – if applicable – to corresponding fallback rates using the rules conveyed by that object. Such conversions will occur "on the fly", as needed during the execution of the portfolio pricing algorithm. The usage of that object is critical in pricing very big portfolios containing thousands of swaps because the pricing can proceed on the original portfolio without the need of a prior creation of an "equivalent" portfolio consisting of converted swaps. Nevertheless, I will demonstrate both approaches below as a proof of their mutual consistency.

As I did in my previous post, I intend here to do three things:

  • 1.Calculate the price of the portfolio and its constituents.
  • 2.Display the cash flows.
  • 3.Calculate the flat DV01 portfolio exposures on the referenced curves.

In my earlier post, each of these jobs required the input of at least one Model object that supplied the various definitions and assumptions. Now, I can carry all these jobs using the same input, albeit enhanced with the presence of the new object &FbackModel.1.

Rather than passing the various Model objects one-by-one to the various formulas, I create below special objects of type Model Set that represent collections of the required objects. These objects are appropriately named for easy reference. 


Below is the pricing result in sheet Pricing:  

For expositional clarity, I display the blue dependency arrows only for the formulas in row 5 that return the Portfolio Price.

The formula in cell H5 returns 19,527,020 based on the input object &Result Orig.1 created in cell C4 out of the data shown in the upper left box. This the NPV in USD of the original portfolio &TrdPfolio.1 as of 21 October 2021 as calculated in the traditional way, without any explicit consideration on the forthcoming LIBOR transition.

The formula in cell I5 returns 19,568,877 based on the input object &Result Fback.1 created in cell C16 out of the data shown in the mid left box. This the NPV in USD of the original portfolio &TrdPfolio.1 as of 21 October 2021 as calculated by assuming that the 3M and 6M LIBOR rates that set after 30 June 2023 are replaced dynamically during pricing by the corresponding fallback rates in accordance with the Bloomberg Spread Adjustments of 0.26161 and 0.42826 respectively, as mentioned above.

The single material difference between the two input sets appearing respectively in the upper left and mid left boxes concerns the value associated with the key Models=. While the upper left box contains the pair Models= &CrvChoice.1, the mid left box contains the pair Models= &FbackMdlCol.1.

The formula in cell J5 returns the exact same value of 19,568,877, which this time is based on the input object &Result Fback Conv.1 created in cell C28 out of the data shown in the lower left box. This the NPV in USD of the converted portfolio &FbackPfolio.1 as of 21 October 2021, as indicated by the pair Object= &FbackPfolio.1 in row 29. The converted portfolio has been created further below in cell C36 by the Fallback function. Note the pair Models= &CrvChoice.1 in row 32 indicates that the converted portfolio is priced in the traditional way without any explicit fallback assumption.

Underneath the box with the portfolio prices lies the Portfolio Breakdown box where the itemized prices of the constituent swaps are displayed. In terms of dependencies, the analysis above applies here as well. 

Understanding the Pricing Result 

At first glance, given the fact that the two prices 19,527,020 and 19,568,877 have been produced on the basis of very different assumptions regarding the floating cash flows, it is almost miraculous they are so close to each other differing by only 41,858 USD!

Clearly, the achieved proximity is due to the used values 0.26161% and 0.42826% of the Bloomberg Spread Adjustments for the 3M and 6m LIBORs. If these spreads were only 10 basis points higher, i.e. 0.36161% and 0.52826% respectively, the fallback price would have become 20,293,009, giving rise to a much bigger discrepancy of 765,990 USD.

But, why aren't these two prices exactly equal?

The main reason is the following:

The value 0.26161% has been calculated by Bloomberg as the median spread between the 3M LIBOR and SOFR over a five-year historical period. Similarly for the value 0.42826%. These two values change every day because the five-year historical period always ends with today's date and therefore changes every day. Most importantly, they are not the values that will actually apply when the extinct LIBOR rates will need to be replaced in the future! If the LIBOR cessation date had occurred before the current valuation date of 21 October 2021, these two values would have been instead constant over time and would have also equalled the actual values being used in future times for LIBOR replacement. The market right now prices the swaps by implicitly assuming effective forward Spread Adjustments that are time dependent and differ from these Bloomberg values.

For a deeper understanding of the meaning and significance of the Bloomberg Spread Adjustments you may want to read my analysis at the bottom of my earlier Libor Cessation article. 

Viewing the Cash Flows 

The CF sheet displays the cash flows as calculated with the currently conventional treatment, whereby the LIBOR never expires. Details are provided in the previous post.

The CF-Fallback sheet displays the cash flows generated with the help of the &FbackCFMdlCol.1 object, which – as we have seen – contains the special object of type Fallback Model. Below is the formula that does the job: 

The output is quite large and spans more than 1,000 rows. The interesting region is the one surrounding the LIBOR transition date of 30 June 2023 and is shown below, with several less important columns being hidden: 

With yellow is marked the very last LIBOR flow, which hinges on a 6M LIBOR rate set on 29 June 2023, which is the last rate lucky enough to survive the coming cessation. The two green-marked rows underneath appear as being of Overnight Composite type, but in reality are ex 3M LIBOR converted rates. The corresponding spread in cell AC214 appears as 0.26161%, which is the supplied Bloomberg Spread Adjustment. The cell AC213 contains 0.26042% because the original swap had a spread of -0.0012%, which makes the final spread equal to -0.0012% + 0.26161% = 0.26042%.

It will be instructive to present these three rows below the corresponding rows of the non- Fallback pricing: 

Now the conversion details of the LIBOR flows can be seen by comparing the respective green rows. In particular, the index period dates in the lower section are produced according to the ISDA-Bloomberg conversion rules.  

Viewing the Flat DV01 Risk 

The Risk sheet displays the flat DV01 exposure of the whole portfolio and its constituent swaps on the four curves. Here same comments apply as in my earlier post about the risk of a single swap under the Fallback assumption. Below is the sheet's screenshot:  

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

You would also need the following two supporting workbooks:




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 

Pricing and Risk Management in Excel of Inhomogene...