How to Use COUPDAYS Function in Google Sheets

Knowing how to use the COUPDAYS function in Google Sheets is useful if you want to calculate the number of days in the coupon or interest payment period that contains the specified settlement date.

Confused by the term ‘coupon’? No need to be. It is just an annual interest payment that the bondholder receives from the bond’s issue date until it matures. Before, investors who bought bonds were given physical certificates attached to it with a series of bond coupons (scheduled interest payments). These physical coupons are now obsolete but the name survived.

Let’s take an example.

Say you have a $1,000 bond with a coupon of 4% which pays $40 per year. If the frequency of the coupon payment is 2 (semiannual), the bondholder will receive $20 twice a year. But now you need to know the number of coupon days.

So how do we do that?

Simple. The COUPDAYS function needs the settlement date, maturity date, frequency, and day count to calculate the number of days in the coupon or interest payment period that contains the specified settlement date.

Before showing you how to write the COUPDAYS function yourself, let’s take a look at the anatomy of the COUPDAYS function where we will explain what each of these terms means.

The Anatomy of the COUPDAYS Function

The syntax (the way we write) the COUPDAYS function is as follows:

=COUPDAYS(settlement, maturity, frequency, [day_count_convention])

Let’s break this down to understand the syntax of the COUPDAYS function and what each of these terms means:

• =the equals sign is the sign we put at the beginning of any function in Google Sheets.
• COUPDAYS() – is our function.
• settlement – the settlement date of the security (bond), the date after issuance when the security is delivered to the buyer.
• maturity – the maturity or expiry date of the security (bond), the day on which it can be redeemed at face or par value.
• frequency – the total number of interest or coupon payments per year.
• day_count_convention[optional, it is 0 by default] is an indicator of what day count method to use.

⚠️ A few notes you should know when writing your own COUPDAYS function in Google Sheets:

• You should enter settlement and maturity by using DATE, TO_DATE or some other date parsing functions rather than by typing text. Invalid input of dates causes the error (#VALUE!).
• frequency can be annual – 1, semiannual – 2, or quarterly – 4.
• day_count_convention can be 0 (US NASD 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (European 30/360).
  – 0 (US NASD 30/360) – as per the National Association of Securities Dealers (NASD) standard, it assumes 30 day months and 360 day years and performs specific adjustments to entered dates that fall at the end of the month.
  – 1 (Actual/Actual) – used for US Treasury Bonds and Bills but also most relevant for non-financial use, it calculates based upon the actual number of days between the specified dates, and the actual number of days in the intervening years.
  – 2 (Actual/360) – it calculates based on the actual number of days between the specified dates but assumes a 360 day year.
  – 3 (Actual/365) – it calculates based on the actual number of days between the specified dates but assumes a 365 day year.
  – 4 (European 30/360) – it assumes 30 day months and 360 day years and performs specific adjustments to entered dates that fall at the end of the month according to European financial conventions.
• If the settlement date is equal or higher than maturity date, and if the frequency or the day_count_convention are not the specified values (out of range), the COUPDAYS function will return the error (#NUM!).

How to Use the COUPDAYS Function in Google Sheets

Let’s begin writing our own COUPDAYS function in Google Sheets, step-by-step.

1. First, open the spreadsheet and click on the cell where you would want to the output of the COUPDAYS function to be. For this guide, we will use cell B13.

2. The first sign we enter when starting any function in Google Sheets is the equals sign ‘=’. Then, start typing the name of the function, which is ‘COUPDAYS’. As you start typing, you will get suggestions for the functions that start with the same letters. Continue typing or choose your function from this list (just make sure you select the right one since sometimes there may be more functions with similar names – COUPDAYS, COUPDAYBS, COUPDAYSNC, and others).

3. After the opening round bracket ‘(‘, we should enter our settlement and maturity dates. Since both these dates are already in our spreadsheet, we can use cell references. Type B5 and B6 and separate them with a comma ‘,’ (you should also add another comma after the maturity date).

4. Now type the frequency (or its cell reference). In this example, frequency is 2 and is presented in cell B9 so you can either type ‘2’ or ‘B9’. Add another comma and type the day_count_convention (or its cell reference). Since in our example day_count_convention is 0, you can also omit it. Type the closing round bracket ‘)’ or hit the Enter key on your keyboard to close the function.

5. If you did everything right, the result in cell B13 will be 180. You can change the day_count_convention and take a look at how the result will change as well (if you type 1 it will be 181, for 2 and 4 it will be 180 again, and for 3 it will be 182.5).

That is all! Now you know how to use the COUPDAYS function in Google Sheets! Make a copy of the spreadsheet using the link below and try it for yourself:

Or you can take a look at the other Google Sheets formulas you can use to create even more effective formulas that will not only help you with your work but save time, as well 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

Subscribe