Related Function:

The MDURATION function returns the modified Macauley duration for a security with an assumed par value of $100.

Note: 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.

Syntax

=MDURATION(settlement,maturity,coupon,yld,frequency,[basis])

Arguments

Argument Description
settlement The security’s settlement date – the date after the issue date when the security is traded to the buyer
maturity The security’s maturity date – the date when the security expires
coupon The security’s annual coupon rate
yld The security’s annual yield
frequency The number of coupon payments per year. Must be one of the following:

  1 Annually
  2 Semi-Annually
  3 Quarterly
[basis] Optional. Defines the day count basis to be used in the calculation

  [basis] Day Count Basis
  0 (or omitted) US (NASD) 30/360
  1 actual/actual
  2 actual/360
  3 actual/365
  4 European 30/360

The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note: The settlement and maturity dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Data Description    
2 01-Jan-2008 Settlement date    
3 01-Jan-2016 Maturity date    
4 8% Percent coupon    
5 9% Percent yield    
6 2 Semiannual frequency    
7 1 Actual/actual basis    
8        
9 Formula Result Notes
10 =MDURATION(A2,A3,A4,A5,A6,A7) 5.736 The modified duration, for the bond with the terms specified above

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the supplied arguments are non-numeric
  the settlement date or the maturity date is not a valid date
#NUM! Occurs if either:

  the supplied settlement date is ≥ maturity date
  invalid numbers are supplied for the coupon, yld, frequency or [basis] arguments
#NAME? Occurs when Analysis ToolPak add-in is not enabled

Modified duration is a measure of the price sensitivity of a bond to interest rate movements. It is calculated as shown below:

    \[ MDURATION= \frac{DURATION}{1+ \left( \frac{annual \ yield}{frequency} \right) } \]