How to Use the DURATION Function in Excel
Introduction
This guide provides a detailed examination of the DURATION function available in Microsoft Excel and Google Sheets. The DURATION function is designed to calculate the annual duration of a security which pays periodic interest.
Syntax
The syntax for the DURATION function is consistent across both Excel and Google Sheets:
DURATION(settlement, maturity, rate, yld, frequency, [basis])
Parameters
settlement
: The settlement date of the security.maturity
: The maturity date of the security.rate
: The annual coupon rate of the security.yld
: The annual yield of the security.frequency
: The number of coupon payments per year (1 = annually, 2 = semi-annually, etc.).basis
: (Optional) Specifies the day count basis to use for calculation (0, or omitted, for US (NASD) 30/360; 1 for Actual/Actual).
Examples
Let’s explore some examples to clarify how to apply the DURATION function:
Example 1
Calculate the duration of a security with a settlement date of 01-Jan-2021, a maturity date of 01-Jan-2031, a coupon rate of 8%, a yield of 7.5%, and interest paid semi-annually.
Parameter | Value |
---|---|
Settlement Date | 01-Jan-2021 |
Maturity Date | 01-Jan-2031 |
Coupon Rate | 8% |
Yield | 7.5% |
Frequency | 2 (semi-annually) |
Example 2
Let’s compute the duration using the DURATION function in Excel:
=DURATION("01-Jan-2021", "01-Jan-2031", 0.08, 0.075, 2)
This formula returns the duration of the security.
Example 3
We can also calculate the duration using the DURATION function in Google Sheets:
=DURATION(DATE(2021,1,1), DATE(2031,1,1), 0.08, 0.075, 2)
This formula will also provide the duration of the security.
Conclusion
The DURATION function in Excel and Google Sheets serves as an essential tool for financial analysts to assess the risk related to fixed-income securities. It calculates the annual duration of a security given essential parameters like settlement date, maturity date, coupon rate, yield, and frequency of payments.
More information: https://support.microsoft.com/en-us/office/duration-function-b254ea57-eadc-4602-a86a-c8e369334038