Its calculates the yield of a fixed-interest security from the settlement date to maturity, accounting for a first interest period that may be shorter or longer than subsequent regular periods.
Syntax
ODDFYIELD(Settlement; Maturity; Issue; First_Interest_Date; Rate; Price; Repayment; Frequency; [Basis])
Arguments
- Settlement (required) – The date the bond is transferred to the buyer.
- Maturity (required) – The date the bond’s principal is repaid.
- Issue (required) – The issuance date of the security.
- First_Interest_Date (required) – The date of the first interest payment.
- Rate (required) – The bond’s nominal annual interest rate (coupon rate).
- Price (required) – The bond’s price at settlement (as a percentage of par value, where par = 100).
- Repayment (required) – Redemption value per 100 units of par value.
- Frequency (required) – Interest payments per year (1 = annual, 2 = semi-annual, 4 = quarterly).
- Basis (optional) – Day-count convention. Defaults to 0 if omitted.
Notes
- Dates must be entered without time values; decimals are truncated.
- Frequency and Basis are truncated to integers.
- Invalid dates return #VALUE!.
- Price and Yield must be non-negative; otherwise, #NUM! is returned.
- If Frequency is not 1, 2, or 4, or Basis is outside 0–4, #NUM! is returned.
- Chronological order must be:
Maturity > First_Interest_Date > Settlement > Issue; otherwise, #NUM! is returned.
Background
For theoretical details, refer to the ODDFPRICE() function background.
ODDFYIELD() computes the effective yield required for a bond to reach its market price, informing investors of the expected return. The calculation mirrors ODDFPRICE() but solves for yield instead of price.
Example
The sample files include a fictitious bond with a shortened first interest period, The yield is derived iteratively (via goal-seek) to match the target price. ODDFYIELD() returns identical results.
