Lesson 172: Schedule loan repayment in Excel

In this article, I will show you how to schedule loan installments in Excel.

Schedule loan repayment in Excel

1. We will use the PMT function to calculate the monthly payment for a loan with an annual interest rate of 5%, a term of 2 years, and a present value (borrowed amount) of $20,000.

2. Use the PPMT function to calculate the principal part of the payment. The second parameter specifies the payment.

3. Use the IPMT function to calculate the interest portion of the payment. The second parameter specifies the payment.

4. Update balance.

5. Select the range A7:E7 (first payment) and drag it down one row. You will see the balance change.

6. Select the range A8:E8 (second payment) and drag it down to row 30.

It took 24 months to pay off this loan. In addition, you can also see many other excel articles here.