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