Converting Quotation Swap Bases in Excel

A quotation swap base is the reference rate that is used to determine the floating interest rate payments in an interest rate swap. An interest rate swap is a contract between two parties to exchange interest payments over a period of time. Usually, one party pays a fixed rate and the other pays a floating rate, which is based on a quotation swap base such as LIBOR, EURIBOR, TIBOR, etc.

Converting between different quotation swap bases means changing the floating rate from one reference rate to another. For example, if a party has a swap contract that pays 3-month LIBOR and wants to convert it to 6-month LIBOR, they can enter into another swap contract that pays 6-month LIBOR and receives 3-month LIBOR. The net effect is that the party now pays 6-month LIBOR and receives the fixed rate from the original swap contract.

The reason why a party may want to convert between different quotation swap bases is to hedge against the interest rate risk or basis risk that arises from having different lending and borrowing rates. For example, if a bank lends money at 3-month LIBOR and borrows money at 6-month LIBOR, they face the risk that the spread between the two rates may change unfavorably. By entering into a swap contract that pays 6-month LIBOR and receives 3-month LIBOR, the bank can eliminate this risk and lock in a fixed spread.

The conversion between different quotation swap bases is not a simple arithmetic operation, because the different reference rates may have different market expectations, liquidity, credit risk, and conventions. Therefore, the conversion requires a market-based approach that takes into account the supply and demand of the swap contracts, the yield curves of the reference rates, and the swap spreads. The swap spreads are the additional amount that the floating rate payer has to pay over the reference rate to enter into a swap contract. For example, if the 3-month LIBOR is 1% and the 6-month LIBOR is 1.2%, and the swap spread for the 3-month LIBOR is 0.1% and the swap spread for the 6-month LIBOR is 0.15%, then the conversion from 3-month LIBOR to 6-month LIBOR is not simply adding 0.2%, but also taking into account the difference in the swap spreads, which is 0.05%. Therefore, the conversion from 3-month LIBOR to 6-month LIBOR is approximately adding 0.25%.

Basic Theory:

Quotation swap bases can be expressed in terms of bid and ask prices. Two common bases are Direct and Indirect quotes. In a Direct quote, the domestic currency is the base currency, while in an Indirect quote, the foreign currency is the base currency. The conversion between these bases involves understanding the relationship between bid and ask prices.

Procedures:

1. Identify the Quotation Base: Determine whether the quote is in the Direct or Indirect base. This information is essential for the conversion process.
2. Understand Bid and Ask Prices: In a Direct quote, the bid price represents how much of the foreign currency you will receive for one unit of the domestic currency. The ask price represents how much of the domestic currency you need to exchange for one unit of the foreign currency. The inverse is true for an Indirect quote.
3. Conversion Formulas: For Direct quotes, the conversion formula is:

   Bid in Indirect = 1 / Bid in Direct

   For Ask quotes:

   Ask in Indirect = 1 / Ask in Direct

   The inverse holds for converting Indirect to Direct quotes.

Scenario:

Let’s consider a scenario with a Direct quote for EUR/USD currency pair:

• Bid Price (Direct): 1.1200
• Ask Price (Direct): 1.1250

Excel Calculation:

Now, we’ll use Excel formulas for Bid (Indirect) and Ask (Indirect):

• Cell C2 (Bid (Indirect)): =1/B2
• Cell D2 (Ask (Indirect)): =1/C2

Results:

Other Approaches:

1. Use Exchange Rate Function: Excel’s EXCHANGERATE function is helpful for direct conversion between two currencies. It takes into account the bid and ask prices.
2. Power Query: For more complex scenarios, Power Query can be employed to fetch and manipulate data, allowing for dynamic and automated updates.