The PMT (Payment) Function for Leases

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 either type the PMT function directly into a cell or use the Insert Function button. 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, and the definitions for the arguments of the PMT function were listed in the previous chapter. 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. Click the Formulas tab on the Ribbon.
3. Click the Financial button in the Function Library group. This opens the Financial Function drop-down list.
4. Scroll down and click on the PMT Function in the drop-down list. This will open the PMT Function Arguments dialog box. See Figure 9.2.1.
5. Click the Collapse Dialog button next to the Rate argument in the PMT Function Arguments dialog box. This will be the first argument defined for the monthly car lease payment.
6. Click cell B4. This is the interest rate being charged for the lease.
7. Type the forward slash / for division.
8. 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.
9. 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.
10. 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.
11. 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. If the term was defined in years, instead of months, we would need to multiply the terms (years) of the loan by 12 to convert the years to months.
12. 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.
13. 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.
14. Type a minus sign −. Remember that cell locations or values used to define the Pv argument must be preceded with a minus sign.
15. Click cell B2 on the worksheet, which is the price of the car.
16. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
17. Click the Collapse Dialog button next to the Fv argument in the Function Arguments dialog box. This is the fourth argument we will define in the function.
18. 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.
19. Press the ENTER key on your keyboard. You will now see the Rate, Nper, Pv and Fv arguments defined for the function.
20. Click the Collapse Dialog button next to the Type argument in the Function Arguments dialog box. This is the fifth and last argument we will define in the function.
21. Type the number 1. We will assume that the lease payments will be due at the beginning of each month. For payments made at the beginning of the period, a 1 will be entered in the Type argument box. For payments made at the end of the period, a 0 will be entered in the Type argument box.
22. Press the ENTER key. You will now see the Rate, Nper, Pv, Fv and Type arguments defined for the function. See Figure 9.2.1.
23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet.

Figure 9.2.1 shows how the the completed Function Arguments dialog box for the PMT function car lease should appear before pressing the OK button.

Figure 9.2.2 shows the result of the PMT function for the car lease. The monthly payments for this lease are $206.56. This monthly payment will be displayed in the Budget Summary worksheet.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.