Buzz
The DURATION function in Excel returns the annual tenure of a security with regular interest payments. Widely applied in the securities sector, it provides broad and precise applications.
DURATION Function and Examples
Guide on using the DURATION function in Excel
Syntax: DURATION(settlement, maturity, coupon, yld, frequency, [basis])
Where:
- Maturity: Maturity date of the security (expiration date), a required parameter
- Coupon: Annual interest rate of the security, a required parameter
- Yld: Annual yield of the security, a required parameter
- Frequency: Number of coupon payments per year
+ Frequency = 1: Payment annually
+ Frequency = 2: Payment semi-annually
+ Frequency = 4: Payment quarterly
- Basis: Day count basis
+ Basis = 0 or omitted: US (NASD) 30/360
+ Basis = 1: Actual/Actual
+ Basis = 2: Actual/360
+ Basis = 3: Actual/365
+ Basis = 4: European 30/360
Considering an Example:
For the spreadsheet below, data is entered corresponding to the parameters of the DURATION function in Excel. In this example, Mytour uses this function on Office 2007, and you can perform the same on Office 2013 and Office 2010. Note that for Office 2003, the interface is slightly different, so pay attention.
To calculate the tenure of bonds with various terms, enter the formula in cell C12= DURATION(C6,C7,C8,C9,C10,C11). The result is shown below.
Through this example, you have gained insights into the usage of the DURATION function in Excel. Apply this knowledge to your specific calculations on your spreadsheet.
