Jump to content

Documentation/Calc Functions/MDURATION

From The Document Foundation Wiki

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