Table of Contents
- PURPOSE OF COUPDAYSNC IN EXCEL
- PREREQUISITES TO LEARN COUPDAYSNC
- SYNTAX: COUPDAYSNC
- EXAMPLE:COUPDAYSNC IN EXCEL
- CONFUSION CLARIFICATIONS
INTRODUCTION
Functions are the heart of any spreadsheet application. This is something which is the major help we get while automating our sheet.
COUPDAYSNC function comes under the FINANCIAL FUNCTIONS category in Excel.
COUPDAYSNC helps us to calculate the number of days from the settlement date to the next coupon date.
Coupon bonds are securities that pay interest at regular interval (say 1,2 or 4 times a year) before the final maturity of the bond.
The COUPDAYSNC function helps in calculating the number of days in the COUPON PERIOD , which contains the settlement date.
PURPOSE OF COUPDAYSNC IN EXCEL
COUPDAYSNC function helps in calculating the number of days , from the settlement date to the next coupon date.
COUPDAYSNC( RESULT IN DAYS)= NEXT COUPON DATE – SETTLEMENT DATE
PREREQUISITES TO LEARN COUPDAYSNC
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Some information about the financial terms is an advantage for the use of such formulas.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: COUPDAYSNC
The Syntax for the function is
=COUPDAYSNC(SETTLEMENT DATE, MATURITY DATE,NUMBER OF COUPON PAYMENTS (FREQUENCY) , BASIS)
SETTLEMENT DATE is 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 DATE is the security’s maturity date. The maturity date is the date when the security expires.
NUMBER OF COUPON PAYMENT(FREQUENCY) is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
BASIS is the type of day count basis to use. [SIMPLY THE NUMBER OF DAYS WHICH WILL BE TAKEN FOR THE YEAR OR MONTH ]
| 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 DATES SHOULD BE PUT USING THE DATE FUNCTION
=DATE(YYYY,MM,DD) OTHERWISE IT’LL RETURN AN ERROR.
EXAMPLE:COUPDAYSNC IN EXCEL
DATA SAMPLE
The settlement date for the example is 19.01.2019
Maturity date is 01.09.2019
Freq of payout is 2
and basis is 1.
STEPS TO USE COUPDAYSNC
The data is put in the cells from F8 TO F11.For the resultwe put the formula in F13 as
=COUPDAYSNC(F8,F9,F10,F11)
The result comes out to be 41.
CONFUSION CLARIFICATIONS
NUMBER OF DAYS CALCULATION
We should always be having knowledge about the working of a function. Otherwise we may be stuck in a situation where we never know whether the answer is correct or not.
These formulas are doing nothing but playing with the dates.
MS Excel is converting the date to a number first. Number is the number of days after JAN 1 1900.
After converting to number it subtracts to find out the number of days between any two dates.
The same procedure is being followed up in these financial formulas.
These can be done with the simple calculations also but when we have a ready function, we can always take help.
