FINANCIAL FUNCTIONS
PART 1:- FINANCIAL FUNCTIONS I
PART 2:- FINANCIAL FUNCTIONS II
PART 3:- FINANCIAL FUNCTIONS III
FINANCIAL FUNCTIONS II
Categories of FINANCIAL functions.
21. FVSCHEDULE:- In Microsoft Excel “FVSCHEDULE” function returns the future value of an initial principal after applying a series of compound interest rates.
Syntax of FVSCHEDULE function:-
=FVSCHEDULE(principal,schedule)
Arguments:-
principal:- The investment amount.
schedule:- A range or array of interest rates.
Example of FVSCHEDULE function:-
The following pictures show the example of the FVSCHEDULE function.
FVSCHEDULE function calculates future value of an investment of 10,000 over 5 years. The investment earns 3% interest during first year and 5%,7%,9% and 11% interest during 2nd, 3rd, 4th and 5th years respectively.
22. INTRATE:- In Microsoft Excel “INTRATE” function returns the interest rate for a invested security.
Syntax of INTRATE function:-
=INTRATE(settlement,maturity,investment,redemption,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
investment:- The principal amount.
redemption:- The amount at the time of maturity.
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 INTRATE function:-
The following pictures show the example of the INTRATE function.
23. IPMT:- In Microsoft Excel “IPMT” function calculates the interest rate of a said loan payment in a specific payment period.
Syntax of IPMT function:-
=IPMT(rate,per,nper,pv,[fv],[type])
Arguments:-
rate:- Rate of interest per period.
per:- Interest payment period.
nper:- The total number of payment periods.
pv:- The present loan 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.
Example of IPMT function:-
The following pictures show the example of the IPMT function.
24. IRR:- In Microsoft Excel “IRR” function returns the internal rate of return for a series of cash flows.
Syntax of IRR function:-
=IRR(values,[guess])
Arguments:-
values:- Reference to cell that contains range of values.
guess:- An estimate percentage for expected internal rate of return. It is an optional part; default is 10% or 0.1.
Example of IRR function:-
The following pictures show the example of the IRR function.
25. ISPMT:- In Microsoft Excel “ISPMT” function calculates the interest to be paid during a specific period of an investment (where principal amounts are equal).
Syntax of ISPMT function:-
=ISPMT(rate,per,nper,pv)
Arguments:-
rate:- Interest
rate.
per:- Period
(starts with 0).
nper:- Number of periods.
pv:- Present value (in negative figure).
Example of ISPMT function:-
The following pictures show the example of the ISPMT function.
26. MDURATION:- In Microsoft Excel “MDURATION” function returns the Macauley modified duration for a security with an assumed par value of $100.
Syntax of MDURATION function:-
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
coupon:- The security’s annual coupon rate.
yld:- The security’s annual yield.
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 MDURATION function:-
The following pictures show the example of the MDURATION function.
27. MIRR:- In Microsoft Excel “MIRR” function that returns the Modified Internal Rate of Return (MIRR) for a series of cash flows.
Syntax of MIRR function:-
=MIRR(values,finance_rate,reinvest_rate)
Arguments:-
values:- Reference to cell that contains cash flows.
finance_rate:- Required rate of return as percentage (discount rate).
reinvest_rate:- Interest rate received on reinvestment as percentage on cash flows.
Example of MIRR function:-
The following pictures show the example of the MIRR function.
28. NOMINAL:- In Microsoft Excel “NOMINAL” function returns the nominal interest rate, given an effective annual interest rate and the number of compounding periods per year.
Syntax of NOMINAL function:-
=NOMINAL(effect_rate,npery)
Arguments:-
effect_rate:- The effective annual interest rate.
npery:- Number of compounding periods per year.
Example of NOMINAL function:-
The following pictures show the example of the NOMINAL function.
29. NPER:- In Microsoft Excel “NPER” function returns the number of periods for loan value or you can get the number of payment periods for a loan amount.
Syntax of NPER function:-
=NPER(rate,pmt,pv,[fv],[type])
Arguments:-
rate:- The interest rate (per period).
pmt:- Payment paid for each period.
pv:- Loan amount or present value.
fv:- The future value (after the last payment is made). It is an optional part 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.
Example of NPER function:-
The following pictures show the example of the NPER function.
30. NPV:- In Microsoft Excel “NPV” function calculates the net present value of an investment using a discount rate and cash flow series.
Syntax of NPV function:-
=NPV(rate,value1,[value2],….)
Arguments:-
rate:- Discount rate over period.
value1:- Representing the first value of cash flows.
value2:- Representing the second value of cash flows. It is an optional part.
Example of NPV function:-
The following pictures show the example of the NPV function.
31. ODDFPRICE:- In Microsoft Excel “ODDFPRICE” function calculates the price per $100 face value of a security with an odd first period.
Syntax of ODDFPRICE function:-
=ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
issue:- The security’s issue date.
first_coupon:- First coupon rate.
rate:- The security’s annual coupon rate.
yld:- Annual required rate of return.
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 ODDFPRICE function:-
The following pictures show the example of the ODDFPRICE function.
32. ODDFYIELD:- In Microsoft Excel “ODDFYIELD” function returns the yield of the security with an odd first period.
Syntax of ODDFYIELD function:-
=ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
issue:- The security’s issue date.
first_coupon:- First coupon rate.
rate:- The security’s annual coupon rate.
pr:- Price of security.
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 ODDFYIELD function:-
The following pictures show the example of the ODDFYIELD function.
33. ODDLPRICE:- In Microsoft Excel “ODDLPRICE” function calculates the price per $100 face value of a security with an odd last period.
Syntax of ODDLPRICE function:-
=ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
last_interest:- The last interest date of security.
rate:- The security’s annual coupon rate.
yld:- Annual required rate of return.
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 ODDLPRICE function:-
The following pictures show the example of the ODDLPRICE function.
34. ODDLYIELD:- In Microsoft Excel “ODDLYIELD” function returns the yield of security with an odd last period.
Syntax of ODDLYIELD function:-
=ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
last_interest:- The last interest date of security.
rate:- The security’s annual coupon rate.
pr:- Price of security.
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 ODDLYIELD function:-
The following pictures show the example of the ODDLYIELD function.
35. PMT:- In Microsoft Excel “PMT” function returns a periodic payment for a loan.
Syntax of PMT function:-
=PMT(rate,per,pv,[fv],[type])
Arguments:-
rate:- Rate of interest per period.
nper:- The number of periods.
pv:- The present loan 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.
Example of PMT function:-
The following pictures show the example of the PMT function.
36. PPMT:- In Microsoft Excel “PPMT” function calculates the principal of a given loan payment.
Syntax of PPMT function:-
=PPMT(rate,per,nper,pv,[fv],[type])
Arguments:-
rate:- The period interest rate.
per:- The payment period of interest.
nper:- The total number of payments (for loan).
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.
Example of PPMT function:-
The following pictures show the example of the PPMT function.
37. PRICE:- In Microsoft Excel “PRICE” function returns the price per $100 face value of a security that pays periodic interest.
Syntax of PRICE function:-
=PRICE(settlement,maturity,rate,yld,redemption,frequency,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
rate:- The security’s annual coupon rate.
yld:- Annual required rate of return.
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 PRICE function:-
The following pictures show the example of the PRICE function.
38. PRICEDISC:- In Microsoft Excel “PRICEDISC” function calculates the price per $100 face value of a discounted security.
Syntax of PRICEDISC function:-
= PRICEDISC(settlement,maturity,discount,redemption,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
discount:- The security’s discount rate.
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 PRICEDISC function:-
The following pictures show the example of the PRICEDISC function.
39. PRICEMAT:- In Microsoft Excel “PRICEMAT” function returns the price per $100 face value of a security that pays interest at maturity.
Syntax of PRICEMAT function:-
= PRICEMAT(settlement,maturity,issue,rate,yld,[basis])
Arguments:-
settlement:- The security’s settlement date.
maturity:- The security’s maturity date.
issue:- The security’s issue date.
rate:- The security’s interest rate.
yld:- Annual yield 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 |
Example of PRICEMAT function:-
The following pictures show the example of the PRICEMAT function.
40. PV:- In Microsoft Excel “PV” function returns the present value of an investment.
Syntax of PV function:-
= PV(rate,nper,pmt,[fv],[type])
Arguments:-
rate:- The period interest rate.
nper:- The total number of payment periods.
pmt:- The payment made each period.
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.
Example of PV function:-
The following pictures show the example of the PV function.
0 Comments:
Post a Comment