Excel Functions

Excel DURATION Function

Pinterest LinkedIn Tumblr

The Excel DURATION Functions help calculate the Macauley Duration. The function calculates the duration of a security that pays interest on a periodic basis with a par value of $100. In MS-Excel the DURATION function is categorized under financial function. The Macauley duration is the weighted average term to maturity of the cash flows from a bond.

Purpose of Excel DURATION Function

To get Macauley duration for an assumed par value of $100.

Return value

DURATION function returns the Macauley duration in years.


= DURATION(settlement, maturity, coupon, yld, frequency, [basis])


settlementThe date when security is purchased.(settlement date) Mandatory
maturityThe date when the security expires.( maturity date) Mandatory
couponThe annual coupon rate of security. Mandatory
yldThe annual yield of security. Mandatory
frequencyThe number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4. Mandatory
[basis]The type of day count basis to use. Optional
0 or OmittedUS (NASD) 30/360
4European 30/360

Usage note

  • The parameters such as settlement, maturity, frequency, basis are truncated to integers.
  • If settlement or maturity is not a valid date, DURATION returns the #VALUE! error value.
  • If coupon < 0 or if yld < 0, DURATION returns the #NUM! error value.
  • If frequency is any number other than 1, 2, or 4, DURATION returns the #NUM! error value
  • If basis < 0 or if basis > 4, DURATION returns the #NUM! error value.
  • If settlement ≥ maturity, DURATION returns the #NUM! error value.
  • Microsoft Excel stores date as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2018 is serial number 43101 because it is 43,101 days after January 1, 1900.


Since 2007 for Windows.
Since 2011 for Mac.

Related Posts

Write A Comment