Teacher (B.Ed/M.Ed)

Teacher (B.Ed/M.Ed)

                                                Teacher (B.Ed/M.Ed)   Introduction Teacher (B.Ed/M.Ed)  literally means who teaches is always been a respected figure in our Life. After Parents, Teacher is considered as the most important figure who holds … Read more

Excel Calculation & Its Meaning VIII

Excel Calculation & Its Meaning VII

                Excel Calculation & Its Meaning VIII   Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        SLN ·        SYD ·        TBILLEQ ·        TBILLPRICE ·        TBILLYIELD ·        VDB   In this Post Excel Calculation & Its Meaning VIII We will cover some financial … Read more

Excel Calculation & Its Meaning VII

Excel Calculation & Its Meaning VII

                Excel Calculation & Its Meaning VII Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        PRICE ·        PRICEDISC ·        PRICEMAT ·        PV ·        RATE ·        RECEIVED ·        RRI    In this Post We will cover some financial terms like ·        SLN ·        … Read more

Excel Calculation & Its Meaning VI

Excel Calculation & Its Meaning VI

                                              Excel Calculation & Its Meaning VI Introduction In this Post of  Excel Calculation & Its Meaning VI, We will cover some financial terms like ·        PRICE ·        PRICEDISC ·        PRICEMAT ·        PV ·        … Read more

Excel Calculation & Its Meaning V

Excel Calculation & Its Meaning V

                     Excel Calculation & Its Meaning V Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        INTRATE ·        IRR ·        ISPMT ·        MDURATION ·        MIRR ·        NOMINAL ·        NPER ·        NPV  Now , In this Post, Excel Calculation & Its … Read more

Excel Calculation & Its Meaning IV

Excel Calculation & Its Meaning IV

Excel Calculation & Its Meaning IV Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        DISC ·        DOLLARDE ·        DOLLARFR ·        DURATION ·        EFFECT ·        FV ·        FVSCHEDULE Now , In this Post Excel Calculation & Its Meaning IV We will cover some financial terms like ·       INTRATE ·       … Read more

Excel Financial Calculation & Its Meaning III

Excel Financial Calculation & Its Meaning III 

Excel Financial Calculation & Its Meaning III

 

Introduction

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

·        PMT, IPMT, PPMT

·        CUMIPMT

·        CUMPRINC

·        DB

·        DDB

In this Post Excel Financial Calculation & Its Meaning III  We will cover some financial terms like

·       DISC

·       DOLLARDE

·       DOLLARFR

·       DURATION

·       EFFECT

·       FV

·       FVSCHEDULE



MS Excel Financial Terms

                                                                                                 

16) 

DISC: It is a short form of Discount. This function returns the discount rate for a security (shares or bond), Mutual FundInsurance

Syntax:

                 =DISC(settlement, maturity, pr, redemption,[basis])

·        Settlement Settlement date of the security (Shares or bond).

·        Maturity Maturity date of the security.

·        pr Security price per  Rs. 100 face value. (Investment)

·        Redemption – Security redemption value per Rs 100 face value. ( The amount which is discounted when you withdraw money before the maturity period of your Security (Shares or Bond)

·        basis[optional] Day count basis (we have to put 3 as we live in India, for US put 0, for Europe put 4)

·        Date: It should be fill in the format as your computer date settings are done.

 

 

Eg 1

 

Settlement

09/15/2019 (Sept 15)

Maturity

09/15/2020 (Sept 15)

Price (pr)

90

Redemption

100

Basis

3 (as we live in India)

 

 

=DISC(settlement, maturity, pr, redemption,[basis])

 

0.099726

 

Eg 2

 

Settlement

01/15/2017 (Jan 15)

Maturity

12/15/2017 (Dec 15)

Price (pr)

100000

Redemption

100

Basis

3 (as we live in India)

 

 

=DISC(settlement, maturity, pr, redemption,[basis])

 

-1091.721557

Note:

Minus sign shows that you will get Rs.1091.721 less than from the amount when you complete the maturity date.

Note:

·        If dates are invalid (i.e. not actually dates) DISC returns #VALUE!

·        DISC returns #NUM when:

o   settlement >= maturity

o   pr <= 0 or redemption <= 0

o   Basis is out-of-range

 

17)

DOLLARDE: This function helps in converting a dollar value in fractional notation into a dollar value expressed in decimal notation. DOLLARDE will divide the fraction part of the value by an integer specified by the user.

Syntax :       

                    =DOLLARDE(fractional_dollar, fraction)

 

·        Fractional_dollar:–The number expressed as an integer part and a fraction part, separated by a decimal point.

Basically, the number after the decimal

 

·        Fraction: — Here the integer is used as a denominator. In case of decimal, Excel will truncate into integer.

Basically 6 will considered as 1/6 ; 16 is considered as 1/16 etc.

 

S.No

Fractional Dollar

 Fraction

DOLLARDE VALUE

1

1.02

16

=DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(1.02,16)=1.125

2

50.3

4

=DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(50.3,4)= 50.75

3

10.1

2

=DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(10.1,2)= 10.50

 

Observation

Eg:

           =DOLLARDE(1.02,16)=1.125

You have to take digit after the decimal  of the fractional dollar (1.02 = 02) and the fraction is treated as denominator like 16 is 1/16

Now, divide it 02/16= 0.125

Now, add the digit before the decimal  (fractional dollar like 1)  with 0.125

Add = 1+0.125= 1.125 is equal to

=DOLLARDE(1.02,16)=1.125

 Eg2

          =DOLLARDE(50.3,4)=50.75

Again, You have to take digit after the decimal  of the fractional dollar (50.3 = 3) and the fraction is treated as denominator like 4 is ¼

Now, Divide it   ¾= 0.75

Now, add the digit before the decimal  (fractional dollar like 50)  with 0.75

Add = 50+0.75= 50.75 is equal to

=DOLLARDE(50.3,4)=50.75

18)

DOLLARFR: This function helps convert the dollar value which was in decimal into a fractional dollar value. It helps in products like securities prices.

 Syntax:  

                 =DOLLARFR(decimal_dollar,fraction)

·        Decimal_dollar= Dollar value expressed as decimal. It is basically considered after the decimal value of the decimal_dollar (Eg: 1.67 = .67 is the decimal dollar)

·        Fraction:–  Denominator value of the fractional unit.

 Here We generally multiply decimal dollar and fraction  

 

S.No

Decimal Dollar

 Fraction

DOLLARFR VALUE

1

1.02

16

=DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(1.02,16)=1.0032

2

50.3

4

=DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(50.3,4)= 50.12

3

10.1

2

=DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(10.1,2)= 10.02

 

Observation

Eg: 1

Decimal dollar = 1.02    & Fraction=16

Here we take the right side of the decimal , i.e.  =.02

 Fraction =16 , here we take 0.16

Now we multiply = .02*.16=.0032

Now add left side of the decimal dollar+.0032 = 1+.0032=1.0032

=DOLLARFR(1.02,16)=1.0032

 

Eg:2

Decimal dollar = 50.3    & Fraction=4

Here we take the right side of the decimal , i.e.  =.3

 Fraction =4 , here we take 0.4

Now we multiply = .3*.4=.12

Now add left side of the decimal dollar+.12 = 50+.12=50.12

=DOLLARFR(50.3,4)=50.12

 

19)

DURATION: It returns (or gives) the annual duration of security with periodic interest payment. It is used by Portfolio Managers. It is also used in Financial modeling.

 Syntax:

                  =DURATION(settlement, maturity, coupon, yield, frequency, [basis])

·        Settlement:- (Required value):- The security’s (Shares or Bond) settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. It means the date when the security is possessed to the buyer.

For eg: The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon is matured. For example, suppose a 30-year bond is issued on January 1, 2018, and is purchased by a buyer six months later. The issue date would be January 1, 2018, the settlement date would be July 1, 2018, and the maturity date would be January 1, 2048, which is 30 years after the January 1, 2018, issue date.

·        Maturity:- (Required value):-The security’s maturity date. It is the date when the security (share or bond) is matured.

·        Coupon:– (Required value ):-The security’s annual coupon rate.

·        Yld: (Required value):-Also called Yield. The security’s annual yield.

·        Frequency:–(Required value). The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4

·        Basis :-  (Optional value). The type of day count basis to use.

 

Basis

Day count Basis

0

30/360 (US/NASD)

1

Actual/Actual

2

Actual/360

3

Actual/365 (India )

4

30/360 (European countries)

 

Settlement Date

Maturity Date

Coupon

Yld

Frequency

Basis

DURATION

15-Jan-2017

15-Dec-2017

4.75%

3%

1

3

=DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,1,3)=0.915068493

15-Jan-2017

15-Dec-2017

4.75%

3%

2

3

=DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,2,3)=0.903565842

15-Jan-2017

15-Dec-2017

4.75%

3%

4

3

=DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,4,3)=0.897773246

 

 

 

 

 

 

 

 

20)

 EFFECT:- This function  will calculate the annual interest rate with the number of compounding periods per year. It (EFFECT function) is generally used to compare financial loans with different compounding terms.

 Syntax:    

                 =EFFECT(nominal_rate,npery)

·        Nominal_rate:–It is the nominal or stated interest rate.

·        Npery :– Number of installment in one year (the number of compounding periods in one year.

 Eg:

How to Calculate EFFECT   Manually

 EFFECT = ( 1+ Nominal_rate) ^ Npery

                      —————————————            — (minus) 1

                                  Npery

 

Nominal Rate or

Interest Rate

Npery

EFFECT (Effective Value)

Manual Effective Value

4%

12

=EFFECT(nominal_value,npery) =EFFECT(4%,12)= 0.040741543

EFFECT=(1+NOMINAL_RATE/NPERY)npery   -1

=POWER(1+4%/12,12) -1 = 0.40741543

6%

12

=EFFECT(6%,12)= 0.061677812

0.061677812

9%

12

=EFFECT(9%,12)= 0.093806898

0.093806898

7%

12

 =EFFECT(7%,12)= 0.072290081

0.072290081

5%

12

 =EFFECT(5%,12)= 0.051161898

0.051161898

 

 

Read more

Excel Financial Calculation & Its Meaning II

Excel Financial Calculation & Its Meaning II

Excel Financial Calculation & Its Meaning II Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD In this Post Excel Financial Calculation & Its Meaning II, We will cover some financial terms like PMT, IPMT, PPMT CUMIPMT CUMPRINC DB DDB         … Read more

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 … Read more

Excel Financial Calculation & its Meaning

                                            What is Microsoft Excel Microsoft Excel is a Worksheet which is in the form of rows and columns. It is one of the software application from Microsoft Office Suite (It consist of Microsoft … Read more