How to use MDURATION Function in Excel

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
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 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:

MDURATION Function - How to use MDURATION Function in Excel

Syntax:  =MDURATION(B1,B2,B3,B4,B5,B6)

Result:

MDURATION Function in Excel 1 - How to use MDURATION Function in Excel

Based on the Excel spreadsheet above, the following MDURATION examples would return:

Syntax: =MDURATION(B1,B2,B3,B4,B5,B6)
Result: 2.484228865

Syntax: =MDURATION(DATE(2018,5,1),DATE(2021,4,1),0.09,0.1,2,1)
Result: 2.484228865

Syntax: =MDURATION(DATE(2018,5,1),DATE(2021,4,1),9%,10%,B5,B6)
Result: 2.484228865

Note:

(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: MDURATION Function in Excel - How to use MDURATION Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *