15 minutes reading time (3028 words)

Pricing Currency Swaps with different Collateral Currencies in Excel. The GBP SONIA vs USD SOFR case


For a review of cross currency swaps and how to build an implied discounting yield curve of the foreign currency from given market basis spreads, read my post Currency Swaps and Basis Curves in Excel, where both the market swaps and the to-be-priced bespoke swap involve the exchange between 3-month USD LIBOR and 3-month EUR LIBOR collateralized through a USD denominated custodian account.

Now I will consider the case of a bespoke currency swap between GBP SONIA and USD SOFR with the additional twist that the collateral account is held in GBP currency even though the market GBP-USD swaps - of which the published basis spreads are used to calibrate the implied discounting curves - are assumed to be collateralized in USD.

Before attempting to price the GBP-collateralized swap, I will first build the prerequisite curves and price the corresponding USD-collateralized swap to a) show how Deriscope handles currency swaps involving compounded overnight indices, such as SOFR and SONIA and b) keep the numerical results of this vanilla case so that they can be compared against the results of the GBP-collateralized swap.

Table Of Contents 

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

Get a free Deriscope activation code that enables you to run the accompanied spreadsheet!

The GBP SONIA vs USD SOFR Currency Swap

The currency swap under consideration will be a 10-year SONIA-SOFR swap that exactly matches one of the market swaps used in the construction of the basis curve.

All payments are quarterly and reference the SOFR index on the USD leg and the SONIA index on the GBP leg.

More precisely, at the end of each quarterly period the calculated compounded average rate R of the respective overnight index forms the final index that determines the paid-out amount A through the usual formula A = NRτ, where N is the swap notional and τ is the period's length in annual units calculated according to some agreed daycount convention.

An overview of the conventions that guide a SONIA-SOFR swap is shown at the Bloomberg screenshot below.

The specifics of the 10-year USD-collateralized swap traded as of 26 June 2023 are shown below.  

The exact same swap structure is created in the spreadsheet cell O6 through the Deriscope spreadsheet formula =ds(O7:P32) that returns the handle name &USD GBP Swap.1 that uniquely identifies the corresponding Deriscope Object of which the Deriscope Type is a Currency Swap as seen below.

Similar ds formulas exist in the cells C6, F6, I6 and L6 that create objects (denoted by red font color) that are then entered as input (denoted by green font color) to the final formula in cell O6. Data shown in black or blue are hard-coded. 

Note in particular the entry -21 in cell P28 next to the key Tgt Spread(BP)=, which equals the market basis spread for the 10-year swap in the table with the market basis spreads displayed further below. I will explain later how one can verify that the two swaps match each other by comparing their respective cash flows.  

Assembling the Market Data 

The following market data are a prerequisite for pricing the swap.

First in line come the two local Yield Curves, i.e. the USD SOFR curve and the GBP SONIA curve, both produced by the respective OIS market rates observed on 26 June 2023.

Below, these two curve objects are produced in cells C4 and K4. 

Next come the data that convey fx information. These consist of the spot fx rate and the basis spreads of the currency swaps.

The spot fx rate along with its conventions is represented by the object &USDGBPSpot.1 of type FX Value in cell S4 shown in the image below.

The basis spreads are used to build the Yield Curve object &GBPBasisCrv.1 in cell X4. 

The prefixes Src and Tgt in the keys of the object &GBPBasis.1 of type Yield Curve Fxb in column X refer to the source and target currencies of the currency basis swaps respectively, the idea being that the produced Yield Curve represents the discounting curve of the target currency as seen by the source currency counterparty. One usually associates the source currency with the domestic currency and the target currency with the foreign currency, which means the above structure is how a US-based trader would proceed to construct the foreign GBP discounting curve.

An important detail is that Deriscope assumes that all swaps are collateralized in the source currency, which justifies the table title "GBP BASIS CURVE WITH USD COLLATERAL".

Below is the upper part of a comparison table between the discount factors implied by the Deriscope basis curve &GBPBasisCrv.1 and the discount factors read off Bloomberg. The small differences are due to the Deriscope's Log Cubic interpolation convention defined in cell Y10, which is not available in Bloomberg. 

Pricing the Currency Swap 

In order to price the currency swap I must first create two important objects, one that bundles all market data together and one that contains the modelling assumptions.

The first object is the &US Trader Mkt (USD Col).1 created in cell C4 with the formula =ds(C5:D7,C8:C12) and is of type Market Set, as shown below. 

The second object is of type Yld Crv Pick Model and produced in cell C17 shown below with the handle name &US Trader Mdl (USD Col).1.  

The purpose of this object is to tell the pricing routine which curves out of those contained in the collection &US Trader Mkt (USD Col).1 should be picked for discounting and forecasting purposes. Normally Deriscope would be able to pick the correct curve by inspecting each curve's currency and Issuer, but in the current case this is not possible because the curves have been built with custom Issuer names in order to use them as either discounting or forecasting depending on context.

The above object essentially declares the following:

  • The curve containing the Issuer %USDOIS|US|I is both discounting and forecasting
  • The curve containing the Issuer %GBPBasis|UK|I is discounting
  • The curve containing the Issuer %GBPOIS|UK|I is forecasting

Putting all together, the formula =ds(I5:J10) in cell I4 returns the price of the swap as of 26 June 2023. The result equals 0 as expected because the swap has been constructed to exactly match the 10-year market swap. 

Next, I can calculate the swap's fair basis spread, as shown in cell I15 below.

The result equals -21 bps, as expected. 

Comparing the Two Swaps by Inspecting their Cash Flows 

The two numerical results above – price of 0 and fair spread of -21 – are a strong indication that the swap being priced is identical with the 10-year market swap. But how can one know for sure this is indeed the case, since it is possible, although unlikely, that two differing swaps have the same price?

The straightforward answer lies in comparing the cash flows. If they are the same, the swaps must be the same.

Deriscope supplies the powerful Show function that can be used to display the cash flows contained – perhaps in a dynamic sense for efficiency reasons – in a given object.

In the case of the Yield Curve object &GBPBasisCrv.1, I start by inspecting its contents in the wizard and finding out its special key named _CashFlows, as below. 

After I click on the indicated lens icon shown, I see a table with all the market swaps that were used for the curve construction:  

Looking at the dates in the #Maturity column, I quickly find the row with maturity 28 June 2033 that corresponds to the 10-year market swap. Then I click on the lens icon in the cell wrapped in the green circle to reach the cash flows of that swap:

All I need to do to transfer these data into my spreadsheet by means of a dynamic formula is clicking on the Go button and selecting Paste Object Contents  

The wizard pastes the formula =ds("ObjectTools::Show","Reference=",YieldCurve!$X$4,"Key=","_Cash Flows","14,14,6,6","ExpandFinal=",TRUE) in cell A1.

The formula returns a very big dynamic array with the chosen swap's cash flows, as shown below. 

My next step is to display the cash flows of the swap being priced.

Just like the Yield Curve object above, all Deriscope objects of type Tradable have a key named _CashFlows that contains the subset of cash flows that is discoverable alone from the tradable's term sheet, i.e. those cash flow details that do not depend on the market information.

If one is interested in the full set of cash flows that contain forecasting indices and amounts – like in the table above -, one needs to call the Price function with the Output= key set to Cash Flows, as shown below. 

The formula =ds(N5:O12) returns the object &SwapCF.1, of which the contents can be seen in the wizard:

There is an unpleasant surprise though! The rows alternate between the USD and GBP currencies in a manner different than that in the cash flows table associated with the 10-year market swap seen earlier. In the earlier table, the top row corresponded to GBP, whereas now the top row corresponds to USD. I need to reshuffle the rows so that the currencies in both tables alternate in the same order before I can visually compare the two tables and determine if the various row elements agree or not.

One solution would be to paste these data in my spreadsheet and then sort the resulting table by means of the Deriscope dsSort function.

A more efficient solution is to add an additional input to the Price function that will be an object of type CF Model and supplies the Price function with custom criteria pertaining to the generation of the cash flows.

The CF Model type is very powerful and contains several optional arguments. Below is a trimmed-down version that utilizes only the key Sorted Columns= with the associated values #PmtDate and #Ccy, which relays the instruction to sort the cash flows table first by the #PmtDate column and then by the #Ccy column.

Here is how the CF Model object is generated: 

Below is the improved Price function that takes the CF Model object as input: 

I may now use the wizard as I did before, to paste the formula =ds("ObjectTools::Show","Reference=",'US-Trader-USD-Collat'!$N$4) in cell A1.

The result is shown below. 

It is now a child's play to verify that the two tables describe the same cash flows – up to the notional – and therefore the two swaps are essentially the same.  

Pricing the GBP-Collateralized Swap under the Assumption of Constant Basis Spreads 

Let us now face the problem of a UK-based trader who enters the currency swap under the agreement that the collateral account is held in the GBP currency.

The main assumption here is that the only available liquid market data with currency swap basis spreads are those we have seen already and relate to USD-collateralized swaps.

This lack of liquidity of traded GBP-collateralized swaps means that there is no way of calculating the impact of the GBP collateral without imposing some sort of simplifying assumption.

One such simplifying assumption could be that changing the collateral currency has no effect on the basis spreads of currency swaps.

For a UK-based trader, USD is the foreign currency and GBP is the domestic currency. It turns out, the UK trader would naturally consider a USD discounting curve that is implied by the market basis spreads rather than the OIS-implied local USD discounting curve used by the US-based trader.

Normally the appropriate market basis spreads would be unknown since they would have to refer to non-traded GBP-collateralized swaps, but under the above assumption the trader would be allowed to use the available market spreads.

The appropriate Yield Curve Fxb object would then be constructed with swapped source and target currencies and lead to the final curve construction as shown below: 

The basis curve &USDBasisCrv.1 representing the implied USD discounting curve is generated in cell AM4. The main differences from the earlier construction are shown in red circles. As already explained, the market spreads in column AQ refer to GBP-collateralized swaps but are nonetheless set to the same values as before due to the stated simplifying assumption.

To price the swap, I bundle all relevant market information into the object &UK Trader Mkt (USD Col).1 created in cell C4 as below: 

Comparing against my earlier similar construction, the only difference here is that the basis curve input &GBPBasisCrv.1 has been replaced by the basis curve &USDBasisCrv.1.

Repeating my earlier steps, I also need an object of type Yld Crv Pick Model that I produce in cell C17 with the handle name &UK Trader Mdl (USD Col).1: 

It is instructing to compare the above structure with the one I used earlier and shown once more below:  

Looking at the upper table's entries in row 21 one notices that the UK trader discounts USD flows with the curve having Issuer %USDBasis|US|I , i.e. the basis-implied &USDBasisCrv.1 curve and GBP flows with the curve having Issuer %GBPOIS|UK|I , i.e. the local OIS-implied &GBPOisCrv.1 curve.

This methodology differs from that employed by the US trader, who, according with the lower table's entries in row 21, discounts USD flows with the curve having Issuer %USDOIS|US|I, i.e. the local OIS-implied &USDOisCrv.1 curve and GBP flows with the curve having Issuer %GBPBasis|UK|I, i.e. the basis-implied &GBPBasisCrv.1 curve.

The formulas for the USD-denominated price and fair spread of the swap are shown below: 

As expected, the calculations return a swap price of 0 and a fair spread of -21 bps, i.e. the same as what the US trader sees under the assumption of USD collateral.

Important note: The two traders agree only on the pricing of currency swaps because of the simplifying assumption that regards the basis spreads to be unaffected by a change of the collateral currency. This does not mean that they would also agree on the pricing of other financial contracts. For example, it is straightforward to demonstrate that the prices of fx forwards would differ substantially! This is an important observation that may serve as an introduction to the next topic.

Pricing the GBP-Collateralized Swap under the Assumption of Constant FX Forwards 

Let us now change the simplifying assumption to one whereby changing the collateral currency has no effect on the fx forwards.

Note, this is the assumption made by Bloomberg as of the time of writing.

One can prove (see below) that this assumption leads to the following relation among the various discount factors for any maturity T:

Dᴬ⁽ᴮ⁾ = Dᴬ Dᴮ/Dᴮ⁽ᴬ⁾


A and B denote the two involved currencies

Dᴬ⁽ᴮ⁾ is the discount factor that applies on A-denominated but B-collateralized cash flows

Dᴮ⁽ᴬ⁾ is the discount factor that applies on B-denominated but A-collateralized cash flows

Dᴬ is the discount factor that applies on A-denominated and A-collateralized cash flows

Dᴮ is the discount factor that applies on B-denominated and B-collateralized cash flows

This notation implies Dᴬ ≡ Dᴬ⁽ᴬ⁾ and Dᴮ ≡ Dᴮ⁽ᴮ⁾

In my case of the GBP-collateralized GBP-USD swap, I can set A = usd and B = gbp to get the following formula for the USD discount factors under a GBP collateral:

Dᵘˢᵈ⁽ᵍᵇᵖ⁾ = Dᵘˢᵈ Dᵍᵇᵖ/Dᵍᵇᵖ⁽ᵘˢᵈ⁾

The relation Dᴬ⁽ᴮ⁾ = Dᴬ Dᴮ/Dᴮ⁽ᴬ⁾ is a special case of the more general relation Dᴬ⁽ᴷ⁾ = Dᴬ⁽ᴹ⁾ Dᴮ⁽ᴷ⁾/Dᴮ⁽ᴹ⁾ that holds in the case where up to four different currencies A,B,M and K are involved, as explained in the description of the Deriscope type ImpYC CSA Disc that is used to produce the Yield Curve that implies discount factors given by the stated formulas.

A proof of the general formula is stated here.

Below is the formula =ds(AU14:AV20) in cell AU13 that creates the object &USDBasisGBPCol.1 of the mentioned type ImpYC CSA Disc. 

This object is then passed as input to the formula that creates the final Yield Curve object &USDBasisGBPColCrv.1 in cell AU4, as shown below:  

Before pricing the swap using this curve, it would be interesting to see to what extent its implied discount factors agree with those in Bloomberg. Below is the comparison table.  

To calculate the price and fair spread of the swap, I follow the same steps as I did with the previous case where constant basis spreads were assumed.

Omitting the details, below is the set of formulas along with the numerical results: 

Quite interestingly, the swap price is not 0 anymore.

Also, the fair spread is calculated as -21.73 bps, which is admittedly very close to the value of -21.729 bps reported by Bloomberg, as shown below: 

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

At https://www.deriscope.com you can find more information about Deriscope and download the Excel Add-In required to run some of the formulas in the spreadsheet. 

Fully Functional Excel Spreadsheet for SOFR Swapti...
Yield Curve Building in Excel with Central Bank Me...