Welcome to your ultimate accounting resource


Using the Bond Duration Add-in Functions

Excel provides two functions that let youthe  settlement  date.
make bond duration calculations: DURATION and
MDURATION. Duration, a weighted average2. If you use a frequency argument other than
measure of the present value of a bond's cash1,  2,  or  4,  Excel  returns  #NUM.
flows, quantifies how a change in the bond
yield  affects  the  bond  price.3. If you use a day-count-basis switch other
than  0,  1,  2, 3, or 4, Excel returns #NUM.
Understanding the Bond Duration Function
Arguments4. If the settlement day follows the maturity
date,  Excel  returns  #NUM.
Both duration functions use the same set of
six arguments: the settlement date, the5. If the rate or yield is less than zero,
maturitydate, the coupon rate, the yield, theExcel  returns  #NUM.
coupon  frequency,  and  the day count basis.
Using  the  DURATION  Function
The settlement date specifies the date the
bond is settled, or purchased. The maturityThe DURATION function calculates a Macauley
datespecifies the date the bond matures, orduration given the settlement date, maturity
expires. As with the other add-in financialdate, coupon rate, yield, frequency, and
functions,you may enter the date argumentsbasis.  It  uses  the  following  syntax:
either as text strings enclosed in quotation
marks  or  asserial  date  values.DURATION (settlement, maturity, coupon,
yield,  frequency,  basis)
The coupon rate argument is the bond's
interest rate and is used to calculate couponFor example, suppose you want to calculate
payments.the duration of a bond you purchased on April
The yield argument is the bond's annual23, 2000, and that will mature on November
yield.30, 2020. Further suppose that the coupon
rateis 8%, which is paid in four quarterly
NOTE: Both duration functions assume that thepayments, but that the bond yield is 7%. If
bond's  face,  or  par,  value  equals  $100.you wantto use the US (NASD) day count basis
of 30 days in a month and 360 days in a year,
The frequency argument gives the number ofyouwould use the following formula to
coupon payments made each year: you specify 1calculate  this  bond's  yield:
to indicate an annual coupon, 2 to indicate a
semiannual coupon, and 4 to indicate a=DURATION ("4/23/2000","11/30
quarterly  coupon.2020",.08,.07,4,0)
The basis argument specifies the number ofThe  formula  returns  the  value  10.6496.
days in the month and year assumed for the
date calculations. You specify the basis as 0Using  the  MDURATION  Function
for the US (or NASD) version of 30 days in a
month and 360 days in a year; as 1 for theThe MDURATION function calculates a modified
actual number of days in the month and actualduration given the settlement date, maturity
number of days; 2 for the actual number ofdate, coupon rate, yield, frequency, and
days in the month but 360 days in a year; 3basis.  It  uses  the  following  syntax:
for the actual number of days in the month
and 365 days in a year; and 4 for theMDURATION (settlement, maturity, coupon,
European version of 30 days in a month andyield,  frequency,  basis)
360  days  in  a  year.
For example, suppose you want to calculate
NOTE: Excel uses only the integer portion ofthe duration of a bond you purchased on April
the arguments you supply to the add-in price
and yield date functions. If you enter an23, 2000, and that will mature on November
argument with decimal values, Excel truncates30, 2020. Further suppose that the coupon
the  argument  to just its integer component.rateis 8%, which is paid in four quarterly
payments, but that the bond yield is 7%. If
Preventing Common Bond Duration Functionyou wantto use the US (NASD) day count basis
Errorsof 30 days in a month and 360 days in a year,
youwould use the following formula to
The duration functions return an error valuecalculate  this  bond's  yield:
in  several  predictable  cases:
=MDURATION ("4/23/2000","11/30
1. If you use an invalid date, Excel returns2020",.08,.07,4,0)
#VALUE. Note that this means your date
argumentsmust make sense collectively, too.The formula returns the value 10.4664.
For example, your maturity date must follow



1 A B C D E 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 130 131 132 133