# The Excel DURATION Function

Related Function:
MDURATION

## Function Description

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

The syntax of the function is:

DURATION( 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 input to the Duration 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 Duration Function Example

In the following example, the Excel Duration function is used to calculate the annual duration of a coupon purchased on 01-Apr-2015, with Maturity date 31-Mar-2025 and a coupon rate of 10%. The yield is 8% and payments are made quarterly.

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

The function returns the Duration 6.671645021 years.

Note that, in the above example:

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

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

## Duration Function Errors

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

Common Errors
 #NUM! - Occurs if either:The supplied settlement date is ≥ maturity dateorInvalid 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-numericorOne or both of the supplied settlement or maturity dates are not a valid Excel dates.