Using the Excel PMT Function to Determine Loan Interest

We start out with a basic premise that we borrow $10,000.00 (D1). Our interest is 3.0% (D2) and the monthly payments are forty-eight months (D3).

The PMT formula calculates what to pay back on a monthly payment loan. There are three arguments in this function; RATE,NPER,PV. Two other arguments shown are not required for a basic loan payment; FV and TYPE.

=PMT(RATE,NPER,PV)

Let’s discuss the components of this calculation.

Rate is the interest rate on the loan. Payments are usually monthly, and interest is usually annual. You must change annual to monthly by dividing the interest by twelve. This gives you a monthly interest rate matching the monthly payment. So, if the interest rate is 3% you enter D2/12 for the RATE portion of the calculation.

NPER is the number of periods for which the loan is to be repaid. This is usually in months. In this example, you would click D3 to represent 48 months.

PV is the present value. This entry is the amount that was borrowed. You would select D1 here to represent the $10,000 loan.

When you do the above steps, your answer will be a negative. You owe that money, thus payment on the loan is a debt and the Excel PMT formula shows all debts as a negative value, automatically.

Should you want to show this as a positive, you have a few options. Multiply the entire function by negative one (-1), or place a negative sign (-) between the equal (=) and the P in PMT (this will make the entire function a negative function, resulting in a positive answer.

For more complex PMT formula, there are two additional arguments that allow further customization to your calculations.

FV is the future value. At the end of the payment, it might be possible that you want to have an unpaid balance, for whatever reason. You can enter an amount in FV and it can calculate the loan to have that unpaid balance. It lowers your payments, but this FV is still due.

Type is a logical (TRUE/FALSE) entry. It allows you to select when the payment will be paid, either at the beginning of the payment (month) period (enter 1) or at the end of the month (enter 0, or omitted).

These last two are optional, and lower case, and not required for a basic loan.