Syntax. DURATION(Settlement,Maturity,Nominal_Interest,Yield,Frequency,Basis)
Definition. This function calculates the (average) capital commitment duration of a fixed-interest security. It is called Macauley Duration, after its developer.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The date when the repayment of the loan certified by the security takes place.
Coupon (required) The agreed-upon yearly interest rate as the price for the loaned money.
Yield (required) Represents the market interest rate on the settlement day, at which during calculation of the duration all future payments are discounted.
Frequency (required) Because it is quite common to have fixed-interest securities with several interest dates per year, this argument gives you their number. Allowed values are 1 = annual; 2 = biannual; 4 = quarterly.
Basis (optional) Defines the method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
All function arguments that are dates use the date without the time; that is, fractions are rounded. The Frequency and Basis arguments also require integers, and decimals are truncated.
If invalid dates are used or no numbers are entered where required, the function returns the #VALUE! error. If invalid numbers are entered for nondate arguments, the function returns the #NUMBER! error.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
The reference to the par value is irrelevant because, due to the creation of the average value, a relative quantity regarding the invested capital is generated.
Background. The duration according to Macauley is a weighted arithmetical average value. The cash values of the guaranteed future earnings (interest and repayment) are calculated from the current market interest for the entire duration until maturity by discounting, and are weighted with the time difference until their payment. With slightly more complicated mathematics, it is possible to prove that the thus-calculated average capital commitment cycle also determines the date at which a (theoretical) immunization against interest changes takes place. The future value of all payments (calculated with the current market interest) at the date specified by the duration is the average value that is actually reached at that moment, independent of the changing market interest.
The result is then used to compare bonds (fixed-interest securities) with similar conditions (annuity and duration) to each other. A bond with a smaller capital commitment cycle is preferable, because the risk is lower.
Of course, the ratio is no guarantor for the future. Market prices and thus yields are caused by supply and demand, which are caused by many different factors. Therefore, an evaluation done today needs to be corrected in the near future. Again, the duration can be a possible criterion.
Example. On August 30, 2010, the following information for two federal securities was published:
Security | Coupon | Maturity | Price | Yield |
|---|---|---|---|---|
Federal loan of 2005 | 3.5% | July 4, 2015 | 109.040 | 1.31% |
Federal medium-term bond series 157 | 2.25% | April 10, 2015 | 104.500 | 1.24% |
A calculation of the duration returns the following result:
Security | Duration |
|---|---|
Federal loan of 2005 | 4.54 years |
Federal medium-term bond series 157 | 4.40 years |
The federal medium-term bond is preferable. However, the difference regarding the duration is very small. There is also another risk advantage for other debtors, and other terms as well as in regard to tax-related aspects (coupon must be reduced depending on the rate of taxation).