Making bond coupon date calculations? Microsoft Excel can help. In total, Excel provides six functions that let you make coupon date calculations more easily
Investing in bonds? Analyzing related fixed-income investments? You'll want to know this bit of trivia: Excel provides six functions that let you make bond coupon date calculations more easily:
COUPDAYBS,
COUPDAYS,
COUPDAYSNC,
COUPNCD,
COUPNUM, and
COUPPCD.
Some Background Info on the Bond Coupon Date Functions
All six coupon date functions use four standard arguments: the settlement date, the maturity date, the frequency, and the basis.
The settlement date specifies the date the bond is settled, or purchased. The maturity date specifies the date the bond matures, or expires. You may enter these date arguments either as text strings enclosed in quotation marks or as serial date values.
The frequency argument gives the number of coupon payments made each year: you specify 1 to indicate an annual coupon, 2 to indicate a semiannual coupon, and 4 to indicate a quarterly coupon.
The basis argument specifies the number of days in the month and 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 number of days in the month and actual number of days; 2 for the actual number 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.
NOTE Excel uses only the integer portion of the arguments you supply to the add-in coupon date functions. If you enter an argument with decimal values, Excel truncates the argument to just its integer component.
Common Bond Coupon Date Function Errors
The coupon date functions return an error value in several predictable cases:
• If you use an invalid date, Excel returns #VALUE.
• If you use a frequency argument other than 1, 2, or 4, Excel returns #NUM.
• If you use a day-count-basis switch other than 0, 1, 2, 3, or 4, Excel returns #NUM.
• If the settlement day follows the maturity date, Excel returns #NUM.
Using the COUPDAYBS Function
The COUPDAYBS function calculates the number of days from the last coupon payment date to the settlement date given the settlement date, maturity date, coupon frequency, and basis. It uses the following syntax:
COUPDAYBS (settlement, maturity, frequency,basis)
For example, suppose you want to calculate the number of days from the last coupon payment date to the settlement date in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays coupons twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
COUPDAYBS ("11/26/2000","4/30/2008",2,0)
The function returns the value 26.
Using the COUPDAYS Function
The COUPDAYS function calculates the number of days in the coupon period that includes the settlement date given the settlement date, the maturity date, the coupon frequency, and the day count basis. It uses the following syntax:
COUPDAYS (settlement, maturity, frequency, basis)
For example, suppose you want to calculate the number of days in the coupon payment in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays its coupon twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
=COUPDAYS("11/26/2000","4/30/2008",2,0)
The function returns the value 180.
Using the COUPDAYSNC Function
The COUPDAYSNC function calculates the number of days from the settlement date to the next coupon date given the settlement date, the maturity date, the frequency, and the basis. It uses the following syntax:
COUPDAYSNC (settlement, maturity, frequency, basis)
For example, suppose you want to calculate the number of days from the settlement date to the next coupon payment in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays its coupon twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
=COUPDAYSNC ("11/26/2000","4/30/2008",2,0)
The function returns the value 154.
Using the COUPNCD Function
The COUPNCD function calculates the coupon date that follows the settlement date given the settlement date, the maturity date, the frequency, and the day-count-basis switch. It uses the following syntax:
COUPNCD (settlement, maturity, frequency, basis)
For example, suppose you want to calculate the next coupon payment after the settlement date in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays its coupon twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
=COUPNCD("11/26/2000","4/30/2008",2,0)
The function returns the value 37011, which is the serial date value for April 30, 2001. NOTE Excel uses serial values to represent dates: 1 for January 1, 1900; 2 for January 2, 1900; and so on. To format a serial date value to look like a date, select the cell, choose the Format menu’s Cell command, click the Number tab, and choose a date format.
Using the COUPNUM Function
The COUPNUM function calculates the number of number of coupons, or interest payments, made between the settlement date and maturity date. The function, which rounds this result up to the nearest integer value, uses the following syntax:
COUPNUM (settlement, maturity, frequency, basis)
For example, suppose you want to calculate the number of coupons, or interest payments, in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays its coupon twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
=COUPNUM("11/26/2000","4/30/2008",2,0)
The function returns the value 15.
Using the COUPPCD Function
The COUPPCD function calculates the coupon date before the settlement date given the settlement date, the maturity date, the frequency, and the basis. It uses the following syntax:
COUPPCD (settlement, maturity, frequency, basis)
For example, suppose you want to calculate the coupon payment date preceding the settlement date in the following situation: Someone purchases a 10-year bond on November 26, 2000, with a maturity date of April 30, 2008. The bond pays its coupon twice a year based on the US, or NASD, assumption. To make this calculation, you use the following formula:
COUPPCD ("11/26/2000","4/30/2008",2,0)
The function returns the value 36830, which is the serial date value for October 31, 2000.
NOTE Excel uses serial values to represent dates: 1 for January 1, 1900; 2 for January 2, 1900; and so on. To format a serial date value to look like a date, select the cell, choose the Format menu’s Cell command, clicks the Number tab, and chooses a date format.
Calculating Interest Rates With Microsoft Excel
Making interest rate calculations? Microsoft Excel can help. Excel's RATE, or interest rate, function lets you precisely calculate the implicit interest rate for a loan given the payment amount, loan amount, and number of payments.
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
