Documentation/Calc Functions/PMT
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
PMT
Category:
Financial Analysis
Summary:
Calculates the periodic payment (capital and interest) for a fixed-rate loan, based on a specified number of regular payments over a defined term.
While the regular payments are always for the same amount, the proportion of interest and capital repaid at each period varies over the duration of the loan. The related IPMT function calculates the interest component of the payment for a specified period, while the PPMT function calculates the corresponding capital component.
PMT can also be used to calculate the regular deposit for a fixed-rate annuity.
Syntax:
PMT(Rate; NPER; PV[; [FV] [; Type] ])
Returns:
Returns a real number, which is the value of the periodic payment or deposit. The number returned is in the same currency unit as the specified present and future values, and by default is formatted in a locale-dependent currency format.
Arguments:
Rate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the periodic interest rate. It is common for interest rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your payment period.
NPER is a real number, or a reference to a cell containing that number, which is the number of payment periods in the term.
PV is a real number, or a reference to a cell containing that number, which is the present value of the amount borrowed or invested.
FV is a real number, or a reference to a cell containing that number, which is the future value of the cash balance desired at the end of the term. If FV is omitted, the function uses the value 0.0. FV must be in the same currency units as PV.
Type is a logical value, or a reference to a cell containing a logical value, which determines whether the due date is at the beginning or end of a payment period. If Type is set to 0 or FALSE, or is omitted, then the function assumes the end of the payment period. If Type is set to any other value, then the function assumes the start of the payment period.
- If any argument is non-numeric, then PMT reports a #VALUE! error.
- PMT does not impose any range checks on the values of the Rate, NPER, PV, and FV arguments. However, inspection of the equations presented in the Additional details section below indicate that it would be possible to generate unrealistic corner cases where the denominator evaluates to 0. In such cases, PMT reports a #NUM! error.
Additional details:
- In common with several of Calc’s financial functions, PMT uses a general model to describe both loans and annuities. This model assumes that an initial lump sum is borrowed or invested, a number of regular payments are made or received, and a final sum is paid or received. A constant interest rate is applied and compounded throughout the term of the investment. This model is expressed by the following equations:
- In the case of a zero interest rate:
- [math]\displaystyle{ PV ~+~ PMT \times n~+~FV~=~0 }[/math]
- In the case of a non-zero interest rate:
- [math]\displaystyle{ PV \times (1+r)^{n} ~+~ PMT \times \frac{\left ( \left ( 1+r \right )^{n} -1\right )}{r}\times \left ( 1+ \left (t \times r \right ) \right )~+~FV~=~0 }[/math]
- where:
- FV = future value
- n = number of payment periods
- PMT = periodic payment value
- PV = present value
- r = periodic interest rate
- t = due date flag (0 when the due date is at the end of each payment period; 1 when the due date is at the beginning of each payment period).
- In the case of a zero interest rate:
- PMT assumes the convention that money received is positive, while money paid out is negative.
Examples:
Note that the formatting of the return values given in the following examples assume the English (USA) locale.
The relationship between PMT, PPMT, and IPMT
 |
Note:
|
John decides to borrow $8,000 over a period of 12 months at an annual interest rate of 4%. There is no cash balance remaining at the end of the loan and payments are due at the end of each month. The following table shows the schedule for his repayments.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan amount | $8,000 | ||
| 2 | Annual rate | 4% | ||
| 3 | ||||
| 4 | Month | PMT | PPMT | IPMT |
| 5 | 1 | -$681.20 | -$654.53 | -$26.67 |
| 6 | 2 | -$681.20 | -$656.71 | -$24.48 |
| 7 | 3 | -$681.20 | -$658.90 | -$22.30 |
| 8 | 4 | -$681.20 | -$661.10 | -$20.10 |
| 9 | 5 | -$681.20 | -$663.30 | -$17.90 |
| 10 | 6 | -$681.20 | -$665.51 | -$15.68 |
| 11 | 7 | -$681.20 | -$667.73 | -$13.47 |
| 12 | 8 | -$681.20 | -$669.96 | -$11.24 |
| 13 | 9 | -$681.20 | -$672.19 | -$9.01 |
| 14 | 10 | -$681.20 | -$674.43 | -$6.77 |
| 15 | 11 | -$681.20 | -$676.68 | -$4.52 |
| 16 | 12 | -$681.20 | -$678.94 | -$2.26 |
| 17 | -$8,174.39 | -$8,000.00 | -$174.39 |
Cells B5:D16 contain formulas as follows:
- Cells B5:B16 all contain the formula
=PMT($B$2/12; $A$16; $B$1). - Cell C5 contains the formula
=PPMT($B$2/12; $A5; $A$16; $B$1); cell C6 contains the formula=PPMT($B$2/12; $A6; $A$16; $B$1); and so on for cells C7:C16. - Cell D5 contains the formula
=IPMT($B$2/12; $A5; $A$16; $B$1); cell D6 contains the formula=IPMT($B$2/12; $A6; $A$16; $B$1); and so on for cells D7:D16.
Significant features of the data in the above table include:
- PMT is used to calculate the fixed monthly payment, in this example $681.20. PMT prefixes the numbers with a minus sign to indicate that the values represent payments.
- PPMT is used to calculate the capital element of each monthly payment. Note how the magnitude of the PPMT values increases throughout the term.
- IPMT is used to calculate the interest element of each monthly payment. Note how the magnitude of the IPMT values decreases throughout the term.
- Note that for each month, the sum of the PPMT and IPMT values for that month is equal to the PMT value.
- Note also that the total of all monthly payments (cell B17) is equal to the sum of the capital elements (cell C17) and the interest elements (cell D17).
Other examples
| Formula | Description | Returns |
|---|---|---|
| =PMT(1.99%/12; 36; 25000; 5000; 1) | Here the function calculates the periodic payments for a loan of 25,000 currency units at an annual interest rate of 1.99% (1.99%/12 per month), with a term of 3 years (36 months). A desired cash balance of 5000 currency units is required at term and payment is due at the beginning of each month. The monthly payment calculated by PMT is 849.45 currency units and this is returned as a negative value. | -$849.45 |
| =PMT(1.99%/12; 36; 25000) | Here the function calculates the periodic payments for a loan of 25,000 currency units at an annual interest rate of 1.99% (1.99%/12 per month), with a term of 3 years (36 months). No cash balance is required at term and payment is due at the end of each month. The monthly payment calculated by PMT is 715.96 currency units and this is returned as a negative value. | -$715.96 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
PMT