Microsoft Office Tutorials and References
In Depth Information
The INTRATE and RECEIVED functions
This function takes the arguments issue , settlement , rate , par , and basis . Using the
preceding example with a maturity date of July 31, 2018, the accrued interest formula is
=ACCRINTM("3/1/14", "7/31/18", 0.017, 1000, 0), which returns 75.0833, indicating that the
$1,000 bond will pay $75.08 interest on July 31, 2018.
The INTRATE and RECEIVED functions
The INTRATE function calculates the rate of interest, or discount rate, for a fully invested
security. This function takes the arguments settlement , maturity , investment , redemption ,
and basis ; for argument definitions, see Table 16-3. For example, suppose a bond has a
settlement date of March 31, 2014, and a maturity date of September 30, 2014. A $1,000,000
investment in this bond will have a redemption value of $1,024,324, using the default
30/360 basis. The bond’s discount rate formula is =INTRATE("3/31/14", "9/30/14", 1000000,
1024324, 0), which returns 0.048648, or 4.86 percent.
Similarly, the RECEIVED function calculates the amount received at maturity for a fully
invested security and takes the arguments settlement , maturity , investment , discount ,
and basis . Using the preceding example with a 5.5 percent discount rate, the
formula =RECEIVED("3/31/14", "9/30/14", 1000000, 0.055, 0) returns the mature value
$1,028,277.63.
The PRICE, PRICEDISC, and PRICEMAT functions
The PRICE function calculates the price per $100 of face value of a security that pays
interest on a periodic basis. This function takes the arguments settlement , maturity , rate , yield ,
redemption , frequency , and basis ; for argument definitions, see Table 16-3. For example,
suppose a bond’s settlement date is March 31, 2014; its maturity date is July 31, 2014; and
the interest rate is 5.75 percent, with semiannual frequency. The security’s annual yield is
6.50 percent, its redemption value is $100, and it is calculated using the standard 30/360
basis. The bond price formula is =PRICE("3/31/14", "7/31/14", 0.0575, 0.065, 100, 2, 0),
which returns $99.73498.
Similarly, the PRICEDISC function returns the price per $100 of face value of a security
that is discounted instead of paying periodic interest. This function takes the arguments
settlement , maturity , discount , redemption , and basis . Using the preceding example with
the addition of a discount amount of 7.5 percent, the formula =PRICEDISC("3/31/14",
"7/31/14", 0.075, 100, 0) returns a price of $97.50.
Finally, the PRICEMAT function returns the price per $100 of face value of a security
that pays its interest at the maturity date. This function takes the arguments settlement ,
maturity , issue , rate , yield , and basis ). Using the preceding example with a settlement date
of July 31, 2014; an issue date of March 1, 2014; and the maturity date changed to July 31,
2015. The formula =PRICEMAT("7/31/14", "7/31/15", "3/1/14", 0.0575, 0.065, 0) returns
$99.15.
Search JabSto ::




Custom Search