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