Understanding Bond Current Yield in Excel

Bond current yield is a measure of the annual income that a bond generates, relative to its current market price. It is expressed as a percentage, and it can be calculated by dividing the annual coupon payment by the bond price.

For example, suppose you buy a bond that pays $50 in interest every year, and the bond price is $900. The bond current yield is ($50) / ($900) = 0.0556, or 5.56%. This means that for every $100 you invest in the bond, you can expect to receive $5.56 in interest income per year.

However, bond current yield is not the same as the actual return that you will receive if you hold the bond until maturity. The actual return depends on other factors, such as the face value of the bond, the coupon rate, the time to maturity, and the reinvestment rate of the coupon payments. These factors are taken into account by another measure, called the yield to maturity (YTM).

The yield to maturity is the total return that you will earn from buying a bond and holding it until it matures. It is also expressed as a percentage, and it can be calculated by finding the discount rate that makes the present value of the bond’s future cash flows equal to its current price.

For example, suppose the bond in the previous example has a face value of $1,000, a coupon rate of 5%, and 10 years to maturity. The yield to maturity is the discount rate that satisfies the following equation:

$900 = $25 / (1 + YTM) + $25 / (1 + YTM)^2 + … + $25 / (1 + YTM)^{19} + $1, 025/ (1 + Y TM)^{20}$

Solving for YTM, we get YTM = 0.0598, or 5.98%. This means that if you buy the bond for $900 and hold it until it matures, you will earn an annualized return of 5.98%.

As you can see, the bond current yield and the yield to maturity are different, because they reflect different aspects of the bond investment. The bond current yield only considers the annual interest income, while the yield to maturity considers the total cash flow over the life of the bond. Therefore, the bond current yield can be higher or lower than the yield to maturity, depending on the bond price, the coupon rate, and the time to maturity.

Basic Theory

Current yield is a straightforward ratio that expresses the annual interest income of a bond as a percentage of its current market price. The formula for current yield is as follows:

$\text{Current Yield} = \left( \frac{\text{Annual Interest Payment}}{\text{Current Market Price}} \right) \times 100$

It’s essential to note that the current yield provides a basic measure of a bond’s return but doesn’t account for factors such as capital gains or losses.

Procedures for Calculating Bond Current Yield in Excel

Understand Bond Details: Gather information about the bond, including its face value, annual coupon rate, annual interest payment, and current market price.
Use Excel Formulas: Employ the following Excel formulas for current yield calculation:

$\text{Annual Interest Payment} = \text{Face Value} \times \text{Coupon Rate}$

$\text{Current Yield} = \left( \frac{\text{Annual Interest Payment}}{\text{Current Market Price}} \right) \times 100$

Create an Excel Table: Organize the bond details and calculations in a clear and structured Excel table.

Explanation with Real Numbers

Consider a bond with the following details:

Face Value: $1,000
Coupon Rate: 5%
Current Market Price: $950

Calculation:

$\text{Annual Interest Payment} = $1,000 \times 5\% = $50$

$\text{Current Yield} = \left( \frac{$50}{$950} \right) \times 100 \approx 5.26\%$

Excel Table Example

Bond Details
Face Value	$1,000
Coupon Rate	5%
Current Market Price	$950
Annual Interest Payment	=B2*B3
Current Yield	=(D2/B4)*100

Results

Results
Annual Interest Payment	$50
Current Yield	5.26%

Other Approaches

Yield Function: Excel also provides the YIELD function, which calculates the yield of a bond based on its price, face value, coupon rate, and maturity. It offers a more comprehensive approach considering the time value of money.
Using Excel’s RATE Function: The RATE function can be employed to calculate the bond yield. This function considers irregular cash flows, making it suitable for bonds with varying coupon payments.