Related Function:

The COUPPCD function returns a number that represents the previous coupon date before the settlement date.

Syntax

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

Arguments

Argument Description
settlement The security’s settlement date – the date after the issue date when the security is traded to the buyer
maturity The security’s maturity date – the date when the security expires
frequency The number of coupon payments per year. Must be one of the following:

  1 Annually
  2 Semi-Annually
  3 Quarterly
[basis] Optional. Defines the day count basis to be used in the calculation

  [basis] Day Count Basis
  0 (or omitted) US (NASD) 30/360
  1 actual/actual
  2 actual/360
  3 actual/365
  4 European 30/360

The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note: The settlement and maturity dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Data Description    
2 25-Jan-2014 Settlement date    
3 15-Nov-2014 Maturity date    
4 2 Semiannual coupon    
5 1 Actual/actual basis    
6        
7 Formula Result Notes
8 =COUPPCD(A2,A3,A4,A5) 15-Nov-2013 The previous coupon date before the settlement date, for a bond with the above terms

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are non-numeric
#NUM! Occurs if either:

  the supplied settlement date is ≥ maturity date
  the supplied frequency argument is any number other than 1, 2 or 4
  the supplied [basis] argument is any number other than 0, 1, 2, 3 or 4
#NAME? Occurs when Analysis ToolPak add-in is not enabled