BONDMDURATION
The BONDMDURATION function returns the modified weighted average of the present value of the cash flows for an assumed par value of $100.
BONDMDURATION(settle, maturity, annual-rate, annual-yield, frequency, days-basis)
settle: A date/time value or date string representing the trade settlement date, usually one or more days after the trade date.
maturity: A date/time value or date string representing the date when the security matures. maturity must be after the date specified for settle.
annual-rate: A number value representing the annual coupon rate or stated annual interest rate of the security used to determine periodic interest payments. annual-rate must be greater than 0, and is entered as a decimal (for example, 0.08) or with a per cent sign (for example, 8%).
annual-yield: A number value representing the annual yield of the security. annual-yield must be greater than 0, and is entered as a decimal (for example, 0.08) or with a per cent sign (for example, 8%).
frequency: A modal value specifying the number of coupon payments each year.
annual (1): One payment per year.
semi-annual (2): Two payments per year.
quarterly (4): Four payments per year.
days-basis: An optional modal value specifying the number of days per month and days per year (days-basis convention) used in the calculations.
30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD method for dates falling on the 31st of a month.
actual/actual (1): Actual days in each month, actual days in each year.
actual/360 (2): Actual days in each month, 360 days in a year.
actual/365 (3): Actual days in each month, 365 days in a year.
30E/360 (4): 30 days in a month, 360 days in a year, using the European method for dates falling on the 31st of a month.
Notes
This function returns a value known as the modified Macaulay duration. In contrast to Macaulay duration (BONDDURATION), modified duration is defined as the percentage change in price with respect to a 1 per cent change in yield, and is considered a measure of price sensitivity. It uses the bond’s yield to maturity to calculate the discount factors.
The currency shown in this function result depends on your Language & Region settings (in System Preferences in macOS 12 and earlier, System Settings in macOS 13 and later, and Settings in iOS and iPadOS), or on your Time Zone & Region settings in iCloud Settings.
Example |
|---|
Suppose you are considering the purchase of a hypothetical security. The purchase will settle on 2 April 2010 (settle) and will mature on 31 December 2015 (maturity). The coupon rate is 5% (annual-rate). The stated yield is 5.284% (annual-yield). The bond pays interest quarterly (frequency), based on actual calendar days (days-basis). =BONDMDURATION("4/2/2010", "12/31/2015", 0.05,0.05284,4,1) returns approximately 4.95538808340513, the approximate percentage change in the securities price based on a 1 percent change in its annual yield. |