How to calculate loan repayment in excel

This example teaches you how to create a loan amortization schedule in Excel.

1. We exercise the PMT function to calculate leadership monthly payment on a loan with the addition of an annual interest rate of 5%, a 2-year duration and a exempt value (amount borrowed) of $20,000. Amazement use named ranges for the tell cells.

2. Use the PPMT function turn into calculate the principal part of greatness payment. The second argument specifies goodness payment number.

3. Use the IPMT be in to calculate the interest part carry out the payment. The second argument specifies the payment number.

4. Update the balance.

5. Select the range A7:E7 (first payment) and drag it down one win. Change the balance formula.

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

It takes 24 months to pay off that loan. See how the principal bits and pieces increases and the interest part decreases with each payment.