Its calculates the annual yield of a fixed-interest security (bond) given its price, coupon rate, and maturity date. This represents the effective return an investor would earn if the bond is held to maturity.
Syntax
YIELD(Settlement; Maturity; Rate; Price; Redemption; Frequency; [Basis])
Arguments
| Argument | Required | Description | Validation Rules |
| Settlement | Yes | Bond purchase date. | Must be valid date < Maturity. |
| Maturity | Yes | Bond maturity/redemption date. | Must be valid date > Settlement. |
| Rate | Yes | Annual coupon rate (decimal). | ≥ 0 (e.g., 5% = 0.05). |
| Price | Yes | Bond price per $100 face value. | > 0 (e.g., 95.50 for $95.50). |
| Redemption | Yes | Redemption value per $100 face value. | Typically 100. |
| Frequency | Yes | Coupon payments per year: 1 = Annual 2 = Semi-annual 4 = Quarterly. |
∈ {1, 2, 4}. |
| [Basis] | No | Day-count convention (0-4). Default=0. | See Table 15-2. |
Error Conditions
- #VALUE!: Invalid dates.
- #NUM!: If:
• Rate < 0 or Price ≤ 0
• Frequency ∉ {1, 2, 4}
• Basis ∉ {0, 1, 2, 3, 4}
• Settlement ≥ Maturity.
Key Formula
Solves for Yield (y) in:
Where:
- f = Frequency
- k = Period number
- N = Total periods
Examples
- Annual Coupon Bond
Scenario:
- Settlement: 31-Aug-2010
- Maturity: 4-Jan-2013
- Coupon Rate: 4.5%
- Price: $109.01 (per $100 face value)
- Redemption: $100
- Frequency: 1 (annual)
- Basis: 1 (Actual/actual)
Calculation:
=YIELD(« 8/31/2010 », « 1/4/2013 », 0.045, 109.01, 100, 1, 1)
Result: 0.617% annual yield.
ISMA Yield Conversion:
=EFFECT(YIELD(…), 2)
Background
- Day-Count Conventions:
| Basis | Method |
| 0 | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
- Yield Types:
- Current Yield: Coupon/Price (simpler but less accurate).
- Yield to Maturity (YTM): Total return (what YIELD() calculates).
- Market Dynamics:
- Price < 100 → Yield > Coupon Rate (discount bond).
- Price > 100 → Yield < Coupon Rate (premium bond).