Documentation/Calc Functions/ODDLPRICE

    From The Document Foundation Wiki
    Other languages:

    Function name:

    ODDLPRICE

    Category:

    Financial Analysis

    Summary:

    Calculates the price per 100 currency units of face value for a security that has an odd last coupon period. For such a security, the final coupon period is shorter or longer than the earlier coupon periods and this means that the interest amount paid at maturity differs from the regular coupon payment.

    Syntax:

    ODDLPRICE(Settlement; Maturity; Last Interest; Rate; Yield; Redemption; Frequency[; Basis])

    Returns:

    Returns a real number which is the price, per 100 currency units of face value, for a security with the specified characteristics. The number returned is in the same currency unit as the specified redemption value.

    Arguments:

    Settlement is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when a trade is "settled". On this date the transfer of the security to the buyer is completed, and the buyer makes the appropriate payment.

    Maturity is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when the security matures. On maturity, the holder is paid the redemption value of the security.

    Last Interest is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies the date of the latest coupon payment before the Settlement date.

    Rate is a positive real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the annual interest rate (coupon rate) of the security.

    Yield is a non-negative real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the annual yield of the security.

    Redemption is a positive real number, or a reference to a cell containing that number, which is the redemption value (in currency units) of the security per 100 currency units of face value.

    Frequency is a positive integer, or a reference to a cell containing that integer, which is the number of interest payments (coupon payments) per year. Valid values are 1 (annual), 2 (semiannual), or 4 (quarterly).


    Basis is an integer in the range 0 to 4, or a reference to a cell containing that integer, which indicates how the year is to be calculated. For more background information, visit Wikipedia's Day count convention page.

    Basis Calculation
    0 or missing US method (NASD), 12 months of 30 days each
    1 Exact number of days in months, exact number of days in year.
    2 Exact number of days in month, year has 360 days.
    3 Exact number of days in month, year has 365 days.
    4 European method, 12 months of 30 days each.


    • If any of Settlement, Maturity, or Last Interest is not a valid date expression, then ODDLPRICE reports a #VALUE! error.
    • If any of Settlement, Maturity, or Last Interest contain time components, then these are ignored by ODDLPRICE.
    • If the Settlement date is on or after the Maturity date, then ODDLPRICE reports an invalid argument error (Err:502).
    • If the Last Interest date is on or after the Settlement date, then ODDLPRICE reports an invalid argument error (Err:502).
    • If any of Rate, Yield, Frequency, or Redemption is non-numeric, then ODDLPRICE reports a #VALUE! error.
    • If Basis is non-numeric, then ODDLPRICE reports an invalid argument error (Err:502).
    • If either Basis or Frequency is a non-integer value, then ODDLPRICE truncates it to an integer value.
    • If Yield is less than 0.0, then ODDLPRICE reports an invalid argument error (Err:502).
    • If either Rate or Redemption is less than or equal to 0.0, then ODDLPRICE reports an invalid argument error (Err:502).
    • If, after any truncation, Frequency is not equal to 1, 2, or 4, then ODDLPRICE reports an invalid argument error (Err:502).
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then ODDLPRICE reports an invalid argument error (Err:502).

    Additional details:

    • The formula for ODDLPRICE given below utilizes the following definitions:
    [math]\displaystyle{ \begin{align} DCi~&=~\text{YEARFRAC}(Last\:Interest;\:Maturity;\:Basis) \:\times\:Frequency \\ DSCi~&=~\text{YEARFRAC}(Settlement;\: Maturity;\: Basis) \:\times\:Frequency\\ Ai~&=~\text{YEARFRAC}(Last\:Interest;\: Settlement;\: Basis) \:\times\:Frequency \end{align} }[/math]
    • The formula for ODDLPRICE is then:
    [math]\displaystyle{ \text{ODDLPRICE} ~=~ \frac{\left ( Redemption\:+\:\left(100\:\times\:DCi\:\times\:{\frac{Rate} {Frequency}} \right) \right ) } {\left( \left({DCSi \:\times\: \frac{Yield} {Frequency} }\right) \:+\:1 \right)} ~-~\left(100\:\times\:Ai\:\times\: \frac{Rate} {Frequency}\right) }[/math]

    Examples:

    Formula Description Returns
    =ODDLPRICE(B1; B2; B3; B4; B5; B6; B7; B8) where cells B1:B3 contain the dates 2020-02-15, 2028-12-31, 2019-08-31 respectively; cells B4:B8 contain the values 5.75%, 6.50%, 100, 2, and 0 respectively. Here ODDLPRICE returns the price per 100 currency units of face value as 94.80 currency units. 94.8035518752716
    =ODDLPRICE(DATE(2020; 2; 15); DATE(2028; 12; 31); DATE(2019; 8; 31); 0.0575; 0.065; 100; 2) Here the function calculates the price for the same security as in the previous example, but utilizes the DATE function instead of typing formatted date values. Also, no value is specified for the Basis argument and so the default value of 0 (12 months of 30 days each) is applied. ODDLPRICE again returns the price per 100 currency units of face value as 94.80 currency units. 94.8035518752716
    =ODDLPRICE("1999-02-15"; "2007-11-15"; "1998-12-31"; 0.0575; 0.0475; 110.5; 4; 3) Here the function calculates the price per 100 currency units of face value as 113.39 currency units. 113.386273502738

    Related LibreOffice functions:

    ODDFYIELD

    ODDFPRICE

    ODDLYIELD

    YEARFRAC

    ODF standard:

    Section 6.12.33, part 2

    Related (or similar) Excel functions:

    ODDLPRICE