FINANCIAL FUNCTIONS
PART 1:- FINANCIAL FUNCTIONS I
PART 2:- FINANCIAL FUNCTIONS II
PART 3:- FINANCIAL FUNCTIONS III
FINANCIAL FUNCTIONS III
Categories of FINANCIAL functions.
|
41. RATE:- In Microsoft Excel “RATE” function returns the interest rate per period of a loan or an investment.
Syntax of RATE function
=RATE(nper,pmt,pv,[fv],[type],[guess])
Arguments:-
nper:- The total number of payment periods.
pmt:- The payment made each period.
pv:- The present value.
fv:- The cash balance is required after the last payment is made. It is an optional part and default is 0.
type:- When payments are due (0=end of period and 1= beginning of period). It is an optional part and default is 0.
guess:- Your guess on the rate. It is an optional part and default is 10%.
Example of RATE function:-
The following pictures show the example of the RATE function.
42. RECEIVED:- In Microsoft Excel “RECEIVED” function returns the amount received at the time of maturity.
Syntax of RECEIVED function
=RECEIVED(settlement,maturity,investment,discount,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
investment:- The amount investment as security.
discount:- The security’s discount rate.
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 RECEIVED function:-
The following pictures show the example of the RECEIVED function.
43. SLN:- In Microsoft Excel “SLN” function returns the depreciation of an asset for one period (calculated in straight line method).
Syntax of SLN function
= SLN(cost,salvage,life)
Arguments:-
cost:- Cost of the asset.
salvage:- The value at the end of the depreciation.
life:- Periods over which asset is depreciated.
Example of SLN function:-
The following pictures show the example of the SLN function.
44. SYD:- In Microsoft Excel “SYD” function returns the “some-of-years” depreciation for an asset for a given period.
Syntax of SYD function
=SYD(cost,salvage,life,per)
Arguments:-
cost:- Cost of the asset.
salvage:- The value at the end of the depreciation.
life:- Periods over which asset is depreciated.
per:- The period to calculate depreciation.
Example of SYD function:-
The following pictures show the example of the SYD function.
45. TBILLEQ:- In Microsoft Excel “TBILLEQ” function returns the bond equivalent yield for a treasury bill.
Syntax of TBILLEQ function
=TBILLEQ(settlement,maturity,discount)
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
discount:- The security’s discount rate.
Example of TBILLEQ function:-
The following pictures show the example of the TBILLEQ function.
46. TBILLPRICE:- In Microsoft Excel “TBILLPRICE” function returns the price per $100 face value of a Treasury Bill.
Syntax of TBILLPRICE function
=TBILLPRICE(settlement,maturity,discount)
Arguments:-
settlement:- The settlement date of the Treasury Bill (the date that the bill is purchased).
maturity:- The maturity date of the Treasury Bill (the date that the bill period is end).
discount:- The discount rate of Treasury Bill.
Example of TBILLPRICE function:-
The following pictures show the example of the TBILLPRICE function.
47. TBILLYIELD:- In Microsoft Excel “TBILLYIELD” function returns the yield for a Treasury Bill
Syntax of TBILLYIELD function
=TBILLYIELD(settlement,maturity,pr)
Arguments:-
settlement:- The settlement date of the Treasury Bill (the date that the bill is purchased).
maturity:- The maturity date of the Treasury Bill (the date that the bill period is end).
pr:- Price per $100.
Example of TBILLYIELD function:-
The following pictures show the example of the TBILLYIELD function.
48. VDB:- In Microsoft Excel “VDB” function returns the depreciation of an asset for a given period using the double – declining balance method. VDB stands for Variable Declining Balance.
Syntax of VDB function
=VDB(cost,salvage,life,start_period,end_period,[factor],[no_switch])
Arguments:-
cost:- Cost of asset.
salvage:- The end of the assets depreciation value.
life:- Asset depreciated periods.
start_period:- The starting period.
end_period:- The end period.
factor:- A rate at which the balance declines. It is an optional part and if omitted the default is 2.
no_switch:- Don’t switch to straight line. It is an optional part and if omitted default is FALSE.
Example of VDB function:-
The following pictures show the example of the VDB function.
49. XIRR:- In Microsoft Excel “XIRR” function returns the Internal Rate of Return (IRR) for a series of cash flows that occurs irregular intervals.
Syntax of XIRR function
=XIRR(values,dates,[guess])
Arguments:-
values:- Contains cash flow series (array).
dates:- The dates reference that correspond to cash flows.
guess:- An estimate for expected IRR. It is an optional part and if omitted then default is 1 (10%).
Example of XIRR function:-
The following pictures show the example of the XIRR function.
50. XNPV:- In Microsoft Excel “XNPV” function calculates the Net Present Value (NPV) of an investment by using discount rate and a series of cash flows with dates.
Syntax of XNPV function
=XNPV(rate,values,dates)
Arguments:-
rate:- The discount rate for cash flows.
values:- The values representing cash flows.
dates:- Dates that indicates to cash flows.
Example of XNPV function:-
The following pictures show the example of the XNPV function.
51. YIELD:- In Microsoft Excel “YIELD” function calculates the yield of a security that pays periodic interest.
Syntax of YIELD function
=YIELD(settlement,maturity,rate,pr,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
rate:- The security’s annual coupon rate.
pr:- The security’s price per $100 face value.
redemption:- Redemption value per $100 face value.
frequency:- Number of coupon payments per year (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 YIELD function:-
The following pictures show the example of the YIELD function.
52. YIELDDISC:- In Microsoft Excel “YIELDDISC” function returns the annual yield for a discounted security.
Syntax of YIELDDISC function
= YIELDDISC(settlement,maturity,pr,redemption,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
pr:- The security’s price per $100 face value.
redemption:- Redemption value 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 YIELDDISC function:-
The following pictures show the example of the YIELDDISC function.
53. YIELDMAT:- In Microsoft Excel “YIELDMAT” function returns the annual yield of a security that pays interest at maturity.
Syntax of YIELDMAT function
=YIELDMAT(settlement,maturity,issue,rate,pr,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
issue:- The security’s issue date.
rate:- The security’s annual coupon rate.
pr:- The security’s 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 |
The following pictures show the example of the YIELDMAT function.
0 Comments:
Post a Comment