Bond yield

From Bogleheads
Jump to: navigation, search

Bond yield shows how to calculate several common types of bond yield.

Microsoft Excel was used for this article. However, LibreOffice Calc will work equally well and is a free application that is supported on several operating systems. Open Office financial functions are very similar to Microsoft's functions.[1]

Excel formulas are shown where applicable. All examples are available for download at the bottom of this page.

Current yield

The current yield formula is:

Current Yield = Annual dollar coupon interest / Price

This formula does not take into account gains or losses if the bond was purchased at a discount or premium.[2][3]

For example: An 18-year, $1,000 par value, 6% coupon bond selling for $700.89 has a current yield of:

8.56% = $1,000 * 6% / $700.89

Yield to maturity

The yield to maturity is calculated using a sum of the present value of the cash flows - the same way as pricing a bond. For a semiannual payment bond, this is shown mathematically as:

p = c/(1 + y)1 + c/(1 + y)2 + c/(1 + y)3 + ... + c/(1 + y)n + M/(1 + y)n
= c(1 - (1 + y)-n) / y + M/(1 + y)n


p = Price
c = Semiannual coupon payment
y = Yield to Maturity / 2
n = Number of periods (number of years x 2)
M = Maturity value

Solving for y requires an iterative approach, it can't be done analytically. This is not a problem, as Excel has built-in financial formulas which solve this equation internally.[4]

Yield to maturity considers not only the current coupon income, but any capital gain or loss realized by holding the bond to maturity.

Yield to maturity on the coupon date

In a similar manner to bond prices, the RATE() function is used to calculate the yield to maturity for transactions that fall exactly on coupon dates. Consider the wiki example: A bond has a $10,000 face value and pays $500 annually until maturity, then pays back the $10,000 principal. If the bond price drops to $9,653 four years before maturity, the yield to maturity is 6.0%:

Maturity: $10,000.00
Coupon Rate: $500.00
Frequency: annual
Purchase Price: $9,653.00
Number of periods: 4
Yield to Maturity: 6.0% =RATE(4,500,-9653,10000,0)

Zero coupon bonds

Zero-coupon bonds have a single cash flow at maturity.

For example, a 15-year zero-coupon bond selling for $274.78 with a maturity value of $1,000 has a yield to maturity of 8.80%:[5]

Maturity: $1,000.00
Frequency: semiannual
Purchase Price: $274.78
Number of periods: 30 =2*15
Yield to Maturity: 8.80% =RATE(2*15,0,-274.78,1000,0)*2

The formula is consistent with coupon bearing bonds: Use a semiannual compounding period (N*2), the yield to maturity is an annual rate.

Yield to maturity in-between coupon dates

The concepts used for purchase and sales (settlement) dates in-between coupon dates are similar to pricing bonds.

For example: A corporate 10% coupon bond maturing on March 1, 2003 has a dirty price of $118.778. With a settlement date of July 17, 1997, the yield to maturity is 6.747%:[9]

Settlement Date: 7/17/1997 B11
Maturity date: 3/1/2003 B12
Frequency: 2 semiannual
Day Convention (basis): 0 30/360
Coupon rate: 10.000% B15
Coupon days accrued: 136 =COUPDAYBS(B11,B12,B13,B14) Cell B16
Number of days in period: 180 =COUPDAYS(B11,B12,B13,B14) Cell B17
Quoted Dirty Price: $118.7780
Accrued interest: 3.777778 =B15*100/2*B16/B17
Clean Price: $115.000222 =B18-B19 (Cell B20)
Yield to Maturity: 6.747% =YIELD(B11,B12,B15,B20,100,B13,B14) annual

Coupon rate, current yield, and yield to maturity relationships

The same relationships apply for yield to maturity as for pricing a bond.[10]

  • Selling at par: Yield to Maturity equals the Coupon rate
  • Selling at discount: Yield to Maturity is greater than the Coupon rate
  • Selling at premium: Yield to Maturity is less than the Coupon rate

Comparing bond yields

Due to the varying frequencies of coupon payments and day count conventions (the number of days in a year), it's important to convert the yield to a common basis when comparing different bonds. These conversions are straightforward. However, combining both compounding period and day count conversions may not be obvious.[11]

Bond equivalent yield

Similar to Comparing Investments, calculation of annual yield is an exponential formula. For a semiannual payment bond:

Effective annual yield = (1 + y)2 - 1


y = semiannual interest rate

Instead of using the above formula, the convention is to double the semiannual yield. Calculating the yield to maturity by doubling the semiannual yield is called the Bond Equivalent Yield (BEY).[12] For periods less than 365 days, most developed countries use simple interest.[13][14]

Other yield comparisons

To compare other securities, refer to How To Compare Yields On Different Bonds for:

  • T-Bills (which are sold on a discount basis), Treasury Notes and Bonds
  • Securities which use a 360 days/year convention vs. 365 days/year convention
  • Understanding why Bond Equivalent Yield should not be used for comparing longer maturity bonds - use Semiannual Bond Basis (SABB)
  • Understanding the differences between APR (Annual Percentage Rate) and APY (Annual Percentage Yield)
  • (Comparison of Bank CDs)

See also


  1. Open Office Calc Financial functions. For example PV(rate; numperiods; payment; futurevalue; type) is identical.
  2. Fabozzi, Frank J., "Fixed Income Mathematics, Analytical & Statistical Techniques", 3rd edition, page 74.
    There are no spreadsheet programs used in this book- the examples are used to verify results from Excel.
  3. Advanced Bond Concepts: Yield and Bond Price, on Investopedia.
  4. Financial functions need the Analysis Toolpak add-in for Excel 2003, but are already built-in for Excel 2007.
  5. Fabozzi, page 77.
  6. Fixed Income Markets and Their Derivatives, from Google books.
  7. The Excel file at the bottom of this page contains an example showing how YIELD() matches the sum of the present value of cash flows, i.e. results match the dirty price by another method.
  8. Bond Valuation Using Microsoft Excel
  9. Fabozzi, page 78.
  10. Fabozzi, page 78.
  11. How To Compare Yields On Different Bonds, on Investopedia
  12. Fabozzi, page 79.
  13. New in wiki - Bond Yield, Looking for feedback, forum discussion, direct link to post.
  14. Bond Equivalent Yield - BEY, on shows this as a simple interest equation.

External links