How to Use the ODDLYIELD Function in Excel
Today, we will explore a potent financial function utilized in Excel and Google Sheets, known as ODDLYIELD. This function is particularly useful for calculating the yield of securities like bonds which have an irregular first coupon period. Let’s delve into the functionalities and application of the ODDLYIELD function to optimize our financial analyses.
Syntax
The syntax for the ODDLYIELD function is as follows:
ODDLYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])
Each parameter is defined as:
- Settlement: The date when the security is considered settled.
- Maturity: The expiration date of the security.
- Issue: The date when the security was issued.
- First_coupon: The date for the first interest payment of the security.
- Rate: The annual coupon rate of the security.
- Pr: The price of the security per $100 of face value.
- Redemption: The redemption value of the security per $100 of face value.
- Frequency: The frequency of the coupon payments per year (annual, semiannual, or quarterly).
- Basis: (Optional) The day count convention to be used for calculating interest (0=30/360, 1=actual/actual, etc.).
Examples
Consider a practical example to illustrate the use of the ODDLYIELD function. Suppose the details of a bond are as follows:
Settlement | Maturity | Issue | First Coupon | Rate | Pr | Redemption | Frequency |
---|---|---|---|---|---|---|---|
1-Jan-2022 | 1-Jan-2027 | 1-Jan-2022 | 1-Jul-2022 | 5% | 97.50 | 100 | 2 |
To calculate the yield of this bond, the function is written as:
=ODDLYIELD("01/01/2022", "01/01/2027", "01/01/2022", "07/01/2022", 0.05, 97.50, 100, 2)
This expression returns the yield of the bond. Additionally, the basis parameter can be included as needed.
Applications
The ODDLYIELD function is invaluable in various financial contexts, notably:
- Calculating yields for bonds with non-standard initial periods.
- Evaluating the appeal of investments in fixed-income securities.
- Analyzing and comparing yields across different bonds that feature atypical terms.
By mastering the ODDLYIELD function, you will significantly enhance your proficiency in assessing and making informed decisions about fixed-income investments.
More information: https://support.microsoft.com/en-us/office/oddlyield-function-c873d088-cf40-435f-8d41-c8232fee9238