Excel provides a number of functions which can be
used when entering formulas to simplify complex
calculations. In addition, Excel uses a Paste Function tool
to help you fill in the necessary values. In this tutorial,
we'll be learning how to calculate a fictitious car loan payment
by using Paste Function.
Setting
Up the Data
Enter the known data in the worksheet, as
below. To calculate a payment, you must at least know the
initial loan amount, the interest rate, and the term.
Entering
the Function
-
Select the cell that is to contain the
function. In our example, cell B5.
-
From the Insert
menu, choose Function or choose the Paste Function
button, 
,
from the standard toolbar. The Paste Function
dialog box is displayed as below.
Note that there are a
number of functions to choose from in various function
categories. When a function is chosen, a short explanation
is given as well as the proper format for the function, as
outlined in yellow above.
-
Select Financial
for the Function category and PMT for the Function
name.
-
Choose OK.
A
box is immediately opened below the formula toolbar to help guide
you through building the function. Variables that are
necessary for the function to work are shown in bold type.
In this case, Rate, Nper and Pv. When
your insertion point is moved to the various fields in the box, an
explanation of the variable is given like the one outlined in
yellow above. There are also several Collapse buttons
that are used to collapse the box for easy cell selection from the
worksheet.
-
Be sure the insertion
point is in the Rate field, and choose the Collapse
button.
-
Select the cell
containing the interest rate, as below.
Excel enters the cell
reference into the formula.
-
Choose the Expand
button to return to the function box.
-
Continue entering
cells for the necessary variables using the Collapse
and Expand buttons. If you know the cell
references, these can also be manually typed in rather than
using the buttons. You can type numeric values as well,
but if the values will change it is best to use cell
references so that there is no need to update the function
each time the values change.
Notice that for the Rate
field, I have added "/12" after the cell
reference. This is because the interest rate is 8% for the
year, but the payments are monthly. So, to calculate
properly, the interest rate must be divided by 12. Excel
also shows the Formula result, as outlined in yellow above,
allowing you to check the result before you enter the function
into the worksheet.
-
Choose OK.
The function is entered and the result is shown in the payment
cell.
The result is shown as a
negative value (red with parenthesis) because you will be less
that amount each month.
Entering a function in
this way, allows you to change any of the values in the worksheet
and see the resulting payment amount.
To learn how to learn how
to do a "What if" analysis, see the goal seeking
tutorial here.
