Insert Function Assistant
|
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.
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
|
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 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 .
OR
Move the cursor to the cell B4.
Press the /.
Move the cursor to the cell B5.
Press the ;.
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.
OR
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.
OR
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. |