Bond yield
Bond yield shows how to calculate several common types of bond yield.
 | This article builds on principles from Bond pricing to show how bond yields are calculated. It does not discuss why yields change, nor should it be used to predict future performance. Please consult the forum for guidance. |
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
where
- 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.
- Use the dirty (quoted) price
- YIELD() uses clean price as an argument.[6][7]
| Do not use Excel's compound interest rate financial functions, such as present value PV(), for any time other than the exact coupon date. The interest in-between coupon payments is simple interest (not compounded). Using these formulas will always result in an error. Always use the PRICE() or YIELD() functions, which are intended for bonds.[8] |
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
where
- 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
References
- ↑ Open Office Calc Financial functions. For example PV(rate; numperiods; payment; futurevalue; type) is identical.
- ↑ 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. - ↑ Advanced Bond Concepts: Yield and Bond Price, on Investopedia.
- ↑ Financial functions need the Analysis Toolpak add-in for Excel 2003, but are already built-in for Excel 2007.
- ↑ Fabozzi, page 77.
- ↑ Fixed Income Markets and Their Derivatives, from Google books.
- ↑ 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.
- ↑ Bond Valuation Using Microsoft Excel
- ↑ Fabozzi, page 78.
- ↑ Fabozzi, page 78.
- ↑ How To Compare Yields On Different Bonds, on Investopedia
- ↑ Fabozzi, page 79.
- ↑ New in wiki - Bond Yield, Looking for feedback, forum discussion, direct link to post.
- ↑ Bond Equivalent Yield - BEY, on Investopedia.com shows this as a simple interest equation.
External links
- Fabozzi, Frank J., "Fixed Income Mathematics, Analytical & Statistical Techniques", 3rd edition. Also available: 4th edition
- Microsoft Excel Financial Functions, enable browser cookies to view
- Bond Yield Calculation Using Microsoft Excel, from http://www.tvmcalcs.com/
- Advanced Bond Concepts: Yield and Bond Price, on Investopedia
- How To Compare Yields On Different Bonds, on Investopedia
- New in wiki - Bond Yield, Looking for feedback, forum discussion
- Download the Excel file used for this article (Google Docs): Bond Yield.xls
- No account required. Contains MS Excel in Office 2003 compatible format.
- So do we care more about change in bond price or bond yield?, forum discussion.
 | U.S. Corporate Bonds | |
|---|---|---|
| U.S. Municipal Bonds | ||
| U.S. Savings Bonds | ||
| U.S. Treasury Bonds | ||
| Government agency bonds | ||
| Non-US Bonds | ||
| Bond Topics | ||
| Comparing Vanguard US funds | ||
| Research Resources | ||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 | Financial models | |
|---|---|---|
| Securities | ||
| Performance | ||
| Finance | ||
| ||||||||||||||||||||||||||