COUPDAYBS, COUPDAYS, and COUPDAYSNC functions are financial functions in Microsoft Excel that are used to calculate the Coupon date or period. The purpose of the COUPDAYBS function is to return the number of days from the beginning of the coupon period to the settlement date. The COUPDAYS function returns the number of days in the coupon period that contains the settlement date. The COUPDAYSNC returns the number of days from the settlement date to the next coupon date.
The formulas and syntax for the COUPDAYBS, COUPDAYS, and COUPDAYSNC functions are below.
Formulas
The formula arguments might be the same, but the functions give a different result.
- COUPDAYBS:
COUPDAYBS(settlement, maturity, frequency, [basis]). - COUPDAYS:
COUPDAYS(settlement, maturity, frequency, [basis]). - COUPDAYSNC:
COUPDAYSNC(settlement, maturity, frequency, [basis]).
Syntax
- Settlement: The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. It is required.
- Maturity: The security’s maturity date. The maturity date is the date when the security expires. It is required.
- Frequency: The number of coupon payments per year. For annual payments frequency = 1, for semiannual frequency = 2 and for quarterly frequency = 4. It is required.
- Basis: The type of day count basis to use. It is optional.
Follow the methods below on how to use the COUPDAYBS, COUPDAYS and COUPDAYSNC function in Microsoft Excel:
How to use the COUPDAYBS function in Excel
Launch Microsoft Word.
Create a table or use an existing one.
Enter where you want to enter the result =COUPDAYBS(B4, B5, B8, B9) and press enter. The result is 106.
- B4 is the settlement.
- B5 is the Maturity.
- B8 is the Frequency.
- B9 is the Basis.
See the photo above.
There are two other methods to use the COUPDAYBS function in Microsoft Excel.
Method one is to click the Fx button on the top of the spreadsheet.
An Insert Function dialog box will open.
In the Select a category list box, select Financial from the list.
In the Select a function list box, select COUPDAYBS from the list.
An Argument Function dialog box will open.
Enter your arguments in the dialog box and click Ok. See the photo above.
Method 2 is to click the Formulas tab and click the Financial button and select the COUPDAYBS function from the menu.
How to use the COUPDAYS function in Excel
Enter where you want to enter the result =COUPDAYS(B4, B5, B8, B9) and press enter. The result is 180.
- B4 is the Settlement.
- B5 is the Maturity.
- B8 is the Frequency.
- B9 is the Basis.
See the photo above.
There are two other methods to use the COUPDAYS function in Microsoft Excel.
Method one is to click the Fx button on the top of the spreadsheet.
An Insert Function dialog box will open.
In the Select a category list box, select Financial from the list.
In the Select a function list box, select COUPDAYS from the list.
An Argument Function dialog box will open.
Enter your arguments in the dialog box and click Ok.
Method 2 is to click the Formulas tab and click the Financial button and select the COUPDAYS function from the menu.
How to use the COUPDAYSNC function in Excel
Enter where you want to enter the result =COUPDAYSNC(B4, B5, B8, B9) and press enter. The result is 74.
- B4 is the settlement.
- B5 is the Maturity.
- B8 is the Frequency.
- B9 is the Basis.
See the image above.
There are two other methods to use the COUPDAYSNC function in Microsoft Excel.
Method one is to click the Fx button on the top of the spreadsheet.
An Insert Function dialog box will open.
In the Select a category list box, select Financial from the list.
In the Select a function list box, select COUPDAYSNC from the list.
An Argument Function dialog box will open.
Enter your arguments in the dialog box and click Ok.
Method 2 is to click the Formulas tab and click the Financial button and select the COUPDAYSNC function from the menu.
Read: How to use the MOD function in Microsoft Excel
What is coupon Frequency?
Coupon Frequency is how regularly an issuer pays the coupon to the holder. Bonds pay interest monthly, quarterly, semi-annually, or annually.
What is the difference between coupon rate and discount rate?
The discount rate is the interest used to price bonds, while the coupon rate is the interest payment received by a bond holder from the date of issuance until the date of maturity of a bond.
We hope you understand how to use the COUPDAYBS, COUPDAYS, and COUPDAYSNC functions in Microsoft Excel.
