Excel Financial Mastery: Creating Loan Repayment Schedules

Creating a loan repayment schedule in Excel is a valuable skill for managing your loans, whether it's for a mortgage, car loan, student loan, or any other type of loan. Here's a step-by-step guide to creating a loan repayment schedule in Excel:

Step 1: Open Excel and Set Up the Worksheet

1. Open Microsoft Excel.
2. In the first row (row 1), create the following column headers in cells A1 to E1: "Payment Number," "Payment Date," "Beginning Balance," "Payment," and "Ending Balance."

Step 2: Input Loan Details

1. Input the key details of your loan in an empty cell, like A2. For example:
   • Loan Amount: $10,000
   • Interest Rate: 5% (0.05 as a decimal)
   • Loan Term (in months): 36

Step 3: Calculate Monthly Payment

1. In an empty cell, calculate the monthly payment using the PMT function. Enter the formula in an empty cell, like B2:

   =PMT(0.05/12,36,-10000)
   

   • The interest rate is divided by 12 to get the monthly interest rate.
   • 36 is the number of months.
   • -10000 represents the loan amount as a negative value.

2. Press Enter. This cell will now display your monthly payment.

Step 4: Create the Payment Schedule

1. In cell A3, enter "1" as the first payment number.

2. In cell B3, enter the first payment date. For a monthly loan, you can start with the loan origination date. You can use Excel's DATE function or enter the date manually.

3. In cell C3, calculate the beginning balance. The beginning balance for the first payment is the loan amount (e.g., A2) because it's the initial balance. Enter the formula in cell C3:

   =$A$2
   

4. In cell D3, reference the monthly payment you calculated in step 3 (e.g., B2).

5. In cell E3, calculate the ending balance for the first payment. Enter the formula in cell E3:

   =C3-D3
   

Step 5: Fill Down the Schedule

1. Select cells A3 to E3.

2. Hover your cursor over the small square in the lower-right corner of the selected cells (the fill handle). The cursor will change to a black cross.

3. Click and drag down to fill the cells with the payment schedule for the remaining months. Excel will automatically increment the payment number, update the payment date, and calculate the balances accordingly.

Step 6: Format the Schedule

1. Format the payment date column (column B) and ending balance column (column E) to display the date and currency format, respectively.

Step 7: Customize the Schedule (Optional)

1. You can customize your schedule further by adding extra columns for tracking additional information, like interest paid and principal paid per payment. You can create formulas to calculate these values based on the payment number.

2. Apply conditional formatting or color-coding to highlight specific payments or milestones.

Creating a loan repayment schedule in Excel allows you to keep track of your loan payments, interest, and the remaining balance. It's a powerful tool for managing your loans and staying on top of your financial obligations.

=PMT(interest rate, loan term, present value)

Principal = Payment - Interest
Interest = PMT(interest rate, remaining balance, loan term - month + 1)
Balance = Remaining balance - Principal

Principal = PPMT(interest rate, loan term, present value, month)
Interest = IPMT(interest rate, loan term, present value, month)
Balance = Remaining balance - Principal