# A to Z of Excel Functions: the DURATION Function

12 November 2018

*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:

**DURATION(settlement, maturity, coupon, yield, frequency, [basis])**

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. *