In this article, I will show you how to schedule loan repayment in installments Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
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 view many other excel articles here.
