Documentation/Calc Functions/DURATION

    From The Document Foundation Wiki
    Other languages:

    Function name:

    DURATION

    Category:

    Financial Analysis

    Summary:

    Calculates the Macaulay duration of a fixed interest security. In simple terms, the Macaulay duration can be thought of as a measure (in years) of how long it might take for the price of a security to be repaid by its cash flows.

    Syntax:

    DURATION(Settlement; Maturity; Coupon; Yield; Frequency[; Basis])

    Returns:

    Returns a positive real number, which is the Macaulay duration of the specified fixed interest security expressed in years.

    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 seller completes the transfer of the security to the buyer, and the buyer makes the appropriate payment to the seller.

    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 issuer must pay the holder the face value of the security.

    Coupon 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.

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

    Additional details:

    • For more information about Macaulay duration, visit Wikipedia’s Bond duration page and the other sources that it references.

    Examples:

    Formula Description Returns
    =DURATION(A1; A2; A3; A4; A5; A6) where cells A1:A2 contain the dates 2022-01-01 and 2027-01-01 respectively; cells A3:A6 contain the values 5%, 4%, 2, and 1 respectively. Here the function calculates the Macaulay duration for the specified security as approximately 4.5 years. 4.49936005712322
    =DURATION(DATE(2022; 1; 1); DATE(2027; 1; 1); 0.05; 0.04; 2) Here the function calculates the Macaulay duration for the same security as in the previous example, but utilizes the DATE function instead of typing formatted date values and uses the default value of 0 for the (omitted) Basis argument. The function returns the approximate value of 4.5 years. 4.49890364452623
    =DURATION("2022-01-01"; "2027-01-01"; 0.0; 0.4; 4; 1) Here the function calculates the Macaulay duration for the specified security, which is a "special case" as it provides no coupon payments. The value of approximately 5.0 years is returned, which is the same as the time until maturity. 5.00045641259699
    =DURATION("2001-01-01"; "2006-01-01"; 8%; 9%; 2; 3) Here the function calculates the Macaulay duration for the specified security, returning an approximate value of 4.2 years. 4.20161802829783

    Related LibreOffice functions:

    MDURATION

    PDURATION

    ODF standard:

    Section 6.12.18, part 2

    Related (or similar) Excel functions:

    DURATION