Using the Bond Duration Add-in Functions

Excel provides two functions that let you make bond duration calculations: DURATION and MDURATION. Duration, a weighted average measure of the present value of a bond’s cash flows, quantifies how a change in the bond yield affects the bond price.

Understanding the Bond Duration Function Arguments

Both duration functions use the same set of six arguments: the settlement date, the maturity date, the coupon rate, the yield, the coupon frequency, and the day count basis. The settlement date specifies the date the bond is settled, or purchased. The maturity date specifies the date the bond matures, or expires. As with the other add-in financial functions, you may enter the date arguments either as text strings enclosed in quotation marks or as serial date values.

The coupon rate argument is the bond’s interest rate and is used to calculate coupon payments. The yield argument is the bond’s annual yield.

NOTE: Both duration functions assume that the bond’s face, or par, value equals $100. The frequency argument gives the number of coupon payments made each year: you specify 1 to indicate an annual coupon, 2 to indicate a semiannual coupon, and 4 to indicate a quarterly coupon.

The basis argument specifies the number of days in the month and year assumed for the date calculations. You specify the basis as 0 for the US (or NASD) version of 30 days in a month and 360 days in a year; as 1 for the actual number of days in the month and actual number of days; 2 for the actual number of days in the month but 360 days in a year; 3 for the actual number of days in the month and 365 days in a year; and 4 for the European version of 30 days in a month and 360 days in a year.

NOTE: Excel uses only the integer portion of the arguments you supply to the add-in price and yield date functions. If you enter an argument with decimal values, Excel truncates the argument to just its integer component.

Preventing Common Bond Duration Function Errors

The duration functions return an error value in several predictable cases:

1. If you use an invalid date, Excel returns #VALUE. Note that this means your date arguments must make sense collectively, too. For example, your maturity date must follow the settlement date.

2. If you use a frequency argument other than 1, 2, or 4, Excel returns #NUM.

3. If you use a day-count-basis switch other than 0, 1, 2, 3, or 4, Excel returns #NUM.

4. If the settlement day follows the maturity date, Excel returns #NUM.

5. If the rate or yield is less than zero, Excel returns #NUM.

Using the DURATION Function

The DURATION function calculates a Macauley duration given the settlement date, maturity date, coupon rate, yield, frequency, and basis. It uses the following syntax:

DURATION (settlement, maturity, coupon, yield, frequency, basis)

For example, suppose you want to calculate the duration of a bond you purchased on April 23, 2000, and that will mature on November 30, 2020. Further suppose that the coupon rate is 8%, which is paid in four quarterly payments, but that the bond yield is 7%. If you want to use the US (NASD) day count basis of 30 days in a month and 360 days in a year, you would use the following formula to calculate this bond’s yield:

=DURATION ("4/23/2000","11/30/2020",.08,.07,4,0)

The formula returns the value 10.6496.

Using the MDURATION Function

The MDURATION function calculates a modified duration given the settlement date, maturity date, coupon rate, yield, frequency, and basis. It uses the following syntax:

MDURATION (settlement, maturity, coupon, yield, frequency, basis)

For example, suppose you want to calculate the duration of a bond you purchased on April 23, 2000, and that will mature on November 30, 2020. Further suppose that the coupon rate is 8%, which is paid in four quarterly payments, but that the bond yield is 7%. If you want to use the US (NASD) day count basis of 30 days in a month and 360 days in a year, you would use the following formula to calculate this bond’s yield:

=MDURATION ("4/23/2000","11/30/2020",.08,.07,4,0)

The formula returns the value 10.4664.