Modified duration

Returns the modified Macauley duration, which is the weighted average of present payments, for a security with an assumed par value of $100. This function is used as a measure of bond-price response to variations in yield.

Syntax

Mduration <Basis> (Settlement, Maturity, CouponRate, YieldRate, Frequency)

Where:

Settlement is the settlement date. This is the date, after issue, on which the security is traded.

Maturity is the date of maturity. This is the date on which the security expires.

CouponRate is the annual interest rate of the coupon.

YieldRate is the annual yield.

Frequency is the number of coupon payments per year. The valid values are 1, 2, and 4 where annual payments =1, semiannual payments =2, and quarterly payments =4.

Basis is a parameter that indicates the time-count basis used. The default value for Basis is 0, which is typically used by American agencies and assumes 30-day months and 360-day years (30/360). Possible values for this parameter are listed in the following table.

Basis value	Application
0 (30/360)	Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)	Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)	Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)	Assumes actual number of days in each month, 365 days in each year.
4 (30/60)	Used by European agencies, assumes the same values as “0” for American institutions.

Expression

Where:

yld is the security annual yield

frequency is the number of coupon payments per year

Usage Notes

If Settlement, Maturity, or Frequency is not an integer, it is truncated.

The engine returns an empty cell if:

• Either Settlement or Maturity is not a valid date
• CouponRate < 0
• YieldRate < 0
• Frequency is a number other than 1, 2, or 4
• Settlement ³ Maturity

The Settlement date and the Maturity date should be included within single quotations in the expression for the expression to be considered as a valid expression.