Documentation/Calc Functions/COUPDAYSNC
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
COUPDAYSNC
Category:
Financial Analysis
Summary:
Calculates the number of days between a bond’s settlement date and the first coupon date after the settlement date.
Syntax:
COUPDAYSNC(Settlement; Maturity; Frequency[; Basis])
Returns:
Returns an integer in the range 1 to 366 (inclusive), which is the number of days from the settlement date until the next coupon date.
Arguments:
Settlement is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when a trade is "settled". On this date the seller completes the transfer of the bond to the buyer, and the buyer makes the appropriate payment to the seller.
Maturity is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when the bond matures. On maturity, the bond issuer must repay the bond holder the full amount of the bond.
Frequency is a positive integer, or a reference to a cell containing that integer, that is the number of coupons paid per year. Valid values are 1 (annual), 2 (semiannual), or 4 (quarterly).
Basis is an integer in the range 0 to 4, or a reference to a cell containing that integer, which indicates how the year is to be calculated. For more background information, visit Wikipedia's Day count convention page.
| Basis | Calculation |
|---|---|
| 0 or missing | US method (NASD), 12 months of 30 days each |
| 1 | Exact number of days in months, exact number of days in year. |
| 2 | Exact number of days in month, year has 360 days. |
| 3 | Exact number of days in month, year has 365 days. |
| 4 | European method, 12 months of 30 days each. |
- If either Settlement or Maturity is not a valid date expression, then COUPDAYSNC reports a #VALUE! error.
- If the Settlement date is on or after the Maturity date then COUPDAYSNC reports an invalid argument error (Err:502).
- If Frequency is non-numeric, then COUPDAYSNC reports a #VALUE! error.
- If Basis is non-numeric, then COUPDAYSNC reports an invalid argument error (Err:502).
- If either Basis or Frequency is a non-integer value, then COUPDAYSNC truncates it to an integer value.
- If, after any truncation, Frequency is not equal to 1, 2, or 4, then COUPDAYSNC reports an invalid argument error (Err:502).
- If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then COUPDAYSNC reports an invalid argument error (Err:502).
Additional details:
- The exact behavior of COUPDAYSNC depends on the specified day-count convention, as follows:
- If the Basis argument is set to either 1, 2, or 3, when the actual number of days per month is used, then COUPNCD is used to determine the first coupon date that occurs after the settlement date. COUPDAYSNC then calculates the difference between the settlement date and that coupon date, in days.
- If the Basis argument is set to either 0 or 4, when 30 days are assumed per month, then COUPDAYS is used to determine the number of days in the coupon period that contains the settlement date. In addition, COUPDAYBS is used to determine the number of days between the latest coupon date that falls on or before the settlement date, and the settlement date. COUPDAYSNC then subtracts these two, to give the number of days from the settlement date until the next coupon date.
- The examples on this page use the standard ISO 8601 format for dates because this should be independent of your selected locale settings. The default date format on your computer may be different. Avoid using locale dependent date formats which may produce errors if the document is loaded under different locale settings. It is often good practice to utilize the DATE function instead of typing formatted date values.
- If either Settlement or Maturity includes time components, they are ignored by COUPDAYSNC.
- For more background information about coupons, visit Wikipedia’s Coupon (finance) page.
Examples:
| Formula | Description | Returns |
|---|---|---|
| =COUPDAYSNC(A1; A2; A3; A4) where cell A1 contains the date 2022-09-01, cell A2 contains the date 2025-11-15, cell A3 contains the number 2, and cell A4 contains the number 3. | An investor purchases a bond with a maturity date of 2025-11-15, with the trade settled on 2022-09-01. The bond pays interest every six months and the applicable day count convention is basis 3 (exact number of days in month, 365 days in year). Here the function calculates the number of days from the settlement date to the next coupon date as 75. | 75 |
| =COUPDAYSNC("2022-09-01"; "2022-11-15"; 2) | An investor purchases a bond with a maturity date of 2025-11-15, with the trade settled on 2022-09-01. The bond pays interest every six months. Since the fourth argument is omitted, COUPDAYSNC defaults to a day count convention of basis 0 (12 months of 30 days each). Here the function calculates the number of days from the settlement date to the next coupon date as 74. | 74 |
| =COUPDAYSNC(DATE(2022; 9; 1); DATE(2025; 11; 15); 2) | Here the formula performs the same function as in the previous example but utilizes the DATE function instead of typing formatted date values. | 74 |
| =COUPDAYSNC(44805; 45976; 2) | Calc treats dates as numbers, where each date corresponds to a date-time serial number. Assuming that the Date option at ▸ ▸ ▸ ( ▸ ▸ ▸ on macOS) is set to its default value, 44805 is the date-time serial number for 2022-09-01 and 45976 is the date-time serial number for 2025-11-15. Here the formula performs the same function as in the previous example. | 74 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
COUPDAYSNC