Documentation/Calc Functions/PRICEMAT

    From The Document Foundation Wiki
    Other languages:

    Function name:

    PRICEMAT

    Category:

    Financial Analysis

    Summary:

    Calculates the price per 100 currency units of face value, for a security that pays interest just once at the maturity date. The price is calculated as at the date of settlement, based on the present values of the future cash flows (the single interest payment and the redemption value). PRICEMAT takes no account of the compounding of interest and the value returned excludes accrued interest.

    PRICEMAT assumes a security with the following general characteristics:

    • A constant interest rate throughout the security's lifetime, with all interest to be paid as a single sum at a specified maturity date.
    • A redemption value to be paid at a specified maturity date.
    • A constant discount / forecast yield rate.

    Syntax:

    PRICEMAT(Settlement; Maturity; Issue; Rate; Yield[; Basis])

    Returns:

    Returns a 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 currency units.

    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 and the accumulated interest.

    Issue is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which is the date of issue 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 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.


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

    Additional details:

    • The following "variables" are used in the equation that follows:
    [math]\displaystyle{ \begin{align} Years_{IM}~&=~\text{YEARFRAC}(Issue;\:Maturity;\: Basis)\\ Years_{IS}~&=~\text{YEARFRAC}(Issue;\:Settlement;\: Basis)\\ Years_{SM}~&=~\text{YEARFRAC}(Settlement;\:Maturity;\: Basis) \end{align} }[/math]
    YearsIM is the time between the issue date and the maturity date – the lifetime of the security; YearsIS is the time between the issue date and the settlement date; and YearsSM is the time between the settlement date and the maturity date.
    • The formula for PRICEMAT is then:
    [math]\displaystyle{ \text{PRICEMAT}~=~100.0\:\times\:\left( \left( \frac{1.0\:+\:Years_{IM}\:\times\: Rate}{1.0\:+\:Years_{SM} \:\times \:Yield}\right) ~ -~ Years_{IS} \:\times\: Rate \right) }[/math]
    • Be aware that PRICEMAT does not report an error if you specify an issue date that is illogical – for example, an issue date that is later than both the settlement and maturity dates.

    Examples:

    Formula Description Returns
    =PRICEMAT(A1; A2; A3; A4; A5; A6) where cells A1:A3 contain the dates 2019-02-15, 2025-04-13, and 2018-11-11 respectively; cells A4:A6 contain the values 5.75%, 6.50%, and 0 respectively. Here PRICEMAT returns the price per 100 currency units of face value as 96.27 currency units. 96.2711878213478
    =PRICEMAT(DATE(2019; 2; 15); DATE(2025; 4; 13); DATE(2018; 11; 11); 0.0575; 0.065) 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. PRICEMAT again returns the price per 100 currency units of face value as 96.27 currency units. 96.2711878213478
    =PRICEMAT("1999-02-15";"1999-04-13";"1998-11-11"; 0.061; 0.061; 0) Here the function calculates the price per 100 currency units of face value as 99.98 currency units. 99.984498875557

    Related LibreOffice functions:

    PRICE

    PRICEDISC

    YEARFRAC

    YIELDMAT

    ODF standard:

    Section 6.12.40, part 2

    Related (or similar) Excel functions:

    PRICEMAT