ODDLPRICE

ODDLPRICE

Returns the price per $100 face value of a security having an odd (short or long) last coupon 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

ODDLPRICE(settlement,maturity,last_interest,rate,yld,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.

Yld   is the security's annual yield.

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, ODDLPRICE returns the #VALUE! error value.
  • If settlement, maturity, or last_interest is not a valid date, ODDLPRICE returns the #NUM! error value.
  • If rate < 0 or if yld < 0, ODDLPRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4, ODDLPRICE returns the #NUM! error value.
  • The following date condition must be satisfied; otherwise, ODDLPRICE returns the #NUM! error value:

    maturity > settlement > last_interest

Example

A bond has the following terms:

February, 7, 1987, settlement date
June 15, 1987, maturity date
October 15, 1986, last interest date
3.75 percent coupon
4.05 percent yield
$100 redemptive value
Frequency is semiannual
30/360 basis

The price per $100 of a security having an odd (short or long) last coupon period is:

ODDLPRICE("2/7/87","6/15/87","10/15/86",0.0375,0.0405,100,2,0) equals 99.87829