# Building in Excel the Implied Risk-Free Discounting Curve when the Collateral is kept in another Currency. Example: Mexico, where no Local OIS Market exists.

Before the 2007-08 financial crisis, all *swap* cash flows were discounted with the same *yield curve* used in "*forecasting*" the future Libor rates and thus the amounts of the floating cash flows.

At any given time, this *yield curve* was constructed so that it reproduced the market prices of the *swaps* traded at that time.

After its construction, the *yield curve* could be used to calculate the price of non-traded custom *swaps* and other *interest rate derivatives*.

This practice has changed since then.

Now two different *curves* are required to price any *collateralized* contract, such as a *vanilla fixed-to-floating interest rate swap* traded over the counter.

One of these *curves* is referred as the *discounting curve* (or *basis curve*) and used exclusively for calculating the present value of future cash flows whose amount is considered known.

The risk profile of that curve must correspond to the risk profile of the collateral used in the collateralization of the swap's flows.

Since the latter is often a margin account holding almost *risk-free* overnight securities, the *discounting curve* is built out of the market-quoted rates of OIS (Overnight Index Swaps) that reference the same, or similar, overnight index as that of the securities held in the collateral account.

The second curve is referred as the *forecasting curve* and used exclusively for calculating the *forward rates* needed in the calculation of the *floating* cash flows.

Given an already constructed *discount curve*, one can easily build the *forecasting curve* by bootstrapping over the swap rates quoted in the market.

The complete process is described in my post about Exogenously Bootstrapping the Discounting and Forecasting USD Libor curve.

A problem arises when the collateral is held on a currency other than that of the collateralized swap.

This may happen when a contractual clause in the Credit Support Annex (CSA) accompanying the swap contracts requires a **USD**-denominated collateral account, as is the case in several countries such as Mexico, South Africa etc.

In the sequel, I will focus on the **MXN** swap market in Mexico, where the collateral accounts of Mexican Peso (**MXN**) swaps are held in **USD** currency and earn the overnight **USD** **Fed Funds** interest.

Also note that no local OIS market exists in Mexico.

### Table Of Contents

- The Interest Rate Swap Market in Mexico
- Extracting the Implied Discounting Curve in the case of MXN Swaps
- Detailed Analysis of the Last Step involving Cross Currency Basis Swaps
- Building the Implied Discounting MXN Yield Curve in Excel: Steps 1 to 3
- The ImpYC XCCY Disc type for Building an Implied Discounting Yield Curve out of XCCY Swaps
- Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Tgt Mkt Crv)
- Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Ccy Swap Curve)
- Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Completion)
- Comparing the Discount Factors against Bloomberg
- Download Spreadsheet

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 Interest Rate Swap Market in Mexico

The Banco de México publishes daily the Interbank Equilibrium Interest Rate, known as **TIIE** from **T**asa de **I**nterés **I**nterbancaria de **E**quilibrio, which reflects the average interbank rate polled from Mexican commercial banks.

There exist four tenors that cover **1, ****28, 91** and **182** **days**.

A typical *fixed-to-floating* **MXN** *swap* involves the exchange of the **TIIE28** index (the **TIIE** index with a **28-day** tenor) for a fixed rate.

As mentioned already, collateral is held in **USD** currency earning the **USD** **Fed Funds **overnight rate.

A liquid swap market exists with tenors from **3** "months" to **30** "years".

I have surrounded the words "months" and "years" with quotes because of the following Mexican market peculiarity:

Due to the **28-day** (**4 weeks**) tenor of the **TIIE28** index, the Mexican "month" in the context of swaps refers to a lunar month consisting of **4 weeks**.

Below I will be using the symbol **L** (from Lunar) for representing a time period of **4 weeks**.

So, a swap tenor of **3** "months" corresponds to a tenor equal to **3L**, which is a period of **12 weeks** (or **48 days**).

A swap tenor of **one** "year" corresponds to a tenor equal to **13L**, which is a period of **52 weeks** (or **364 days**).

Note that the maturity of a **one** "year" swap will occur at least **one** calendar day earlier than the end date of a regular **one-year** period.

The longest quoted tenor of **30** "years" corresponds to a tenor equal to **390L**, which is a period of **30*52** **weeks** (or **30*364 days**).

It follows that the maturity of a **30** "year" swap will occur at least **30** calendar days earlier than the end date of a regular **30-year** period.

Below is a screenshot of the Bloomberg quotation of **MXN** vs **TIIE28** swaps as of **06 May 2019**.

Note the notation **Nx1** for a **NL** (**N** Lunar months) term.

### Extracting the Implied Discounting Curve in the case of MXN Swaps

In order to extract the implied *discounting curve*, I will progress with the following steps:

__Step 1__

Build the "*risk-free*", *discounting* **USD** *yield curve* **USD-OIS** out of the **USD** OIS market rates because this is the curve associated with the risk profile of the **USD**-denominated collateral.

The details are described in my post about building *discounting* and *forecasting* curves using exogenous bootstrapping.

Diagrammatically, this step is represented as:

__Step 2__

Build the forecasting **USD 3M Libor** *yield curve* **USD-3M **out of the **USD-OIS** *yield curve* and the **USD 3M Libor** *swap* market rates because it will be needed in the next step for the construction of the forecasting **USD-1M** *yield curve*.

The details are described in the link mentioned above.

Diagrammatically, this step is represented as:

__Step 3__

Build the forecasting **USD 1M Libor** *yield curve* **USD-1M **out of the **USD-OIS** and **USD-3M** *yield curves* and the **USD 3M** vs **1M ***tenor basis swap market spreads *because it will be needed in the final step for the construction of the implied *discounting* **MXN** *yield curve*.

The details are described in my post about building a *forecasting* curve out of *tenor basis swap spreads*.

Diagrammatically, this step is represented as:

__Step 4__

Build the *discounting* MXN *yield curve* **MXN-DISC **out of the **USD-OIS** and **USD-1M** *yield curves*, the **MXN** **TIIE28 ***swap market rates* and the **USD** vs **MXN** *cross currency basis swap* market spreads.

Details of this construction are presented further below.

Diagrammatically, this step is represented as:

Below is the consolidated flowchart of all the four steps.

###
Detailed Analysis of the Last Step involving Cross Currency Basis Swaps

Deriscope can build the *discounting* curve associated with a currency such as **MXN** provided the following is known:

- A.The market spreads of a group of
*cross currency basis swaps*between**USD**and**MXN** - B.The "
*risk-free*"*discounting**yield curve*for**USD** - C.The
*forecasting**yield curve*for**USD** - D.The
*forecasting**yield curve*for**MXN**

The **USD vs MXN** XCCY *basis swaps* are *floating-to-floating swaps* exchanging **USD 1M Libor** plus *spread* for **MXN TIIE28**, which is by the way opposite to the common convention having the spread added on the **non-USD **index.

Both legs have the same **28-day** *accrual period*, which means the *accrual period* on the **USD** leg is slightly less than the **one-month** *tenor* of its **1M Libor** index.

More details on XCCY *swaps* and the corresponding *yield curve* extraction can be found in my post about building a curve out of *cross currency basis swaps*.

Unfortunately, we cannot use the above recipe due to missing the **D** part, i.e. the *forecasting* *yield curve* for **MXN**.

But, why is the **D** part important?

Only because the market data refer to XCCY *floating-to-floating basis swaps*, which are instruments involving the future **TIIE28** fixings. The *forecasting* *yield curve* for **MXN** is then needed only for forecasting these fixings.

This observation brings us to the idea of replacing the XCCY *floating-to-floating basis swaps* with XCCY *floating-to-fixed swaps*, which are *cross currency swaps* exchanging *floating* **USD 1M Libor** against a *fixed* **MXN** rate. Then we could imply the **MXN** *discounting* curve without worrying about the unknown **MXN** *forecasting* curve.

The problem is that no such swaps are traded!

On the other hand, such swaps may be synthesized from XCCY **USD** vs **MXN** *floating-to-floating basis swaps* and *vanilla* **MXN** *floating-to-fixed swaps*.

More specifically, the following holds, at least approximately:

Let:

**Swap 1**: Receive **USD** **1M Libor** plus *Spread* vs Pay **MXN TIIE28**

**Swap 2**: Receive **MXN TIIE28** vs Pay **MXN** Fixed Rate

**Synthetic Swap**: Receive **USD 1M Libor** plus *Spread* vs Pay **MXN** Fixed Rate

Then:

**Synthetic Swap** = **Swap 1** + **Swap 2**

Diagrammatically:

This synthetic construction is only valid in an approximate sense because the *floating* **MXN TIIE28** leg paid in **Swap 1** does not exactly cancel out the corresponding leg received in **Swap 2**, due to differing conventions.

Concretely, **Swap 1** settles at **T+1** while **Swap 2** settles at **T+2**, a fact that leads to mismatched accrual and payment dates. Also **Swap 1** follows the union of the US and Mexican calendars, whereas **Swap 2** follows just the Mexican calendar.

Although the effect is minor, one may calculate an appropriate adjustment that can be applied on the *fixed rate* in **Swap 2**, so it is at par with a slightly modified *floating* leg that exactly matches that of **Swap 1**.

### Building the Implied Discounting MXN Yield Curve in Excel: Steps 1 to 3

I will now implement in Excel the four steps that lead to the creation of the implied *discounting* *yield curve* for **MXN** by using actual market data from Bloomberg as of **May 22, 2019**.

The three prerequisite curves in steps **1** to **3** are constructed as described in my post about building the *forecasting* *yield curve* implied by *tenor basis swaps*.

Below are the formulas and data that lead to the creation of the respective objects of type *Yield Curve* at the top/left of each image:

### The ImpYC XCCY Disc type for Building an Implied Discounting Yield Curve out of XCCY Swaps

The fourth and last step requires as input all the above curves plus the market rates of the vanilla **TIIE28** *swaps* and the market *spreads* of the XCCY *basis swaps*.

Although it could be technically possible, it is not necessary to build in the spreadsheet the set of synthetic XCCY *floating-to-fixed swaps*.

Deriscope provides a special type called *ImpYC XCCY Disc* that derives from *Yield Curve* *Input* and contains all data needed to build these synthetic swaps, as well as the final implied *discounting* curve for the **MXN** currency.

*ImpYC XCCY Disc* belongs to a family of special types inheriting from *ImpYC*, itself a sub-type of *Yield Curve Input*.

All *ImpYC* sub-types – such as the *ImpYC XCCY Disc* – represent collections of implied (or synthetic) instruments, i.e. instruments that are not directly traded in the market but can be assumed they exist in a mathematical sense with price quotes derived from proper traded instruments.

I can access the parent type *ImpYC* in the *Type Selector* of the Deriscope wizard, as shown below.

The four sub-types are also visible, as well as the description of *ImpYC* at the *Info Area* at the bottom.

If I now select the type *ImpYC XCCY Disc* appearing at the top of the dropdown list, the wizard displays the **Create** function of the selected type, as shown below:

The *Info Area* at the bottom contains the description of the currently selected *ImpYC XCCY Disc* type.

Before I go over the meaning of the various input parameters of the **Create** function, I notice the top key/value pair **-Currency= %GBP**.

This entry determines the currency of the *discounting* curve implied by the object under construction here. It is set by the wizard to **%GBP** only because my computer happens to use the English locale.

Since I am interested in producing an implied *discounting* curve for **MXN**, I can click on the pen-on-a-pad sign on the cell with the **%GBP** entry and change the currency to **%MXN**. Then the wizard automatically adjusts all remaining entries, with the following result:

The meaning of the various entries is as follows:

On the outset, there exist two currencies, the **USD** and **MXN**, which are referred as *source* (**Src**) and *target* (**Tgt**) currency respectively.

The key **Src Disc Curve=** supplies the "*riskless*" *discounting* curve associated with the source currency **USD**.

The key **Ccy Swaps Type=** specifies the type of the supplied *cross currency swaps*. Here the associated value is **SrcFltTgtFlt**, which is an abbreviated form of **Source Floating Target Floating** meaning that the XCCY *swaps* involve the exchange of a *floating* rate of the source currency against a *floating* rate of the target currency.

The key **Src Forc Crv=** supplies the *forecasting* curve associated with the source currency **USD**. The associated value has been left blank by the wizard and must be filled by me in the spreadsheet later.

The key **Tgt Mkt Crv=** supplies an object of type *Yield Curve* created by market rates of *vanilla swaps* in the target currency **MXN**.

The key **Ccy Swap Curve****=** supplies an object of type *Yield Curve* created by XCCY *basis swap spreads*.

The key **Method=** specifies the algorithm used to extract the discounting curve of the target currency **MXN**. The associated value **ImplSrcFltTgtFxd** is an abbreviated form of **Implied Source Floating Target Fixed**, which means that the algorithm is based on the synthetic construction of (implied) *swaps* that involve the exchange of a *floating* rate of the source currency against a *fixed* rate of the target currency.

Note that as explained earlier, this algorithm makes use of only the market rates of the *vanilla* **MXN** *swaps* and the market spreads of the *XCCY* *basis swaps*. Nevertheless, a full *Yield Curve* object is required as input to the **Ccy Swap Curve=** key. This is for technical reasons in order to support a better orthogonalization of the involved data sets and to anticipate more complex requirements of other, not yet implemented, methods.

The last key **Adjust Fxd Rates=** expects a boolean value that defines whether the *fixed* rates of the *vanilla* **MXN** *swaps* should be adjusted, as explained in the description of the step 4 above, or not.

### Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Tgt Mkt Crv)

The following screenshot shows the creation of the *Yield Curve* object **&MXN-TIIE.1** in **cell C4** that contains the **MXN** *swap* rates. It is required as input to the **Tgt Mkt Crv** key:

The rates are taken from Bloomberg as of **May 22, 2019**.

The single-element group of deposit instruments created as the handle **&YldCrvDep_C20:1.1** in **cell C20** is not required by the current algorithm. It is nevertheless provided with the actual Bloomberg **MXN** deposit rate of **8.5066%** for completeness in case one is interested in studying the related **MXN** curve.

As explained above, our algorithm only needs the **MXN** *swap* rates supplied by the handle **&YldCrvSwp_F4:1.1** created in **cell F4**.

Note the values for the keys **Fxd Ref Period=** and **Ibor Index=** in **cells G7** and **G9**.

They are respectively **%1L** and **%MXNTiie|1L**, where **L** is used as symbol for the Lunar month period of **4 weeks**.

The label **MXNTiie **points to the Deriscope built-in index for the Mexican **TIIE** rate.

Combining it with the **1L** time period, I form the object **%MXNTiie|1L** that is considered "*trivial*" in the sense that its contents are fully defined by the text in its handle name, as explained in my introduction.

The built-in conventions are seen in the wizard if I select the cell containing the handle **%MXNTiie|1L** as below:

### Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Ccy Swap Curve)

The following screenshot shows the creation of the *Yield Curve* object that contains the **USD-MXN** *cross currency swap basis spreads*. It is required as input to the **Ccy Swap Curve** key:

The Yield Curve &USD-MXN.1 is constructed in **cell C4** by using one set of instruments of type *cross-currency basis swap* corresponding to the object **&YldCrvFxb_F4:3.1 **entered to the right of the key **Market Data=** in **cell C15**.

The shown *basis spreads* are taken from Bloomberg as of **May 22, 2019**.

As already mentioned, these spreads are added on the **USD** **Libor** leg, contrary to the usual convention of being added on the **non-USD** leg. This is specified by the entry **Spread On Src= TRUE**.

Details on the meaning of the various keys can be found on my XCCY basis swaps curve post.

Note though that several entries are not required by the current algorithm, which only needs the *basis spreads*. In fact, the whole curve construction here is superfluous and is carried out only because the key **Ccy Swap Curve** expects as value an object of type *Yield Curve*. Nevertheless, most entries have been filled with reasonable values for completeness and convenience in case one wants to study this particular curve.

### Building the Implied Discounting MXN Yield Curve in Excel: Step 4 (Completion)

Putting everything together, the final implied discounting curve for **MXN** is created as the object with handle name **&MXN-DISC.1** by the formula *=ds(C5:D16)* in **cell C4**, as shown below:

I can see the contents of the object **&MXN-DISC.1** by selecting the **cell C4** while the Deriscope taskpane is open:

Note the curve is constructed without a direct input of any market rates.

All such rates are fed in indirectly through the *Yield Curve* objects **&USD-OIS.1**, **&USD-1M.1**, **&MXN-TIIE.1** and **&USD-MXN.1**.

The dependency upon the various curves is shown schematically below:

As mentioned above, this curve produces discount factors by relying on a dynamically constructed *yield curve* created out of synthetic XCCY *swaps* that exchange *floating* **USD 1M Libor** against *fixed* **MXN**.

The latter curve can be seen next to the key **_Synthesized Curve=**.

Clicking on the respective lens sign, I see the following:

At this point, I may browse through the cash flows of the synthetically created XCCY *swaps* as explained in this post.

But I would rather click on the lens sign to the right of **Market Data=** in order to see the definitions (not the cash flows) of the synthetic XCCY *swaps*.

What I see above, are the conventions applying on the synthetic XCCY *swaps* and the curves required in order to build the *discounting* curve associated with the target currency **MXN**.

For example, I see on the top that **TYPE= Yield Curve Fxb**, which tells me that the currently seen object represents a collection of XCCY *basis swaps*.

Then the **Src Index= %USDLibor|1M** and **Tgt Index= %MXNTiie|1L** tell me that the source *index* **USD 1M Libor** is exchanged against the target *index* **MXN TIIE28**.

But the **Tgt Gearing= 1.000E-69** indicates that the *multiplier* to the target *index* is practically zero, a fact that makes the XCCY *basis swap* into a *floating-to-fixed* XCCY *swap*!

I also see that both *discounting* and *forecasting* curves for the **USD** currency are provided through the pairs **Src Disc Crv= &USD-OIS.1** and **Src Forc Crv= &USD-1M.1** respectively.

Note that a forecasting curve for the **MXN** currency is also provided through the pair **Tgt Forc Crv= &MXN-TIIE.1**, but is practically not used because of the zero **TIIE28** *multiplier*.

So, the only unknown remains the *discounting* curve for the **MXN** currency, which is the curve being finally produced.

The set of synthetic *swaps* can be seen by clicking on the lens sign appearing at the bottom, next to the **Set=** key:

The middle column contains the *spreads* in the market XCCY **USD 1M Libor** vs **MXN TIIE28** *basis swaps*.

The last column contains the *fixed* rates in the market **MXN** *fixed-to-floating swaps*.

The title appears as **#Spread2** because the synthetic XCCY *swaps* are structured as *floating-to-floating basis swaps*, where the **MXN** legs are structured with a zero *index* *multiplier* and a *spread* set equal to the shown rate.

### Comparing the Discount Factors against Bloomberg

Given the handle name **&MXN-DISC.1** I may now use the Deriscope function DF to calculate the discount factor implied by the *discounting* curve for any maturity.

The following table shows the *discount factors* calculated by Deriscope and compares them to those returned by the Bloomberg terminal:

The last column shows the difference between the Deriscope and Bloomberg *discount factors*. A difference like **0.0001** for a maturity of **one year** represents roughly a corresponding zero rate difference of **one basis point**. We observe that the annualized differences are about **1/3 bp** for the first **5 years** until **2024** and then increase to about **1 bp**.

The main reason for these differences is the approximation applied by Bloomberg in its effort to construct the implied *discounting* curve on the absence of OIS market data.

At least as of December 2017, Bloomberg's approach was to create synthetic *floating-to-floating* XCCY OIS *basis swaps*, where the OIS of the collateral currency (eg USD) was exchanged against the OIS of the local currency (eg MXN) plus a *spread*.

Then the following formula was used:

In case the XCCY basis spread is paid on the leg of the collateral currency **1** (the MXN case), the above formula must be modified to:

In my next post, I will also build the *forecasting* *yield curve*, use both curves to price various swaps and analyze the impact of the implied *discounting* and *forecasting* curves on pricing and risk output.

Click on **YieldCurveBlbgImpliedOIS.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