Calculate Loan Repayments with Interest in Excel: A Complete Step-by-Step Guide

Understanding Loan Repayments and Interest Tracking in Excel

Previously, we covered basic loan repayments, setting up a payment schedule to reach zero over 36 months. But what if you want to see how much interest you're paying each month? This guide will show you how to create a loan schedule that includes both the principal and interest payments, allowing you to track the remaining balance with ease.

Step-by-Step Guide to Setting Up a Loan Repayment Schedule with Interest in Excel

1. Set Up Basic Loan Details

• Open your Excel spreadsheet and set up the following cells:
• Loan Amount: Set to £10,000 (enter 10000 in a cell and format as currency with Ctrl + Shift + 4).
• Interest Rate: Set to 5.25%.
• Repayment Period: Set to 36 months (number of payments).

2. Calculate Monthly Payment Using the PMT Function

• To determine the monthly payment, use the PMT function:
1. In an empty cell, type:

   =PMT(Interest_Rate/12, Number_of_Payments, -Loan_Amount)

2. Ensure the interest rate is divided by 12 for monthly payments, and use a negative sign before the loan amount to display a positive result.
3. The monthly payment should calculate as £300.83.

3. Create a Month-by-Month Schedule Using SEQUENCE

• In the first cell of the Month column, use the SEQUENCE function to list numbers from 1 to 36:
1. Type:

   =SEQUENCE(Number_of_Payments, 1, 1, 1)

2. This will automatically generate numbers from 1 to 36, updating as the period changes.

4. Calculate Interest for Each Period

• For the interest on the remaining balance each month, enter this formula:
1. In the first cell of the Interest column, type:

   =Remaining_Balance * (Interest_Rate/12)

2. Press F4 to lock the reference to the Interest Rate cell.
3. This calculates the interest due based on the current remaining balance.

5. Calculate Principal Payment Each Month

• To find out how much of each payment goes toward the principal, subtract the interest from the total payment:
1. In the Principal Payment column, type:

   =Monthly_Payment - Interest_Payment

2. Use F4 to fix the Monthly Payment reference.

6. Calculate Remaining Balance

• In the first cell of the Balance column, subtract the principal payment from the previous balance:
1. Type:

   =Previous_Balance - Principal_Payment

2. This reduces the balance each month until it reaches zero.

7. Use IPMT and PPMT Functions for a Turbocharged Schedule

• To calculate interest and principal amounts dynamically, use the IPMT (interest payment) and PPMT (principal payment) functions:
1. For the Interest column, enter:

   =IPMT(Interest_Rate/12, Period, Number_of_Payments, -Loan_Amount)

2. For the Principal Payment column, enter:

   =PPMT(Interest_Rate/12, Period, Number_of_Payments, -Loan_Amount)

3. These functions calculate specific interest and principal amounts for each period, allowing quick assessments for any point in the schedule.

8. Summarize and Visualize Your Loan Repayment

• Once your schedule is complete, use a line chart to visualize the balance, principal, and interest over time:
1. Select the Month, Balance, Principal, and Interest columns.
2. Go to Insert > Chart > Line Chart to create a visual representation.
3. This chart provides a clear view of how each payment impacts the loan balance and the interest over time.

Analyzing Loan Repayments with Excel

Using functions like PMT, IPMT, and PPMT in Excel allows you to build a comprehensive loan repayment schedule that includes both interest and principal breakdowns. Whether you’re evaluating loan costs over time or answering specific questions about a payment period, this approach empowers you to manage finances efficiently.

Choosing Between Basic and Advanced Loan Tracking

If you’re interested in tracking payments for specific periods or comparing interest to principal payments, consider the advanced method with IPMT and PPMT functions. Otherwise, the basic method may be sufficient for simpler schedules. Experiment with both to see which best suits your needs!