Excel Financial Functions and Formulas
|
613-749-4695 |
Tips and Ideas on Excel |
Tips and Ideas on Macros |
Excel Chapter 16 of 24: Excel Financial Functions and Formulas Excel offers you 53 functions in the financial category. Here are the ones (4) that you will use more often. |
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 |
PMT The two questions
to which PMT brings an answer to are: The final formula
looks like this. The formula above sits in cell B7 of my spreadsheet. Notice the minus (-) sign at the beginning of the formula. If you do this in a workbook the result in cell B7 should be $436.70. Follow the instructions below: So enter the following values in column B. In B1: The annual interest rate. Remember that 4.90% is really .049 so if the format of your cell is "Percentage" you can enter 4.9 but if the format is "General" or "Number" you need to write .049. Once the rate is entered in cell B1 notice that in the formula I just don't enter "B1" as first argument but " B1/12" . I do this because my payments are monthly so the rate that applies is 1/12 of the annual rate. In B2: Number of payments. In my example I entered 48 (monthly over 4 years). If over 4 years I would make payments each 3 months I would enter 12 in cell B2 and B1/4 in the formula. In B3: The total amount to be paid $19,000 In B4: The balance or what part of the loan wont be paid at the end of 48 months. Enter 0 In B5: Will the payment be made at the beginning of the period (1) or at the end (0). Enter 1 Select cell B7 go to the menu bar and click on "Insert/Function" and select PMT. Submit the addresses B1 to B5 in the 5 text boxes. Click "Enter" . FV The question
to which FV brings an answer to is: The final formula
looks like this. The formula above sits in cell B7 of my spreadsheet. Notice the minus (-) sign at the beginning of the formula. If you do this in a workbook the result in cell B7 should be $26,507.44. Follow the instructions below: So enter the following values in column B. In B1: The annual interest rate. Remember that 4.90% is really .049 so if the format of your cell is "Percentage" you can enter 4.9 but if the format is "General" or "Number" you need to write .049. Once the rate is entered in cell B1 notice that in the formula I just don't enter "B1" as first argument but "B1/12" . I do this because my payments are monthly so the rate that applies is 1/12 of the annual rate. In B2: Number of deposits. In my example I entered 48 (monthly over 4 years). In B3: The amount of the deposits In B4: The balance in the account (0 if you start from scratch). Enter 0 In B5: Will the deposits be made at the beginning of the period (1) or at the end (0). Enter 1 Select cell B7 go to the menu bar and click on "Insert/Function" and select PMT. Submit the addresses B1 to B5 in the 5 text boxes. Click "Enter" . NPER The question
to which NPER brings an answer to is: The final formula
looks like this. The formula above sits in cell B7 of my spreadsheet. Notice the minus (-) sign at the beginning of the formula. If you do this in a workbook the result in cell B7 should be 14.547 periods. Follow the instructions below: So enter the following values in column B. In B1: The annual interest rate. Remember that 4.90% is really .049 so if the format of your cell is "Percentage" you can enter 4.9 but if the format is "General" or "Number" you need to write .049. Once the rate is entered in cell B1 notice that in the formula I just don't enter "B1" as first argument but "B1/12" . I do this because my payments are monthly so the rate that applies is 1/12 of the annual rate. In B2: The amount of the payments. In B3: The amount of the loan. In B4: The balance left to pay at the end of the period. Enter 0 In B5: Will the deposits be made at the beginning of the period (1) or at the end (0). Enter 1 Select cell B7 go to the menu bar and click on " Insert/Function" and select PMT. Submit the addresses B1 to B5 in the 5 text boxes. Click " Enter" . RATE The question
to which RATE brings an answer to is: The final formula
looks like this. The formula above sits in cell B7 of my spreadsheet. If you do this in a workbook the result in cell B7 should be 14.547 periods. Follow the instructions below: So enter the following values in column B. In B1: The number of period. If you want an annual rate for monthly payment you will enter the number of period multiplied by 12. In B2: The amount of the loan. In B3: The amount of the payments. In B4: The balance left to pay at the end of the period. Enter 0 In B5: Will the payments be made at the beginning of the period (1) or at the end (0). Enter 1 Select cell B7 go to the menu bar and click on "Insert/Function" and select PMT. Submit the addresses B1 to B5 in the 5 text boxes. Click "Enter". |
Next Chapter: Information Functions and Formulas in Excel |
Excel Table of Contents |
Discover Even More in 50 Excel spreadsheets |
|
613-749-4695 |
Tips and Ideas on Excel |
Tips and Ideas on Macros |
Excel Financial Functions and Formulas