FINANCIAL FUNCTIONS
PART 1:- FINANCIAL FUNCTIONS I
PART 2:- FINANCIAL FUNCTIONS II
PART 3:- FINANCIAL FUNCTIONS III
FINANCIAL FUNCTIONS I
Categories of FINANCIAL functions.
1. ACCRINT:- In Microsoft Excel “ACCRINT” function returns the accrued
interest for a fixed security that pays interest periodically.
Syntax of ACCRINT function:-
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])
Arguments:-
issue:- Issue date of the security.
first_interest:- First interest date of security.
settlement:- Settlement date of security.
rate:- Interest rate of the security.
par:- Par value of security.
frequency:- Number of coupon payments per year
(1=annual, 2=semiannual or 4=quarterly)
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
Example of ACCRINT function:-
The following pictures show the example of the ACCRINT function.
2. ACCRINTM:- In Microsoft Excel “ACCRINTM” function returns the accrued
interest for a fixed security that pays interest at maturity (i.e. pays
interest one time only).
Syntax of ACCRINTM function:-
=ACCRINTM(issue,
settlement,rate,par,[basis])
Arguments:-
issue:- Issue date of the security.
settlement:- Settlement date of security.
rate:- Annual coupon rate.
par:- Par value of security.
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
ACCRINTM function.
3. AMORDEGRC:- In
Microsoft Excel “AMORDEGRC” function returns
the depreciation of an asset for each accounting period on a pro rated basis.
Syntax of AMORDEGRC function:-
= AMORDEGRC (cost,date_purchased,first_period,salvage,period,rate,[basis])
Arguments:-
cost:- Cost of asset.
date_purchased:- Purchased date of the asset.
first_period:- End date of the first period.
salvage:- Salvage value of the asset.
period:- Calculate depreciation for the
specific period.
rate:- Percentage rate of depreciation.
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
AMORDEGRC function.
4. AMORLINC:- In Microsoft Excel “AMORLINC” function calculates the
depreciation of an asset for each accounting period.
Syntax of AMORLINC function:-
= AMORLINC(cost,date_purchased,first_period,salvage,period,rate,[basis])
Arguments:-
cost:- Cost of asset.
date_purchased:- Purchased date of the asset.
first_period:- End date of the first period.
salvage:- Salvage value of the asset.
period:- Calculate depreciation for the
specific period.
rate:- Percentage rate of depreciation.
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
AMORLINC function.
5. COUPDAYBS:- In Microsoft Excel “COUPDAYBS” function calculates the
number of days from the starting of a coupons period to the settlement date.
Syntax of COUPDAYBS function:-
=COUPDAYBS(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
Example of COUPDAYBS function:-
The
following pictures show the example of the
COUPDAYBS function.
6. COUPDAYS:- In Microsoft Excel “COUPDAYS” function returns the total
number of days in a coupon period (includes settlement date).
Syntax of COUPDAYS function:-
=COUPDAYS(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
COUPDAYS function.
7. COUPDAYSNC:- In Microsoft Excel “COUPDAYSNC” function returns the
number of days (from the settlement date to the next coupon date).
Syntax of COUPDAYSNC function:-
= COUPDAYSNC(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
COUPDAYSNC function.
8. COUPNCD:- In Microsoft Excel “COUPNCD” function returns the next
coupon date after the settlement date.
Syntax of COUPNCD function:-
= COUPNCD(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
Example of COUPNCD function:-
The
following pictures show the example of the
COUPNCD function.
9. COUPNUM:- In Microsoft Excel “COUPNUM” function returns the number
of coupons payable between the settlement date and maturity date.
Syntax of COUPNUM function:-
= COUPNUM(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
COUPNUM function.
10. COUPPCD:- In Microsoft Excel “COUPPCD” function returns the previous
coupon date before the settlement date.
Syntax of COUPPCD function:-
= COUPPCD(settlement,maturity,frequency,[basis])
Arguments:-
settlement:- Security’s settlement date.
maturity:- Security’s maturity date.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
COUPPCD function.
Syntax of CUMIPMT function:-
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
Arguments:-
rate:- The interest rate per period.
nper:- The total number of payment for the
loan.
pv:- The present value.
start_period:- The first payment for calculation.
end_period:- The last payment for calculation.
type:- Payments argue (0=end of period,
1=beginning of period)
Example of CUMIPMT function:-
The
following pictures show the example of the
CUMIPMT function.
12. CUMPRINC:- In Microsoft Excel “CUMPRINC” function returns the
cumulative principal paid on a loan amount between start period and end period.
Syntax of CUMPRINC function:-
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
Arguments:-
rate:- The interest rate per period.
nper:- The total number of payment for the
loan.
pv:- The present value.
start_period:- The first payment for calculation.
end_period:- The last payment for calculation.
type:- Payments argue (0=end of period,
1=beginning of period)
Example of CUMPRINC function:-
The
following pictures show the example of the
CUMPRINC function.
13. DB:- In Microsoft Excel “DB” function returns the asset’s
depreciation for a specified period using the fixed balance method (the asset’s
depreciation calculation based on asset’s cost, salvage, the number of periods
and the number of months).
Syntax of DB function:-
=DB(cost,salvage,life,period,[month])
Arguments:-
cost:- Cost of asset (initial).
salvage:- The value at the end of the
depreciation.
life:- Asset is depreciated over the
periods.
period:- To calculate depreciation for period.
month:- Number of month (1st
year). It is an optional part and the default is 12.
Example of DB function:-
The
following pictures show the example of the
DB function.
14. DDB:- In Microsoft Excel “DDB” function returns the asset’s
depreciation for a specified period using the double balance method (the
asset’s depreciation calculation based on asset’s cost, salvage, the number of
periods and factor).
Syntax of DDB function:-
=DDB(cost,salvage,life,period,[factor])
Arguments:-
cost:- Cost of asset (initial).
salvage:- The value at the end of the
depreciation.
life:- Asset is depreciated over the
periods.
period:- To calculate depreciation for period.
factor:- The rate at which the balance turn
down. It is an optional part and the default is 2.
Example of DDB function:-
The
following pictures show the example of the
DDBfunction.
15. DISC:- In Microsoft Excel “DISC” function calculates the discount
rate for a security.
Syntax of DISC function:-
=DISC(settlement,maturity,pr,redemption,[basis])
Arguments:-
settlement:- Security settlement date.
maturity:- Security maturity date.
pr:- Security price.
redemption:- Security price per 100 face value.
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
Example of DISC function:-
The
following pictures show the example of the
DISC function.
16. DOLLARDE:- In Microsoft Excel “DOLLARDE” function converts a dollar
price, expressed as a fraction, into a dollar price, expressed as a decimal number.
Syntax of DOLLARDE function
=DOLLARDE(fractional_dollar,fraction)
Arguments:-
fractional_dollar:- Dollar component in specific
fractional notation.
fraction:- Fractional unit of the denominator
(12=1/12, 9=1/9, 32=1/32, etc.)
Example of DOLLARDE function:-
The
following pictures show the example of the
DOLLARDE function.
17. DOLLARFR:- In Microsoft Excel “DOLLARFR” function in a particular fractional
notation. It converts a dollar price in a decimal number format to a dollar
price.
Syntax of DOLLARFR function
=DOLLARFR(decimal_dollar,fraction)
Arguments:-
decimal_dollar:- Normal decimal number (as pricing).
fraction:- Fractional unit of the denominator
(12=1/12, 9=1/9, 32=1/32, etc.)
Example of DOLLARFR function:-
The
following pictures show the example of the
DOLLARFR function.
18. DURATION:- In Microsoft Excel “DURATION” function returns the annual
duration of a security with periodic interest payments.
Syntax of DURATION function
=DURATION(settlement,maturity,coupon,yld,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
coupon:- Security’s annual coupon rate.
yld:- The security’s annual yield.
frequency:- Number of coupon payments per annum
(1=annual, 2=semiannual, 4=quarterly).
basis:- It specifies the day count basis to
be used in the calculation. It is an optional part and if omitted the default
is 0.
|
BASIS |
Day
Count Basis |
|
0
(omitted) |
US
(NASD) 30/360 |
|
1 |
actual/actual |
|
2 |
actual/360 |
|
3 |
actual/365 |
|
4 |
EUROPEAN
30/360 |
The
following pictures show the example of the
DURATION function.
19. EFFECT:- In Microsoft Excel “EFFECT” function returns the effective
annual interest rate (with the arguments of nominal interest rate and the
number of compounding periods per year)
Syntax of EFFECT function:-
=EFFECT(nominal_rate,npery)
Arguments:-
nominal_rate:- Nominal interest rate.
npery:- Number of compounding periods per
year.
Example of EFFECT function:-
The
following pictures show the example of the
EFFECT function.
20. FV:- In Microsoft Excel “FV” function returns the future value
of an investment.
Syntax of FV function:-
=FV(rate,nper,pmt,[pv],[type])
Arguments:-
rate:- The interest rate per period.
nper:- The total number of payments
(periods).
pmt:- The payment made each period (entered
as negative number).
pv:- The present value of future payments.
It is an optional part and if omitted the default is 0.
type:- When payments are deled (0=end of period and 1=beginning of
period). If omitted default is 0.
Example of FV function:-
The following pictures show the example of the FV function.
0 Comments:
Post a Comment