ODDFYIELD Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Calculate Excel yield for an odd first coupon with ODDFYIELD. Build full-precision short and long first-period price fixtures, verify dates and understand iterative solving.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: ODDFYIELD finds the annual yield implied by a security’s price when its first coupon period is irregular. The first coupon may be short or long. The function uses the explicit issue and first-coupon dates rather than treating the schedule as regular from issue.
Syntax and arguments
=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])
All arguments except basis are required. Pr is the quoted price on the same per-100 scale as redemption. Rate is the annual coupon rate, distinct from the yield being solved. The function exposes no guess or iteration-limit argument.
Basis is optional: 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360. Frequency is the normal schedule’s 1, 2 or 4 annual payments, even though the first period is irregular. Rate is annual; do not divide it by frequency before passing it.
Use DATE values. The reference documents truncating dates and basis to integers. The required order is strict: maturity > first_coupon > settlement > issue. Equal dates in those adjacent positions do not satisfy the documented condition.
Worked example: recover the short-period yield
For a short first coupon, enter these inputs:
| Cell | Meaning | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,5,15) |
| B3 | Maturity | =DATE(2027,7,15) |
| B4 | Issue | =DATE(2026,4,15) |
| B5 | First coupon | =DATE(2026,7,15) |
| B6 | Annual coupon rate | 6% |
| B8 | Redemption per 100 | 100 |
| B9 | Frequency | 2 |
| B10 | Basis | 0 |
The ordinary semiannual coupon is 3 per 100. The short first period is 90 convention days out of a normal 180, so its payment is 1.5. There are 30 accrued days at settlement, meaning 0.5 accrued interest. The later payments are 3 in January 2027 and 103 at July maturity.
- In B7 enter
=1.5/1.04^(1/3)+3/1.04^(4/3)+103/1.04^(7/3)-0.5. - Its expected price is approximately 97.82007179703774.
- In D2 enter
=ODDFYIELD(B2,B3,B4,B5,B6,B7,B8,B9,B10). - Expected yield: approximately 8%, subject to numerical solution tolerance.
The price was independently constructed at per-period factor 1.04, corresponding to annual yield 8% for two coupons per year. Keeping the price expression avoids a mismatch introduced by rounding to 97.82 before solving.
Second example: change the known yield in the price construction
Replace every 1.04 in the B7 arithmetic expression with 1.03. The price is now independently built at 6% annual yield. The same ODDFYIELD formula is expected to return approximately 6%, while issue, first coupon and coupon rate are unchanged.
The coupon rate happens to equal that constructed yield, but the irregular first payment and accrued portion still matter. Do not replace this fixture with an assumed price of 100 without checking the actual equation.
Advanced application: verify a long first coupon
For issue October 15, 2025; settlement February 15, 2026; first coupon July 15, 2026; maturity July 15, 2027; rate 6%; redemption 100; frequency 2 and basis 0, construct price:
=4.5/1.04^(5/6)+3/1.04^(11/6)+103/1.04^(17/6)-2
Expected price is approximately 97.31429569711298. Use that full-precision value as pr in the corresponding ODDFYIELD call; expected yield is approximately 8%. The 4.5 first coupon spans nine 30/360 months, and accrued interest at settlement is 2.
Microsoft documents Newton-method iteration based on ODDFPRICE, changing yield through up to 100 iterations until calculated price is close to the input. The independent monotone price/known-yield controls here are not proof of native convergence for every unusual or ill-conditioned input. A display-rounded price can legitimately give a slightly different yield.
Errors and model boundaries
Microsoft documents #VALUE! for an invalid date; #NUM! for negative rate, zero or negative price, basis outside 0–4, or failure of the strict date ordering. Use supported frequency and coherent positive redemption; the individual error list is not exhaustive.
Keep maturity on the regular schedule implied by first_coupon. Separate a short first period from a short last period: they are different contracts and require different inputs. No behavior from OpenOffice, LibreOffice or another engine is used as a substitute for Excel evidence, and no security price was retrieved from a live market.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
DATE in Excel | RATE in Excel | XIRR in Excel | ROUND in Excel
- Microsoft ODDFYIELD reference, retrieved October 17, 2026.