Bus coupons

Excel provides two functions that help you with accrued interest calculations for securities

that pay interest. Accrint calculates accrued interest for a security (such as a bond) that

pays periodic interest. Accrintm calculates accrued interest for a security (such as a zerocoupon

bond) that pays interest upon maturity.

Some Background Info on the Accrued Interest Functions

Coupon

The accrued interest functions use a similar set of arguments, along with the issue date, first

interest date, community date, maturity date, coupon rate, par value, frequency, and basis.

The date arguments are self-explanatory for the most part. The issue date is the date the security

is issued. The first interest payment date is the first coupon date. The community date is the

date you purchased, or settled, the bond. The maturity date is the date the bond matures, or expires.

You may enter the date arguments either as text strings enclosed in quotation marks (for example,

“7/4/99″) or as serial date values (for example, 37000 for April 19, 2001).

The coupon rate and par value arguments let Excel presuppose the interest. The coupon rate is

the every year interest rate multiplied by the par value to presuppose the every year interest. For example,

if a bond pays 8% interest annually and the par value is ,000, Excel would calculate

the every year interest by multiplying the 8% by the ,000 if the coupon is paid annually.

The frequency argument gives the estimate of coupon payments made each year: you specify

1 to indicate an every year coupon, 2 to indicate a semiannual coupon, and 4 to indicate a

quarterly coupon.

The basis argument specifies the estimate of days in the month and in the year assumed for

the date calculations. You specify the basis as 0 for the Us (or Nasd) version of 30 days

in a month and 360 days in a year; as 1 for the actual estimate of days in the month and year;

2 for the actual estimate of days in the month but 360 days in a year; 3 for the actual number

of days in the month and 365 days in a year; and 4 for the European version of 30 days

in a month and 360 days in a year.

Common Errors When Using the Accrued Interest Functions

Both the Accrint and Accrintm functions return an error value in the following

situations:

1. If you enter an invalid date argument, Excel returns #Value.

2. If the coupon rate or par value argument is less than 0, Excel returns #Num.

3. If the payment frequency is some estimate other than 1, 2, or 4, Excel returns #Num.

4. If the day-count-basis switch isn’t 1, 2, 3, or 4, Excel returns #Num.

o If issue date follows the community date, Excel returns #Num.

Using the Accrint Function

The Accrint function calculates the accrued interest for a security that pays periodic

interest given the issue date, first interest payment date, community date, coupon rate, par

value, payment frequency, and a day-count-basis switch. It uses the following syntax:

Accrint (issue, first interest, settlement, rate, par, frequency, basis)

For example, if you want to presuppose the accrued interest on a bond that was issued on

February 8, 1999, first paid interest on April 8, 1999, was purchased on May 23, 2000, pays

an 8% coupon, shows a ,000 par value, pays interest four times a year, and uses the Us,

or Nasd, day-count-basis assumption, you use the following formula:

=Accrint(“2/8/99″,”4/8/99″,”5/23/00″,0.08,1000,4,0)

The function returns the value 103.33.

Using the Accrintm Function

The Accrintm function calculates the accrued interest for a security that pays interest at maturity given the issue date, the maturity date, coupon rate, par value, and a day-countbasis switch. It uses the following syntax:

Accrintm (issue, maturity, rate, par, basis)

For example, if you want to presuppose the accrued interest on a bond that was issued on February 8, 1991, matures on May 23, 2010, accrues an 8% coupon, shows a ,000 par value, accrues interest two times a year, and uses the Us, or Nasd, day-count-basis assumption, you use the following formula:

=Accrintm (“2/8/91″,”5/23/10″,0.08,1000,2)

The function returns the value 1565.33.

Using Microsoft Excel’s Accrued Interest Add-In Functions

Recommend : Galaxy HDTV Earning host Pheromone Hybrid auto car Hawk diving

Comments are closed.