Understanding Noncallable Bonds and Excel Formulas

Noncallable bonds are bonds that cannot be redeemed or paid back by the issuer before the maturity date. This means that the bondholder will receive fixed interest payments until the bond expires, regardless of the changes in the market interest rates. Noncallable bonds are different from callable bonds, which give the issuer the right to buy back the bonds at a certain price before the maturity date. Callable bonds are usually issued when the issuer expects the interest rates to decline in the future, so they can save money by paying off the bonds early and issuing new ones at a lower rate. Noncallable bonds are more favorable for the bondholder, as they are protected from the risk of losing interest income if the issuer calls the bonds early. However, noncallable bonds usually offer lower interest rates than callable bonds, as the issuer has to compensate the bondholder for the lack of flexibility and the higher interest rate risk. Noncallable bonds are also more predictable and stable than callable bonds, as the bondholder knows exactly how much and how long they will receive the interest payments. Some examples of noncallable bonds are US Treasury bonds and most municipal bonds.

Basic Theory

Callable bonds give the issuer the option to redeem the bond before maturity, usually when interest rates have
declined. On the other hand, noncallable bonds lack this feature, providing more stability to investors. The
yield of a noncallable bond is crucial for investors to assess the profitability of their investment.

Procedures for Calculating Noncallable Bond Yields

Yield to Maturity (YTM) Calculation:

The Yield to Maturity is the total return anticipated on a bond if it is held until it matures. The YTM formula
for a noncallable bond can be expressed as follows:

$YTM = \frac{C + \frac{(F - P)}{n}}{\frac{(F + P)}{2}}$

Where:

$C$ is the annual coupon payment,
$F$ is the face value of the bond,
$P$ is the purchase price of the bond, and
$n$ is the number of years to maturity.

Scenario: ABC Corporation Noncallable Bond

Let’s consider an example with ABC Corporation issuing a noncallable bond with the following details:

Face value ( $F$ ): $1,000
Annual coupon payment ( $C$ ): $60
Years to maturity ( $n$ ): 5
Purchase price ( $P$ ): $950

Excel Calculation

Create a table in Excel with the following headers: “Year,” “Coupon Payment,” “Principal Repayment,”
“Total Cash Flow,” and “Discount Factor.”
Fill in the “Year” column from 1 to 5.
Use the formula $C$ for the “Coupon Payment” column. In Excel, the formula would be =C.
Calculate the “Principal Repayment” using the formula $\frac{(F - P)}{n}$ .
Compute the “Total Cash Flow” by summing the “Coupon Payment” and “Principal Repayment.”
Determine the “Discount Factor” using the formula
$\frac{1}{(1 + YTM)^{\text{Year}}}$ , where YTM is the yield to maturity.
Calculate the present value of each cash flow by multiplying the “Total Cash Flow” by the corresponding
“Discount Factor.”
Sum the present values to get the present value of cash flows.
Finally, use Excel’s Goal Seek or Solver function to find the YTM that makes the present value of cash
flows equal to the purchase price.

Result

After performing the calculations, the Yield to Maturity for the ABC Corporation noncallable bond is approximately
6.8%.

Other Approaches

Using Excel Functions: Excel provides functions like IRR or
YIELD that can be utilized to calculate YTM directly.
Data Table for Sensitivity Analysis: Construct a data table to analyze how changes in
purchase price or coupon rate affect the YTM.
Graphical Representation: Create a line chart to visually represent the relationship
between YTM and purchase price.