Excel Financial Calculation & its Meaning I

by

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

 

  

Post Views: 178

Leave a Comment