How to use Excel DURATION Function


DURATION function in excel is a financial function. It is used to find duration of a security in Macaulay form using Macaulay duration formula. Duration is expressed as weighted average of cash flows at present. It is a worksheet function and can be entered in the cell in which value is required.


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


  • Settlement: It is the date on which security is settled. It comes after issue date when security is rated to the buyer.
  • Maturity: It is the date on which security will be matured.
  • Coupon: Annual Coupon Rate.
  • YLD: Annual yield of the security.
  • Frequency: Number of coupon payments in a year.
  • Basis: Day count basis to be used.


  • Microsoft Excel stores dates as serial numbers. By default, January 1, 1900 is serial number 1 and corresponding dates have serial number greater than 1.
  • Settlement, maturity, frequency, and basis are converted to integers.
  • If settlement or maturity is not a valid excel date, #VALUE! error is displayed.
  • If coupon < 0 or if yld < 0, #NUM! error is displayed.
  • If frequency is a number different from 1, 2, or 4, #NUM! error is displayed.
  • If basis < 0 or if basis > 4, #NUM! error is displayed.


In this example, various arguments are entered in column A and their values are entered in column B, the resulted value is found using DURATION formula in column D.

Leave a Comment