| Excel provides two functions that let you | | | | the settlement date. |
| make bond duration calculations: DURATION and | | | | |
| MDURATION. Duration, a weighted average | | | | 2. If you use a frequency argument other than |
| measure of the present value of a bond's cash | | | | 1, 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 | | | | |
| Arguments | | | | 4. If the settlement day follows the maturity |
| | | | date, Excel returns #NUM. |
| Both duration functions use the same set of | | | | |
| six arguments: the settlement date, the | | | | 5. If the rate or yield is less than zero, |
| maturitydate, the coupon rate, the yield, the | | | | Excel 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 maturity | | | | The DURATION function calculates a Macauley |
| datespecifies the date the bond matures, or | | | | duration given the settlement date, maturity |
| expires. As with the other add-in financial | | | | date, coupon rate, yield, frequency, and |
| functions,you may enter the date arguments | | | | basis. 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 coupon | | | | For example, suppose you want to calculate |
| payments. | | | | the duration of a bond you purchased on April |
| | | | |
| The yield argument is the bond's annual | | | | 23, 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 the | | | | payments, 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 of | | | | youwould use the following formula to |
| coupon payments made each year: you specify 1 | | | | calculate 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 of | | | | The formula returns the value 10.6496. |
| days in the month and year assumed for the | | | | |
| date calculations. You specify the basis as 0 | | | | Using the MDURATION Function |
| for the US (or NASD) version of 30 days in a | | | | |
| month and 360 days in a year; as 1 for the | | | | The MDURATION function calculates a modified |
| actual number of days in the month and actual | | | | duration given the settlement date, maturity |
| number of days; 2 for the actual number of | | | | date, coupon rate, yield, frequency, and |
| days in the month but 360 days in a year; 3 | | | | basis. It uses the following syntax: |
| for the actual number of days in the month | | | | |
| and 365 days in a year; and 4 for the | | | | MDURATION (settlement, maturity, coupon, |
| European version of 30 days in a month and | | | | yield, frequency, basis) |
| 360 days in a year. | | | | |
| | | | For example, suppose you want to calculate |
| NOTE: Excel uses only the integer portion of | | | | the duration of a bond you purchased on April |
| the arguments you supply to the add-in price | | | | |
| and yield date functions. If you enter an | | | | 23, 2000, and that will mature on November |
| argument with decimal values, Excel truncates | | | | 30, 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 Function | | | | you wantto use the US (NASD) day count basis |
| Errors | | | | of 30 days in a month and 360 days in a year, |
| | | | youwould use the following formula to |
| The duration functions return an error value | | | | calculate this bond's yield: |
| in several predictable cases: | | | | |
| | | | =MDURATION ("4/23/2000","11/30 |
| 1. If you use an invalid date, Excel returns | | | | 2020",.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 | | | | |