How to use the DURATION Function in Excel

In this article, we will learn about how to use the DURATION function in Excel.

The DURATION function is a built-in financial function which returns the Macauley duration per $100 face value of a security that pays periodic interest.
Syntax:

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

Settlement : Settlement date of the security
Maturity : Maturity date of the security. The maturity date is the date when the security expires.
Rate : The security’s annual coupon rate
Yld : The security’s annual yield
Frequency : Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4.
basis : [optional] Day count basis.

Let’s understand this function using it in an example.
41
Here we have values to calculate the Macauley DURATION per $100 face value of a security that pays periodic interest.

Use the formula:

=DURATION(B1, B2, B3, B4, B5, 0)

B1 : Settlement date of the security
B2 : Maturity date of the security. The maturity date is the date when the security expires.
B3 : The security’s annual coupon rate
B4 : The security’s annual yield
B5 : Coupon payments per year (semiannual = 2).
0 : [optional] Day count basis.
42
Note: Dates in the formula must be entered using cell reference or DATE function. Excel date format creates #VALUE! error while computing results. Use the cell reference or DATE function in excel while taking date as input to the function

Now Press Enter to get the DURATION
43
The DURATION is 7.42

As you can see the formula returns the DURATION per $100 face value of a security that pays periodic interest.

Hope you understood how to use DURATION function and referring cell in Excel. Explore more articles on Excel function here. Please feel free to state your query or feedback for the above article.

Related Articles:

How to use the DURATION Function in Excel

Excel IPMT Function

How to Use Compound Interest Function in Excel

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube