May 9, 2021

# 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:

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

Result:

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.

READ:  How to use PDURATION Function in Excel

(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:

#### Excel

View all posts by Excel →