Yield to Maturity

Executive Summary

Yield to Maturity is an essential financial metric used to determine the annual return an investor can expect if they hold a bond until its maturity date, assuming all payments are made as scheduled. It’s a comprehensive measure that reflects the coupon income as well as any gains or losses that occur when the bond is redeemed at its face value. Yield to Maturity is commonly used by investors to compare the relative value of various fixed-income securities.

Formula Deep Dive

Yield to Maturity (Coupon Bond)

Present Value (PV): represents today’s value of the zero-coupon bond
Face Value: is the amount the bond will be worth at maturity (its par value)
Yield to Maturity (ytm): is the return anticipated on a bond if it is held until it matures.
Time to Maturity (T): represents the total number of years until the bond matures.
Coupon Payments (C_t): are made by the bond at each period t until maturity. Typically, these payments are fixed and known in advance.

Yield to Maturity is the internal rate of return of a bond, incorporating all coupon payments and the redemption at maturity. It considers both the current income (coupon payments) and the capital gain or loss (the difference between the bond’s current market price and its face value) upon maturity.

The formula above assumes that the PV of the bond is given in order to be able to solve for the yield to maturity. However, algebraically solving for the yield to maturity of coupon bonds is often not possible due to its definition as a solution to a polynomial equation. In practice, one can solve it through iterative approximation methods.

Additional Formulas

Yield to Maturity (Zero Bond)

Present Value (PV): represents today’s value of the zero-coupon bond
Face Value: is the amount the bond will be worth at maturity (its par value)
Yield to Maturity (ytm): is the return anticipated on a bond if it is held until it matures.
Time to Maturity (T): represents the total number of years until the bond matures.

The calculation of yield to maturity for a zero-coupon bond differs fundamentally from that of a coupon-bearing bond due to the absence of periodic interest payments. For a zero-coupon bond, the yield to maturity calculation does not necessitate solving a polynomial equation, as it does with coupon bonds. Instead, the yield to maturity for a zero-coupon bond is directly derived through the bond’s present value, its face value, and the time to maturity, offering a direct computation of the annualized rate of return assuming the bond is held to maturity.

Application in Excel

To compute the yield to maturity using Excel, it is essential to first determine the bond’s present value (bond price). In the provided example, the bond price can be calculated using the SUMPRODUCT(array1, [array2], [array3], …) function. In this case we insert the bond’s cash flows, which are located in cells B8:F8 as well as the discount factors, expressed as 1/(1+r)^t, which are in cells B11:F11. In this example the bond’s price can be obtained through the following formula:

=SUMPRODUCT(B8:F8,B11:F11)

To compute the yield to maturity, Excel’s YIELD function can be employed, with the syntax being YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]). In situations where the ’settlement‘ or ‚maturity‘ dates are not specified, the DATE function (DATE(year, month, day)) can be used to define these dates, ensuring a five-year gap between them. Here, ‚rate‘ denotes the coupon rate found in cell B3, ‚pr‘ is the calculated bond price in cell B15. ‚Redemption‘ corresponds to the face value mentioned in cell B1, and ‚frequency‘ refers to the number of times coupons are paid annually, which is 1 in this case. The formula for this example is as follows:

=YIELD(DATE(0,1,1),DATE(B2,1,1),B3,B15,B1,1)

In this example, the yield to maturity is 1.1% The shown Excel functions are particularly helpful for financial analysts and investors in evaluating the return on a bond investment compared to other investment opportunities. The yield to maturity can further be used to calculate the Modified Duration of the bond.