The ODDFYIELD function in Excel is designed to calculate the yield of a security that features an odd first period. This function is particularly useful for investors and financial analysts who need to assess the income generated from bonds or other fixed-income investments that do not adhere to the standard payment schedule.

Syntax

=ODDFYIELD(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

• settlement: The date when the security is purchased.
• maturity: The date when the security matures.
• issue: The date when the security was issued.
• first_coupon: The date of the first coupon payment.
• rate: The annual coupon rate of the security.
• yld: The annual yield of the security.
• redemption: The redemption value of the security at maturity.
• frequency: The number of coupon payments per year.
• basis: The day count basis to use (optional).

Example #1

=ODDFYIELD("2023-01-01", "2025-01-01", "2022-01-01", "2023-07-01", 0.05, 0.04, 1000, 2)

This function computes the yield of a security with a settlement date of January 1, 2023, and maturity on January 1, 2025. Given a first coupon payment on July 1, 2023, a coupon rate of 5%, an annual yield of 4%, a redemption value of 1000, and semi-annual payments (frequency of 2), the result might be approximately 0.0532 or 5.32%.

Example #2

=ODDFYIELD("2023-03-15", "2024-03-15", "2022-03-15", "2023-09-15", 0.06, 0.05, 2000, 1)

In this example, the function calculates the yield for a security purchased on March 15, 2023, maturing on March 15, 2024, with a first coupon on September 15, 2023. With a coupon rate of 6% and an annual yield of 5%, the function gives a yield of approximately 0.0565 or 5.65%.

Example #3

=ODDFYIELD("2023-05-05", "2026-05-05", "2022-05-05", "2023-11-05", 0.045, 0.038, 1500, 2)

This function evaluates the yield of a security bought on May 5, 2023, set to mature on May 5, 2026, with the first coupon occurring on November 5, 2023. Assuming a coupon rate of 4.5% and an annual yield of 3.8%, the yield calculated would be around 0.0404 or 4.04%.

Error handling

• VALUE: This error occurs when one or more arguments are of the wrong type (e.g., text instead of date).
• NUM: This error signifies that one of the numeric input arguments is invalid (e.g., negative values for frequency).
• REF: This indicates that one of the cell references is not valid, typically due to deleted cells.

Conclusion

In summary, the ODDFYIELD function is an essential tool for investors analyzing securities with unconventional first periods. It provides valuable insights into the yield potential of such investments, allowing for informed decision-making. By understanding its syntax and leveraging this function effectively, users can better navigate the complexities of fixed-income securities.