Microsoft Office Tutorials and References

In Depth Information

**The DISC function**

The DISC function

The DISC function calculates the discount rate for a security and takes the arguments

settlement
,
maturity
,
price
,
redemption
, and
basis
. (For argument definitions, see Table 16-3.)

For example, suppose a bond has a settlement date of June 15, 2014; has a maturity date

of December 31, 2014; has a price of $96.875; has a $100 redemption value; and uses

the standard 30/360 basis. The bond discount rate formula =DISC("6/15/14", "12/31/14",

96.875, 100, 0) returns 0.057398, or 5.74 percent.

The YIELD, YIELDDISC, and YIELDMAT functions

The YIELD function determines the annual yield for a security that pays interest on a

periodic basis and takes the arguments
settlement
,
maturity
,
rate
,
price
,
redemption
,
frequency
,

and
basis
; for definitions of these arguments, see Table 16-3. For example, suppose a bond

has a settlement date of February 15, 2014; has a maturity date of December 1, 2014; has

a coupon rate of 3.75 percent with semiannual frequency; has a price of $99.2345; has a

$100 redemption value; and uses the standard 30/360 basis. The annual bond yield formula

=YIELD("2/15/14", "12/1/14", 0.0375, 99.2345, 100, 2, 0) returns 0.047363, or 4.74 percent.

The YIELDDISC function, on the other hand, calculates the annual yield for a discounted

security. It takes the arguments
settlement
,
maturity
,
price
,
redemption
, and
basis
. Using

the preceding example but changing the price to $97.00, the bond yield formula

=YIELDDISC("2/15/14", "12/1/14", 97, 100, 0) returns 0.03893, or 3.89 percent.

The YIELDMAT function calculates the annual yield for a security that pays its interest at

maturity. This function takes the arguments
settlement
,
maturity
,
issue
,
rate
,
price
, and
basis
.

Using the arguments from the YIELD example but adding an issue date of January 1, 2014,

and changing the price to $99.2345, the yield-at-maturity formula =YIELDMAT("2/15/14",

"12/1/14", "1/1/14", 0.0375, 99.2345, 0) returns 0.04728, or 4.73 percent.

The TBILLEQ, TBILLPRICE, and TBILLYIELD functions

The TBILLEQ function calculates the bond-equivalent yield for a U.S. Treasury bill. It

takes the arguments
settlement
,
maturity
, and
discount
. (For argument definitions, see

Table 16-3.) For example, suppose a U.S. Treasury bill has a settlement date of

February 1, 2014; a maturity date of July 1, 2014; and a discount rate of 0.86 percent. The

formula for calculating the bond yield that is equivalent to the yield of a U.S. Treasury bill is

=TBILLEQ("2/1/14", "7/1/14", 0.0086), which returns 0.008751, or .88 percent.

You use the TBILLPRICE function to calculate the price per $100 of face value for a

U.S. Treasury bill. This function takes the arguments
settlement
,
maturity
, and
discount
.

Using the preceding example, the formula to calculate the price per $100 of face value,

=TBILLPRICE("2/1/14", "7/1/14", 0.0086), returns 99.6417, or $99.64.