What is COUPDAYBS in Google Sheets?
The COUPDAYBS function in Google Sheets calculates the number of days from the beginning of a coupon period to the settlement date for a security that pays periodic interest. It is a financial function used in bond calculations.
If a bond has a settlement date of 2024-03-15, a maturity date of 2025-03-15, a coupon frequency of 2 (semi-annual), and you want to use the actual/actual day count (basis=1), the formula would be:
=COUPDAYBS(DATE(2024,3,16), DATE(2027,3,15), 2, 1)
This formula would return the days between 2024-03-16 and the beginning of the first coupon period.
Key Takeaways
- The COUPDAYBS function in Google Sheets calculates the number of days from the beginning of the current coupon period to the bond’s settlement date.
- Its syntax is as follows: =COUPDAYBS(settlement, maturity, frequency, basis)
- In this function, settlement is the purchase date, maturity is the bond’s end date, frequency is the number of coupon payments per year, and basis refers to the day count method used.
- It is commonly used to determine how far into a coupon period a bond was purchased, which is important for interest and pricing calculations.
- Use the DATE function to ensure that all date inputs are correctly formatted in Google Sheets.
Syntax
The syntax of the COUPDAYBS function in Google Sheets is as follows:
=COUPDAYBS(settlement, maturity, frequency, [basis])
Let us look at the arguments one by one:
- settlement: The date the security is purchased or traded.
- maturity: The date the security matures.
- frequency: The number of coupon payments per year.
- basis: (Optional) The day count basis to use. If omitted, it defaults to 0 (US (NASD) 30/360).
Day Count Basis Options:
- 0 or omitted: US (NASD) 30/360,
- 1: Actual/actual,
- 2: Actual/360,
- 3: Actual/365, and
- 4: European 30/360.
How To Use COUPDAYBS Function in Google Sheets?
The COUPDAYBS function is a powerful financial tool that calculates the number of days between the beginning of the current coupon period and the settlement date of a bond.
This function is very useful when analysing bond investments with periodic interest payments. One can use COUPDAYBS in two ways:
- By manually typing COUPDAYBS
- By selecting it from the Google Sheets menu
Using the COUPDAYBS Function Manually
Let’s walk through an example to see how COUPDAYBS works step-by-step when we enter the function manually.
A person buys a bond on March 1, 2025, which will mature on March 1, 2027. This bond pays interest four times per year (quarterly) and follows the default day count convention. Find the number of days from the beginning of a coupon period to the settlement date.
Step 1: Enter bond details into a sheet by filling in the relevant data like settlement date, maturity date, and frequency. Frequency 4 is entered for quarterly.
Step 2: Choose cell B5 to see the result. Now, type the function name:
=COUPDAYBS(
Step 3: Supply each required argument inside the parentheses, separated by commas. To ensure compatibility, use the DATE function for all date entries. You can also directly enter the cell references as follows:
=COUPDAYS(B1,B2,B3)
or
=COUPDAYBS(DATE(2025,3,1), DATE(2027,3,1), 4)
After entering the formula, press Enter.
Step 4: The function will return the number of days from the start of the current coupon period up to the settlement date (March 1, 2025). This gives you valuable insight into how far into the coupon period the bond is at the time of purchase.
Using COUPDAYBS From the Menu
If you prefer to insert the function through the menu:
- Click on any empty cell.
- Navigate to the top menu, going to Insert → Function → Financial.
- From the list of financial functions, choose COUPDAYBS.
- Fill in the required arguments (settlement, maturity, frequency, etc.) and press Enter.
By using the COUPDAYBS function, one can gauge how much of the coupon period has elapsed at the point of settlement. This value is very essential for bond valuation and interest calculation.
Examples
Let us look at the different real-time scenarios where we can use COUPDAYBS in Google Sheets.
Example #1 – Semi-Annual Bond Purchased Mid-Period
An investor purchases a bond on April 15, 2025. The bond has a maturity date of May 15, 2028, and pays interest twice a year (semi-annually)—typically every March 15 and September 15. The investor wants to determine how many days have passed in the current coupon period before they purchased the bond. We can easily find this using COUPDAYBS in Google Sheets.
Step 1: Open a sheet and enter the following details in it.
- Settlement Date is when the bond was bought.
- Maturity Date is when the bond expires.
- Frequency is 2 for semi-annual payments.
Step 2: Use the COUPDAYBS Formula as shown below.
=COUPDAYBS(B1, B2, B3). Press Enter.
Step 3: The formula returns the number of days from the start of the current coupon period to the settlement date (April 15, 2025).
In this case, the result is 120, which means that 120 days have passed in the current coupon period before the bond was purchased. This information helps the investor understand how much interest has already accrued and how close the next coupon payment is.
Example #2 – Using COUPDAYBS with IF Function
An investor purchases a semi-annual bond on April 10, 2025, with a maturity date of October 10, 2027. The bond pays interest every 6 months. The investor wants to determine how many days had already passed in the current coupon period at the time of purchase. He also wants a clear message saying whether the bond was purchased early or late in the coupon period, with the cutoff set at 90 days.
Step 1: Enter the bond details in a spreadsheet.
Step 2: Use COUPDAYBS along with the IF function in the following way:
=IF(COUPDAYBS(B1, B2, B3) > 90, “Late”, “Early”)
COUPDAYBS(B1, B2, B3) calculates the number of days since the start of the current coupon period.
The IF function checks if that number is greater than 90.
If TRUE, it returns “Late”.
If FALSE, it returns “Early”.
Step 3: Press Enter. In this case, COUPDAYBS returns 0, so the formula returns “Early. “
Why we get zero is because, with a maturity date of October 10 and semi-annual frequency, the bond likely pays coupons on April 10 and October 10. That means the current coupon period began on April 10, 2025, and the investor purchased the bond on that date. So, 0 days have passed in the coupon period.
Example #3 – Using COUPDAYBS with Conditional Formatting
A financier is reviewing a list of bond purchases. They want to highlight bonds that were bought more than 60 days into the current coupon period, which might affect accrued interest and investment decisions.
They can use the COUPDAYBS function combined with conditional formatting to highlight such rows.
Step 1: Enter the details in a sheet. The range of the table is A2:C5.
Step 2: Enter the following formula in D2 to calculate the days since the beginning of the coupon period:
=COUPDAYBS(A2, B2, C2).
Drag the formula down all the way till cell D5.
Column D shows the coupon period of each bond that was purchased.
Step 3: Apply conditional formatting by selecting the range D2:D5 containing the COUPDAYBS result.
Go to Format → Conditional formatting.
Under Format cells if, choose “Custom formula is”.
Enter the following formula:
=D2 > 90
Choose a format style of your choice. Click Done.
All bonds purchased more than 60 days into the current coupon period will be automatically highlighted.
Important Things to Note
- All arguments of the COUPDAYBS function are truncated to integers.
- If the arguments of the function include time, it will be ignored.
- If the basis is omitted, the default US 30/360 (0) is used.
- The settlement date is the date of purchase of a coupon, like a bond, by the buyer. The maturity date is the date when the coupon expires.
Frequently Asked Questions (FAQs)
The COUPDAYBS formula calculates the number of days between two dates, from the beginning of the coupon period to the settlement date. It considers leap years and the month of coupon payment. Hence, this function can be used to calculate the number of days between two sets of coupon payments. It can also find the number of days between the bond’s issue date and the coupon payment date. It can find the number of days between a given coupon’s payment date and the date it is set to expire as well.
We get the $VALUE error when:
The settlement or maturity is not a valid date.
We get the #NUM! error when
The frequency is any number other than 1, 2, or 4.
If basis < 0 or if basis > 4.
If the settlement date ≥ maturity date.
The frequency argument in the COUPDAYBS function tells Google Sheets how often a bond pays interest each year.
A frequency of 1 means the bond pays interest once a year (annually).
2 means twice a year (every six months)
4 means four times a year (every three months).
It helps us understand the bond’s payment schedule so it we can correctly calculate how many days have passed in the current interest period.
Download Template
This article must help understand COUPDAYBS Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is COUPDAYBS Function in Google Sheets. We learn how to use COUPDAYBS to find the number of days with step-wise examples. You can learn more from the following articles. –
Leave a Reply