The COUPNUM function

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp


Welcome back to our usual blog of Excel functions from A to Z. Today we look at the CUPNUM function.

The COUPNUM function

A coupon bond is a bond that pays interest before maturity. Once you own the bonus, you can calculate the on oneber de knockabout the payments you will receive during the term of the bonus through the use of CUPNUM function. Simply, this function returns the number of coupons payable between the settlement date and the expiration date, rounded to the nearest full coupon.

The CUPNUM The function uses the following syntax to operate:

COUPNUM (liquidation, expiration, frequency, [basis])

The CUPNUM The function has the following arguments:

  • settlement: this represents the settlement date of the security. The settlement date of the security is the date after the issue date when the security is traded with the buyer.
  • maturity: this is the expiration date of the security, In other words when security expires
  • frequency: The number of coupon payments per year. For annual payments, frequency it is 1; for semester, frequency it is 2; for quarterly, frequency it is 4. These are the only options (look down)
  • base: the type of day count base to use. This is optional. There are five options:


Day Count Base

0 or omitted

United States (NASD) 30/360


Real / real


Real / 360


Actual / 365


European 30/360

It should be noted at the same time that:

  • microsoft excel stores dates as sequential serial numbers so that they can be used in calculations. By default, the 1 of January of 1900 is the serial number 1 and the 1 of January of 2008 is the serial number 39448 because it is 39,448 days after 1 of January of 1900.
  • dates must be entered using the DATE function, or as a consequence of other formulas or functions. As an example, use = DATE (2020,2,29) for him 29th February 2020. Problems can arise if dates are entered as text
  • the settlement date is the date a buyer purchases a coupon, as a bonus. The expiration date is the date a coupon expires. As an example, Suppose a bond is issued to 30 years the 1 of January of 2008 and a buyer buys it six months later. The issue date would be 1 of January of 2008, the settlement date would be 1 July 2008 and the expiration date would be 1 of January of 2038, 30 years after the date of issue of the 1 of January of 2008
  • all arguments are truncated to integers
  • And settlement O maturity is not a valid date, CUPNUM return the #VALUE! error value
  • And frequency is any number other than 1, 2 O 4, CUPNUM return the #ON ONE! error value
  • And base <0 o si base > 4, CUPNUM return the #ON ONE! error value
  • And settlementmaturity, CUPNUM return the #ON ONE! error value.

Please, see my example below:

Subscribe to our Newsletter

We will not send you SPAM mail. We hate it as much as you.