What's new


Office button
Page Layout

Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Pivot table
Scenario Manager


Excel 2003


Demonstration files
Texte en français


By e-mail
Join our Newsletter

Share this page

Excel - Use an Excel function

Before we start
Insert Function Assistant
Write a function


Before we start

To follow this exercise, you can use the file demo-excel2007-1.xlsx on the demonstrations files web page.

*Select the worksheet PMT.


Insert Function Assistant

In this next example, you will use the Insert Function box to find the Excel function that you need to find how much your payments will be if you borrow 200 000$ from the bank at a rate of 5,5% for a 25 year mortgage. You will be making payments every two weeks or 26 payments per year.

*Insert the text and values in the appropriate cells of a worksheet.
*Place the cursor in the B8 cell.
*Select the Formula tab.
*Press the Insert Function button.

Excel 2007 : Insérer la function VPM

The function assistant window will appear. In the first box, you will enter the search terms for the function you need.

*For this example, enter "mortgage payments".

*Make sur that All categories is selected. Otherwise, Excel will only look in the selected function categories instead of all possible functions.

As soon as you select all categories, the list of every possible functions will appear in the box below in alphabetical order. You can select of the function from the list and see a description of it just belowthe box. You can also press the Help on ths function link at the bottom of the window.

*Press the OK button next to the search box at the top of the window.

A list of functions will appear in the box below that could be what you're searching.


*From the list of available functions, select the PMT function.
*Press the OK button.

Excel 2007 : Insérer the arguments of a function

* In the Rate box, enter the formula B4/B5.

Don't forget that you must enter the interest rate per period and not per year. That's why you need to divide the rate by the number of payments per year.

* In the Nper box, enter the formula B5*B6.

To determine the total number of payments, you need to know the number of payments per year multiplied by the number of years of the mortgage.

*In the box PV, enter -B3.

PV represente the present value. Without the minus sign ( - ) in front the cell address with the borrowed amount (B3), the result of the PMT function would be negative. It's easier to understand the amount borrowed when it's a positive value. You will see at the bottom of the window the result of the function. In this case, it's 539,32 $. As soon as you enter the informations to the three firsts arguments of this function, the one with the titles in bold, you will be able to see a result. You can still use the other two arguments to get a result that takes into consideration the added information.

Write the function

After a while, you know and understand how functions work.The next step consists to write this formula "by hand", without using the Insert Function assistant; only by using the keyboard.

*Place the cursor in B9.
*Enter =p

Excel 2007 : Assistant lors de l'écriture d'une formule

Even when you write a formula, Excel offers you assistance. You can choose a function within the list by using TAB or the tabulation button on the keyboard.

*Continue writting the formula by entering =pm

Excel 2007 : Assististant d'écriture de function - Liste de fonctions

Excel understands that you want to use the PMT function. It offers you to continue entering the formula by showing you the names of the argument to enter. The argument in bold is what you can presently enter. According to the picture frame seen bellow, you have to enter the interest rate per period of payment. There are a few ways you can enter this information.

*Enter B4/B5 .
*Move the cursor to the cell B4.
*Press the /.
*Move the cursor to the cell B5.

*Press the ;.

Excel 2007 : Passage au deuxième paramètre de la function

The ; indicates to Excel to go to the next argument of the function. You now have to determine the number of payments requires to reimburse the total amount of the mortgage. In this particular case, it's the number of payments per year multiplied by the number of years.

*Enter B5*B6.
*Move the cursor to the cell B5.
*Press the *.
*Move the pointer to the cell B6.

*Press the ;.

You now need to write the amount of the mortgage in the cell B3.

*Enter B3.
*Move the cursor to the cell B3.

*Press the key ).
*Press the Enter key.

You have just finished entering the formula by using the keyboard only and possibly the mouse. The insert formula assistant is very good when you're searching for a function that suits your needs. But once you know how to apply that function, you no longer need it. You can simply write the function in a cell. Even then, a small assistant will remind you of the order that you have to enter the arguments.

By the way, did you know that the word mortgage actually comes from two French words: mort (death) and gage (borrow). Does the word mortgage makes more sense to you now? Please don't ever over borrow. Don't ever come close to the maximum limit that a banker offers you. Life will bring it's share of unfortunate events: illness, disasters, job loss and much much more. You may need to borrow more money to face these hard times.


You like what you read? Share it with your friends.

This site is hosted by 1&1.com