Formulas and Functions

Chapter 6 Financial Functions 103
BONDDURATION
The BONDDURATION function returns the weighted average of the present value of
the cash ows for an assumed par value of $100.
BONDDURATION(settle, maturity, annual-rate, annual-yield, frequency, days-basis)
 settle: The trade settlement date. settle is a date/time value. The trade settlement
date is usually one or more days after the trade date.
 maturity: The date when the security matures. maturity is a date/time value. It must
be after settle.
 annual-rate: The annual coupon rate or stated annual interest rate of the security.
annual-rate is a number value and is either entered as a decimal (for example, 0.08)
or with a percent sign (for example, 8%).
 annual-yield: The annual yield of the security. annual-yield is a number value and is
either entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
 frequency: The number of coupon 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 argument specifying the number of days per month and
days per year 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 (European 30/360).
Usage Notes
This function returns a value known as the Macauley duration. Â
Example
Assume you are considering the purchase of a hypothetical security. The purchase will settle April 2,
2010 and the maturity will mature on December 31, 2015. The coupon rate is 5%, resulting in a yield
of approximately 5.284% (the yield was calculated using the YIELD function). The bond pays interest
quarterly, based on actual days.
=BONDDURATION(“4/2/2010”, “12/31/2015”, 0.05, 0.05284, 4, 1) returns approximately 5.0208, the
present value of the future cash ows (the bond duration), based on the Macauley duration. The cash
ows consist of the price paid, interest received, and principal received at maturity.