COUPDAYSNC function
PRO
The COUPDAYSNC
function in Jspreadsheet Formulas Pro is used to compute the number of days from the settlement date to the forthcoming coupon date for a financial security that offers periodic interest. The unique aspect is that this particular security has an irregular first coupon period. In simpler terms, it helps determine the period until the next payout for an investment, especially when the first payout was not standard or regular. This function is highly valuable in financial analysis and investment planning.
Documentation
Calculates the number of days from the settlement date to the next coupon date for a security that pays periodic interest, but has an irregular first coupon period.
Category
Financial
Syntax
COUPDAYSNC(settlement, maturity, frequency, [basis])
Parameter | Description |
---|---|
settlement |
Required. The date the security is purchased. Must be a valid date. |
maturity |
Required. The date the security matures. Must be after the settlement date. |
frequency |
Required. Number of coupon payments per year: 1 (annual), 2 (semiannual), or 4 (quarterly). |
[basis] |
Optional. Day count basis: 0 = US (NASD) 30/360 (default); 1 = Actual/Actual; 2 = Actual/360; 3 = Actual/365; 4 = European 30/360 |
Behavior
The COUPDAYSNC
function calculates the number of days from the settlement date until the next coupon date. The function takes four arguments: settlement date, maturity date, frequency of the coupon, and basis (the type of day count to use).
- If any of the date cells are empty or contain non-date data, the function will return a #VALUE! error.
- If the frequency argument is not one of the accepted values (1, 2, or 4), the function will return a #NUM! error.
- If the basis argument is not one of the accepted values (0, 1, 2, 3, or 4), the function will return a #NUM! error.
- The function does not handle boolean values. If a boolean value is entered, the function will return a #VALUE! error.
- If the settlement date is after the maturity date, the function will return a #NUM! error.
Common Errors
Error | Description |
---|---|
#VALUE! | The function returns this error when any of the date cells are empty or contain non-date data, or if a boolean value is entered. |
#NUM! | The function returns this error if the frequency is not one of the accepted values (1, 2, or 4), if the basis is not one of the accepted values (0, 1, 2, 3, or 4), or if the settlement date is after the maturity date. |
Best practices
- Always ensure that the settlement and maturity dates are valid dates.
- Make sure the frequency and basis arguments are within the accepted range of values. Frequency should be 1, 2, or 4 and basis should be 0, 1, 2, 3, or 4.
- Do not use boolean values as arguments for this function.
- Always check that the settlement date is not later than the maturity date.
Usage
A few examples using the COUPDAYSNC function.
| Formula | Description | Result |
|------------------------------------------------|------------------------------------------------------------------|--------|
| COUPDAYSNC("2022-01-01", "2022-06-30", 2) | Semiannual bond; returns days from Jan 1 to next coupon | 179 |
| COUPDAYSNC("2023-04-01", "2026-04-01", 4, 1) | Quarterly bond using Actual/Actual basis | 91 |
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YWM2ODU2ZWYwNWEwNjI2ODgxZDI5ODZhZDQ3MTg2ZjM1ZTU0ZTIwNjljOThhYzY1M2RiNDhkNzYxNjg5MjY1YmJiMzQ1OWI2OTEwMmVkNjhhMjQ5MGFmYzkzMjVmZjRiM2Y0Y2IxMDY2YzY5ZmQ0NGVhZDNlNTU2YzlkMmUyNzEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Settlement Date",
"Maturity Date",
"Frequency",
"Days to Next Coupon"
],
[
"2023-03-15",
"2025-12-31",
2,
"=COUPDAYSNC(A2,B2,C2)"
],
[
"2023-07-20",
"2026-06-30",
4,
"=COUPDAYSNC(A3,B3,C3)"
],
[
"2023-11-10",
"2027-03-15",
1,
"=COUPDAYSNC(A4,B4,C4)"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('YWM2ODU2ZWYwNWEwNjI2ODgxZDI5ODZhZDQ3MTg2ZjM1ZTU0ZTIwNjljOThhYzY1M2RiNDhkNzYxNjg5MjY1YmJiMzQ1OWI2OTEwMmVkNjhhMjQ5MGFmYzkzMjVmZjRiM2Y0Y2IxMDY2YzY5ZmQ0NGVhZDNlNTU2YzlkMmUyNzEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Settlement Date",
"Maturity Date",
"Frequency",
"Days to Next Coupon"
],
[
"2023-03-15",
"2025-12-31",
2,
"=COUPDAYSNC(A2,B2,C2)"
],
[
"2023-07-20",
"2026-06-30",
4,
"=COUPDAYSNC(A3,B3,C3)"
],
[
"2023-11-10",
"2027-03-15",
1,
"=COUPDAYSNC(A4,B4,C4)"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('YWM2ODU2ZWYwNWEwNjI2ODgxZDI5ODZhZDQ3MTg2ZjM1ZTU0ZTIwNjljOThhYzY1M2RiNDhkNzYxNjg5MjY1YmJiMzQ1OWI2OTEwMmVkNjhhMjQ5MGFmYzkzMjVmZjRiM2Y0Y2IxMDY2YzY5ZmQ0NGVhZDNlNTU2YzlkMmUyNzEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Settlement Date",
"Maturity Date",
"Frequency",
"Days to Next Coupon"
],
[
"2023-03-15",
"2025-12-31",
2,
"=COUPDAYSNC(A2,B2,C2)"
],
[
"2023-07-20",
"2026-06-30",
4,
"=COUPDAYSNC(A3,B3,C3)"
],
[
"2023-11-10",
"2027-03-15",
1,
"=COUPDAYSNC(A4,B4,C4)"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YWM2ODU2ZWYwNWEwNjI2ODgxZDI5ODZhZDQ3MTg2ZjM1ZTU0ZTIwNjljOThhYzY1M2RiNDhkNzYxNjg5MjY1YmJiMzQ1OWI2OTEwMmVkNjhhMjQ5MGFmYzkzMjVmZjRiM2Y0Y2IxMDY2YzY5ZmQ0NGVhZDNlNTU2YzlkMmUyNzEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPVEF4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Settlement Date",
"Maturity Date",
"Frequency",
"Days to Next Coupon"
],
[
"2023-03-15",
"2025-12-31",
2,
"=COUPDAYSNC(A2,B2,C2)"
],
[
"2023-07-20",
"2026-06-30",
4,
"=COUPDAYSNC(A3,B3,C3)"
],
[
"2023-11-10",
"2027-03-15",
1,
"=COUPDAYSNC(A4,B4,C4)"
]
]
}]
});
}
}