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.
Search JabSto ::

Custom Search