Saylor URL: http://www.saylor.org/books
based on the purchase price of $25,000. We will look at a demonstration of leasing a car as well as buying
a home in the next section.
The PMT (Payment) Function for Loans
Follow-along file: Continue with Excel Objective 2.00. (Use file Excel Objective 2.10 if starting here.)
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. With the PMT function, you must accurately define a
series of arguments in order for the function to produce a reliable output. Table 2.6 "Arguments for the
PMT Function" lists the arguments for the PMT function. It is helpful to review the key loan and lease
terms in Table 2.5 "Key Terms for Loans and Leases" before reviewing the PMT function arguments.
Table 2.6 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.