Microsoft Office Tutorials and References

In Depth Information

**The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD functions**

Finally, the TBILLYIELD function calculates a U.S. Treasury bill’s yield. It takes the arguments

settlement
,
maturity
, and
price
. Using the preceding example with its precise result, 99.6417,

the yield formula =TBILLYIELD("2/1/14", "7/1/14", 99.6417) returns the yield 0.00863, or

0.86 percent.

The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD,

COUPNUM, and COUPPCD functions

This group of functions performs calculations related to bond coupons. For all the sample

formulas in this section, we use as our example a bond with a settlement date of March 1,

2014, and a maturity date of December 1, 2014. Its coupons are payable semiannually,

using the actual/actual basis (that is, a
basis
argument of 1). All these functions take the

arguments
settlement
,
maturity
,
frequency
, and
basis
. (For definitions of these arguments,

see Table 16-3.)

The COUPDAYBS function calculates the number of days from the beginning of the coupon

period to the settlement date. Using our sample data, the formula =COUPDAYBS("3/1/14",

"12/1/14", 2, 1) returns 90.

The COUPDAYS function calculates the number of days in the coupon period that contains

the settlement date. Using our sample data, the formula =COUPDAYS("3/1/14", "12/1/14",

2, 1) returns 182.

The COUPDAYSNC function calculates the number of days from the settlement date to the

next coupon date. Using our sample data, the formula =COUPDAYSNC("3/1/14", "12/1/14",

2, 1) returns 92.

The COUPNCD function calculates the next coupon date after the settlement date. Using

our sample data, the formula =COUPNCD("3/1/14", "12/1/14", 2, 1) returns 41791, or

June 1, 2014.

The COUPNUM function calculates the number of coupons payable between the

settlement date and the maturity date and rounds the result to the nearest whole coupon. Using

our sample data, the formula =COUPNUM("3/1/14", "12/1/14", 2, 1) returns 2.

The COUPPCD function calculates the coupon date before the settlement date. Using

our sample data, the formula =COUPPCD("3/1/14", "12/1/14", 2, 1) returns 41609, or

December 1, 2013.

The DURATION and MDURATION functions

The DURATION function calculates the annual duration for a security whose interest

payments are made on a periodic basis.
Duration
is the weighted average of the present value

of the bond’s cash low and measures how a bond’s price responds to changes in the yield.