COUPNCD Excel Formula

Microsoft Excel is the best application to use in managing, storing and organizing information. Aside from this, it is also a great means in computing financial and accounting matters. The best thing about this application, it is very useful in different calculation in an efficient manner. This can be effective by learning its different functions. With its functions, a mathematical problem could be solved in just few minutes. When it comes to accounting and financial computations, one of the functions that a person should be familiar with is the COUPNCD. This is the function to use when computing the succeeding coupon date after the settlement date.

What is the COUPNCD Function Formula and Arguments?

To efficiently use Microsoft Excel, right formula for the function should be properly used. For COUPNCD function, the syntax to use is:

=COUPNCD(settlement, maturity, frequency, [basis]

The arguments:

  • settlement -  this is the date when the coupon is purchased by the buyer.
  • maturity – this is the date when the coupon will expire.
  • frequency – refers to the value that tells about the coupon payments to be paid every year.
  • basis – this is an not obligatory argument. The value indicates the kind of day count to be utilized during the computation.

Common Mistakes with the COUPNCD Function

With regards to the formula, there are certain details to take note to avoid error or to handle computation once error occurs. Listed below are the usual errors that can be encountered.

  • #VALUE! Appears when a certain date is keyed in the worksheet is not accepted as valid date. Here, all the dates used during the computation should be keyed in on its DATE function.
  • Once the value of frequency is not equal to 1, 2 or 4 then the function result will be #NUM error. So, make sure to use only these values during computation.
  • Once the basis is greater than 4 or the basis is less than 0, then the result of the function will also be #NUM error. So, in using the COUPNCD function make sure that the value of its basis ranges from 0 to 4 only.


To understand more about the function, try the following example:

Place in the cells the following data:

A1 – =DATE(2010, 5, 12) – settlement

A2 – =DATE(2012, 9, 20) – maturity

A3 – 2 – frequency

A4 – 0 – basis

Once you contain the data, do the following procedures:

  • Click the cell where to locate the answer.
  • In the chosen cell, type in “=COUPNCD(“.
  • Click the reference cell for settlement and type in comma.
  • Click again the reference cell for maturity and key in comma.
  • Click once more the reference cell for frequency and input comma.
  • Click the reference cell for basis and type in closing parenthesis.
    After keying in the closing parenthesis press the enter key.
  • The answer is 40441 or September 20, 2010. So, this is the date to return the succeeding coupon date after the date of settlement.

With this example, you can learn how to efficiently use COUPNCD function.

Post comment