How to use ODDFYIELD function in Excel?

The `ODDFYIELD` function in Excel is used to calculate the yield of a security with an odd (irregular) first period. This function is generally applicable for calculating the yield of bonds with unusual first interest periods.

Here’s the syntax for the `ODDFYIELD` function:

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

Parameters:

settlement: The security’s settlement date. This is the date after the issue date when the security is traded to the buyer. It must be a valid Excel date.
maturity: The security’s maturity date. This is when the security expires or is paid off.
issue: The security’s issue date. This is when the security was first issued.
first_coupon: The security’s first coupon date. This is when the first interest payment will be made.
rate: The security’s annual coupon rate.
pr: The security’s price per $100 face value.
redemption: The security’s redemption value per $100 face value. Generally, this value is 100 if you receive the face value at maturity.
frequency: The number of coupon payments per year. Possible values are:

1 for annual payments
2 for semi-annual payments
4 for quarterly payments

[basis] (optional): The day count basis to use. Defaults to 0 if omitted. Possible values are:

0: US (NASD) 30/360
1: Actual/actual
2: Actual/360
3: Actual/365
4: European 30/360

Example:

Assuming you have a bond with the following characteristics:

Settlement date: March 1, 2022
Maturity date: March 1, 2026
Issue date: December 1, 2021
First coupon date: September 1, 2022
Annual coupon rate: 5%
Price: $95
Redemption value: $100
Semi-annual payments
Basis: US (NASD) 30/360

You would enter the function in Excel like this:

=ODDFYIELD(DATE(2022, 3, 1), DATE(2026, 3, 1), DATE(2021, 12, 1), DATE(2022, 9, 1), 5%, 95, 100, 2, 0)

This function will give you the yield of the bond considering its odd first period. Ensure all dates are entered using the DATE function or are preformatted as dates in Excel. Adjust the parameters according to your specific bond data and financial conventions.

facebooklinkedin