The DURATION function, categorized as a Financial function, calculates the Macauley duration of a security that has a par value of $100 and pays interest on a periodic basis. The function is useful for predicting future cash flows of investments.
DURATION(settlement, maturity, coupon, yld, frequency, [basis])
settlement – Required. The security’s settlement date. The date on which the security was traded to the buyer.
maturity – Required. The security’s expiry date. The date on which the security will expire.
coupon – Required. The annual coupon rate of the security.
yld – Required. The security’s annual yield.
frequency – Required. The frequency of coupon payments to be made in a year.
[basis] – Optional. The type of day count basis to be used in the calculation.
|Basis||Day count basis|
|0 or omitted||US (NASD) 30/360|
The Duration function returns the Duration 6.6220851 years.
|#VALUE!||If settlement or maturity date is not valid.|
|#NUM!||If coupon < 0 or if yld < 0.|
|If supplied frequency argument is any number other than 1, 2, or 4.|
|If basis < 0 or if basis > 4.|
|If settlement ≥ maturity.|