Other Links
This is one of the 33 lessons from the
Tutorial on Excel
Lesson 3-10: Excel Financial Functions and Formulas
There are 53 Excel functions in the Financial category. See them all with a brief description in appendix 18-D
Here are the functions that you will use more often with tips and examples.
| Functions | What it Does |
| FV | Returns the future value of an investment |
| NPER | Returns the number of periods for an investment |
| PMT | Returns the periodic payments |
| RATE | Returns the interest rate per period |
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
|
You can solve a financial problem with other formulas also. In two templates created by www.excel-examples.com a report is automated calculating the value of coupons at each anniversary or each end of year. Download "excel-template-coupons-end.xls" and "excel-template-coupons-ann.xls" |
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. |
|
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. |
|
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. |
|
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;
This is one of the 33 lessons from the
Tutorial on Excel