Understanding Bond-Equivalent Yields in Excel

Bond-equivalent yield (BEY) is a way of comparing the returns of different bonds that pay interest at different frequencies. For example, some bonds pay interest every six months, while others pay interest every three months or every year. To compare these bonds, we need to convert their yields to a common basis, such as an annual yield.

BEY is calculated by taking the annual percentage yield (APY) of a bond and adjusting it for the number of times the bond pays interest per year. APY is the effective annual rate of return that takes into account the compounding effect of interest. For example, if a bond pays 5% interest every six months, the APY is not 10%, but 10.25%, because the interest earned in the first six months is reinvested and earns more interest in the second six months.

To find the BEY of a bond, we first need to find its APY. Then, we raise the APY to the power of the number of times the bond pays interest per year, and subtract one. For example, if a bond pays 5% interest every six months, its APY is 1.05^2 – 1 = 0.1025 or 10.25%. To find its BEY, we raise the APY to the power of 2, since the bond pays interest twice a year, and subtract one. The BEY is (1.1025)^2 – 1 = 0.2156 or 21.56%.

BEY helps investors compare the returns of different bonds and choose the ones that offer the best value. However, BEY has some limitations, such as not taking into account the time value of money, the risk of default, or the tax implications of bond income. Therefore, investors should also consider other factors when evaluating bonds, such as yield to maturity, current yield, duration, and credit rating.

Basic Theory

The basic formula for Bond-Equivalent Yield is:

Where:

• is the bond-equivalent yield.
• is the bond’s yield to maturity.
• is the number of compounding periods per year.

Procedures

1. Determine the Yield to Maturity (YTM): YTM represents the expected total return on a bond if held until maturity. This is a critical input for calculating BEY.
2. Identify the Number of Compounding Periods (n): Understand how frequently the bond pays interest (e.g., semiannually, quarterly) to determine the compounding periods per year.
3. Apply the BEY Formula: Plug the YTM and into the BEY formula to calculate the bond-equivalent yield.

Excel Formulas

In Excel, you can use the following formulas to calculate BEY:

Scenario: ABC Corp 5% Bond

Let’s consider a scenario with an ABC Corp bond with a 5% annual coupon rate, maturing in 5 years, and a YTM of 4.5%. The bond pays interest semiannually.

Excel Table

Excel Formulas

1. Calculate Semiannual Coupon Payment:

      

2. Calculate Total Coupon Payments:

      

3. Calculate Future Value of Principal:

      

4. Calculate Total Future Value:

      

5. Calculate Bond-Equivalent Yield (BEY):

      

Calculation

Using the above formulas and applying the given scenario, the calculated BEY for the ABC Corp 5% Bond is approximately 4.62%.

Other Approaches

1. Using RATE Function:
   In Excel, you can also use the RATE function to calculate BEY. For this scenario, you can use the formula:

      

2. Data Table for Sensitivity Analysis:
   Construct a data table in Excel to analyze how changes in YTM or compounding periods affect BEY.