Oddfyield Function

Purpose

Return value

Syntax

=ODDFYIELD(sd,md,id,fd,rate,pr,redem,freq,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• id - Issue date of the security.
• fd - First coupon date.
• rate - Annual coupon rate of security.
• pr - Price of security.
• redem - Redemption value per $100 face value.
• freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).
• basis - [optional] Day count basis (see below, default =0).

Using the ODDFYIELD function

Some bonds have an irregular first or last period, so interest payments don’t fit a normal schedule. To calculate the yield of a bond with an irregular first period, you can use the ODDFYIELD function. The ODDFYIELD Function returns the yield (as a percentage) of a security with a short or long first period.

Example

In the example shown, we want to calculate the yield of a bond with a settlement date of 1-Feb-2019. The bond matures on 15-Feb-2022, and pays a coupon rate of 5%, with the first coupon paid on 15-Feb-2019. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is:

=ODDFYIELD(C10,C12,C9,C11,C7,C5,C6,C13,C14)

With these inputs, the ODDFYIELD function returns a yield of 6.10%, with the percentage number format applied.

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, the DATE function is the best option. To illustrate, the formula below has all values hardcoded, with the DATE function used for each of the four required dates:

=ODDFYIELD(DATE(2019,2,1),DATE(2022,2,15),DATE(2018,12,1),DATE(2019,2,15),0.05,97,100,2,0)

Basis

The basis argument controls how days are counted. The ODDFYIELD function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis . This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• All dates, plus frequency and basis , are truncated to integers.
• If dates are invalid (i.e. not actually dates) ODDFYIELD returns #VALUE!
• ODDFYIELD returns #NUM when: ( maturity > first_coupon > settlement > issue ) is NOT true Rate < 0 or pr <= 0 Basis is out-of-range

Purpose

Return value

Syntax

=ODDLPRICE(sd,md,id,rate,yld,redem,freq,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• id - Last interest date of security.
• rate - Interest rate of security.
• yld - Annual required rate of return.
• redem - Redemption value per $100 face value.
• freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).
• basis - [optional] Day count basis (see below, default =0).

Using the ODDLPRICE function

Some bonds have an irregular first or last period, so interest payments don’t fit a normal schedule. To calculate the price of a bond with an irregular last period, you can use the ODDLPRICE function. The ODDLPRICE Function returns the price per $100 face value of a security having a short or long last period.

Example

Assume we need to calculate the price per $100 face value of a bond with a last interest date of 15-Oct-2017 and a settlement date of 5-Feb-2018. The bond matures on 15-Jun-2018, and pays a coupon rate of 5% with a required return of 6%. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is:

=ODDLPRICE(C8,C10,C9,C6,C7,C5,C11,C12)

With these inputs, the ODDLPRICE function returns 99.61, with currency number format applied.

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, the DATE function is the best option. To illustrate, the formula below has all values hardcoded. The DATE function is used to supply each of the three required dates:

=ODDLPRICE(DATE(2018,2,5),DATE(2018,6,15),DATE(2017,10,15),0.05,0.06,100,2,0)

Basis

The basis argument controls how days are counted. The ODDLPRICE function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis . This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• All dates, plus frequency and basis , are truncated to integers.
• If dates are invalid (i.e. not actually dates) ODDLPRICE returns #VALUE!
• ODDLPRICE returns #NUM when: ( maturity > settlement > last_interest ) is NOT true Basis is out-of-range