Functions for Personal Finance

The PMT (Payment) Function for Loans

If you own a home, your mortgage payments are a major component of your household budget. Understanding your monthly payments is critical for maintaining strong financial health if you plan to buy a house. In Excel, mortgage payments are conveniently calculated through the PMT (payment) function.

With statistical functions, you must add only a range of cells or selected cells within the function's parentheses. With the PMT function, you must accurately define a series of arguments for the function to produce a reliable output. Table 2 lists the arguments for the PMT function. It is helpful to review the key loan and lease terms in Table 1 before reviewing the PMT function arguments.

We will use the PMT function in the Personal Budget workbook to calculate the monthly mortgage payments for a house. These calculations will be made in the Mortgage Payments worksheet and then displayed in the Budget Summary worksheet through a cell reference link. So far, we have demonstrated several methods for adding functions to a worksheet. The following steps explain a new method using the Insert Function command for adding the PMT function:

1. Click the Mortgage Payments worksheet tab.
   
   
2. Click cell B5.
   
   
3. Click the Formulas tab on the Ribbon.
   
   

4. Click the Insert Function button (see Figure 2 Mortgage Payments Worksheet). This opens the Insert Function dialog box, which allows you to search for all Excel functions.
   
   

   
   

   
   

   Figure 2 Mortgage Payments Worksheet

5. In the "Search for a function:" input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 3). The current description in the "Search for a function:" input box will already be highlighted. You can begin typing, and your entry will replace the description.
   
   
6. Click the Go button in the upper right side of the Insert Function dialog box. This adds all the Excel functions that match your description in the "Select a function:" box in the lower half of the Insert Function dialog box (see Figure 3).
   
   
7. Click the PMT option in the "Select a function:" box in the lower half of the Insert Function dialog box.
   
   

8. Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.

    

   Figure 3 Insert Function Dialog Box

   Mouseless Commands

   Insert Function: Hold the SHIFT key while pressing the F3 key.

9. Click the Collapse Dialog button next to the Rate argument in the Function Arguments dialog box. This will be the first argument defined for the function.
   
   
10. Click cell B3 on the worksheet. This is the rate being charged on the loan.
    
    
11. Type a forward slash (/) for division.
    
    
12. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to divide the rate, which is stated in annual terms, by 12. This converts the annual rate to a monthly rate.
    
    
13. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
    
    
14. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
    
    
15. Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.
16. Type an asterisk (*) for multiplication.
    
    
17. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the loan terms by 12. This converts the terms of the loan from years to months.
    
    
18. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
    
    
19. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
    
    
20. Type a minus sign (−). When defining the Pv argument of the PMT function, any cell location or value must be preceded with a minus sign.
    
    
21. Click cell B2 on the worksheet. This is the principal of the loan.
    
    
22. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
    
    
23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet. Since we are not paying any lump sums at the end of the loan, there is no need to define the Fv argument. Also, we assume that monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.

Figure 4 shows the completed Function Arguments dialog box for the PMT function. Notice that the dialog box shows the values for the Rate and Nper arguments. The Rate is divided by 12 to convert the annual interest rate to a monthly interest rate. The Nper argument is multiplied by 12 to convert the loan terms from years to months. Finally, the dialog box provides you with a definition for each argument. The definition appears when you click in the input box for the argument.

Figure 4 Function Arguments Dialog Box for the PMT Function

Figure 5 shows the final appearance of the Mortgage Payments worksheet after the PMT function is added. The function's result in cell B5 will be displayed in the Budget Summary worksheet.

Figure 5 Mortgage Payments Worksheet with the PMT Function