BONDDURATION
The BONDDURATION function returns the weighted average of the present value of the cash flows for an assumed par value of $100.
BONDDURATION(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 percent 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 percent sign (for example, 8%).
frequency: A modal value specifying the number of coupon or periodic interest payments each year.
annual (1): One payment per year.
semiannual (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 Macaulay duration. It is a measure of the weighted average maturity of cash flows, often referred to as the duration of a bond. 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).
Example |
|---|
Suppose you are considering the purchase of a hypothetical security. The purchase will settle April 2, 2010 (settle) and will mature on December 31, 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). =BONDDURATION("4/2/2010", "12/31/2015", 0.05, 0.05284, 4,1 ) returns approximately 5.02084875998691, the weighted average maturity of cash flows (the bond duration) in years. The cash flows consist of interest received during the life of the security and principal received at maturity. The maturity is approximately 5.75 years in the future and the principal received at maturity is much larger than the interest cash flows, so the duration is only approximately 9 months shorter than time to maturity. |