Documentation/Calc Functions/MDURATION
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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:
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:
ODF standard:
Related (or similar) Excel functions:
MDURATION