Functions for Personal Finance

The PMT (Payment) Function for Leases

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:

Click cell B6 in the Car Lease Payments worksheet.
Type an equal sign: =
Type the letters PMT
Type an open parenthesis: ( Excel then provides a tip box showing the function's arguments.
Click cell B4. This is the interest rate being charged for the lease.
Type the forward slash for division: /
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.
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.
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.
Type a comma. This advances the function to the Pv argument.
Type a minus sign: − Remember that cell locations or values used to define the Pv argument must be preceded by a minus sign.
Click cell B2 on the worksheet, which is the price of the car.
Type a comma. This advances the function of the [Fv] argument.
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.
Type a comma. This advances the function to the [Type] argument.
Type the number 1. We assume the lease payments will be due at the beginning of each month.
Type a closing parenthesis: )
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.

PMT Function Constructed to Calculate Lease Payments - looks at formula PMT (rate, nper, pv, [fv], [type])

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.

Results of the PMT Function in the Car Lease Payments Worksheet - PMT function in cell B6 =PMT(B4/12,B5,-B2,B#) and output

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

Skill Refresher: PMT Function

Type an equal sign: =
Type the letters PMT followed by an open parenthesis, or double click the function name from the function list.
Define the Rate argument with a cell location that contains the rate being charged by the lender for the loan or lease.
Define the Nper argument with a cell location that contains the amount of time to repay the loan or lease.
Define the Pv argument with a cell location that contains the principal of the loan or the price of the item being leased. Cell locations or values used for this argument must be preceded by a minus sign.
Define the [Fv] argument with a cell location that contains the residual value of the item being leased or the lump sum payment for a loan.
Define the [Type] argument with a 1 if payments are made at the beginning of each period or 0 if payments are made at the end of each period.
Type a closing parenthesis: )
Press the ENTER key.