COUPNUM function

PRO

The COUPNUM function in Jspreadsheet Formulas Pro helps determine the total number of periodic interest payments for a financial security between its settlement date and maturity date. Essentially, it allows you to figure out how many times interest will be paid during the lifespan of the security. This function is very useful for managing and planning financial investments, especially for bonds or similar securities that have periodic interest payments.

Documentation

Calculates the number of coupon payments between the settlement date and the maturity date for a security that pays periodic interest.

Syntax

COUPNUM(settlement, maturity, frequency, [basis])

Parameter	Description
`settlement`	The settlement date.
`maturity`	The maturity date.
`frequency`	The number of coupon payments per year.
`[basis]`	Optional. The type of day count basis to use. If omitted, assumed to be 0 (US or NASD) by default.

Behavior

The COUPNUM function calculates the number of coupons (periods) payable between the settlement date and the maturity date of a security. Its behavior is as follows:

The function requires three primary arguments—settlement, maturity, and frequency—plus an optional basis for the day-count convention.
settlement and maturity dates should be valid dates and the settlement date should be earlier than the maturity date.
frequency should be an integer representing the number of coupon payments per year. This can be 1 (annual), 2 (semi-annual), or 4 (quarterly).
The function will ignore any cells that contain text, unless the text can be interpreted as a valid date or number.
Boolean values are treated as 0 for False and 1 for True.
Empty cells are treated as zero.
If any argument is an error, the function will return an error.

Common Errors

Error	Description
#VALUE!	This error occurs when the `settlement`, `maturity` or `frequency` argument is not a valid data type.
#NUM!	This error occurs when the `settlement` date is later than the `maturity` date, or if the `frequency` is any number other than 1, 2 or 4.

Best practices

Always ensure that the settlement date is earlier than the maturity date, otherwise you'll get a #NUM! error.

Be sure to input the frequency as one of the following integers: 1, 2, or 4. Any other value will return a #NUM! error.

Keep in mind that this function assumes a "30/360" day count basis, which means each month is assumed to have 30 days and each year is assumed to have 360 days. This might not reflect actual timelines in some cases.

Ensure that the data types for all arguments are correct to avoid #VALUE! errors. The settlement and maturity arguments should be valid dates, and frequency should be a number.

Usage

A few examples using the COUPNUM function.

COUPNUM("2022-01-01", "2022-06-30", 2)        // Semi-annual payments between Jan and Jun 2022 → returns 1
COUPNUM("2022-01-01", "2023-01-01", 4, 1)     // Quarterly payments with Actual/Actual basis → returns 4
COUPNUM("2022-01-01", "2025-01-01", 2, 3)     // Semi-annual, Actual/365 → returns 6

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('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Settlement Date",
        "Maturity Date",
        "Frequency",
        "Coupon Payments"
    ],
    [
        "2023-01-15",
        "2025-01-15",
        2,
        "=COUPNUM(A2,B2,C2)"
    ],
    [
        "2023-03-01",
        "2024-09-01",
        4,
        "=COUPNUM(A3,B3,C3)"
    ],
    [
        "2023-06-15",
        "2026-12-15",
        2,
        "=COUPNUM(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('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "Coupon Payments"
    ],
    [
        "2023-01-15",
        "2025-01-15",
        2,
        "=COUPNUM(A2,B2,C2)"
    ],
    [
        "2023-03-01",
        "2024-09-01",
        4,
        "=COUPNUM(A3,B3,C3)"
    ],
    [
        "2023-06-15",
        "2026-12-15",
        2,
        "=COUPNUM(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('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Settlement Date",
        "Maturity Date",
        "Frequency",
        "Coupon Payments"
    ],
    [
        "2023-01-15",
        "2025-01-15",
        2,
        "=COUPNUM(A2,B2,C2)"
    ],
    [
        "2023-03-01",
        "2024-09-01",
        4,
        "=COUPNUM(A3,B3,C3)"
    ],
    [
        "2023-06-15",
        "2026-12-15",
        2,
        "=COUPNUM(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('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "Coupon Payments"
    ],
    [
        "2023-01-15",
        "2025-01-15",
        2,
        "=COUPNUM(A2,B2,C2)"
    ],
    [
        "2023-03-01",
        "2024-09-01",
        4,
        "=COUPNUM(A3,B3,C3)"
    ],
    [
        "2023-06-15",
        "2026-12-15",
        2,
        "=COUPNUM(A4,B4,C4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub