by The Excel Oddlprice function calculates the price per $100 face value of a security with an odd (short or long) last period.
Syntax:= ODDLPRICE (sd, md, id, rate, yld, redem, freq, [basis])
The ODDLPRICE function syntax has the following arguments:
- Settlement Required. 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 Required. The security’s maturity date. The maturity date is the date when the security expires.
- Last_interest Required. The security’s last coupon date.
- Rate Required. The security’s interest rate.
- Yld Required. The security’s annual yield.
- Redemption Required. The security’s redemption value per $100 face value.
- Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
- Basis Optional. 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 |
Example: Let’s look at some Excel ODDLPRICE function examples and explore how to use the ODDLPRICE function as a worksheet function in Microsoft Excel:
Syntax: =ODDLPRICE(B1,B2,B3,B4,B5,B6,B7,B8)
Result:
Based on the Excel spreadsheet above, the following ODDLPRICE examples would return:
Syntax: =ODDLPRICE(B1,B2,B3,B4,B5,B6,B7,B8)
Result: 99.72724208
Syntax: =ODDLPRICE(DATE(2019,2,7),DATE(2019,6,15),DATE(2018,10,15),0.048,0.055,100,2,0)
Result: 99.72724208
Syntax: =ODDLPRICE(DATE(2019,2,7),DATE(2019,6,15),DATE(2018,10,15),4.8%,5.5%,B6,B7,B8)
Result: 99.72724208
Note:
- #NUM! error – Occurs when:
- The given issue date is greater than or equal to the settlement date.
- The given settlement date is greater than or equal to the maturity date.
- We provided invalid numbers for the rate, yield, redemption, frequency or [basis] arguments. That is if either rate is less than 0; yield is less than 0; redemption is less than or equal to 0; frequency is any number other than 1, 2, or 4; or [basis] is any number other than 0, 1, 2, 3, or 4).
- #VALUE! error – Occurs when:
- The given settlement, maturity, last_interest arguments are not valid Excel dates.
- Any of the given arguments is non-numeric.
