Quick Navigation

YIELD Function

Excel 2007+

Summary

The YIELD function calculates the annual yield for a bond or security that makes periodic interest payments, helping investors assess return based on current market price and coupon terms.

Syntax

YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Parameters

Parameter	Type	Required	Description
settlement	`Date`	Yes	Security's settlement date - when traded to buyer after issue
maturity	`Date`	Yes	Security's maturity date - when it expires
rate	`Number`	Yes	Annual coupon interest rate
pr	`Number`	Yes	Security's price per $100 face value
redemption	`Number`	Yes	Redemption value per $100 face value (typically 100)
frequency	`Number`	Yes	Coupon payments per year (1=annual, 2=semiannual, 4=quarterly)
basis	`Number`	No	Day count basis (0=30/360 US, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=30/360 European)

Using the YIELD Function

YIELD is essential for bond investors and financial analysts to determine the effective yield earned when purchasing securities at current market prices. Use it to compare investment opportunities across different bonds with varying coupons, maturities, and pricing.

Common YIELD Examples

Standard Corporate Bond Yield

=YIELD(DATE(2008,2,15), DATE(2016,11,15), 0.0575, 95.04287, 100, 2, 0)

Calculates 6.5% yield for semiannual coupon bond purchased at discount

Municipal Bond Analysis

=YIELD("1/15/2025", "6/30/2030", 0.04, 98.5, 100, 2, 1)

Yield for municipal bond using actual/actual day count

Quarterly Coupon Security

=YIELD(A2, B2, C2, D2, 100, 4)

Calculates yield assuming quarterly payments (frequency=4)

Frequently Asked Questions

Frequency=2 means semiannual payments (twice per year), common for most corporate bonds.

Different markets use different day count conventions. Basis selects the appropriate method for accurate yield calculation.

No, YIELD requires a coupon rate > 0. Use YIELDDISC for discount instruments.

Common Errors and Solutions

#NUM! - Invalid frequency

Cause: Frequency must be 1, 2, or 4 only

Solution: Use 1 (annual), 2 (semiannual), or 4 (quarterly)

#VALUE! - Invalid dates

Cause: Settlement or maturity not recognized as dates

Solution: Use DATE() function: DATE(2025,1,15)

#NUM! - Invalid basis

Cause: Basis outside range 0-4

Solution: Use 0=US 30/360, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=European 30/360

Notes

Always use DATE(year,month,day) for dates to avoid #VALUE! errors
Settlement must be before maturity date
Redemption typically equals 100 for par value bonds
For single period remaining, uses simple yield formula; multi-period uses Newton iteration
Excel truncates settlement/maturity/frequency/basis to integers

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+