Excel Downloads

Excel Online Consulting

Tips on Excel

Tips on VBA for Excel

Excel Index and Search Tool (Click on the buttons below)

Excel Lesson 23 of 30: Microsoft Excel Financial Functions

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. Enterr 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 interst 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 perion. 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 annal 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".

Excel Index and Search Tool (Click on the buttons below)

Comments: Click Here

Excel VBA Online Consulting

Tips on Excel

Tips on VBA for Excel