The ODDLYIELD Function[1] is categorized under Excel Financial functions. It helps calculate the yield of a security with an odd (short or long) last period.
In financial analysis, some bonds come with irregular first or last periods. Due to the irregular first or last period, the payment sometimes doesn’t fit in any of the given patterns. If we wish to calculate the yield of a security with an odd last period, we can use the ODDLYIELD function for such bonds.
The ODDLYIELD function uses the following arguments:
Settlement (required argument) – This is the security’s settlement date. It is the date after the issue date when the security is traded to the buyer.
Maturity (required argument) – This is the security’s maturity date. It is the date when the security expires.
Last_interest (required argument) – This is the security’s last coupon date.
Rate (required argument) – The security’s interest rate.
Pr (required argument) – The security’s price.
Redemption (required argument) – This is the security’s redemption value per $100 face value.
Frequency (required argument) – This is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis (optional argument) – This the type of day count basis to use. The possible values of the basis are as follows:
Basis
Day Count basis
0 or omitted
US(NASD) 30/360
1
Actual/actual
2
Actual/360
3
Actual/365
4
European 30/360
How to use the ODDLYIELD Function in Excel?
To understand the uses of the ODDLYIELD function, let’s consider an example:
Example
Let’s assume we have to calculate the price per $100 face value of a security with last interest date Jan. 31, 2017, settlement date Apr. 15, 2017, maturity date Jun. 30, 2017. The rate of interest is 5%, the price is $99.9 and the redemption value is $100. Payments are made quarterly and the US (NASD) 30/360 day count basis is used:
The formula used is:
We get the result below:
The above function returned the value 5.43%. In the example:
The date arguments were supplied to the ODDLYIELD function as references to cells containing dates.
The rate argument was entered as a percentage, 5%. However, we can enter the argument as the simple numeric value, 0.005.
As we intend to omit the [basis] argument, the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis).
Notes about the ODDLYIELD Function:
#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).
#VALUE! error – Occurs when:
The given settlement, maturity, or issue arguments are not valid Excel dates.
Thanks for reading CFI’s guide to the Excel ODDLYIELD function. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.