Using the Bond Duration Add-in Functions

Excel provides two functions that let you make#VALUE. Note that this means your date
bond duration calculations: DURATION andargumentsmust make sense collectively, too. For
MDURATION. Duration, a weighted averageexample, your maturity date must follow the
measure of the present value of a bond's cashsettlement date.
flows, quantifies how a change in the bond yield2. If you use a frequency argument other than 1,
affects the bond price.2, or 4, Excel returns #NUM.
Understanding the Bond Duration Function3. If you use a day-count-basis switch other than
Arguments0, 1, 2, 3, or 4, Excel returns #NUM.
Both duration functions use the same set of six4. If the settlement day follows the maturity
arguments: the settlement date, the maturitydate,date, Excel returns #NUM.
the coupon rate, the yield, the coupon frequency,5. If the rate or yield is less than zero, Excel
and the day count basis.returns #NUM.
The settlement date specifies the date the bondUsing the DURATION Function
is settled, or purchased. The maturityThe DURATION function calculates a Macauley
datespecifies the date the bond matures, orduration given the settlement date, maturity date,
expires. As with the other add-in financialcoupon rate, yield, frequency, and basis. It uses
functions,you may enter the date argumentsthe following syntax:
either as text strings enclosed in quotation marksDURATION (settlement, maturity, coupon, yield,
or asserial date values.frequency, basis)
The coupon rate argument is the bond's interestFor example, suppose you want to calculate the
rate and is used to calculate coupon payments.duration of a bond you purchased on April
The yield argument is the bond's annual yield.23, 2000, and that will mature on November 30,
NOTE: Both duration functions assume that the2020. Further suppose that the coupon rateis 8%,
bond's face, or par, value equals $100.which is paid in four quarterly payments, but that
The frequency argument gives the number ofthe bond yield is 7%. If you wantto use the US
coupon payments made each year: you specify 1(NASD) day count basis of 30 days in a month
to indicate an annual coupon, 2 to indicate aand 360 days in a year, youwould use the
semiannual coupon, and 4 to indicate a quarterlyfollowing formula to calculate this bond's yield:
coupon.=DURATION ("4/23/2000","11/30
The basis argument specifies the number of days2020",.08,.07,4,0)
in the month and year assumed for the dateThe formula returns the value 10.6496.
calculations. You specify the basis as 0 for the USUsing the MDURATION Function
(or NASD) version of 30 days in a month and 360The MDURATION function calculates a modified
days in a year; as 1 for the actual number ofduration given the settlement date, maturity date,
days in the month and actual number of days; 2coupon rate, yield, frequency, and basis. It uses
for the actual number of days in the month butthe following syntax:
360 days in a year; 3 for the actual number ofMDURATION (settlement, maturity, coupon, yield,
days in the month and 365 days in a year; and 4frequency, basis)
for the European version of 30 days in a monthFor example, suppose you want to calculate the
and 360 days in a year.duration of a bond you purchased on April
NOTE: Excel uses only the integer portion of the23, 2000, and that will mature on November 30,
arguments you supply to the add-in price and yield2020. Further suppose that the coupon rateis 8%,
date functions. If you enter an argument withwhich is paid in four quarterly payments, but that
decimal values, Excel truncates the argument tothe bond yield is 7%. If you wantto use the US
just its integer component.(NASD) day count basis of 30 days in a month
Preventing Common Bond Duration Functionand 360 days in a year, youwould use the
Errorsfollowing formula to calculate this bond's yield:
The duration functions return an error value in=MDURATION ("4/23/2000","11/30
several predictable cases:2020",.08,.07,4,0)
1. If you use an invalid date, Excel returnsThe formula returns the value 10.4664.