Excel Financial Functions and Formulas

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

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:
- 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 my payments?
- If I want to have a certain amount of money in the bank at the end of a certain period of time what amount should I save each month?

The final formula looks like this.
=-PMT(B1/12,B2,B3,B4,B5)

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:
- 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?

The final formula looks like this.
=-FV(B1/12,B2,B3,B4,B5)

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:
- 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?

The final formula looks like this.
=-NPER(B1/12,B2,B3,B4,B5)

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:
- What is the real interest rate if they ask me for a certain amount each period to pay a loan?

The final formula looks like this.
=RATE(B1*12,B2,B3,B4,B5)

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
or use the site map link below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel Financial Functions and Formulas