DURATION Function Examples – Excel, VBA, & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 7, 2023

This tutorial demonstrates how to use the Excel DURATION Function in Excel to calculate the Macaulay duration of a security.

Duration Excel Function

DURATION Function Overview

The DURATION Function Calculates the security’s duration.

To use the DURATION Excel Worksheet Function, select a cell and type:
DURATION Function Syntax



(Notice how the formula inputs appear)

DURATION Function Syntax and Inputs:


settlement – It’s the settlement date of the security or the date at which the security is purchased. It’s the date that comes after the issuing date of the security.

maturity – It’s the date at which the bond or security expires, and the principal amount is paid back to bond or security holder.

coupon – The annual coupon rate of the security.

yld –  It’s the annual yield of bond or security.

frequency –  It refers to the number of periodic coupon payments per year. The value of frequency for annual, semi-annual, and quarterly payments are 1, 2, and 4, respectively.

basis – OPTIONAL: It specifies the type of day counting to be used by the security or bond. Possible values can be:

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

If the basis argument is omitted, it assumes its default value i.e. US (NASD) 30/360.


It’s a measure of the sensitivity of a bond’s price to a change in its yield. The duration of a bond can refer to two different things i.e., Macaulay Duration and Modified Duration. Here, it means Macaulay Duration. The Macaulay Duration is the weighted average time until the repayment.

The Macaulay Duration is calculated using the following equation:

Duraction function formula


ti = time until the ith payment is received

PVi = the present value of the ith payment from the asset

V = the present value of all the future cash payments from the asset

What is the Excel DURATION Function?

The Excel DURATION function calculates the Macaulay Duration of a bond or security that pays interest periodically and assuming a par value of $100.

Calculate Annual Duration of a bond

duration function ex 1 data

In this example, we want to calculate the bond’s duration with an annual coupon rate of 7%. The settlement date of the bond is 3/23/2010 with a 4% yield. Other details of the bond are in the above figure:

The Formula to use is:


DURATION function example 1

The Excel Duration function returns the value of

DURATION = 7.56 years

Calculate Annual Duration of a fixed-income security

duration function ex 2 data

Let’s take a look at another example, here we are going to find out the duration of fixed-income security until it is paid back. The fixed-income security is purchased on July 1st, 2019, with a maturity date of June 30th, 2024, and an annual coupon rate of 10%. The coupon payments are made quarterly and the yield is 7.3%

The formula to calculate the duration of the fixed-income security is:


DURATION function example 2

The Excel Duration function returns the value of

DURATION = 4.05 years

It means it would take a little more than 4 years before the fixed-income security is paid back.

Additional Notes

#NUM! Error occurs if the settlement date is greater or equal than the maturity date; or the values of the rate, yld, redemption, frequency, or [basis] arguments are not valid numbers (i.e. rate < 0; or yld < 0; or redemption ≤ 0; or frequency is any value other than 1, 2 or 4; or [basis] value is other than 0, 1, 2, 3, or 4)

#VALUE! Error occurs if the dates of the settlement or the maturity arguments are not valid Excel dates.

It is recommended that the settlement and maturity dates should be entered in the DURATION function as references to cells containing the dates or dates returned from formulas.

Return to the List of all Functions in Excel


DURATION in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

DURATION Function Google Sheets

DURATION Examples in VBA

You can also use the DURATION function in VBA. Type:


For the function arguments (settlement, etc.), you can either enter them directly into the function or define variables to use instead.

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions