Documentation/Calc Functions/PRICE

    From The Document Foundation Wiki
    Other languages:

    Function name:

    PRICE

    Category:

    Financial Analysis

    Summary:

    Calculates a quoted price for a fixed interest security, per 100 currency units of face value. The quoted price is calculated as at the date of settlement, based on the present values of the future cash flows (regular interest payments and redemption value). PRICE returns the so-called "clean price", which excludes accrued interest.

    PRICE assumes a security with the following general characteristics:

    • Regular interest / coupon payments (1, 2, or 4 per year).
    • A constant interest / coupon rate.
    • A redemption value to be paid at a specified maturity date.
    • A constant discount / forecast yield rate.

    Syntax:

    PRICE(Settlement; Maturity; Rate; Yield; Redemption; Frequency[; Basis])

    Returns:

    Returns a positive real number which is the quoted 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.

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

    Additional details:

    • The following figure shows a timeline that clarifies some of the "variables" used in the equations that follow.
    Generic timeline used to explain the PRICE function
    • Time period A is the number of days in the coupon period that contains the settlement date.
    • Time period B is the number of days from the beginning of the same coupon period to the settlement date.
    • Time period C is the number of days from the settlement date to the end of the same coupon period.
    • N is the number of interest / coupon payments between the settlement date and the maturity date.
    • PRICE calculates and then combines three terms, as follows.
    • The first term (PVRedemption) is the present value (at the settlement date) of the redemption value that will be received at the maturity date. This is given by:
    [math]\displaystyle{ PV_{Redemption}~=~\frac{Redemption}{\left( 1+\frac{Yield}{Frequency} \right)^{C-1+\frac{A}{B}}} }[/math]
    • The second term (PVCoupons) is the present value (at the settlement date) of the future interest / coupon payments that will be received between the settlement date and the maturity date. This is given by:
    [math]\displaystyle{ PV_{Coupons}~=~ \sum_{k=1}^{C} \frac{\frac{100 \times Rate}{Frequency} }{\left( 1+\frac{Yield}{Frequency} \right)^{k-1+\frac{A}{B}}} }[/math]
    • The third term represents accrued interest that should not be included in the final result.
    [math]\displaystyle{ Accrued\: Interest~=~100\times \frac{Rate}{Frequency} \times \frac{D}{B} }[/math]
    • The final value returned by PRICE is then:
    [math]\displaystyle{ \text{PRICE}~=~PV_{Redemption}\:+\:PV_{Coupons}\:-\:Accrued\:Interest }[/math]

    Examples:

    Formula Description Returns
    =PRICE(B1; B2; B3; B4; B5; B6; B7) where cells B1 and B2 contain the dates 2020-02-15 and 2028-12-31 respectively; cells B3:B7 contain the values 5.75%, 6.50%, 100, 2, and 0 respectively. Here PRICE returns the price per 100 currency units of face value as 94.99 currency units. 94.9932662376627
    =PRICE(DATE(2020; 2; 15); DATE(2028; 12; 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. PRICE again returns the price per 100 currency units of face value as 94.99 currency units. 94.9932662376627
    =PRICE("1999-02-15"; "2007-11-15"; 0.0575; 0.0475; 110.5; 4; 3) Here the function calculates the price per 100 currency units of face value as 114.10 currency units. 114.104795343665

    Related LibreOffice functions:

    PRICEDISC

    PRICEMAT

    ODF standard:

    Section 6.12.38, part 2

    Related (or similar) Excel functions:

    PRICE