# The Excel MDURATION Function

Related Function:
DURATION

## Function Description

The Excel Mduration function calculates the Modified Macaulay Duration of a security that pays periodic interest, assuming a par value of \$100.

The syntax of the function is:

MDURATION( settlement, maturity, coupon, yld, frequency, [basis] )

Where the arguments are as follows:

settlement-The settlement date of the security (i.e. the date that the coupon is purchased).
maturity-The maturity date of the security (i.e. the date that the coupon expires).
coupon-The security's annual coupon rate.
yld-The security's annual yield.
frequency-

The number of coupon payments per year. This must be one of the following:

 1 - Annually 2 - Semi-Annually 4 - Quarterly
[basis]-

An optional integer argument which specifies the financial day count basis that is used by the security. Possible values are:

BasisDay Count Basis
0 (or omitted)US (NASD) 30/360
1actual/actual
2actual/360
3actual/365
4European 30/360
For a detailed explanation of the financial day count basis rules, see the Wikipedia Day Count Convention page.

Note that the date arguments should be supplied to the function as either:

• References to cells containing dates
or
• Dates returned from formulas.

Warning: If you attempt to input text representations of dates into Excel functions, they may be interpreted differently, depending on the date system and date interpretation settings on your computer.

## Excel Mduration Function Example

The following spreadsheet uses the Excel Mduration function to calculate the modified Macaulay Duration of a security that with a settlement date 01-Apr-2015, a maturity date 31-Mar-2025 and a yield of 8%. The coupon rate is 10% and payments are made quarterly.

AB
1Settlement Date:01-Apr-2015
2Maturity Date:31-Mar-2025
3=MDURATION( B1, B2, 10%, 8%, 4 )

The above Mduration function returns the value 6.540828452 years.

Note that, in the above Mduration function call:

• As recommended, the date arguments have been input as references to cells containing dates.
• The [basis] argument has been omitted and so the function uses the default value 0 (denoting the US (NADS) 30/360 day count basis).

Further examples of the Excel Mduration function are provided on the Microsoft Office website.

## Mduration Function Errors

If you get an error from the Mduration function, this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The supplied settlement date is ≥ maturity date  orInvalid numbers are supplied for the coupon, yld, frequency or [basis] arguments.(I.e. if either: coupon < 0; yld < 0; frequency is not equal to 1, 2 or 4; or [basis] is supplied and is not equal to 0, 1, 2, 3 or 4). #VALUE! - Occurs if either:Any of the supplied arguments are non-numeric  orOne of both of the supplied settlement or maturity dates are not a valid Excel dates.