Easy Explanation Notes with Examples

DURATION function in Excel


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
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360


The Duration function returns the Duration 6.6220851 years.

Function Errors

Error Occurs
#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.

Copyright © 2016 - 2018 Explainry.com | All Rights Reserved