A loan repayment schedule is a tool for the calculation of Loan EMI. Use it to see how much you will need to pay each month to repay a loan in full. Excel is a powerful spreadsheet program that can be used to create a loan repayment schedule. Here are the steps to create a basic loan repayment schedule in Excel:

1. Open a new Excel document and create a table with the following columns: “Payment Number,” “Due Date,” “Payment Amount,” “Principal,” “Interest,” and “Remaining Balance.”
2. In the “Payment Number” column, enter the number of the payment. This can be done by using the formula “=ROW()-1” in the first cell and dragging it down to fill the entire column.
3. In the “Due Date” column, enter the date when each payment is due. You can use the “EDATE” formula to calculate the due date for each payment.
4. In the “Payment Amount” column, enter the formula “=PMT(Interest Rate/12, Loan Term, -Loan Amount)” to calculate the monthly payment.
5. In the “Principal” column, enter the formula “=PPMT(Interest Rate/12, Payment Number, Loan Term, -Loan Amount,0,0)” to calculate the amount of each payment that goes towards paying down the loan principal.
6. In the “Interest” column, enter the formula “=IPMT(Interest Rate/12, Payment Number, Loan Term, -Loan Amount,0,0)” to calculate the amount of each payment that goes towards paying the interest on the loan.
7. In the “Remaining Balance” column, enter the formula “=BALANCE(Interest Rate/12,Payment Number,Payment Amount,-Loan Amount)” to calculate the remaining balance of the loan after each payment is made.
8. By using the above formulas you can also format the cells as currency, by going to the “Home” tab and selecting “Currency” from the Number dropdown.

By following these steps, you can easily create a loan repayment schedule in Excel. This shows the payment number, due date, payment amount, principal, interest, and remaining balance for each payment. This can be helpful for budgeting and making sure you stay on track with your loan repayment.

Tips to improve the excel sheet

• You can use conditional formatting to highlight payments that are late. Or that have a remaining balance that is above a certain threshold.
• You can also use charts to visualize the data and make it more understandable.
• You can also use the “Data” tab, “Sort” option to sort the schedule based on due date, payment number or remaining balance.

A loan repayment schedule is a useful tool for keeping track of your loan payments and budgeting for them. Excel is a powerful program to create this type of tool quickly and easily. Customize the spreadsheet to suit your specific needs. You can ensure that you stay on track with your loan repayment and budget accordingly.

Here is a Loan Repayment Schedule format for you. Download from here.