The PMT function in Excel is used to calculate the periodic payment required to repay a loan or an investment over a specified period, with a fixed interest rate. This function is particularly useful for determining monthly mortgage payments, car loans, or any other type of loan that is paid off over time with equal payments.
Syntax
| Formula | Description |
|---|---|
| =PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan based on constant payments and a constant interest rate. |
- rate: The interest rate for each period.
- nper: The total number of payment periods in the loan or investment.
- pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal.
- [fv]: An optional argument that represents the future value, or the cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0.
- [type]: An optional argument that specifies when payments are due. Use 0 if payments are due at the end of the period (default), or 1 if payments are due at the beginning of the period.
Example
Consider a scenario where you take out a loan of $20,000 to be repaid over 5 years with an annual interest rate of 6%. The loan payments are made monthly.
| Input | Value |
|---|---|
| Loan Amount (pv) | $20,000 |
| Annual Interest Rate | 6% |
| Number of Years (nper) | 5 |
| Payments per Year | 12 |
To calculate the monthly payment, use the following formula:
=PMT(6%/12, 5*12, -20000)This formula divides the annual interest rate by 12 to get the monthly rate, multiplies the number of years by 12 to get the total number of payments, and uses the negative sign before the loan amount because it represents an outflow of cash.
Practice Exercise 1
Task: Calculate the monthly payment for a loan of $50,000 to be repaid over 10 years, with an annual interest rate of 5%. The payments are made at the end of each month.
Hint: Remember to adjust the interest rate for monthly payments and multiply the number of years by 12 to find the total number of payments.
Practice Exercise 2
Task: Calculate the quarterly payment for a loan of $75,000 with an interest rate of 4% annually, to be repaid over 8 years. Assume that the payments are made at the beginning of each quarter.
Hint: Since payments are made at the beginning of each quarter, use 1 for the type argument in the PMT function.
Solution for Exercise 1:
The formula to calculate the monthly payment for Practice Exercise 1 is:
=PMT(5%/12, 10*12, -50000)Solution for Exercise 2:
The formula to calculate the quarterly payment for Practice Exercise 2 is:
=PMT(4%/4, 8*4, -75000, 0, 1)