The YIELD function in Excel gives you the annual return you’d earn on a bond if you buy it at its current price and hold it until it matures. This is what investors call the yield to maturity.
The coupon rate printed on a bond doesn’t tell you this, because you rarely pay exactly 100 for it.
Below, I start with one bond and then work up to comparing a whole list, callable bonds, prices quoted per $1,000, and why the RATE shortcut gets it slightly wrong.
Follow along with the example file
YIELD Function Examples.xlsx
YIELD Function Syntax
Here is the syntax of the YIELD function:
=YIELD(settlement,maturity,rate,pr,redemption,frequency,[basis])
- settlement – the date you buy the bond (not the date it was first issued).
- maturity – the date the bond matures and pays back its face value.
- rate – the bond’s annual coupon rate, such as 4.5%.
- pr – the price you pay per $100 of face value.
- redemption – the amount the bond pays back at maturity per $100 of face value. For most bonds, this is 100.
- frequency – the number of coupon payments per year. Use 1 for annual, 2 for semiannual, and 4 for quarterly.
- [basis] – optional. The day count method: 0 (US 30/360), 1 (actual/actual), 2 (actual/360), 3 (actual/365), or 4 (European 30/360). If you leave it out, Excel uses 0.
Because the price is per $100 of face value, a price of 96.40 means you pay 96.4% of what the bond pays back at maturity, whatever its actual face value is.
A frequency other than 1, 2, or 4 (like 12 for a bond that pays monthly) returns the #NUM! error.
The day count method usually makes only a tiny difference. On the bond in Example 1, basis 0 and basis 1 both return 5.16%.
The settlement and maturity dates should be real dates in cells, or dates built with the DATE function. If Excel can’t read a date, YIELD returns the #VALUE! error.
The YIELD function works in Excel 365, 2024, 2021, 2019, 2016, and Excel for Mac.
When to Use the YIELD Function
Use this function when you need to:
- Find the yield to maturity of a bond before you buy it
- Compare bonds that have different prices, coupons, and maturity dates
- Work out the yield to call and yield to worst on a callable bond
- Check how much of a bond’s return comes from buying it below or above 100
Let me show you a few practical examples of how to use the YIELD function.
Example 1: Calculate Yield to Maturity in Excel
Let’s start with a single bond.
Below are the details of a bond, and I want to get its yield to maturity in cell B9.
Here is the formula:
=YIELD(B2,B3,B4,B5,B6,B7)
The formula returns 5.16%, which is higher than the bond’s 4.5% coupon. That’s because you pay 96.40 and get 100 back at maturity, so on top of the coupons, you also earn that 3.60 difference.
YIELD returns a decimal (0.0516). If you see that instead of a percentage, format the cell as a percentage with two decimal places.
Example 2: Compare Bond Yields With One Formula
Now let’s get the yield for a whole list of bonds at once.
Below is a list of ten bonds with their maturity dates, coupon rates, prices, and payments per year, and I want the yield of each bond in column F, using the settlement date in cell I2.
Here is the formula:
=YIELD(I2,+B2:B11,+C2:C11,+D2:D11,100,+E2:E11)
This one formula spills all ten yields into F2:F11. Each bond uses its own maturity date, coupon, price, and payments per year, while the settlement date in I2 and the redemption of 100 apply to every bond.
The plus sign (+) in front of each range is what makes this work. YIELD can’t handle a range of cells, but the plus sign turns each range into a plain list of values, and YIELD returns one result for each value.
This is the same trick that fixes other Excel functions that won’t spill, like EOMONTH and EDATE.
Important: If you give YIELD a plain range like B2:B11 without the plus sign, it returns a single #VALUE! error instead of spilling. This happens even when only one argument is a range, so put + in front of every range you pass to it.
The results also show why you can’t compare bonds by their coupon alone. Oakridge Hospital costs exactly 100, so its yield matches its 4.00% coupon.
Summit Telecom costs 104.20, so its yield (5.44%) is lower than its 6.10% coupon. Harbor City Water costs 93.60, so it yields 4.87% even though its coupon is only 2.90%.
If you are using Excel 2019 or earlier, enter the below formula in cell F2 and then copy it down the range:
=YIELD($I$2,B2,C2,D2,100,E2)
Example 3: Bond Price Quoted per $1,000
Here’s a mistake that’s easy to make when you copy a bond price from somewhere else.
Sometimes a bond’s price is shown as the dollar amount for one $1,000 bond, like $962.50, instead of a price per $100. Below are the details of such a bond, and I want to get its yield to maturity in cell B9.
Here is the formula:
=YIELD(B2,B3,B4,B5/B6*100,100,B7)
The B5/B6*100 part converts the $962.50 quote into a price per $100 (96.25), which is what YIELD expects. With that, the formula returns 5.11%.
Since it divides by the face value in B6, the same formula also works for a bond with a different face value, such as $5,000.
Important: If you use the $962.50 quote as the price with a redemption of 100, YIELD returns -39.15%. Changing the redemption to 1,000 doesn’t fix it either (you get 1.20%), because YIELD always works out the coupon on $100 of face value.
Example 4: Calculate Yield to Call and Yield to Worst
Let’s step it up with a callable bond.
A callable bond lets the issuer pay it back early, on a set call date and at a set call price.
Below are the details of a callable bond that costs more than 100, and I want its yield to maturity in B10, its yield to call in B11, and its yield to worst in B12.
Here is the formula for the yield to maturity:
=YIELD(B2,B3,B5,B6,100,B8)
This is the regular yield if the bond runs all the way to its 2036 maturity date, and it returns 5.54%.
Here is the formula for the yield to call:
=YIELD(B2,B4,B5,B6,B7,B8)
It’s the same YIELD formula with two changes. The first call date (B4) takes the place of the maturity date, and the call price (B7) takes the place of 100, which gives 4.51%.
The yield to call is lower here because you paid 107.25 for the bond. If it’s called in 2029, you get only 102 back after three years, so there’s less time for the coupons to make up the extra you paid.
And here is the formula for the yield to worst:
=MIN(B10,B11)
The yield to worst is the lower of the two yields, so it’s the least you can expect to earn, whether or not the bond gets called. Here it’s 4.51%, the yield to call.
Example 5: Skip Matured Bonds in a Bond List (#NUM! Error)
Here’s a problem that shows up when you keep a running list of bonds.
Once a bond’s maturity date is on or before the settlement date, YIELD returns the #NUM! error for it, because the bond has nothing left to pay. So a plain YIELD formula would show #NUM! for every bond that has already matured.
Below is a list of bonds where two have already matured, and I want the yield of each bond in column E, using the settlement date in H2 and the payments per year in H3.
Here is the formula:
=IF(B2:B11<=H2,"Matured",YIELD(H2,+B2:B11,+C2:C11,+D2:D11,100,H3))
The IF part checks each maturity date against the settlement date in H2. If the bond has already matured, it shows Matured, and for every other bond, it returns the yield.
In this list, Maple Grove Schools and Clearwater Bank both matured earlier in 2026, so they show Matured instead of #NUM!.
You could wrap YIELD in IFERROR instead, but that would also hide real input mistakes (like a frequency of 12) and label them as Matured. Testing the maturity date keeps those errors visible.
If you are using Excel 2019 or earlier, enter the below formula in cell E2 and then copy it down the range:
=IF(B2<=$H$2,"Matured",YIELD($H$2,B2,C2,D2,100,$H$3))
Example 6: YIELD vs RATE for Yield to Maturity
The RATE function can also give you a bond’s yield to maturity, so let’s compare it with YIELD.
Below are the details of a bond bought between two coupon dates, and I want to compare the YIELD result in B8 with the RATE shortcut in B9.
Here is the YIELD formula:
=YIELD(B2,B3,B4,B5,100,B6)
And here is the RATE shortcut:
=RATE(COUPNUM(B2,B3,B6),B4*100/B6,-B5,100)*B6
How this formula works:
- COUPNUM(B2,B3,B6) counts the coupon payments left until maturity, which is 10.
- B4*100/B6 is the coupon paid each period per $100 of face value, which is 2.50.
- -B5 is the price you pay, entered as a negative number because the money goes out.
- RATE returns the return per period, and multiplying by B6 turns it into an annual rate.
The RATE shortcut returns 5.98%, which is a bit lower than the 6.05% from YIELD. The difference comes from buying the bond between coupon dates.
RATE assumes the first coupon is a full six months away. But this bond was bought on March 1, 2027, and its next coupon comes on April 15, only about six weeks later.
YIELD works with the actual dates, including the interest that has built up since the last coupon. The two only match when you buy a bond exactly on a coupon date (with a settlement date of 10/15/2026, both return 5.98%).
So these are six practical ways to use the YIELD function in Excel. It gets you a bond’s yield to maturity, compares a whole list of bonds with one formula, and works out the yield to call and yield to worst.
I hope you found this Excel tutorial useful.
Other Excel Articles You May Also Like:
