A floating rate note (FRN) is a type of bond that pays interest based on a variable rate that changes periodically. The variable rate is usually linked to a short-term benchmark rate, such as the Fed funds rate or LIBOR, plus a fixed margin that does not change. For example, an FRN might pay interest every three months at a rate of 3 months USD SOFR +0.20%. This means that the interest rate for each quarter is equal to the SOFR rate on the first day of the quarter plus 0.20%.
FRNs are attractive to investors who want to benefit from rising interest rates, since the interest rate on the FRN adjusts to the current market conditions. FRNs also have less price volatility than fixed-rate bonds, since the interest rate on the FRN is always close to the market rate. However, FRNs also have some disadvantages, such as the risk that the benchmark rate may fall below the expected level, or that the FRN may underperform the market if the benchmark rate does not reflect the true interest rate environment.
Some FRNs have special features that modify the interest rate calculation, such as caps, floors, or collars. These features limit the maximum or minimum interest rate that the FRN can pay, or set a range for the interest rate to fluctuate within. For example, a capped FRN may have a maximum interest rate of 5%, a floored FRN may have a minimum interest rate of 2%, and a collared FRN may have a range of 3% to 4%. These features can provide some protection to the investor or the issuer, depending on the direction of the interest rate movements.
Basic Theory
Floating Rate Notes typically consist of a reference rate (e.g., LIBOR or the government bond yield) and a spread.
The interest payments on FRNs are calculated by adding the reference rate and the spread. As the reference rate
changes, the interest payments on the FRN also change.
Excel Formulas for Floating Rate Notes
1. Interest Payment Calculation
The formula to calculate the interest payment for a period on a Floating Rate Note is:
Interest Payment = Principal * (Reference Rate + Spread)
2. Future Value Calculation
To calculate the future value of the FRN after a certain number of periods, you can use the FV function in Excel:
Future Value = FV(Reference Rate + Spread, Number of Periods, 0, -Principal)
Procedures
- Input Data: Gather the necessary information, including the principal amount, reference rate,
spread, and the number of periods. - Create an Excel Table: Organize your data into an Excel table to facilitate calculations and
analysis. - Interest Payment Calculation: Use the formula mentioned earlier to calculate the interest
payment for each period. - Future Value Calculation: Utilize the FV function to determine the future value of the FRN
after a specified number of periods.
Comprehensive Example
Let’s consider a scenario:
- Principal Amount: $1,000,000
- Reference Rate: 3%
- Spread: 2%
- Number of Periods: 5 years
Excel Table:
| Period | Reference Rate | Spread | Interest Payment | Future Value |
|---|---|---|---|---|
| 1 | 3% | 2% | =B2+C2 | =FV(B2+C2, 5, 0, -$A$2) |
| 2 | 3% | 2% | =B3+C3 | =FV(B3+C3, 5, 0, -$A$2) |
| … | … | … | … | … |
| 5 | 3% | 2% | =B6+C6 | =FV(B6+C6, 5, 0, -$A$2) |
Result:
The total interest payments over the 5-year period and the future value of the FRN can be obtained by summing the
respective columns.
Other Approaches
- Interest Rate Functions: Excel provides various functions like RATE and IRR, which can be used
to calculate the implied interest rate of the FRN. - Scenario Analysis: Create scenarios with different reference rates and spreads to analyze the
impact on interest payments and future values.
