DURATION Function Examples – Excel, VBA, & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to use the Excel DURATION Function in Excel to calculate the Macaulay duration of a security.
DURATION Function Overview
The DURATION Function Calculates the security’s duration.
To use the DURATION Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
DURATION Function Syntax and Inputs:
=DURATION(settlement,maturity,coupon,yld,frequency,[basis])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.
What Is DURATION?
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:
Where:
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
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(C4,C5,C6,C7,C8,C9)
The Excel Duration function returns the value of
DURATION = 7.56 years
Calculate Annual Duration of a fixed-income security
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(C4,C5,C6,C7,C8,C9)
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 Examples in VBA
You can also use the DURATION function in VBA. Type:
application.worksheetfunction.duration(settlement,maturity,coupon,yld,frequency,[basis])For the function arguments (settlement, etc.), you can either enter them directly into the function or define variables to use instead.
