Lesson 167: How to use the PMT function in Excel

PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate.

How to use the PMT function in Excel

Consider a loan with an annual interest rate of 6%, a term of 20 years, a present value of $150,000 (the amount borrowed) and a future value of 0 (this is what you hope to achieve). paid off the loan).

1. The PMT function below calculates the annual payment.

2. The PMT function below calculates the quarterly payment.

3. The PMT function below calculates the monthly payment.

4. The PMT function below calculates the annual deposit amount.

5. The PMT function below calculates the monthly withdrawal amount.

PPMT and IPMT

Consider a loan with an annual interest rate of 5%, a term of 2 years, and a present value (borrowed amount) of $20,000.

1. The PMT function below calculates the monthly payment.

2. The PPMT function in Excel calculates the principal part of the payment. The second parameter specifies the payment number.

3. The IPMT function in Excel calculates the interest portion of the payment. The second parameter specifies the payment number.

4. It takes 24 months to pay off this loan. Create a loan amortization schedule (see image below) to clearly see the principal increase and the interest decrease with each payment.

In addition, you can also view many other excel articles here.