Select Page

The PMT function returns the payment amount for a loan based on an interest rate and a constant payment schedule. In the example below, B6 contains a function: =PMT(B3/12,B4,B2)

The PMT functions has 3 required arguments (and 2 optional arguments not covered here):

1. Interest rate
2. Number of payments
3. Loan amount

When using the PMT function, you must be consistent with your “time periods”. In other words, because we want to know what the monthly payment is, the interest rate must be expressed as a monthly rate (that is why the first argument is B3 divided by 12) and the number of payments must be expressed in months.

By default, the result of the PMT function is shown as a negative. This is because it represents an outgoing payment. Most people are not aware of this. They simply look at the underlying figures – imagine presenting your Manager with the above spreadsheet. They’d probably query why the monthly payment was negative.

The simplest way to force the result to be displayed as a positive value is to put a minus sign in front of the 3rd argument: =PMT(B3/12,B4,-B2)

You can also wrap the entire function inside an ABS function. ABS returns the absolute value of a number, in other words, the number without its sign.

=ABS(PMT(B3/12,B4,B2))