A to Z of Excel Functions: The DURATION Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the DURATION function.

The DURATION function

The Macaulay Duration is a concept developed and named after Frederick Macaulay.  It is measure of weighted average maturity of cash flows.  Each cash flow weight can be found by dividing the present value of the cash flow by the price.  The concept is commonly used by portfolio managers.  Its formula is as follows:

where:

• t = respective time period
• C = periodic coupon payment
• y = periodic yield
• n = total number of periods
• M = maturity value
• Current Bond Price = present value of cash flows (time value of money).

This function returns the Macauley duration for an assumed par value of $100.  The duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield.

The DURATION function employs the following syntax to operate:

The DURATION function has the following arguments:

• settlement: this is required and represents the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer
• maturity: this is also required. This is the security's maturity date. The maturity date is the date when the security expires
• coupon: this is required. This is the security's annual coupon rate
• yield: again, this is required. This is the security's annual yield
• frequency: this is required and represents the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4
• basis: this is optional. The type of day count basis to use, viz.

It should be further noted that:

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations.  By default, January 1, 1900 is serial number 1, and January 1, 2020 is serial number 43831 because it is 43,830 days after January 1, 1900
• The settlement date is the date a buyer purchases a coupon, such as a bond.  The maturity date is the date when a coupon expires.  For example, suppose a 30-year bond is issued on January 1, 2008, and is purchased by a buyer six months later.  The issue date would be January 1, 2008, the settlement date would be July 1, 2008 and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date
• settlement, maturity, frequency, and basis are truncated to integers
• if settlement or maturity is not a valid date, DURATION returns the #VALUE! error value
• if coupon < 0 or if yield < 0, DURATION returns the #NUM! error value
• if frequency is any number other than 1, 2 or 4, DURATION returns the #NUM! error value
• if basis < 0 or if basis > 4, DURATION returns the #NUM! error value
• if settlement ≥ maturity, DURATION returns the #NUM! error value.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.