|
|
Lesson 4: Excel Spreadsheets Financial Functions (53)
| Frequency * |
Functions |
What it Does |
| Rarely Used |
FV |
Returns the future value of an
investment |
| Rarely Used |
NPER |
Returns the number of periods
for an investment |
| Rarely Used |
PMT |
Returns the periodic payment for
an annuity |
| Rarely Used |
RATE |
Returns the interest rate per
period of an annuity |
| Rarely Used |
ACCRINT |
Returns the accrued interest for
a security that pays periodic interest |
| Rarely Used |
ACCRINTM |
Returns the accrued interest for
a security that pays interest at maturity |
| Rarely Used |
AMORDEGRC |
Returns the depreciation for
each accounting period by using a depreciation coefficient |
| Rarely Used |
AMORLINC |
Returns the depreciation for
each accounting period |
| Rarely Used |
COUPDAYBS |
Returns the number of days from
the beginning of the coupon period to the settlement date |
| Rarely Used |
COUPDAYS |
Returns the number of days in
the coupon period that contains the settlement date |
| Rarely Used |
COUPDAYSNC |
Returns the number of days from
the settlement date to the next coupon date |
| Rarely Used |
COUPNCD |
Returns the next coupon date
after the settlement date |
| Rarely Used |
COUPNUM |
Returns the number of coupons
payable between the settlement date and maturity date |
| Rarely Used |
COUPPCD |
Returns the previous coupon date
before the settlement date |
| Rarely Used |
CUMIPMT |
Returns the cumulative interest
paid between two periods |
| Rarely Used |
CUMPRINC |
Returns the cumulative principal
paid on a loan between two periods |
| Rarely Used |
DB |
Returns the depreciation of an
asset for a specified period using the fixed-declining balance method |
| Rarely Used |
DDB |
Returns the depreciation of an
asset for a specified period using the double-declining balance method or
some other method you specify |
| Rarely Used |
DISC |
Returns the discount rate for a
security |
| Rarely Used |
DOLLARDE |
Converts a dollar price, expressed as a fraction, into a dollar price,
expressed as a decimal number |
| Rarely Used |
DOLLARFR |
Converts a dollar price, expressed as a decimal number, into a dollar
price, expressed as a fraction |
| Rarely Used |
DURATION |
Returns the annual duration of a
security with periodic interest payments |
| Rarely Used |
EFFECT |
Returns the effective annual
interest rate |
| Rarely Used |
FVSCHEDULE |
Returns the future value of an
initial principal after applying a series of compound interest rates |
| Rarely Used |
INTRATE |
Returns the interest rate for a
fully invested security |
| Rarely Used |
IPMT |
Returns the interest payment for
an investment for a given period |
| Rarely Used |
IRR |
Returns the internal rate of
return for a series of cash flows |
| Rarely Used |
ISPMT |
Returns the interest paid during
a specific period of an investment |
| Rarely Used |
MDURATION |
Returns the Macauley
modified duration for a security with an assumed par value of $100 |
| Rarely Used |
MIRR |
Returns the internal rate of
return where positive and negative cash flows are financed at different rates |
| Rarely Used |
NOMINAL |
Returns the annual nominal
interest rate |
| Rarely Used |
NPV |
Returns the net present value of
an investment based on a series of periodic cash flows and a discount rate |
| Rarely Used |
ODDFPRICE |
Returns the price per $100 face
value of a security with an odd first period |
| Rarely Used |
ODDFYIELD |
Returns the yield of a security
with an odd first period |
| Rarely Used |
ODDLPRICE |
Returns the price per $100 face
value of a security with an odd last period |
| Rarely Used |
ODDLYIELD |
Returns the yield of a security
with an odd last period |
| Rarely Used |
PPMT |
Returns the payment on the
principal for an investment for a given period |
| Rarely Used |
PRICE |
Returns the price per $100 face
value of a security that pays periodic interest |
| Rarely Used |
PRICEDISC |
Returns the price per $100 face
value of a discounted security |
| Rarely Used |
PRICEMAT |
Returns the price per $100 face
value of a security that pays interest at maturity |
| Rarely Used |
PV |
Returns the present value of an
investment |
| Rarely Used |
RECEIVED |
Returns the amount received at
maturity for a fully invested security |
| Rarely Used |
SLN |
Returns the straight-line
depreciation of an asset for one period |
| Rarely Used |
SYD |
Returns the sum-of-years' digits
depreciation of an asset for a specified period |
| Rarely Used |
TBILLEQ |
Returns
the bond-equivalent yield for a Treasury bill |
| Rarely Used |
TBILLPRICE |
Returns the price per $100 face
value for a Treasury bill |
| Rarely Used |
TBILLYIELD |
Returns the yield for a Treasury
bill |
| Rarely Used |
VDB |
Returns the depreciation of an
asset for a specified or partial period using a declining balance method |
| Rarely Used |
XIRR |
Returns the internal rate of
return for a schedule of cash flows that is not necessarily periodic |
| Rarely Used |
XNPV |
Returns the net present value
for a schedule of cash flows that is not necessarily periodic |
| Rarely Used |
YIELD |
Returns the yield on a security
that pays periodic interest |
| Rarely Used |
YIELDDISC |
Returns the annual yield for a
discounted security for example, a Treasury bill |
| Rarely Used |
YIELDMAT |
Returns the annual yield
of a security that pays interest at maturity |
The financial functions are among the less user friendly, the less intuitive in Excel. Here are 4 examples trying to clarify the matter.
The RATE Function
The question to which RATE brings an answer to is:
- What is the real interest rate if they ask me for a certain amount each period to pay a loan?
|
|
A
|
Descriptions |
|
1
|
48
|
Number of periods (years, months, weeks..etc) |
|
2
|
$550
|
Periodic payment |
|
3
|
$24,000
|
Total amount of loan |
|
4
|
0
|
The balance left to pay at the end of the period. If you omit this argument Excel uses "0".
|
|
5
|
0
|
Payment made at the beginning of the period (1) or at the end of the period (0). If you omit this argument Excel uses "0" saying that the payment is made at the end of each period which is usually the reality when you borrow money.
|
|
6
|
5.00%
|
The result with the formula using the RATE function.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2 |
Here is the formula in cell A6:
=RATE(A1,-A2,A3,A4,A5)*12
Notes on the formula: The payment argument is negative (-A2); If you use months as periods and you want an annual rate you multiply by 12, if you use a years as periods and you want an annual rate you don't multiply......; If you don't use the "Percentage" format in cell A6 the result of this example will be 0.05; The formula could also be =RATE(A1,-A2,A3)*12 the arguments in A4 and A5 being optional
The PMT Function
The question to which PMT brings an answer to is:
- If I borrow a certain amount of money and I want it repaid at the end of a certain period of time what will be the periodic payment?
|
|
A
|
Descriptions |
|
1
|
5.00%
|
The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2
|
|
2
|
48
|
Number of periodic payments (years, months, weeks) |
|
3
|
$24,000
|
Total amount of loan |
|
4
|
0
|
The balance left to pay at the end of the period. If you omit this argument Excel uses "0".
|
|
5
|
0
|
Payment made at the beginning of the period (1) or at the end of the period (0). If you omit this argument Excel uses "0" saying that the payment is made at the end of each period which is usually the reality when you borrow money.
|
|
6
|
-$550.41
|
The result with the formula using the PMT function.
|
Here is the formula in cell A6:
=PMT(A1/12,A2,A3,A4,A5)
Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =PMT(A1/12,A2,A3) the arguments in A4 and A5 being optional; If you want the payment to show as a positive value add a minus sign before the equal sign (=-PMT(A1/12,A2,A3,A4,A5))
The FV Function (Future value)
The question to which FV brings an answer to is:
- If I put a certain amount of money in the bank each month how much money will I have saved at the end of a certain period of time?
|
|
A
|
Descriptions |
|
1
|
5.00%
|
The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2
|
|
2
|
48
|
Number of periodic deposits (years, months, weeks) |
|
3
|
$550
|
Amount of periodic deposits |
|
4
|
$0
|
Beginning balance. If you omit this argument Excel uses "0".
|
|
5
|
1
|
Deposits made at the beginning of the period (1) or at the end (0). If you omit this argument Excel uses "0". In the case of the FV function make sure that you enter "1".
|
|
6
|
-$29,279.68
|
The result with the formula using the FV function.
|
Here is the formula in cell A6:
=FV(A1/12,A2,A3,A4,A5)
Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =FV(A1/12,A2,A3) the arguments in A4 and A5 being optional; If you want the RESULT to show as a positive value add a minus sign before the equal sign (=-FV(A1/12,A2,A3,A4,A5))
The NPER Function
The question to which NPER brings an answer to is:
- How many months would it take me to repay a certain loan at a certain interest rate if I pay a certain amount each month?
|
|
A
|
Descriptions |
|
1
|
5.0%
|
The annual interest rate.
Note: the format of this cell must be "Percentage" with any number of decimals. In this example the number of decimals is 2
|
|
2
|
$550
|
Periodic payment |
|
3
|
$24,000
|
Total amount of loan |
|
4
|
0
|
The balance left to pay at the end of the period. If you omit this argument Excel uses "0".
|
|
5
|
0
|
Payment made at the beginning of the period (1) or at the end (0). If you omit this argument Excel uses "0".
|
|
6
|
48.26
|
The result with the formula using the NPER function.
|
Here is the formula in cell A6:
=NPER(D1/12,-D2,D3,D4,D5)
Notes on the formula: If you don't use the "Percentage" format in cell A1 enter 0.05; The second argument MUST BE NEGATIVE; If you use months as periods the rate must be divided by 12 (A1/12), if you use weeks then you divide by 52 (A1/52), if there are 4 payments per year you will divide the rate by 4 (A1/4)and if the payment is annual you don't divide the rate argument (A1) ; The formula could also be =NPER(A1/12,A2,A3) the arguments in A4 and A5 being optional;
Financial Functions in Excel
Links to the Top of the Page
If you are using a version of Excel earlier than 2007 you need to activate the "Excel Analysis Toolpack" to make some of the functions above available.
Excel Functions and Formulas Sitemap
Downloadable Tutorials, Examples and Templates
|
