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

Basis Day Count Basis
0 (or omitted) US (NASD) 30/360
1 actual/actual
2 actual/360
3 actual/365
4 European 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.

A B
1 Settlement Date: 01-Apr-2015
2 Maturity 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   or Invalid 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   or One of both of the supplied settlement or maturity dates are not a valid Excel dates.