Understanding ACT/ACT Swap in Excel

An ACT/ACT swap is a type of security-based swap (SBS) that is regulated by the U.S. Securities and Exchange Commission (SEC). A security-based swap is a contract between two parties that involves the exchange of payments based on the value or performance of a security, such as a stock, bond, or loan.

An ACT/ACT swap uses a specific day count convention to calculate the interest payments for each party. A day count convention determines how interest accrues over time in a transaction34. The ACT/ACT convention means that the actual number of days in each interest period and the actual number of days in the year are used to calculate the interest rate. For example, if the interest period is from January 1 to March 31, the number of days is 90, and if the year is a leap year, the number of days in the year is 366. The interest rate is then 90/366 times the annual rate.

An ACT/ACT swap can be used for various purposes, such as hedging, speculation, or arbitrage. For example, one party may want to swap a fixed interest rate for a variable interest rate, or vice versa, depending on their expectations of the market movements. The parties agree on the terms of the swap, such as the notional amount, the interest rates, the frequency of payments, and the maturity date. The payments are usually netted, meaning that only the difference between the two payments is exchanged.

Basic Theory:

The ACT/ACT day count convention, also known as Actual/Actual, calculates the fraction of a year based on the actual number of days in both the numerator and denominator. This method is particularly prevalent in the bond markets and is considered more precise than other day count methods, such as 30/360.

Procedures:

To calculate the ACT/ACT interest for a given period, the following steps can be followed:

Determine the Number of Days: Identify the actual number of days in the interest period, considering leap years.
Calculate the Fraction of the Year: Divide the actual number of days by the total days in the year to get the fraction of the year.
Apply the Interest Rate: Multiply the fraction of the year by the annual interest rate to obtain the interest accrued for the given period.

Comprehensive Explanation:

Let’s delve into a scenario to illustrate the ACT/ACT swap with real numbers. Consider a bond with a face value of $1,000, an annual coupon rate of 5%, and semi-annual payments. The bond has an ACT/ACT day count convention.

Scenario:

Face Value: $1,000
Annual Coupon Rate: 5%
Semi-Annual Payments
ACT/ACT Day Count Convention

Assuming a semi-annual interest period, calculate the interest accrued for the first semi-annual period.

Calculation Using Excel Formula:

    
      =FACEVALUE * (COUPDAYBS(START_DATE, END_DATE, FREQUENCY, [Basis]) / DAYS360(START_DATE, END_DATE))

In this formula:

FACEVALUE is the face value of the bond.
START_DATE is the start date of the interest period.
END_DATE is the end date of the interest period.
FREQUENCY is the number of coupon payments per year (in this case, 2 for semi-annual).
Basis is the day count basis, which is not explicitly required for ACT/ACT as it is the default.

Result:

For our scenario, let’s assume the interest period starts on January 1, 2024, and ends on June 30, 2024. Applying the formula:

    
      =1000 * (COUPDAYBS("2024-01-01", "2024-06-30", 2) / DAYS360("2024-01-01", "2024-06-30"))

The calculated interest accrued for the first semi-annual period is $25.14.

Other Approaches:

While the COUPDAYBS and DAYS360 functions are commonly used, alternative methods include using the DATEDIF function for day count or creating a custom formula using the IF statement for handling leap years.