Microsoft Office Tutorials and References

In Depth Information

**The DOLLARDE and DOLLARFR functions**

You can type dates by using any of the following: the date’s serial number, the date

enclosed in quotation marks, or a reference to a cell that contains a date. For example, you

can type the date June 30, 2013, as the serial date value
41455
, as
6/30/13
, or as a

reference to a cell containing this date. If the security-analysis function results in a #NUM! error

value, be sure the dates are in the correct form and that they meet the criteria described in

Table 16-3.

For more information about serial date values, see “Understanding how Excel records dates

and times” in Chapter 15, “Formatting and calculating date and time.”

The DOLLARDE and DOLLARFR functions

One of this pair of functions converts fractional pricing of securities to decimals, and the

other converts decimals to fractions. The DOLLARDE function takes the arguments

fractional dollar
and
fraction
, and the DOLLARFR function takes the arguments
decimal dollar

and
fraction
). The
fractional dollar
argument is the value you want to convert expressed as

an integer, followed by a decimal point and the numerator of the fraction you want to

convert. The
decimal dollar
argument is the value you want to convert expressed as a decimal.

The
fraction
argument is an integer indicating the denominator you want to use in the

conversion. For the DOLLARFR function,
fraction
is the unit that the function should use when

converting the decimal value, effectively rounding the decimal number to the nearest half,

quarter, eighth, sixteenth, thirty-second, and so on.

For example, the formula =DOLLARDE(1.03, 32) translates as 1+3/32, which is equivalent to

1.09375. On the other hand, the formula =DOLLARFR(1.09375, 32) returns the result 1.03.

The ACCRINT and ACCRINTM functions

The ACCRINT function returns the interest accrued by a security that pays interest on a

periodic basis. This function takes the arguments
issue
,
first interest
,
settlement
,
rate
,
par
,

frequency
,
basis
, and
calculation method
, in which
first interest
indicates the date on which

interest is first accrued and
calculation method
is a logical value (1 or TRUE; 0 or FALSE). The

default value of TRUE for
calculation method
returns the total accrued interest; a value of

FALSE returns the interest accrued after the
first interest
date. For other argument

definitions, see Table 16-3. For example, suppose a U.S. Treasury bond has an issue date of March

1, 2014; a settlement date of April 1, 2014; a first interest date of September 1, 2014; a

1.7 percent coupon rate with semiannual frequency; a par value of $1,000; and a basis of

30/360. The accrued interest formula is =ACCRINT("3/1/14", "9/1/14", "4/1/14", 0.017, 1000,

2, 0), which returns 1.4167, indicating that $1.42 accrues from March 1, 2014, to April 1,

2014.

Similarly, the ACCRINTM function returns the interest accrued by a maturity security (a

type of security not only with a rhyming name but that also pays interest at maturity).