- Understand the fundamentals of loans and leases.
- Use the PMT function to calculate monthly mortgage payments on a house.
- Use the PMT function to calculate monthly lease payments for an automobile.
- Learn how to summarize data in a workbook by using worksheet links to create a summary worksheet.
The PMT (Payment) Function for Loans
Data file: Continue with CH2 Personal Budget.
If you own a home, your mortgage payments are a major component of your household budget. If you are planning to buy a home, having a clear understanding of your monthly payments is critical for maintaining strong financial health. In Excel, mortgage payments are conveniently calculated through the PMT (payment) function. This function is more complex than the statistical functions covered in Section 2.2 “Statistical Functions”. With statistical functions, you are required to add only a range of cells or selected cells within the parentheses of the function, also known as the argument. With the PMT function, you must accurately define a series of arguments in order for the function to produce a reliable output. Table 9.1.1 lists the arguments for the PMT function. It is helpful to review the key loan and lease terms below before reviewing the PMT function arguments.
Table 9.1.1 Arguments for the PMT Function
|This is the interest rate the lender is charging the borrower. The interest rate is usually quoted in annual terms, so you have to divide this rate by 12 if you are calculating monthly payments.
|The argument letters stand for number of periods. This is the term of the loan, which is the amount of time you have to repay the bank. This is usually quoted in years, so you have to multiply the years by 12 if you are calculating monthly payments.
|The argument letters stand for present value. This is the principal of the loan or the amount of money that is borrowed. When defining this argument, a minus sign must precede the cell location or value. For leases, this argument is used for the price of the item being leased.
|The argument letters stand for future value. The brackets around the argument indicate that it is not always necessary to define it. It is used if there is a lump-sum payment that will be made at the end of the loan terms. This is also used for the residual value of a lease. If it is not defined, Excel will assume that it is zero.
|This argument can be defined with either a 1 or a 0. The number 1 is used if payments are made at the beginning of each period. A 0 is used if payments are made at the end of each period. The argument is in brackets because it does not have to be defined if payments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined.
By default, the result of the PMT function in Excel is shown as a negative number. This is because it represents an outgoing payment. When making a mortgage or car payment, you are paying money out of your pocket or bank account. Depending on the type of work that you do, your employer may want you to leave your payments negative or they may ask you to format them as positive numbers. In the following assignments, the payments calculated using the PMT function will be made positive to make them easier to work with. To do this, when defining the PV argument (amount of money borrowed) in the PMT dialog box, a minus sign must precede the cell location or value.
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:
- Click the Mortgage Payments worksheet tab.
- Click cell B5.
- Click the Formulas tab on the Ribbon.
- Click the Insert Function button (see Figure 9.1.1). This opens the Insert Function dialog box, which can be used for searching all functions in Excel.
- In the “Search for a function:” input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 9.1.2). Note that the current description in the “Search for a function:” input box will already be highlighted. You can begin typing and the description will be replaced with your entry.
- 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 9.1.3).
- Click the PMT option in the “Select a function:” box in the lower half of the Insert Function dialog box.
- Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.
- 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.
- Click cell B3 on the worksheet. This is the rate being charged on the loan.
- Type a forward slash / for division.
- 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.
- 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.
- 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.
- Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.
- Type an asterisk * for multiplication.
- Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the terms of the loan by 12. This converts the terms of the loan from years to months.
- 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.
- 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.
- 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.
- Click cell B2 on the worksheet. This is the principal of the loan.
- Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
- 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 of money at the end of the loan, there is no need to define the Fv argument. Also, we will assume that the monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.
- Hold the SHIFT key while pressing the F3 key.
Function Arguments Dialog Box
- After the equal sign = and function name are typed into cell a location, hold down the CTRL key and press the letter A on your keyboard.
Figure 9.13 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 terms of the loan 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.
Comparable Arguments for PMT Function
When using functions such as PMT, make sure the arguments are defined in comparable terms. For example, if you are calculating the monthly payments of a loan, make sure both the Rate and Nper argument are expressed in terms of months. The function will produce an erroneous result if one argument is expressed in years while the other is expressed in months.
Figure 9.1.4 shows the final appearance of the Mortgage Payments worksheet after the PMT function is added. The result of the function in cell B5 will be displayed in the Budget Summary worksheet.
- 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. If the interest rate given is an annual rate, divide it by 12 to convert it to a monthly rate.
- Define the Nper argument with a cell location that contains the amount of time to repay the loan or lease. If the amount of time is in years, multiply it by 12 to convert it to number of months.
- 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.
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.