YIELD function

The YIELD function in Excel calculates the annual yield of a bond based on its price, interest rate, and other characteristics. This is useful for evaluating the return on investment for fixed-income securities.

Syntax:

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

Arguments:

1. settlement: The bond’s settlement date (the date the bond is purchased).
2. maturity: The bond’s maturity date (when the principal is repaid).
3. rate: The bond’s annual coupon rate (interest rate).
4. pr: The bond’s price (expressed as a percentage of its face value, e.g., 95 = 95% of face value).
5. redemption: The bond’s redemption value (usually 100, representing 100% of face value).
6. frequency: The number of coupon payments per year:
   • 1 = Annual
   • 2 = Semiannual
   • 4 = Quarterly
7. basis (optional): The day-count basis to use (defaults to 0).
   • 0 = US (NASD) 30/360
   • 1 = Actual/actual
   • 2 = Actual/360
   • 3 = Actual/365
   • 4 = European 30/360

Example:

A bond has the following characteristics:

• Settlement Date: January 1, 2023
• Maturity Date: January 1, 2030
• Annual Coupon Rate: 5%
• Price: 95 (95% of face value)
• Redemption Value: 100 (100% of face value)
• Semiannual Payments: Frequency = 2
• Day Count Basis: 0 (US 30/360)

Formula:

=YIELD(DATE(2023,1,1), DATE(2030,1,1), 5%, 95, 100, 2, 0)

Result:

The formula returns the annual yield of the bond as a percentage.

Key Points:

1. Settlement Date:
   • Must be earlier than the maturity date.
   • Use Excel’s DATE() function to ensure valid dates.
2. Price and Redemption:
   • Price and redemption values are expressed as percentages of the face value.
3. Frequency:
   • Bonds with semiannual payments (frequency = 2) are common.
4. Basis:
   • The default (0) assumes a 30/360 convention, commonly used in the U.S. bond market.
5. Error Handling:
   • If invalid inputs are provided (e.g., settlement after maturity), the function returns a #NUM! error.