November 30, 2023

How to use COUPPCD Function in Excel

The Excel COUPPCD function returns the previous coupon date before the settlement date for a coupon bond

Syntax:= COUPPCD(settlement, maturity, frequency, [basis])

The COUPPCD function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Frequency    Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Example: Let’s look at some Excel COUPPCD function examples and explore how to use the COUPPCD function as a worksheet function in Microsoft Excel:

COUPPCD Function - How to use COUPPCD Function in Excel

Syntax:  =COUPPCD(B1,B2,B3,B4)


COUPPCD Function in Excel - How to use COUPPCD Function in Excel

Based on the Excel spreadsheet above, the following COUPPCD examples would return:

Syntax: =COUPPCD(DATE(2019,3,25),DATE(2019,12,15),2,1)
Result: 15/12/2018

Syntax: =COUPPCD(DATE(2019,3,25),DATE(2019,12,15),B3,B4)
Result: 15/12/2018


  1. #NUM! error – Occurs in the following scenarios:
    • When the settlement date provided is greater than or equal to (≥) the maturity date.
    • When the given frequency argument provided by the user is not equal to 1, 2 or 4.
    • When the given basis argument is a number other than 0, 1, 2, 3 or 4.
  2. #VALUE! – Occurs in the following scenarios:
    • When the given settlement date or maturity date is not a valid date. Remember that we need to enter dates in date format or else use the DATE function to convert them into proper dates. The function doesn’t work when dates given are in text format. For example, =COUPPCD(“1/25/2023″,”11/15/2024”,2,1) = #VALUE.
    • Any of the arguments given is non-numeric.
  3. The COUPPCD function truncates all arguments to integers
READ:  How to use DURATION Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *