# 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

21)

FV:– FV means Future Value. As the name suggest, It gives (returns) the future value of an investment assuming periodic, constant payment with a constant interest rate.

Note: The value under Bracket in Syntax is optional.

Syntax:

=FV(rate,nper,pmt,[pv],type])

Note:

·        Annuitya fixed amount of money that is paid to somebody each year, usually for the rest of his/her life

·        Rate (required)This is the interest rate for each period.

·        Nper (required)The total number of payment periods.

·        Pmt (Required)- The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

·        PV (optional) – This specifies the present value (PV) of the investment/loan. The PV argument, if omitted, defaults to zero. If we omit the argument, we need to provide the Pmt argument.

·        Type (optional) – This defines whether payments are made at start or end of the year. The argument can either be 0 (payment is made at the end of the period) or 1 (the payment is made at the start of the period).

Remarks

·        You have to very consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.

·        For every arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

We should examine through Excel Sheet function as well as Manually

Excel Sheet function

 Monthly amt (Pmt) 100 Months (nper=number of payment) 6 Rate of Interest (rate) 9% FV (Future Value) =FV(9%/12,6,100,0,1) = 615.95

Manually

Principal Amount= Opening Balance+ Monthly amount

Interest= Principal Amount* Rate of Interest/12 (as we have to calculate interest in month)

100*9%/12 = 100*9/100*12 = 0.75

Principal with Interest = Principal Amount+ Interest

 S.No Months Opening Balance Monthly Amount Principal Amount Interest Principal with Interest 1 January 0 100 0+100= 100 0.75 100+0.75= 100.75 2 February 100.75 100 100.75+100=200.75 1.505625 200.75+1.505625=202.255625 3 March 202.255625 100 202.255625+100=302.255625 2.2669171875 302.255625+2.2669171875=304.5225421875 4 April 304.5225421875 100 304.5225421875+100=404.5225421875 3.03391906640625 404.5225421875+3.03391906640625= 407.5564612539063 5 May 407.5564612539063 100 407.5564612539063+100=507.5564612539063 3.806673459404297 507.5564612539063+3.806673459404297= 511.3631347133106 6 June 511.3631347133106 100 511.3631347133106+100=611.3631347133106 4.58522351034983 611.3631347133106+4.58522351034983 = 615.9483582236604 = 615.95 FV FV Future Value 615.95

So, We calculated both in Excel Sheet and Manually and the result is same.

22)

FVSCHEDULE:– It calculates the future value of an investment with a variable or adjustable rate. In financial analysis, we have to make a decision on investments made by a company. Sometimes, we make investments that will guarantee a certain percentage for the first year, say 5%, 6% on the second year, etc. In such case, we need to evaluate the investment, which can be done using FVSCHEDULE function.

Syntax:

=FVSCHEDULE(principal, schedule)

·        Principal (required): The present value of the Investment.

·        Schedule (required): It is basically an array of rate of interest to be applied to the principal

We will  check if Excel calculation using FVSCHEDULE and Manually calculation are same

Excel Calculation

 Initial Investment (Principal) 5000000 1st Year (Rate of Interest) 5% 2nd Year (Rate of Interest) 3.5% 3rd Year (Rate of Interest) 3.5% 4th Year (Rate of Interest) 3% 5th Year (Rate of Interest) 3% FVSCHEDULE =FVSCHEDULE(5000000,1styearInterestrate:5thyearinterestrate) = =FVSCHEDULE(5000000,5%:3%)=   5966428.663 = 5966429

Manually

Initial Investment —- 5000000

1st Year

Rate of Interest= 5%

5000000*5% = 5000000*5/100 = 5000000*.05= 250000

Interest on 01st Year= 250000

Principal with Interest = 5000000+250000= 5250000

2nd year

Initial Investment of the 2nd Year= Principal with Interest of the 01st Year =5250000

Rate of Interest=3.5%

5250000*3.5% = 5250000*3.5/100=5250000*.035= 183750

Interest of the 2nd Year=183750

Principal with Interest=5250000+183750= 5433750

3rd year

Initial Investment of the 3rd Year=Principal with Interest of the 2nd Year =5433750

Rate of Interest=3.5%

5433750*3.5% = 5433750*3.5/100= 5433750*.035= 190181.25

Interest of the 3rd Year = 190181.25

Principal with Interest= 5433750+190181.25 = 5623931.25

4th Year

Initial Investment of the 4th Year= Principal with Interest of the 3rd Year=5623931.25

Rate of Interest=3%

5623931.25*3% =5623931.25*3/100=5623931.25*.03=168717.9375

Interest of the 4th Year=168717.9375

Principal with Interest=5623931.25+168717.9375= 5792649.1875

5th Year

Initial Investment of the 5th Year= Principal with Interest of the 4th Year=5792649.1875

Rate of Interest=3%

5792649.1875*3% = 5792649.1875*3/100= 5792649.1875*.03=173779.475625

Interest of the 5th Year=173779.475625

Principal with Interest=5792649.1875+173779.475625 = 5966428.663125 = 5966428.663 =5966429

FVSCHEDULE= 5966428.663 =5966429