Investing in bonds? Analyzing related fixed-income investments? You will want to know this little bit of trivia: Excel gives six functions that enable you to make bond coupon day calculations easier:

COUPDAYBS,

COUPDAYS,

COUPDAYSNC,

COUPNCD,

COUPNUM, and

COUPPCD.

**Some Background Information on the Bond Coupon Time Functions**

All six coupon time functions use four normal arguments: the settlement time, the maturity

date, the regularity, and the foundation.

The settlement day specifies the day the relationship is settled, or bought. The maturity date

specifies the time the relationship matures, or expires. You might enter these day arguments either as text message strings enclosed in quotation marks or as serial time values.

The frequency argument provides number of coupon obligations made every year: you specify 1 to point an twelve-monthly coupon, 2 to point a semiannual discount, and 4 to point a quarterly coupon.

The basis argument specifies the quantity of days and nights in the month and time assumed for the time calculations. You specify the foundation as 0 for the united states (or NASD) variation of thirty days in per month and 360 times in a season; as 1 for using the number of days and nights in the month and genuine number of days; 2 for some of the number of days and nights in the month but 360 times in a year; 3 for some of the number of days and nights in the month and 365 times in a yr; and 4 for the European version of thirty days in per month and 360 days and nights in a year.

NOTE Excel uses simply the integer part of the arguments you source to the add-in coupon

date functions. In the event that you enter a disagreement with decimal ideals, Excel truncates the argument to merely its integer component.

**Common Bond Coupon Day Function Errors**

The coupon time functions return one value in a number of predictable cases:

If you are using an invalid time, Excel returns #VALUE.

If you are using a frequency argument apart from 1, 2, or 4, Excel returns #NUM.

If you are using a day-count-basis switch apart from 0, 1, 2, 3, or 4, Excel returns #NUM.

If the settlement evening follows the maturity day, Excel returns #NUM.

**Using the COUPDAYBS Function**

The COUPDAYBS function calculates the amount of days from the previous coupon payment day to the settlement time given the settlement day, maturity date, coupon rate of recurrence, and basis. It uses the next syntax:

COUPDAYBS (settlement, maturity, rate of recurrence,basis)

For example, suppose you would like to calculate the quantity of days from the previous coupon payment

date to the settlement time in the next situation: Someone buys a 10-year relationship on November 26, 2000, with a maturity day of April 30, 2008. The bond pays discount codes twice a year predicated on the united states, or NASD, assumption. To create this calculation, you utilize the following formula:

COUPDAYBS (“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 26.

**Using the COUPDAYS Function**

The COUPDAYS function calculates the quantity of days in the discount period which includes the settlement time given the settlement day, the maturity time, the coupon frequency, and

the working day count basis. It uses the next syntax:

COUPDAYS (settlement, maturity, frequency, basis)

For example, suppose you intend to calculate the amount of days in the discount payment in

the following situation: An individual purchases a 10-calendar year bond on November 26, 2000, with a

maturity time of April 30, 2008. The relationship pays its voucher twice a year predicated on the US, or

NASD, assumption. To create this calculation, you utilize the following formula:

=COUPDAYS(“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 180.

**Using the COUPDAYSNC Function**

The COUPDAYSNC function calculates the quantity of times from the settlement day to another coupon time given the settlement day, the maturity time, the frequency, and the foundation. It uses the next syntax:

COUPDAYSNC (settlement, maturity, frequency, basis)

For example, suppose you need to calculate the quantity of times from the settlement date

to another coupon payment in the next situation: Someone buys a 10-year bond

on November 26, 2000, with a maturity day of April 30, 2008. The relationship pays its coupon

twice a year predicated on the united states, or NASD, assumption. To create this calculation, you utilize the

following formula:

=COUPDAYSNC (“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 154.

**Using the COUPNCD Function**

The COUPNCD function calculates the voucher date that comes after the settlement time given the settlement time, the maturity time, the regularity, and the day-count-basis move. It uses

the following syntax:

COUPNCD (settlement, maturity, frequency, basis)

For example, suppose you wish to calculate another coupon payment following the settlement

date in the next situation: Someone buys a 10-year relationship on November 26, 2000,

with a maturity time of April 30, 2008. The relationship pays its discount twice a year predicated on the

US, or NASD, assumption. To create this calculation, you utilize the following formula:

=COUPNCD(“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 37011, which may be the serial date benefit for April 30, 2001.

NOTE Excel uses serial ideals to represent dates: 1 for January 1, 1900; 2 for January 2, 1900;

and etc. To file format a serial time value to appear to be a date, choose the cell, choose the

Format menu’s Cell order, click on the Number tab, and pick a date format.

**Using the COUPNUM Function**

The COUPNUM function calculates the amount of number of discount coupons, or interest repayments, made between your settlement time and maturity day. The function, which rounds this final result up to the nearest integer benefit, uses the next syntax:

COUPNUM (settlement, maturity, frequency, basis)

For example, suppose you would like to calculate the quantity of coupons, or interest obligations, in the next situation: Someone buys a 10-year relationship on November 26, 2000, with a maturity time of April 30, 2008. The relationship pays its voucher twice a year predicated on the united states, or NASD, assumption. To create this calculation, you utilize the following formula:

=COUPNUM(“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 15.

**Using the COUPPCD Function**

The COUPPCD function calculates the voucher date prior to the settlement date given the

settlement time, the maturity day, the frequency, and the foundation. It uses the next syntax:

COUPPCD (settlement, maturity, frequency, basis)

For example, suppose you would like to calculate the coupon repayment date preceding the settlement

date in the next situation: Someone buys a 10-year relationship on November 26, 2000, with a maturity time of April 30, 2008. The relationship pays its voucher twice a year predicated on the united states, or NASD, assumption. To create this calculation, you utilize the following formula:

COUPPCD (“11/26/2000″,”4/30/2008”,2,0)

The function returns the worthiness 36830, which may be the serial date worth for October 31, 2000.

NOTE Excel uses serial ideals to represent dates: 1 for January 1, 1900; 2 for January 2, 1900;

and etc. To style a serial day value to appear to be a date, choose the cell, pick the Format menu’s Cell order, clicks the quantity tab, and chooses a time format.

## Recent Comments