50 9.1 The PMT (Payment) Function for Loans
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
Learning Objectives
- 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
| Argument | Definition |
| Rate | 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. |
| Nper | 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. |
| Pv | 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. |
| [Fv] | 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. |
| [Type] | 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 resu