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.