June 18, 2021

How to use ODDLYIELD Function in Excel

The Excel ODDLYIELD function calculates the yield of a security with an odd (short or long) last period.

Syntax:= ODDLYIELD (sd, md, ld, rate, pr, redem, freq, [basis])

The ODDLYIELD function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Last_interest    Required. The security’s last coupon date.
  • Rate    Required. The security’s interest rate
  • Pr    Required. The security’s price.
  • Redemption    Required. The security’s redemption value per $100 face value.
  • Frequency    Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Example: Let’s look at some Excel ODDLYIELD function examples and explore how to use the ODDLYIELD function as a worksheet function in Microsoft Excel:

ODDLYIELD Function - How to use ODDLYIELD Function in Excel

Syntax:  =ODDLYIELD(B1,B2,B3,B4,B5,B6,B7,B8)


ODDLYIELD Function in Excel - How to use ODDLYIELD Function in Excel

Based on the Excel spreadsheet above, the following ODDLYIELD examples would return:

Syntax: =ODDLYIELD(B1,B2,B3,B4,B5,B6,B7,B8)
Result: 0.072103278

Syntax: =ODDLYIELD(DATE(2019,6,20),DATE(2019,8,15),DATE(2018,11,15),0.0425,99.525,100,2,0)
Result: 0.072103278

Syntax: =ODDLYIELD(DATE(2019,6,20),DATE(2019,8,15),DATE(2018,11,15),4.25%,B5,B6,B7,B8)
Result: 0.072103278


  1. #NUM! error – Occurs when:
    • The last interest date is greater than or equal to the settlement date.
    • The given settlement date is greater than or equal to the maturity date.
    • We provided invalid numbers for the rate, pr, redemption, frequency or [basis] arguments. That is if either rate is less than 0; pr is less than 0; redemption is less than or equal to 0; frequency is any number other than 1, 2, or 4; or [basis] is any number other than 0, 1, 2, 3 or 4).
  2. #VALUE! error – Occurs when:
    • The given settlement, maturity, or issue arguments are not valid Excel dates.
    • Any of the given arguments is non-numeric.
READ:  How to use PRICEMAT Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *