PRDV006: Spreadsheets II: Formatting and Functions

In addition to calculating the mortgage payments for a home, we use the PMT function in the Personal Budget workbook to calculate the lease payments for a car. The lease payment details are in the Car Lease Payments worksheet. Similar to the statistical functions, we can type the PMT function directly into a cell. However, you must know the definitions for each function argument and understand how to define these arguments based on your objective. The terms for loans and leases are in Table 1, and the definitions for the arguments of the PMT function are in Table 2.

The following steps explain how to add the PMT function to the Personal Budget workbook to calculate the car's lease payments:

1. Click cell B6 in the Car Lease Payments worksheet.
   
   
2. Type an equal sign: =
   
   
3. Type the letters PMT
   
   
4. Type an open parenthesis: (   Excel then provides a tip box showing the function's arguments.
   
   
5. Click cell B4. This is the interest rate being charged for the lease.
   
   
6. Type the forward slash for division: /
   
   
7. Type the number 12. Since we aim to calculate the monthly lease payments, we divide the interest rate by 12 to convert the annual rate to a monthly rate.
   
   
8. Type a comma. When you type a function containing arguments, you must separate each argument with a comma. This signals to Excel that one argument has been defined, and you are ready to define the next argument in the function.
   
   
9. Click cell B5. This is the term or the length of time for the lease contract. Since the term has already been expressed in months, we can reference cell B5 and move to the next argument.
   
   
10. Type a comma. This advances the function to the Pv argument.
    
    
11. Type a minus sign: −   Remember that cell locations or values used to define the Pv argument must be preceded by a minus sign.
    
    
12. Click cell B2 on the worksheet, which is the price of the car.
    
    
13. Type a comma. This advances the function of the [Fv] argument.
    
    
14. Click cell B3 on the worksheet. This is the residual value of the car. Note that cell location and values used to define the [Fv] argument are NOT preceded by a minus sign.
    
    
15. Type a comma. This advances the function to the [Type] argument.
    
    
16. Type the number 1. We assume the lease payments will be due at the beginning of each month.
    
    
17. Type a closing parenthesis: )
    
    
18. Press the ENTER key.

Figure 6 shows how the PMT function should appear before pressing the ENTER key. Notice the commas that separate each argument of the function. Also, the tip box will show the current argument being defined in bold font.

Figure 6 PMT Function Constructed to Calculate Lease Payments

Figure 7 shows the PMT function results. The monthly payment for this lease is $206.56, which will be displayed in the Budget Summary worksheet.

Figure 7 Results of the PMT Function in the Car Lease Payments Worksheet