PRDV006: Spreadsheets II: Formatting and Functions

In addition to calculating the mortgage payments for a home, the PMT function will be used in the Personal Budget workbook to calculate the lease payments for a car. The details for the lease payments are found 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 argument of the function and understand how these arguments need to be defined based on your objective. The terms for loans and leases are in Table 1 Key Terms for Loans and Leases, and the definitions for the arguments of the PMT function are in Table 2 Arguments for the PMT Function. The following steps explain how the PMT function is added to the Personal Budget workbook to calculate the lease payments for a car:

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 arguments of the function.
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 our goal is 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 is already expressed in months, we can just 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 with 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 to 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 will assume that the lease payments will be due at the beginning of each month.
17. Type a closing parenthesis: )
18. Press the ENTER key.

Figure 6 PMT Function Constructed to Calculate Lease Payments 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 Results of the PMT Function in the Car Lease Payments Worksheet shows the result of the PMT function. The monthly payments for this lease are $206.56. This monthly payment will be displayed in the Budget Summary worksheet.

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