Excel 2016: Using PMT to Compute Loan Payments

Updated 2014.01.09

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:

 

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-PMT-Function_files/image001.jpg

 

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 (as we did above).