The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
Syntax:= PMT (rate, nper, pv, [fv], [type])
The PMT function syntax has the following arguments:
- Rate Required. The interest rate for the loan.
- Nper Required. The total number of payments for the loan.
- Pv Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
- Type Optional. The number 0 (zero) or 1 and indicates when payments are due.
| Set type equal to | If payments are due |
|---|---|
| 0 or omitted | At the end of the period |
| 1 | At the beginning of the period |
Example: Let’s look at some Excel PMT function examples and explore how to use the PMT function as a worksheet function in Microsoft Excel:
Example 1:
Syntax: =PMT(B1/12,B2,B3)
Result: -207406.42
Example 2:
Syntax: =PMT(B1/12,B2*12,0,B3)
Result: -9464.85
Note:
- #NUM! error – Occurs when:
- The given rate value is less than or equal to -1.
- The given nper value is equal to 0.
- #VALUE! error – Occurs when any of the arguments provided are non-numeric.
- When calculating monthly or quarterly payments, we need to convert annual interest rates or the number of periods to months or quarters.
- If we wish to find out the total amount that was paid for the duration of the loan, we need to multiply the PMT as calculated by nper.
