# The Excel ODDLYIELD Function

Related Function:
ODDFYIELD

## Basic Description

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

The syntax of the function is :

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

Where the arguments are as follows:

settlement-The settlement date of the security (ie. the date that the coupon is purchased)
maturity-The maturity date of the security (ie. the date that the coupon expires)
last_interest-The date of the security's last coupon
rate-The security's interest rate
pr-The security's price
redemption-The security's redemption value per \$100 face value
frequency-The number of coupon payments per year. This must be one of the following:
 1 - Annually 2 - Semi-Annually 4 - Quarterly
[basis]-

An optional integer argument which specifies the financial day count basis that is used by the security. Possible values are:

BasisDay Count Basis
0 (or omitted)US (NASD) 30/360
1actual/actual
2actual/360
3actual/365
4European 30/360
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

The date arguments must satisfy the following:

last_coupon   <   settlement   <   maturity

Note also, that the date arguments should be supplied to the function as either:

• References to cells containing dates
or
• Dates returned from other functions or formulas

Warning:

• If you input the date arguments as text, these may be interpreted differently, depending on the date system and date interpretation settings on your computer.
• You can input the date arguments as serial numbers, but this is not recommended, as date serial numbering varies across different computer systems.

## Excel Oddlyield Function Example

The following example shows the Excel Oddlyield function used to calculate the yield of a security with a last coupon date of 31-Jan-2011, a settlement date of 15-Apr-2011, and a maturity date 30-Jun-2011. The security's interest rate is 5%, the price is \$99.50 and the redemption value is \$100. Payments are made quarterly and the US (NASD) 30/360 day count basis is used:

AB
1Settlement Date:15-Apr-2011
2Maturity Date:30-Jun-2011
3Last Coupon Date:31-Jan-2011
4=ODDLYIELD( B1, B2, B3, 5%, 99.5, 100, 4 )

The above Oddlyield function returns the value 7.36%.

Note that, in the above example:

• As recommended, the date arguments are supplied to the function as references to cells containing dates.
• As the [basis] argument has been omitted, the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis).

Further information and examples of the Excel Oddlyield function can be found on the Microsoft Office website.

## Oddlyield Function Errors

The following table lists the most common Oddlyield function errors, along with their causes:

Common Errors
 #NUM! - Occurs if either:the last_coupon date is ≥ settlement datethe settlement date is ≥ maturity dateInvalid numbers are supplied for the rate, pr, redemption, frequency or basis arguments(i.e. either: rate < 0; pr ≤ 0; redemption ≤ 0; frequency is any number other than 1, 2 or 4; or basis is any number other than 0, 1, 2, 3 or 4 #VALUE! - Occurs if either:any of the supplied arguments are non-numericany of the supplied date arguments are not a valid Excel dates #NAME? - Occurs when Analysis ToolPak add-in is not enabled in your Excel.You will need to enable the add-in if you want to use the Excel Oddlyield function.