How is bond yield calculated?
Bond yield comes in several versions, and they answer different questions. Current yield is the quick one: yearly coupon divided by price. For the example, $45 ÷ $950 is 4.74%.
Current yield = yearly coupon ÷ priceCurrent yield misses the gain or loss when the bond pays back. Yield to maturity fixes this. It's the single rate at which all future coupons and the principal, discounted back to today, equal the price you pay. For the example, that rate is 5.15%, which is what this tool solves for.
Price = C/(1 + y/2) + C/(1 + y/2)² + … + (C + F)/(1 + y/2)ⁿHere C is each semiannual coupon, F is the face value, y is the yield, and n is the number of coupons left. There's no algebra trick that solves this for y when n is large, so calculators try rates until the price matches, as this one does. TreasuryDirect states the rule that ties it together: when yield is above the interest rate, the price is below par, and when yield is below it, the price is above par.
Yields are stated with semiannual compounding by convention, so 5.15% works out to 5.21% as an effective annual yield. Use effective yield when you compare a bond with a CD or something that compounds on another schedule, including a monthly-pay bond.
What is the bond yield formula in Excel?
Excel uses =YIELD(settlement, maturity, rate, price, redemption, frequency, basis), and =PRICE to go the other way. Both quote price per $100 of face value. Microsoft's own YIELD example, a 5.75% bond priced at 95.04287 with 8.75 years left, returns 6.5%, and this tool gets 6.5% from the same inputs. Its PRICE example returns $94.63 at a 6.5% yield with 9.75 years left, and this tool gets $94.63.
Excel also treats a bond with one coupon left using simple interest rather than compounding, and this tool does too. On a bond bought between coupon dates, you pay accrued interest as well as the quoted price. In the Excel case, that's $1.44 per $100, returned to you with the next coupon.
Why do bond prices fall when yields rise?
A bond's coupon is fixed, so when new bonds pay more, the old bond must sell for less to compete. Longer bonds move more. Yields rising 1 point takes the example bond from $950.00 to $878.39, but a 30-year bond at 4.5% goes from $922.73 to $792.43 as yields move from 5% to 6%.
| Years left | 3% yield | 4% yield | 5% yield | 6% yield | 7% yield |
|---|---|---|---|---|---|
| 2 | $1,038.54 | $1,019.04 | $1,000.00 | $981.41 | $963.27 |
| 5 | $1,092.22 | $1,044.91 | $1,000.00 | $957.35 | $916.83 |
| 10 | $1,171.69 | $1,081.76 | $1,000.00 | $925.61 | $857.88 |
| 20 | $1,299.16 | $1,136.78 | $1,000.00 | $884.43 | $786.45 |
| 30 | $1,393.80 | $1,173.80 | $1,000.00 | $861.62 | $750.55 |
Duration measures this sensitivity. The example bond has a modified duration of 7.90, so a 1 point yield change moves its price by roughly that percent. Its Macaulay duration is 8.10 years, the weighted average time until you get your cash. The tool also reprices the bond exactly, since duration is only an estimate for large moves. Hold to maturity and these swings don't change what the bond pays you, if the issuer pays.
What rate of return does a bond really give me?
If you hold to maturity and the issuer pays, your profit is the coupons plus any gain, or less any loss, against face value. Yield to maturity turns that into a yearly rate on the money you have tied up. These three bonds each make the same profit, but their yields differ, because the money tied up differs.
| Bond | Price | Coupon | Total coupons | Gain or loss | Profit | Yield to maturity |
|---|---|---|---|---|---|---|
| Discount | $900 | 4% | $400 | $100 | $500 | 5.3% |
| At par | $1,000 | 5% | $500 | $0 | $500 | 5% |
| Premium | $1,100 | 6% | $600 | -$100 | $500 | 4.73% |
A coupon schedule changes what a stated yield means. A 5% yield compounds to a different effective rate depending on how often the bond pays, so compare effective yields when schedules differ: once a year is 5%, twice a year is 5.06%, quarterly is 5.09%, monthly is 5.12%. Set the coupon schedule under advanced options.
When does a bond yield mislead you?
The biggest trap is current yield on a premium bond. A 6% coupon bond at $1,100 shows a current yield of 5.45%, but you get only $1,000 back, so the yield to maturity is 4.73%. A high coupon can hide a built-in loss.
Call features are next. A callable bond can be repaid early. A 6% bond at $1,060 with 15 years left yields 5.41% to maturity. If it's called in 3 years at $1,020, the yield is 4.47%. The lower one is called yield to worst. Turn on the callable option above to check yours.
Then there's reinvestment. Yield to maturity assumes each coupon earns that same rate. FINRA notes that computations generally make that assumption. On the example bond, reinvesting at 5.15% matches the 5.15% figure. With coupons earning 2%, your money compounds at 4.59%, and at 4.27% if coupons earn nothing.
Yield isn't a promise. It assumes every payment arrives on time, and it leaves out taxes and trading costs. A yield far above similar bonds can signal credit risk. A broker's fixed income desk or a fee-only adviser can review the bond's documents.
Common questions
What is the difference between coupon rate and yield?
The coupon rate is fixed when the bond is issued, as a percent of face value. Yield moves with the price. Pay less than face and the yield is above the coupon. Pay more and it is below.
Does a bond held to maturity lose money if rates rise?
The price on your statement can fall, but if the issuer pays as promised, you still get your coupons and the face value at maturity. The real cost is that your money earns the old rate while new bonds pay more.
How do I find the yield of a zero-coupon bond?
Enter a coupon rate of 0. With no coupons, the yield comes only from the gap between your price and the face value. Federal tax can apply each year as the discount accrues, under original issue discount rules in IRS Publication 550, even with no cash paid.
Does this work for Treasury bonds?
Yes, for a standard fixed-coupon note or bond, since those pay interest every six months. Published Treasury auction prices can differ slightly from an even-period result. It doesn't handle TIPS, whose principal changes with inflation.
What is accrued interest?
Between coupon dates, the seller has earned part of the next coupon. You pay that amount at settlement, on top of the quoted price, and get it back in the next coupon. Enter fractional years, like 9.75, and the tool shows it.
Sources
- Bond Yield and Return · FINRA
- Understanding Pricing and Interest Rates · U.S. Department of the Treasury
- YIELD function · Microsoft
- PRICE function · Microsoft
- Publication 550: Investment Income and Expenses · Internal Revenue Service