Home » Spreadsheet Software » Coupon Spreadsheet App

Coupon Spreadsheet App

Coupon Spreadsheet App – This article describes the syntax of the formula and the use of the COUPON.DIAS function in Microsoft Excel.

Description

Returns the number of days in the period (between two coupons) where the settlement date is.

Syntax

COUPONDIAS (liquidation; vencto; frequency; [base])

IMPORTANT:  Dates must be specified using the DATE function or as a result of other formulas or functions. For example, . Problems can occur if the dates are entered as text.

Coupon Spreadsheet App

The syntax of the COUPON.DIAS function has the following arguments:

  • Settlement     Required. The settlement date of the stock exchange. The date of settlement of the stock price is the date after the date of issue in which the buyer acquires the stock market value.
  •     Compulsory Venue . The expiration date of the stock market value. The expiration date is the date on which the stock value expires.
  • Frequency     Required. The number of coupon payments per year. For annual payments, frequency = 1; For semiannual payments, frequency = 2; For quarterly payments, frequency = 4.
  •     Optional Base . Determine in what type of basis the days must be counted.
Base Base for counting days
0 or omitted US (NASD) 30/360
1 Real / real
2 Real / 360
3 Real / 365
4 European 30/360

Observations

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is the serial number 1, and the date January 1, 2008 is the serial number 39448, because it is 39,448 days after January 1, 1900.
  • The settlement date is the date the coupon is purchased, for example a bond. The expiration date is the date on which the coupon expires. For example, suppose a 30-year bond is issued on January 1, 2008, and six months later, a buyer buys it. The issue date will be January 1, 2008, settlement date, July 1, 2008 and maturity date, January 1, 2038, that is, thirty years after January 1, 2008, issue date .
  • All arguments are truncated to integers.
  • If the liq or vencto arguments are not valid dates, COUPON.DIAS returns the #VALUE! Error value.
  • If the frec argument is a number other than 1, 2 or 4, COUPON.DIAS returns the # NUM! Error value.
  • If base <0 or if base> 4, COUPONDIAS returns the # NUM! Error value.
  • If the argument liq ≥ vencto, COUPON.DIAS returns the # NUM! Error value.

Coupon Spreadsheet App

Example

Copy the sample data into the following table and paste it into cell A1 in a new Excel worksheet. For the formulas to show the results, select them, press F2 and then ENTER. If you need it, you can adjust the width of the columns to see all the data.

Data Description
25-Jan-11 Settlement date
15-Nov-11 Due date
2 Semi-annual coupon (see above)
1 Base real / real (see above)
Formula Description Result
= COUPON (A2, A3, A4, A5) Number of days in the period of a coupon containing the settlement date for a bond with the above terms.

 

Coupon Spreadsheet App