# Excel Financial Calculation & its Meaning I

## Introduction

In our previous series of Financial Calculation through MS-Excel tools , we covered

• ACCRINT
• ACCRINTM
• AMORDEGRC
• AMORLINC

In this Post of Excel Financial Calculation & its Meaning I,  We will cover some financial terms like

• COUPDAYBS
• COUPDAYS
• COUPDAYSNC
• COUPNCD
• COUPNUM
• COUPPCD

 MS-Excel Terms & Its Meaning

5)

COUPDAYBS : Coupon days from beginning. It gets the number of days from start of a coupon period (coupon period)  until settlement date.

Syntax :

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

·        Settlement :– Settlement date of the Shares or bond (Security). In this date, the investor takes possession (rights or ownership) of a security (shares or bond)

·        Maturity :– Maturity date of the Shares or bond (Security). In this date, Investment is ended and the principal + accrued interest is returned to the investor.

·        Frequency:- number of coupon payments per year  (annual=1 ; semi-annual=2 ; quarterly=4)

·        [basis] :– It controls how day are counted.

 Basis Day count 0 30/360 (US/NASD) 1 Actual/Actual 2 Actual/360 3 Actual/365 [India] 4 30/360 [European]

For Example

 Issue_date Jan 1, 2019 Settlement Feb 15,2019 Maturity date Jan 1, 2024 Years 5 Coupon Rate 8% Frequency 2  [Semi-Annually] Basis 0  [used in USA] =COUPDAYBS(settlement, maturity, frequency,[basis]) 44 days

Observation

If issue date is Jan 1 — then if manually i have to count then count number of days from Jan 2

If settlement date is Feb 15– then manually count up to Feb 15

Result: 44 days

Since basis is 0  then number of days is 30 and year:360 days

For example

 Issue date Jan 1, 2019 Settlement Feb 15,2019 Maturity date Jan 1, 2024 Years 5 Coupon Rate 8% Frequency 2  [Semi-Annually] Basis 3  [used in India] =COUPDAYBS(settlement, maturity, frequency,[basis]) 45 days

Observation

If issue date is Jan 1 — then if manually i have to count then count number of days from Jan 2

If settlement date is Feb 15– then manually count up to  Feb 15

Result: 45 days

Since basis is 3  then number of days will be Actual (Eg Jan: 31 days ; Feb 28 or 29 days depending) and year:365 days

6)COUPDAYS: This function gets (or returns) the number of days in a coupon period that includes the settlement date.

Syntax:

=COUPDAYS(settlement, Maturity, Frequency, basis)

For example

 Issue date Jan 1,2019 Settlement Feb 15,2019 Maturity date Jan 1,2024 Years 5 Coupon rate 8% Frequency 2 [semi-annually] Basis 0 [used in America] =COUPDAYS(settlement, Maturity, Frequency, basis) 180 days

Observation:  It generally calculate the number of days of frequency and basis.

In USA

• If frequency is 2 (semi-annually) and basis is 0 (30/360 days used in USA) then calculation will be 180 days
• If frequency is 1 (annually) and basis is 0 (30/360 days used in USA) then calculation will be 360 days
• If frequency is 4 (quarterly) and basis is 0 (30/360 days used in USA) then calculation will be 90 days

In India

• If frequency is 1 (annually) and basis is 3 (Actual/Actual (365 or 366) days used in India) then calculation will be 365 days
• If frequency is 2 (semi-annually) and basis is 3 (Actual/Actual (365 or 366) days used in India) then calculation will be 182.50 days
• If frequency is 4 (quarterly) and basis is 3 (Actual/Actual (365 or 366) days used in India) then calculation will be 91.25 days

7)COUPDAYSNC: This function  get (or return) the number of days from the   settlement date to the next coupon date.

Syntax:

= COUPDAYSNC(Settlement, Maturity, Frequency, Basis)

For example

 Issue date Jan 1, 2019 Settlement Feb 15,2019 Maturity date Jan 1, 2024 years 5 coupon rate 8% frequency 2  [Semi-Annual] basis 0  [used in USA] =COUPDAYSNC(Settlement, Maturity, Frequency, Basis) 136 days

Observation:  It generally calculate the (number of days in an year -(minus) difference of date between issue date and settlement) according to frequency and basis.

In USA :

Year=360 days : Month: 30 days : semi-annually=180 days  basis: 0 (used in USA)

180 days- (minus) (COUPDAYBS) = 180-44=136 days

• If frequency   is 2 (semi-annually) and basis is 0 (30/360 days used in USA) then calculation will be 136 days
• If frequency is 1 (annually) and basis is 0 (30/360 days used in USA) then calculation will be 316 days
• If frequency is 4 (quarterly) and basis is 0 (30/360 days used in USA) then calculation will be 46 days

In India:

Year: Actual (365 or 366 days) — Month: Actual (31,30,28,29 days) , Basis:3 (Actual)

• If frequency   is 1 (annually) and basis is 3 (actual/actual days used in India) then calculation will be 320 days
• If frequency is 2 (semi-annually) and basis is 3 (actual/actual days used in India) then calculation will be 136 days
• If frequency is 4 (quarterly) and basis is 3 (actual/actual days used in India) then calculation will be 45 days

8) COUPNCD : This function gets (or returns) the next coupon date after the settlement date.

Syntax

=COUPNCD(Settlement, Maturity, Frequency,[Basis])

For Example

 Issue date Jan 1, 2019 Settlement Feb 15,2019 Maturity date Jan 1, 2024 years 5 coupon rate 8% frequency 2 [Semi-Annual] Basis 0 [used in USA] = COUPNCD(Settlement, Maturity, Frequency,[Basis]) July 01, 2019

Observation:  It generally calculate (or give) the date from the Issue date and according to frequency and basis

In USA:

Year=360 days : Month: 30 days : semi-annually=180 days

basis: 0 (used in USA)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual) ; Basis:0  — then add 6 month to Issue date — (COUPNCD) will be  July 01,2019
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ; Basis:0  — then add 12 month to Issue date — (COUPNCD) will be  Jan 01,2020
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly) ; Basis:0  — then add 3 month to Issue date — (COUPNCD) will be  April 01,2019

In India:

Year=Actual (365 or 366 days) : Month: Actual (28,29,30,31)

days :  semi-annually=180 days  basis: 3 (used in India)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual) ; Basis:3  — then add 6 month to Issue date — (COUPNCD) will be  July 01,2019
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ; Basis:3  — then add 12 month to Issue date — (COUPNCD) will be  Jan 01,2020
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly) ; Basis:3  — then add 3 month to Issue date — (COUPNCD) will be  April 01,2019

9)COUPNUM : It gives (or returns) the number of coupon or interest payment, payable between the settlement date and the maturity date

Syntax:

=COUPNUM(Settlement, Maturity, Frequency, Basis)

For example

 Issue date Jan 1, 2019 Settlement Feb 15,2019 Maturity date Jan 1, 2029 years 10 coupon rate 8% frequency 2 [Semi-Annually] basis 0 [used in USA] = COUPNUM(Settlement, Maturity, Frequency, Basis) 20

Observation : Here the investor has to pay installment (or number of interest) till maturity date according to frequency. Eg In 5 years , if  frequency is semiannual (2), then COUPNUM  will be 10 (Installment should be paid 10 times)

In USA:

Year=360 days : Month: 30 days : semi-annually=180 days

basis: 0 (used in USA)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual) ; then (COUPNUM) will be  20
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ; then  (COUPNUM) will be  10
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly) ; then (COUPNUM) will be  40

In India:

Year=Actual (365 or 366 days) : Month: Actual (28,29,30,31) days :

semi- annually=180 days  basis: 3 (used in India)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual)   —  (COUPNUM) will be  20
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ;   — then  (COUPNUM) will be  10
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly)   — then (COUPNUM) will be  40

10)COUPPCD : It returns (or gets) the previous coupon date before the  settlement date.

Syntax:

=COUPPCD(Settlement, Maturity, Frequency, Basis)

For example

 Issue_date Jan 1, 2019 Settlement Sept 1,2019 Maturity date Jan 1, 2029 years 10 coupon rate 8% frequency 2 [Semi-Annually] basis 0  [used in USA] = COUPPCD(Settlement, Maturity, Frequency, Basis) July 1, 2019

In USA:

Year=360 days : Month: 30 days : semi-annually=180 days

basis: 0 (used in USA)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual) ; Basis:0  — then add 6 month to Issue date — (COUPPCD) will be  July 01,2019
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ; Basis:0  — then add 12 month to Issue date — (COUPPCD) will be  Jan 01,2020
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly) ; Basis:0  — then add 3 month to Issue date — (COUPPCD) will be  April 01,2019

In India:

Year=Actual (365 or 366 days) : Month: Actual (28,29,30,31) days :

semi- annually=180 days  basis: 3 (used in India)

• If Issue Date is Jan 01, 2019 ; Frequency:2 (semi-annual) ; Basis:3  — then add 6 month to Issue date — (COUPPCD) will be  July 01,2019
• If Issue Date is Jan 01, 2019 ; Frequency:1 (annual) ; Basis:3  — then add 12 month to Issue date — (COUPPCD) will be  Jan 01,2020
• If Issue Date is Jan 01, 2019 ; Frequency:4 (quarterly) ; Basis:3  — then add 3 month to Issue date — (COUPPCD) will be  April 01,2019