Excel MDURATION function
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
(1) Settlement: Required. It’s the security’s settlement date that the buyer purchased the security. It’s later than the issue date.
(2) Maturity: Required. It’s the security’ maturity date when the security expires.
(3) Coupon: Required. It’s the annual coupon rate of the security.
(4) Yld: Required. It’s the security’s annual yield.
(5) Frequency: Required. It’s the number of frequencies you can get coupon payments in one year.
- For annual coupon payments, the number of frequencies is 1;
- For semi-annual coupon payments, the number of frequencies is 2;
- For quarterly coupon payments, the number of frequencies is 4;
(5) Basis: Optional. It’s the type of day count basis to use. There are 5 types:
|0 or omitted||US (NASD) 30/360|
The MDURATION function returns the modified Macauley duration for a security with assuming per value of $100.
(1) The settlement, maturity, and basis are truncated to integers. For example, you type 1.4 as the basis value, the MDURATION function will take it as 1 automatically.
(2) The MDURATION function returns #VALUE! error value, if the settlement or maturity is not a valid date.
(3) The MDURATION function returns #NUM! error value, if the settlement date is no less than the maturity date.
(4) The MDURATION function returns #NUM! error value, if the yld or coupon is less than 0.
(5) The frequency can only be one of these numbers: 1, 2, or 4. Otherwise, the MDURATION function returns #NUM! error value.
(6) The MDURATION function returns #NUM! error value, if the basis is less than 0 or greater than 4.
Supposing there is a bond with an annual coupon rate of 8% and semi-annual coupon payments. The yield to maturity is 10%, the settlement date is 7/1/2017, the maturity date is 12/31/2018. This bond uses the day count basis of Actual/360.
In this case, you can figure out the Macaulay modified duration with one of below formulas: