Documentation/Calc Functions/MDURATION

    From The Document Foundation Wiki
    Other languages:

    Function name:

    MDURATION

    Category:

    Financial Analysis

    Summary:

    Calculates the modified duration of a fixed interest security in years.

    Modified duration is related to Macaulay duration as calculated by the DURATION function. However, modified duration is considered to provide a better indication of price and value sensitivity to changes in interest rate.

    Syntax:

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

    Returns:

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

    Additional details:

    • MDURATION uses the following relationship to calculate the modified duration from the Macaulay duration:
    [math]\displaystyle{ \text{MDURATION}~=~\frac{\text{DURATION}}{1+\frac{Yield}{Frequency}} }[/math]
    where it is assumed that the same argument values are passed to both MDURATION and DURATION functions.
    • For more information about modified duration, visit Wikipedia’s Bond duration page.

    Examples:

    Data for examples
    A B C D E F
    1 2022-01-01 2027-01-01 5% 4% 2 1
    Formula Description Returns
    =MDURATION(A1; B1; C1; D1; E1; F1) The modified duration for the specified security is approximately 4.4 years. 4.41113731090512
    =MDURATION(DATE(2022; 1; 1); DATE(2027; 1; 1); 0.05; 0.04; 2)

    The same security as in the previous example, but utilize the DATE function instead of typing formatted date values and uses the default value of 0 for the (omitted) Basis argument.

    4.41068984757474
    =MDURATION("2022-01-01"; "2027-01-01"; 0.0; 0.4; 4; 1) Calculate the modified duration for the specified security, which provides no coupon payments. Returns a value of approximately 4.5 years. 4.54586946599726
    =MDURATION("2001-01-01"; "2006-01-01"; 8%; 9%; 2; 3) Calculate the modified duration for the specified security, returning an approximate value of 4 years. 4.02068710841898

    Related LibreOffice functions:

    DURATION

    PDURATION

    ODF standard:

    Section 6.12.26, part 2

    Related (or similar) Excel functions:

    MDURATION