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])
|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.|
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.
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.
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.
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))
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|
|Number of periods||10||10||25||4||8|
Payment due each period