DURATION Excel Function

What Is DURATION In Excel?

The DURATION Excel function calculates the bond’s annual duration to pay the loan interest periodically. The cash inflow depends on the coupon and the maturity value at the end. The calculation is also known as Macaulay Duration. The DURATION function in Excel is an inbuilt financial function, so we can insert the formula from the “Function Library” or enter it in the worksheet directly.

For example, the image below depicts the Description in column A and Values in column B. We will apply the DURATION Excel function to calculate the output.

Select cell B4, enter the formula =DURATION(B2,B3,2%,3%,4), and press the “Enter” key.

The result is ‘4.76353017’, as shown above.

DURATION() Excel Formula

The syntax of the DURATION Excel formula is,

The arguments of the DURATION Excel formula are,

• settlement: It is a mandatory argument. It is the security’s settlement date, or the day the coupon is purchased.
• maturity: It is a mandatory argument. It is the security’s maturity date or the date on which the coupon expires.
• coupon: It is a mandatory argument. It is the security’s coupon rate.
• yld: It is a mandatory argument. The security’s annual yield.
• frequency: It is a mandatory argument. It is the number of coupon payments per year.

1	Annually
2	Semi-Annually
4	Quarterly

• basis: It is an optional argument. It is the type of day count basis to be used. 

Basis	Day Count
0 (or omitted)	US (NASD) 30/360
1	Actual/actual
2	actual / 360
3	actual / 365
4	European 30/360

How To Use DURATION Excel Function?

We can use the DURATION function in 2 ways, namely,

1. Access from the Excel ribbon.
2. Enter in the worksheet manually.

Method #1 – Access from the Excel ribbon

Choose an empty cell for the output 🡪 select the “Formulas” tab 🡪 go to the “Function Library” group 🡪 click the “Financial” option drop-down 🡪 select the “DURATION” function, as shown below.

The “Function Arguments” window appears. Enter the arguments in the “Settlement”, “Maturity”, “Coupon”, “Yld”, “Frequency”, and “basis” fields 🡪 click “OK”, as shown below.

Method #2 – Enter in the worksheet manually

1. Select an empty cell for the output.
2. Type =DURATION( in the selected cell. [Alternatively, type =D or =DU and double-click the DURATION function from the list of suggestions shown by Excel.]
3. Enter the arguments as cell values or cell references and close the brackets.
4. Press the “Enter” key.

Let us understand this function with a basic example.

The succeeding example depicts the values of the investment made by Mr. Ron. We will calculate the output using DURATION Excel function.

In the table, the data is,

• Column A contains the Description.
• Column B contains the Value.

Examples

We will understand some advanced scenarios using the DURATION function examples.

Example #1

The succeeding example depicts the values of the investment made by Mrs. Atlenta. We will calculate the output using the DURATION Excel function.

In the table, the data is,

• Column A contains the Description.
• Column B contains the Value.

The procedure to calculate the value using the DURATION function is,

Select cell B4, enter the formula =DURATION(B2,B3,6%,1%,2), and press the “Enter” key.

[Note: The value of ‘settlement’ is B2, ‘maturity’ is B3, ‘coupon’ is 6%, ‘yld’ is 1%, and ‘frequency’ is 2.]

The result is “0.919113647”, as shown above.

Example #2

The succeeding example depicts the investment values. We will calculate the “#NUM!” error using the DURATION Excel function.

In the table, the data is,

• Column A contains the Description.
• Column B contains the Value.

The procedure to calculate the value using the DURATION function is,

Select cell B4, enter the formula =DURATION(B2,B3,9%,3%,1), and press the “Enter” key.

[Note: The value of ‘settlement’ is B2, ‘maturity’ is B3, ‘coupon’ is 9%, ‘yld’ is 3%, and ‘frequency’ is 1.]

The result is a “#NUM!” error, as shown above.

Example #3

The succeeding example depicts the investment values. We will calculate the “#VALUE!” error using the DURATION Excel function.

In the table, the data is,

• Column A contains the Description.
• Column B contains the Value.

The procedure to calculate the value using the DURATION function is,

Select cell B4, enter the formula =DURATION(B2,B3,2%,3%,4), and press the “Enter” key.

[Note: The value of ‘settlement’ is B2, ‘maturity’ is B3, ‘coupon’ is 2%, ‘yld’ is 3%, and ‘frequency’ is 4.]

The result is a “#VALUE!” error, as shown above.

Important Things To Note

• The “#NUM!” error occurs when,
  • The settlement date is the same as the maturity date.
  • The cell values of the arguments are invalid.
• The “#VALUE!” error occurs when,
  • If the argument is non-numeric.
  • The dates provided are not valid Excel dates.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the DURATION Excel function’s formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to DURATION Excel Function. Here we use the function to calculate annual duration of a Bond, examples & downloadable excel template. You can learn more from the following articles –

• PRICE Function In Excel
• XIRR In Excel
• Excel YIELD Function