Description
Use this function to calculate the payment for a loan based on constant payments and a constant interest rate. Supported in Chains.
PMT is particularly useful for calculating monthly mortgage payments, car loan payments, or any fixed-term loan with regular payments.
Note: By default, the PMT function assumes that payments are made at the end of each period. For payments made at the beginning of the period, use the type argument to change this.
Syntax
PMT(rate, nper, pv, [fv], [type])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
rate |
Yes | The interest rate per period. | A decimal number, a reference to a cell containing a decimal number, or a formula which results in either of these. |
nper |
Yes | The total number of payment periods in the loan. | A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these. |
pv |
Yes | The present value (or principal) of the loan. | A positive number, a reference to a cell containing a positive number, or a formula which results in either of these. |
fv |
No | The future value or cash balance after the last payment. | A number, a reference to a cell containing a number, a cell range,or a formula which results in any of these. If omitted, this is assumed assumed to be 0. |
type |
No | Indicates when payments are due. | 0 (end of period) or 1 (beginning of period). If omitted, it's assumed to be 0. |
Example
Sample data
| A | B | |
|---|---|---|
| 1 | Loan Amount | 200000 |
| 2 | Annual Interest Rate | 0.05 |
| 3 | Loan Term (years) | 30 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Calculate the monthly payment for the specified loan value. | =PMT(B2/12, B3*12, B1) |
This formula works in the following manner:
For this data set this formula returns -1073.64. |
| Calculate the monthly payment with payments at the beginning of each period. | =PMT(B2/12, B3*12, B1, 0, 1) |
This formula works in the following manner:
Practical Implication of type = 1: When payments are made at the beginning of the period, the principal is reduced sooner, resulting in slightly lower overall interest paid and thus a slightly lower (less negative) payment compared to end-of-period payments. For this data set this formula returns -1069.19. |
| Calculate the quarterly payment for the loan. | =PMT(B2/4, B3*4, B1) |
This formula works in the following manner:
For this data set this formula returns -3234.81. |
| Calculate the monthly payment with a future value of 10000. | =PMT(B2/12, B3*12, B1, 10000) |
This formula works in the following manner:
For this data set this formula returns approximately -1053.77. |
| Calculate the total annual payment for the loan. | =ABS(PMT(B2/12, B3*12, B1))*12 |
This formula works in the following manner:
For this data set this formula returns 12883.68. |
Notes
- The PMT function returns a negative number by default, indicating an outgoing payment.
- For monthly payments, divide the annual interest rate by 12 and multiply the number of years by 12.
- The
rateandnperarguments must use the same time unit. Ifrateis per year and payments are monthly,rateshould be divided by 12 and nper multiplied by 12. -
fv(future value) is optional. If omitted, it's assumed to be 0 (the loan is fully paid off). - The type argument is also optional. Use 1 for payments at the beginning of the period, or 0 (or omit) for end of period.
Tips
- Use PMT in combination with other financial functions such as FV, NPV, XNPV, IRR, or XIRR, for more comprehensive financial analysis.
- Always ensure your rate is in the correct periodic form (e.g., monthly rate for monthly payments).
- Use the ABS function with PMT if you need the payment amount as a positive number.