


The 2 Most Important Excel Functions AND
AND

Lesson 4: Excel Spreadsheets Financial Functions (53)In annex 4 you have found a description of all 53 Excel functions in the "Financial" category. Below is the list of the 4 most useful ones.
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. The RATE Function The question to which RATE brings an answer to is:
Here is the formula in cell A6: 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:
Here is the formula in cell A6: 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:
Here is the formula in cell A6: 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:
Here is the formula in cell A6: 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; Excel Functions and Formulas Sitemap




SUMPRODUCT ¦ INDEX/MATCH ¦ SUBTOTAL ¦ ISERROR ¦ HLOOKUP ¦ LOOKUP ¦ VLOOKUP
AVERAGEIF ¦ AVERAGEIFS ¦ COUNTIF ¦ COUNTIFS ¦ SUMIF ¦ SUMIFS ¦ IFERROR