Understanding Basis Swaps in Excel

A basis swap is a type of interest rate swap that involves exchanging two floating interest rates based on different money market reference rates. The purpose of a basis swap is to hedge against the interest rate risk that arises when a company has different borrowing and lending rates. For example, a company may lend money at a rate that is tied to the London Interbank Offered Rate (LIBOR), but borrow money at a rate that is tied to the Treasury Bill (T-Bill) rate. By entering into a basis swap, the company can swap the T-Bill rate for the LIBOR rate, and eliminate the risk of losing money if the spread between the two rates changes.

A basis swap can be customized according to the needs of the parties involved. The terms of the swap can include the frequency of payments, the notional amount, the maturity date, and the reference rates. One of the most common forms of a basis swap is a plain vanilla swap, where a fixed interest rate is exchanged for a floating interest rate or vice versa. For example, a company may swap a fixed rate of 5% for the LIBOR rate. This way, the company can benefit from lower interest payments if the LIBOR rate falls below 5%, or hedge against higher interest payments if the LIBOR rate rises above 5%.

A basis swap can also be used to express a view on the direction of interest rates, the credit quality of borrowers, or the liquidity of markets. For example, a trader may enter into a basis swap to bet on the divergence of the federal funds effective rate and the federal funds target rate. If the trader expects the effective rate to rise above the target rate, they can swap the target rate for the effective rate and receive the difference as a profit. Alternatively, if the trader expects the effective rate to fall below the target rate, they can swap the effective rate for the target rate and pay the difference as a loss.

Basic Theory

In a basis swap, two parties agree to exchange the cash flows associated with their floating-rate instruments for a specified period. The goal is often to benefit from the difference in the basis points (bps) between the two rates. The formula for calculating the basis swap payment is:

Where:

• Notional Amount is the agreed-upon principal on which the interest payments are based.
• Index Rate\_1 is the rate tied to the first benchmark.
• Index Rate\_2 is the rate tied to the second benchmark.
• Basis Points represent the unit of measurement for the difference between the two rates.

Procedures

1. Agree on Terms: The parties agree on the notional amount, the indices, and the basis points.
2. Calculate Basis Swap Payment: Use the formula mentioned above to determine the periodic basis swap payment.
3. Exchange Payments: Periodically exchange cash flows based on the calculated payment.

Comprehensive Explanation with Real Numbers

Let’s consider a scenario where Party A and Party B enter into a basis swap. Party A has a floating-rate loan tied to LIBOR, while Party B has a floating-rate loan tied to EURIBOR. The notional amount is $10 million, and the agreed basis points are 20.

Excel Calculation

1. Create an Excel table with the following columns: Notional Amount, Index Rate\_1, Index Rate\_2, Basis Points, and Basis Swap Payment.
2. Input the values into the table.
3. In the cell corresponding to “Basis Swap Payment,” use the formula:

    =Notional_Amount * (Index_Rate1 - Index_Rate2) / Basis_Points

Result

In this example, the basis swap payment would be:

So, Party A would pay Party B $100,000 periodically as part of the basis swap agreement.

Other Approaches

• Excel Data Table: Use Excel’s Data Table feature to analyze how the basis swap payment changes with different combinations of index rates.
• Scenario Analysis: Perform scenario analysis by changing the notional amount or basis points to understand the impact on basis swap payments.
• Graphical Representation: Create a graph to visualize the relationship between basis swap payments and different variables.