The Excel MDURATION function returns the Macauley modified duration for a security with an assumed par value of $100
Syntax:= MDURATION (settlement, maturity, coupon, yld, freq, [basis])
The MDURATION function syntax has the following arguments:
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
- Coupon Required. The security’s annual coupon rate.
- Yld Required. The security’s annual yield.
- Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
- Basis Optional. The type of day count basis to use.
|Basis||Day count basis|
|0 or omitted||US (NASD) 30/360|
Example: Let’s look at some Excel MDURATION function examples and explore how to use the MDURATION function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following MDURATION examples would return:
(1) The settlement, maturity, and basis are truncated to integers. For example, you type 1.4 as the basis value, the MDURATION function will take it as 1 automatically.
(2) The MDURATION function returns #VALUE! error value, if the settlement or maturity is not a valid date.
(3) The MDURATION function returns #NUM! error value, if the settlement date is no less than the maturity date.
(4) The MDURATION function returns #NUM! error value, if the yld or coupon is less than 0.
(5) The frequency can only be one of these numbers: 1, 2, or 4. Otherwise, the MDURATION function returns #NUM! error value.
(6) The MDURATION function returns #NUM! error value, if the basis is less than 0 or greater than 4.
Modified duration is defined as follows: