How to use the ODDLPRICE() function in Excel

Its calculates the price of a fixed-interest security with a final interest period that differs in length from previous regular periods, without considering compound interest.

Syntax

ODDLPRICE(Settlement; Maturity; Last_Interest_Date; Rate; Yield; Repayment; Frequency; [Basis])

Arguments

• Settlement (required): Date when ownership of the bond transfers to buyer
• Maturity (required): Date when principal repayment occurs
• Last_Interest_Date (required): Date of last regular interest payment
• Rate (required): Bond’s nominal annual interest rate (coupon rate)
• Yield (required): Market interest rate for bonds of equivalent duration
• Repayment (required): Redemption value as percentage of par value (where par = 100)
• Frequency (required): Interest payments per year (1=annual, 2=semi-annual, 4=quarterly)
• Basis (optional): Day-count convention (see Table 15-2). Defaults to 0 if omitted.

Notes

1. Date inputs must not include time values; decimal places are truncated
2. Frequency and Basis are converted to integers
3. Invalid dates return #NUM! error
4. Rate and Yield must be non-negative; otherwise returns #NUM!
5. Returns #NUM! if:
   • Frequency is not 1, 2, or 4
   • Basis is outside 0-4 range
   • Chronological order is violated: Maturity > Settlement > Last_Interest_Date

Background

The function applies the financial principle:
Creditor’s Payment = Debtor’s Payment

At transaction initiation:

• Security price + accrued interest = Present value of future cash flows
• Price is expressed as percentage of par value (100 units)

Calculation is straightforward when:

• Settlement coincides with interest payment date, and
• Interest is paid annually

Complexities arise when:

• Settlement occurs between interest dates, or
• Multiple annual payments exist

Common financial methods for partial periods include:

• Moosmüller
• Braess/Fangmeyer
• ISMA (see PRICE() and YIELD() background for Excel-ISMA correlation)

Calculation Method

Excel uses simple yield (no compounding) with these principles:

1. Accrued interest calculated from days since last interest payment
2. Partial yield derived from days to maturity (based on year length)
3. Only applicable during final period before maturity

Example

Sample files include a fictitious bond calculation matching the logic, demonstrating:

• Terms with irregular final period
• Price calculation methodology
• Identical results to ODDLPRICE() function output