Using the Excel Loan Payment Function

Unless you majored in Differential Equations in college or are the secret love child of Albert Einstein, you probably don't know how to use the Excel payment calculating function, PMT. Bill Gates, in his wisdom has made this function as difficult to use as possible, but, with this quick lesson, you will be able to quickly and easily find the payment for any loan at any interest rate for any period of time.

For this example, we will assume you want to build an addition to your home. You will need to borrow $100,000 from the bank for 5 years at an interest rate of 6.25%. You now have the information you need to calculate the payment. You set up a simple spreadsheet and start the payment function:

You type =pmt( to start the function.

For those who have never used an Excel function, they always start with an equals (=) sign, the function name (pmt) and then, all arguments go inside parentheses. Excel gives you some help with a display of the arguments needed (below the cell).

You see rate, nper, pv, [fv], [type] as hints to complete the function. Microsoft could have been a bit less cryptic. Ignore anything in square brackets. Those arguments are unnecessary.

What to do

• Rate is the interest rate per payment. Unfortunately, the interest rate you have is annual and your payments are monthly. You must divide the interest rate by 12 to determine the monthly interest rate, so your first argument must be b2/12. Type a comma to tell Excel that you have completed this argument.
• Nper is the total number of payments of the loan. Since your payments are monthly, you must multiply the years of the loan by 12. Your second argument must be c2*12. Type another comma.
• PV means present value. It is the principal of the loan. Microsoft could have use the term principal or amount but they chose PV. Worse, since a loan is technically a liability, it is treated as a negative by Excel. To avoid a negative result, we must deceive Excel into believing we are loaning the money as opposed to borrowing. Here is what you must enter as the present value: -a2.

Here is what the formula looks like at this point

You are almost finished. Close the formula with ). Press enter and you are done.

The completed formula can be seen in the formula bar above columns B and C. You can now change the principal, rate and term of the loan to see what the payment will be. Note: If your loan is in months rather than years, you will not need to multiply the term by 12.

In future articles, we will show you how to calculate the current balance of an ongoing loan and how to easily create a sheet that shows your payment for a wide range of interest rates.

Written by Steven Tuttle - sdt@unh.edu