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.