ODDLYIELD

ODDLYIELD

Returns the yield of a security that has an odd (short or long) last period.

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

Syntax

ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)

Settlement   is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Last_interest   is the security's last coupon date.

Rate   is the security's interest rate.

Pr   is the security's price.

Redemption   is the security's redemption value per $100 face value.

Frequency   is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.

Basis   is the type of day count basis to use.

Basis

Day count basis

0 or omitted

US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360


Remarks

  • The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January 1, 1996, the settlement date would be July 1, 1996, and the maturity date would be January 1, 2026, which is 30 years after the January 1, 1996, issue date.
  • Settlement, maturity, last_interest, and basis are truncated to integers.
  • If any argument is nonnumeric, ODDLYIELD returns the #VALUE! error value.
  • If settlement, maturity, or last_interest is not a valid date, ODDLYIELD returns the #NUM! error value.
  • If rate < 0 or if pr £ 0, ODDLYIELD returns the #NUM! error value.
  • If basis < 0 or if basis > 4, ODDLYIELD returns the #NUM! error value.
  • The following date condition must be satisfied; otherwise, ODDLYIELD returns the #NUM! error value:

    maturity > settlement > last_interest

  • ODDLYIELD is calculated as follows:

    Where:

    Ai = number of accrued days for the ith, or last, quasi-coupon period within odd period counting forward from last interest date before redemption.

    DCi = number of days counted in the ith, or last, quasi-coupon period as delimited by the length of the actual coupon period.

    NC = number of quasi-coupon periods that fit in odd period; if this number contains a fraction it will be raised to the next whole number.

    NLi = normal length in days of the ith, or last, quasi-coupon period within odd coupon period.

Example

A bond has the following terms:

April 20, 1987, settlement date
June 15, 1987, maturity date
December 24, 1986, last interest date
3.75 percent coupon
$99.875 price
$100 redemptive value
Frequency is semiannual
30/360 basis

The yield of a security that has an odd (short or long) first period is:

ODDLYIELD("4/20/87","6/15/87","12/24/86",0.0375,99.875,100,2,0) equals 0.045192