1. Calculation functions
  2. All Functions
  3. Financial Functions
  4. MDURATION

Use the MDURATION function to calculate the modified Macauley duration for an assumed parity value of 100 monetary units.

The modified Macauley duration expresses the measurable change in the value of a bond in response to a change in interest rates.

The result represents the effect that a 1% change in interest rates will have on the price of a bond.

The modified Macauley duration is calculated with the following formula:

Image contains maths calculation – Calculation is: MDuration = fraction start, Duration over (, 1 + fraction start, y over f, end of fraction,,), end of fraction, with 11 items.

Where:

  • y is yield, and
  • f is frequency.

Syntax

MDURATION(settlement, maturity, rate, yield, frequency[, basis])

The MDURATION function has the following arguments:

Argument Data type Description
settlement (required) Date The bond settlement date — the date the bond is traded to the buyer.
maturity (required) Date The bond maturity date — the date when the bond expires.
rate (required) Number The bond annual coupon date.
yield (required) Number The bond annual yield.
frequency (required) Number

The number of coupon payments per year.

Enter:

  • 1 for annual,
  • 2 for semi-annual, or
  • 4 for quarterly.
basis (optional) Number

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US 30/360, Actual/360, and EUR 30/360 are used;
  • 365 days when basis Actual/365 is used; and
  • 365 or 366 days when Actual/Actual is used.

US 30/360 is the default basis for DURATION. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual,
  • 2 for  Actual/360,
  • 3 for  Actual/365, or
  • 4 for European 30/360.

Learn about the conventions used to calculate the day count for basis.

 

Returns
Number

Constraints

The MDURATION function has the following constraints:

  • the settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399;
  • the maturity date must be later than the settlement date;
  • the rate and yield must be positive or zero;
  • the frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly); and
  • the basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Examples

The following tables show some example formulas using the MDURATION function.

You can reference line items or list properties in your formula.

Formula Description Result
MDURATION(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.1, 1, 4)

This example shows a modified Macauley duration calculation that specifies a basis.

The basis is given as 4 (European 30/360).

The example has:

  • a settlement date of 01/15/2015,
  • a maturity date of 01/15/2018,
  • a rate of 0.12 (12%),
  • a yield of 0.1 (10%),
  • and a frequency of 1 (annual).
 2.4524373
DURATION(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.1, 4)

In this example, the Macauley duration is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

Here:

  • the settlement date is 01/15/2015,
  • the maturity date is 01/15/2018,
  • the rate is 0.12 (12%),
  • the yield is 0.1 (10%),
  • and the frequency is 4 (quarterly).
 2.5131791

Excel equivalent