The PMT function calculates the payments due for a loan or annuity over a specified number of periods, given a consistent interest rate and payment amount.
For example, you can use the PMT function to calculate the monthly amount required to pay back a loan given each payment is equal.
PMT(Interest rate, Number of periods, Present value [, Future value] [, Timing])
Arguments
| Argument | Data type | Description |
| Interest rate (required) | Number | The interest rate of the loan. This argument is best used with the Percent format, as 0.1 equals 10%, and so on. |
| Number of periods (required) | Number | The number of periods that the loan is paid over. |
| Present value (required) | Number | The present value of the instrument, or initial investment. |
| Future value | Number | The amount of loan left after the final payment is made. If omitted, this assumed to be 0. |
| Timing | Number | Whether interest payments are made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it. A value of 0 means payments are made at the end of each period. This is the default behavior if you omit the Payment timing argument. A non-zero value means payments are made at the start end. |
The PMT function returns a number.
Additional information
Periods used with PMT function
The Number of periods argument enables you to specify the number of periods interest accrues over.
The values you provide for the Interest rate and Number of periods arguments should use the same time scale. For example, if the periods are years, you should provide the annual interest rate.
Positive and negative values
For any values you give for the Present value or Future value arguments, or that the PMT function returns:
- A positive value represents money you receive, such as a dividend or loan.
- A negative value represents money paid, such as a deposit or interest payment.
How the PMT function is calculated
You can recreate the behavior of the PMT function. To do this, you can use the formula below with the values you would otherwise use as arguments for the PMT function.
IF Interest rate = 0 THEN (-Present value -Future value) / Number of periods ELSE Interest Rate / ((1 - POWER(1 + Interest rate, Number of periods)) * (1 + Interest Rate * Timing)) * (Future value + Present value * POWER(1 + Interest rate, Number of periods))
Excel equivalent
Examples
In this example, there is a Contracts list on columns, and line items on rows. There are five line items, one for each argument of the PMT function. The sixth line item uses the PMT function to calculate the required payments.
| Contract 1 | Contract 2 | Contract 3 | Contract 4 | Contract 5 | |
| Interest rate | 8.00% | 8.00% | 5.00% | 3.50% | 0.10% |
| Number of periods | 10 | 10 | 25 | 4 | 8 |
| Present value | -10,000 | -10,000 | -250,000 | -5,000 | -1,000 |
| Future value | 0 | 0 | 0 | 0 | 4,000 |
| Timing | 0 | 1 | 0 | 0 | 1 |
Payment due each period
| 1,490.29 | 1,379.90 | 17,738.11 | 1,361.26 | -348.59 |
