Using the PMT Function
The PMT function is used to calculate payments on loans. In order to use the PMT function, you need to know:
Rate. The interest rate.
Nper. The number of payments.
Pv. The present value of the future payments, or the amount of the loan.
Fv. The future value, or the cash balance after the final payment has been made.
Type. Whether the payments are made at the beginning or end of each period.
To use the PMT function:
- On the FORMULAS tab, in the Function Library group, click the Insert Function command:
- In the Insert Function dialog box:
- Search on "Payment" or, in the Or select a category drop-down box, select Financial.
- Under Select a function, select PMT.
- Click OK.
- In the Function Arguments dialog box:
- Enter the interest rate (Rate) or the cell in which it is located. If your worksheet contains the annual interest rate and payments will be made monthly, then select the annual rate and divide by 12.
- Enter the number of payments (Nper).
- Enter the present value (Pv).
- Enter the future value (Fv). If you leave this blank, Excel will assume the future value is $0.
- For Type, enter "0" if payments are made at the end of the period and "1" if payments are made at the beginning of the period. If you leave this blank, Excel will assume payments are made at the end of the period.
- Click OK.
Here are some examples:
- To calculate a 24-month $3,000 loan with 9% interest, assuming the loan is to be completely paid off and payments are made at the end of each period:
- =PMT(0.09/12,24,3000,0,0) or =PMT(0.09/12,24,3000):
- To calculate a 15-year $200,000 loan with 6% interest, assuming half the loan is to be paid off and payments are made at the end of each period:
Note that 15 years = 180 months.
- To calculate a 15-year $200,000 loan with 6% interest, assuming half the loan is to be paid off and payments are made at the beginning of each period: