Currency Swaps and Basis Curves in Excel
The basic concepts of spot fx rates, forward fx contracts, fx swaps and the construction of foreign yield curves out of fx forward rates have been described in detail in my previous fx rates article.
While these instruments cover the short end of the maturity spectrum – typically about a year -, the tenor of so-called currency swaps (also known as cross currency swaps) extends to several years.
There exist several types of currency swaps, the simplest of which is the fixed-to-fixed currency swap, whereby the two counterparties commit to exchange a fixed amount of a certain currency against an also fixed amount of a different currency in regular time intervals until the maturity of the swap contract.
Contrary to interest rate swaps, there also typically exists an initial and a final exchange of the notional amounts between the two currencies.
A more intuitive way of understanding a currency swap is to view it as an agreement to borrow (i.e. receive) funds denominated in one currency by lending (i.e. paying) funds denominated in a different currency. While the two involved currencies may be unrelated to the host countries of the swap counterparties, it will be notationally useful to treat one of the currencies as "domestic" and denote it as DOM and the other as "foreign" and denote it as FOR.
Then, entering into a Borrower currency swap will mean receiving Nf units of FOR in exchange of Nd units of DOM today, while assuming the obligation to pay back a) the notional Nf at maturity and b) the accrued FOR interest in regular coupon intervals in exchange of receiving a) the notional Nd at maturity and b) the accrued DOM interest in the same regular coupon intervals.
While the OTC nature of currency swaps permits the arbitrary setting of Nd and Nf, these are usually set so that their ratio Nd/Nf equals the spot fx FOR/DOM rate s observed when the swap starts. Thus, typically Nd = Nf s.
Also note that the amounts exchanged at maturity are still the same Nd and Nf, i.e. the same initial spot fx rate s still applies at maturity. This is in contrast with the fx swaps, where the terminal amounts depend on the forward fx rate f observed when the contract starts.
A slightly more complex but far more liquid currency swap is the floating-to-floating currency swap, whereby the fixed coupons are replaced by floating payments linked to some agreed index.
The most common swap type under this category is the ibor-to-ibor currency swap, also referred as basis currency swap, whereby the index relating to the floating payments in currency CCY is an ibor rate in that same currency.
As an example – see diagram below - of an ibor-to-ibor currency swap, consider a 2-year EUR-USD swap based on the 3-month Euribor and 3-month USD Libor, whereby an American bank A borrows 10,000,000 EUR from a European bank E for a period of 2 years. Assuming the spot fx rate EUR/USD at inception is 1.14, then at inception A would pay E 10,000,000*1.14 = 11,400,000 USD and receive 10,000,000 EUR. Every 3 months, A would be obligated to pay interest on the received 10,000,000 EUR based on a floating interest rate that equals the fixing of the 3-month Euribor at the beginning of the respective 3-month accrual period.
Symmetrically E would be obligated to pay interest on the received 11,400,000 USD based on a floating interest rate that equals the fixing of the 3-month USD Libor.
At the end of the swap, A would need to return the 10,000,000 EUR to E, whereas E would need to return the 11,400,000 USD to A.
In reality, a so called "basis spread" is added to the floating payments associated with one of the legs, typically the domestic or non-USD leg, in order to make the present value of the swap to equal 0 when the swap starts its life. So, A would need to pay Euribor + basis spread every 3 months and receive just USD Libor.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Types of Currency Swaps Supported by Deriscope
Deriscope allows you to build several types of currency swaps in Excel that are then priced by the integrated ORE library, which is an extension of the QuantLib library sponsored by Quaternion.
You normally start by defining two legs - the domestic and foreign leg – by setting their respective currencies and leg type, which may be one of:
- 1.Fixed
- 2.Floating linked to an ibor index
- 3.Floating linked to a cms index
- 4.Floating linked to an inflation index of the CPI type
Since each leg can be specified independently, there exist 10 possible combinations, such as Fixed-Fixed, Fixed-Ibor, Fixed-Cms, Fixed-Cpi, Ibor-Ibor etc.
By default, both initial and final notional exchanges are assumed, but the interface allows you to switch off any, or both, of them.
Ibor and Cms indices may carry a custom, possibly time-dependent, gearing, spread, cap and floor level. They can also be flagged as being set in arrears.
Currency swaps with notional resets are also supported, provided the affected legs are linked to an interest rate index. Fixed legs may still carry notional resets by having these legs represented as floating legs with zero gearing. The reset feature may be based on the same fx rate as that of the swap, or on a completely different cross currency rate.
On the absence of notional reset - and provided that both legs are linked to an interest rate index - you may specify a notional amortization – or appreciation – schedule. In that case, you can even decide whether the notional changes should be paid out as extra fixed cash flows or not.
It is also possible to customize the coupon schedules of both legs so that they do not necessarily coincide.
Finally, you may also build multi-leg products that contain more than two legs and reference several currencies.
Creating a vanilla, Ibor-Ibor, Currency Basis Swap in Excel
The following short video shows how I can use the Deriscope wizard to create an Excel object of type Currency Swap that represents borrowing the foreign currency EUR under EUR Libor in exchange of lending the domestic currency USD under USD Libor.
To keep things simple, I accept all the defaults presented by the wizard and I suppress the display of all optional entries. The wizard chooses USD as my domestic currency because my windows system is set with the US locale.
The image below shows both the generated spreadsheet formulas and the wizard on the right, which displays the in-memory-held contents of the object referenced by the handle name &CcySwp_A1:1.1 in the currently selected cell A1.
The wizard pasted the formula =ds(A2:B13) in cell A1 that takes as input the single range A2:B13 and returns the text &CcySwp_A1:1.1 which is the handle name of a newly created object of type Currency Swap.
The input data are arranged as key/value pairs, where the keys are on the first column and carry the = suffix and the values are on the second column. The two top keys
Type= and Function= are universal (apply in all contexts), whereas the remaining keys supply the details of the two legs. The keys with the Dom prefix refer to the domestic leg, while those with the For prefix refer to the foreign leg.
The 5-year schedule is created by a separate formula in cell A15 as an object of type Schedule and identified by the handle name &Schedule_A15:1.1. It is referenced by both legs in the green-colored cells B8 and B13.
The wizard displays several key-value pairs that are not present in the spreadsheet ranges. This is because these pairs bear their designated default values.
For example, Dom Gearings= 1, since 1 is the default value of the index multiplier when the key Dom Gearings= is not part of the input ranges.
Also, both Dom Caps= and Dom Floors= appear empty since no index caps or floors have been specified in the spreadsheet.
Simple Pricing
Creating a simple formula that calculates the price of this product is straightforward, as the next video demonstrates:
Below you see the two formulas as pasted by the wizard in cells D1 and D6. All market data are grouped together in a separate object of type Ccy Swap Mkt created in cell D6. This object is then fed into the formula in cell D1 that returns the price of 8.29573E-14. I have manually set the USD flat rate to 0.01 so that the two economies (USD vs EUR) are not identical and therefore the forward fx rates are not flat.
The price practically equals zero, due to the fact that our currency swap product in cell A1 has been constructed with a domestic notional of 113.96 and a foreign notional of 100, the ratio of which exactly matches the spot fx EUR/USD rate of 1.1396 in cell E11 that is used as market input in the pricing of the contract.
Pricing using Input Blocks of Market Objects
The following video demonstrates the older approach of calculating the price using fully fledged objects of type Market:
Below you see how my spreadsheet looks like after the wizard has created a total of five spreadsheet formulas that produce four red-colored handle names. I have shifted the previous formulas elsewhere so that the new ones appear in the range starting with the cell D1:
The main formula is in the currently selected cell D1 and has the form =ds(D2:E4).
This is the formula that carries out the pricing calculation and returns practically 0, as it should, given the fact that our currency swap product in cell A1 has been constructed with a domestic notional of 113.96 and a foreign notional 100, the ratio of which exactly matches the spot fx EUR/USD rate of 1.1396 in cell H24 that is used as market input in the pricing of the contract.
I have manually set the USD flat rate to 0.01 so that the two economies (USD vs EUR) are not identical and therefore the forward fx rates are not flat.
Additional output data – including a detailed table of all cash flows – can be displayed as explained below in the context of currency swaps with notional resets.
Building the Foreign Yield Curve out of Basis Swap Spreads
When it comes to constructing market data that are required for a particular pricing job, the wizard follows the lazy route of creating as simple data structures as possible. In particular, any required yield curves are constructed as flat.
It is my job to replace the wizard-generated market data with suitable structures that reflect the real world.
As already pointed out in my article about fx forwards and swaps, the correct curve to use is one that is built out of fx instruments. That article showed how market fx forward rates may be used as input to the foreign yield curve construction. When faced with longer maturities, it is desirable to also use longer dated market quotes, such as currency basis swap spreads.
The next video shows how I can use the wizard to paste in the spreadsheet the formula that creates an object of type Yield Curve out of market currency basis swap spreads.
The following image shows the formula =ds(J2:K5) pasted by the wizard in cell J1, which returns the handle name &YldCrv_J1:1.1 that represents an object of type Yield Curve.
The denomination currency is set by the key-value pair Currency= %EUR.
The key-value pair Market Data= &YldCrvFxb_J7:1.1 is the most important element.
Its value &YldCrvFxb_J7:1.1 is the handle name of an object of type Yield Curve Fxb created below in cell J7.
The latter object is constructed out of the domestic Yield Curve object &YldCrv_G7:1.1 and foreign Yield Curve object &YldCrv_G13:1.1 that have already been created earlier in cells G7 and A13 respectively. The latter curve is only used to forecast the foreign ibor index as the key name For Frcast Crv= implies.
The wizard generates a simple table of input basis spreads consisting of only two tenors - %1Y and %2Y - with a flat spread of 0.002, i.e. 20 basis points. I can obviously insert additional rows and edit the #Spread column so that actual market rates are included.
Include Amortization
The following short video shows how I may use the Deriscope wizard to create an Excel object of type Currency Swap that includes amortization, so that the notional becomes time-dependent and the amortized amounts in each currency are added to the regular interest-only cash flows.
For simplicity, the display of all optional entries is suppressed during construction. Since the flag that determines whether the amortized amounts are paid or not is optional with default value TRUE, I do not need to set it explicitly.
You will also notice that I click on the Links Resolution button once so that the wizard will consider the whole workbook when it searches for reusable objects that may have been already created by spreadsheet formulas in cells. I do that because I am working in a new sheet and I want to reuse the Schedule object already created in the previous sheet.
The image below shows both the generated spreadsheet formulas and the wizard on the right, which displays the in-memory-held contents of the object referenced by the handle name &CcySwp_A1:2.1 in the currently selected cell A1.
As already mentioned, the wizard did not generate a spreadsheet formula responsible for creating the object &Schedule_A15:1.1 referenced by the cells B8 and B13. Instead, it has placed in those two cells simple links (showed green-colored) to the cell in sheet CcySwap1, where the Schedule object is produced.
The only new element here is the Amortization object &Amortization_A16:2.1 created in cell A16.
As I did before, I may now choose Price in the wizard's Function Selector and click on Go to have the pricing formula generated and pasted in the spreadsheet.
If I make sure that the Links Resolution button to the left of Go shows the book icon (see image below), then any existing objects in this or other sheets within the current workbook will be reused.
Below you see the result after the wizard has inserted the pricing formula in cell D1.
As explained, no new formulas have been created that would have generated the Market object in cell E4. The shown &Mkt_D6:1.1 is already created in sheet CcySwap1 and cell E4 only contains a link to the source cell.
Additional output data – including a detailed table of all cash flows – can be displayed as explained below in the context of currency swaps with notional resets.
Include Notional Reset
The following short video shows how I can use the Deriscope wizard to create an Excel object of type Currency Swap that includes notional reset, so that the notional that applies at each coupon period is adjusted according to the prevailing fx rate at the start of the interval.
I keep the default EUR/USD fx rate setting, although Deriscope allows specifying a separate cross currency fx rate if so desired.
The image below shows both the generated spreadsheet formula and the wizard on the right, which displays the in-memory-held contents of the object referenced by the handle name &CcySwp_A1:3.1 in the currently selected cell A1.
The notional reset feature is carried by the three new keys: DomFX Reset=, Dom Reset Ccy= and Dom Reset Not=
As I did before, I may now choose Price in the wizard's Function Selector and click on Go to have the pricing formula generated and pasted in the spreadsheet.
Below you see the result after the wizard has inserted the pricing formula in cell D1.
As explained, no new formulas have been created that would have generated the Market object in cell E4. The shown &Mkt_D6:1.1 is already created in sheet CcySwap1 and cell E4 only contains a link to the source cell.
Additional output data
The Info Area of the wizard in the image above displays the sentence "To display extra pricing data click here". If I click on the here hyperlink, I see the following:
If I then click on the lens sign next to CashFlows=, I obtain the table below, where the middle rows have been removed so that both the initial and final cash flows of the swap can be displayed together. This table contains all cash flows in chronological order, with complete information about the projected ibor indices, fx rates and the respective notional amounts and floating coupons. The very last column contains handle names of objects that – when clicked upon – display even more information about the respective cash flows.
Click on the image to visit the source web page that contains an interactive html representation of the complete pricing output allowing you to explore all the contents.
You may also access all these data within the spreadsheet by calling the pricing formula with the extra key-value pair Output= CashFlows as shown below. Then the output becomes an object, the contents of which become visible in the wizard as soon as the containing cell is selected.
Any portion or all of the displayed data may be transferred in the spreadsheet as explained in my introductory article about Deriscope
Advanced Topic: Following the pricing algorithm step-by-step
It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate QuantLib and ORE structures and numerical outputs as described in the respective section of my Interest Rate Swap article.
Click on CurrencySwapCrv.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
