Related Function:

The COUPNUM function returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.

Syntax

=COUPNUM(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-2013 Settlement date    
3 15-Nov-2014 Maturity date    
4 2 Semiannual coupon    
5 1 Actual/actual basis    
6        
7 Formula Result Notes
8 =COUPNUM(A2,A3,A4,A5) 4 The number of coupon payments for a bond with the above terms

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the settlement date or the maturity date is not a valid date
  the frequency or the [basis] argument is 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