How to Make a Loan Repayment Schedule in Excel

Creating a loan repayment schedule in Excel is a practical way to manage and track your loan payments. This guide will walk you through the steps to build a detailed repayment schedule, ensuring you stay organized and on track with your loan obligations.

Step 1: Set Up Your Excel Sheet

1. Open Excel and start a new workbook.
2. Create Column Headers:
   • In cell A1, type “Payment Date”.
   • In cell B1, type “Payment Amount”.
   • In cell C1, type “Principal Paid”.
   • In cell D1, type “Interest Paid”.
   • In cell E1, type “Remaining Balance”.

Step 2: Input Loan Details

1. Enter Loan Information:

   • In a separate area of the sheet (say A10), enter your loan amount, annual interest rate, and total number of payments (months). For example:
     • Loan Amount: $10,000
     • Annual Interest Rate: 5%
     • Total Payments: 24 (for a 2-year loan)

2. Calculate Monthly Interest Rate:

   • In cell B10, type the formula =A11/12 where A11 is the annual interest rate (5% in this example). This converts the annual rate to a monthly rate.

Step 3: Create the Repayment Schedule

1. Determine Payment Amount:

   • Use the PMT function to calculate the monthly payment amount. In cell B11, type =PMT(B10, A12, -A10) where B10 is the monthly interest rate, A12 is the total number of payments, and A10 is the loan amount. This formula calculates the fixed monthly payment.

2. Set Up the First Row of Your Schedule:

   • In cell A2, enter the start date of the first payment.
   • In cell B2, enter the formula =$B$11 to reference the payment amount calculated earlier.

3. Calculate Principal Paid:

   • In cell C2, enter the formula =B2 - (E1 * B10) to calculate the principal portion of the payment. E1 is the remaining balance from the previous period.

4. Calculate Interest Paid:

   • In cell D2, enter the formula =E1 * B10 to calculate the interest portion of the payment.

5. Calculate Remaining Balance:

   • In cell E2, enter the formula =E1 - C2 to update the remaining balance.

6. Copy Formulas Down:

   • Select cells A2 to E2, and drag the fill handle down to cover all payment periods (based on the total number of payments). Excel will automatically adjust the formulas for each row.

Step 4: Format the Schedule

1. Date Format:

   • Format the Payment Date column (A) to display dates properly. Right-click the column, choose Format Cells, and select Date.

2. Currency Format:

   • Format the Payment Amount, Principal Paid, Interest Paid, and Remaining Balance columns (B to E) to display as currency. Right-click the columns, choose Format Cells, and select Currency.

Step 5: Review and Adjust

1. Verify Calculations:

   • Ensure all calculations are correct by checking a few rows. The remaining balance should reduce to zero at the end of the schedule.

2. Adjust for Additional Payments:

   • If you make additional payments or adjustments, update the relevant cells, and the schedule will recalculate accordingly.

By following these steps, you will have a comprehensive loan repayment schedule in Excel. This will help you keep track of each payment, monitor your progress, and manage your finances more effectively.