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.
#If you have any other info about this subject , Please add it free.# |
