Understanding Asset Swap in Excel

An asset swap is a type of contract that involves exchanging two different kinds of assets, usually a fixed-rate asset and a floating-rate asset. For example, a fixed-rate asset could be a bond that pays a fixed amount of interest every year, while a floating-rate asset could be a loan that pays interest based on a variable rate, such as LIBOR.

The purpose of an asset swap is to change the cash flow characteristics of the assets to suit the needs or preferences of the parties involved. For instance, one party may want to hedge against the risk of interest rate changes, credit events, or currency fluctuations. Another party may want to take advantage of the potential benefits of these factors.

An asset swap typically consists of two steps:

1. The swap buyer purchases a fixed-rate asset, such as a bond, from the swap seller at the full price, which includes the principal and the accrued interest.
2. The swap buyer and the swap seller agree to exchange the cash flows from the fixed-rate asset for the cash flows from a floating-rate asset, such as a loan, for a certain period of time. The swap buyer pays the fixed interest payments from the bond to the swap seller, and the swap seller pays the variable interest payments from the loan to the swap buyer. The difference between the fixed and the variable interest rates is called the asset swap spread, and it can be either positive or negative, depending on the market conditions and the quality of the assets.

An asset swap can be beneficial for both parties, as it allows them to customize their exposure to different types of risks and returns. For example, the swap buyer can hedge against the credit risk of the bond issuer by receiving a floating rate that is independent of the bond’s performance. The swap seller can benefit from the fixed rate that is higher than the floating rate, or from the potential appreciation of the bond’s value.

Basic Theory:

An asset swap is essentially a combination of a bond and an interest rate swap. One party, often an investor, agrees to exchange the cash flows of a fixed-rate bond for the cash flows of a floating-rate instrument, such as a Treasury bill or LIBOR-based instrument. The motivation behind asset swaps is often to customize the cash flow profile of an investment or to take advantage of interest rate differentials.

Procedures:

1. Selecting the Fixed-Rate Bond: Choose a fixed-rate bond as the underlying security for the asset swap.
2. Identifying the Floating-Rate Instrument: Select a floating-rate instrument whose cash flows will be exchanged with the fixed-rate bond.
3. Calculating Fixed and Floating Cash Flows: Determine the fixed and floating cash flows for both the fixed-rate bond and the floating-rate instrument.
4. Agreeing on the Spread: The two parties involved in the swap agree on a spread that will be added to or subtracted from the floating-rate cash flows.
5. Net Cash Flow Calculation: Calculate the net cash flow for both parties by subtracting the fixed-rate cash flows from the adjusted floating-rate cash flows.
6. Excel Formulas for Asset Swap Calculations:
   • For fixed-rate cash flows: =PMT(rate, nper, pv)
   • For floating-rate cash flows: =principal * (LIBOR + spread)

Scenario:

Let’s consider an investor who holds a 5-year fixed-rate bond with a face value of $1,000, a coupon rate of 4%, and an annual payment frequency. The investor decides to enter into an asset swap and agrees to exchange cash flows with a 5-year Treasury bill with a face value of $1,000.

Fixed-rate bond details:

• Face value: $1,000
• Coupon rate: 4%
• Term: 5 years

Treasury bill details:

• Face value: $1,000
• LIBOR: 2%
• Spread: 1%

Calculations:

1. Fixed-rate cash flow for the bond: =PMT(4%/1, 5*1, -1000) = $236.90 per year
2. Floating-rate cash flow for the Treasury bill: =1000 * (2% + 1%) = $30 per year
3. Net Cash Flow: Subtract the fixed-rate cash flow from the adjusted floating-rate cash flow: =30 - 236.90 = -$206.90 per year

Result:

In this scenario, the investor would pay $206.90 annually as part of the asset swap arrangement.

Other Approaches:

1. Using Excel Data Tables: Utilize Excel’s Data Table feature to analyze various scenarios by changing the swap spread and observing the impact on cash flows.
2. Scenario Analysis with Goal Seek: Employ Excel’s Goal Seek function to find the required swap spread that achieves a specific target net cash flow.
3. Graphical Representation: Create a graphical representation of the cash flows over time using Excel charts to visually understand the impact of different swap parameters.