Excel 2019/365: Using PMT to Compute Loan Payments

Updated 2019-08-07

# Objective:

·        Learn how to use the PMT function to determine the regular payment on a loan

# PMT arguments

·        RATE: Rate per period, not rate per year.

·        NPER: Number of periods, not number of years.

·        PV: Present value. If doing a savings problem, the amount you have in the bank at the beginning (a negative number). If doing a loan problem, the amount you have borrowed (a positive number).

·        FV: Future value. The amount you will have at the end of the number of periods if you are doing a savings problem, or the amount you will owe at the end of the number of periods if you are doing a loan problem (usually, but not always, 0).

·        TYPE: Type of payment. 1=payments at the beginning of the period, 0=at the end of the period.

## Example: Paying off a Loan

You want to borrow \$150,000 for 30 years at an annual rate of 6%. You will make monthly payments (12 per year), and payments will be at the end of each interest period. The balance at the end of the loan will be \$0 (you will completely pay it off). What is your monthly payment?

#### Solution

Once you realize that what you are trying to find is the regular payment (requiring the PMT function), you need to identify the arguments:

·        "You want to borrow \$150,000…" This is how much money you have now: the present value.

·        "…for 30 years...monthly payments" These two numbers determine the number of periods: 30*12 = 360.

·        "…at an annual rate of 6%" This is the annual rate, but there are 12 periods per year, so the rate per period is 6%/12 = 0.5%, or .005.

·        "…balance at the end of the loan will be \$0…" This is the future value of the loan (amount you owe when it is paid off): \$0.

·        "…payments will be at the end of each interest period." Type is "end of period", so use 0.

The PMT arguments are: PMT(rate, nper, pv, fv, type)

So the formula is:

=PMT(.005, 360, 150000, 0, 0)

The pmt function will return a negative value, because it represents money that you are "paying out".

It is never a good idea to build numbers into a formula. Use references to the cells that hold the numbers:

Note that the formula is =PMT(C8,C9,C5,C7,C10). To change any of the numbers, all you have to do is change any of the cells C2 through C10; you don't have to change the formula!

Note that the payment will be red with parentheses. This indicates that it is a negative number. You can make it a positive number by putting a minus sign in front of the word PMT.