Creating a Loan Amortization Schedule with Prepayments using Python and Pandas

Introduction

Managing a loan can be a complex task, especially when it comes to tracking payments, interest, and prepayments. In this article, we’ll explore a Python script that generates a loan amortization schedule with the ability to apply prepayments. The script utilizes the Pandas library for data manipulation and Excel export.

Loan Amortization Schedule

A loan amortization schedule is a detailed table that breaks down each loan payment into its principal and interest components over the loan term. It helps borrowers understand how their payments are allocated and how the loan balance decreases over time.

The Python Script

The provided Python script calculates a loan amortization schedule with prepayments and generates an Excel file containing the schedule. It also calculates the total interest savings and tenure reduced due to the prepayments.

Get TrendingNow’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Understanding the Code

1. Importing Libraries: The script starts by importing the required libraries — pandas for data manipulation.
2. Defining the Main Function: The calculate_loan_schedule function is defined to calculate the loan schedule and prepayment-related details. It takes the loan amount, annual interest rate, loan term in months, and monthly prepayment amount as input.
3. Calculating Monthly Payment: The function calculates the monthly payment using the formula for calculating a fixed monthly payment for a loan with compound interest.
4. Initializing Variables: The function initializes variables like schedule to store the monthly payment details, remaining_balance to track the outstanding loan amount, and original_loan_amount and original_loan_term_months to store the original loan details for later use.
5. Iterating Through Loan Term: The function then iterates through each month of the loan term and calculates the interest payment, principal payment, and remaining balance for each month.
6. Applying Prepayments: The function applies prepayments during the first 8 years (96 months) of the loan term. The prepayment amount is added to the monthly payment during this period to expedite the loan payoff.
7. Creating the Schedule: The function appends the calculated details for each month to the schedule list.
8. Calculating Summary: The function calculates the total interest savings and the total tenure reduced due to prepayments.
9. Example Usage: The script provides an example usage section where you can input your loan details and get the loan schedule and summary.
10. Creating DataFrame and Exporting to Excel: The script converts the schedule list into a Pandas DataFrame and exports it to an Excel file named ‘loan_schedule.xlsx’.
11. Displaying Summary: The script prints the generated loan schedule’s Excel file path and displays the summary of total interest savings and tenure reduced.

import pandas as pd

def calculate_loan_schedule(loan_amount, annual_interest_rate, loan_term_months, monthly_prepayment):
    monthly_interest_rate = annual_interest_rate / 12 / 100
    monthly_payment = loan_amount * (monthly_interest_rate * (1 + monthly_interest_rate) ** loan_term_months) / (
            (1 + monthly_interest_rate) ** loan_term_months - 1)

    schedule = []
    remaining_balance = loan_amount

    original_loan_amount = loan_amount
    original_loan_term_months = loan_term_months

    for month in range(1, loan_term_months + 1):
        interest_payment = remaining_balance * monthly_interest_rate
        principal_payment = monthly_payment - interest_payment

        if month <= 8 * 12:  # Apply prepayments only for the first 5 years
            remaining_balance -= (monthly_payment + monthly_prepayment - interest_payment)
        else:
            remaining_balance -= (monthly_payment - interest_payment)

        if remaining_balance <= 0:
            # Loan is fully paid, no need to continue calculating the schedule
            loan_term_months = month
            break

        schedule.append({
            'Month': month,
            'Monthly Payment': monthly_payment,
            'Interest Payment': interest_payment,
            'Principal Payment': principal_payment,
            'Prepayment': monthly_prepayment if month <= 8 * 12 else 0,
            'Remaining Balance': max(0, remaining_balance)  # Ensure balance doesn't go negative
        })

    # Calculate total interest savings and total tenure reduced
    original_interest = (monthly_payment * original_loan_term_months) - original_loan_amount
    new_interest = (monthly_payment * loan_term_months) - loan_amount
    interest_savings = original_interest - new_interest
    tenure_reduced_months = original_loan_term_months - loan_term_months

    summary = {
        'Total Interest Savings': interest_savings,
        'Total Tenure Reduced (months)': tenure_reduced_months
    }

    return schedule, summary

if __name__ == "__main__":
    # Example usage:
    loan_amount = 10600000  # Replace with your loan amount
    annual_interest_rate = 9.0  # Replace with your annual interest rate
    loan_term_years = 30  # Replace with your loan term in years
    monthly_prepayment = 50000  # Replace with your monthly prepayment amount

    loan_term_months = loan_term_years * 12

    schedule, summary = calculate_loan_schedule(loan_amount, annual_interest_rate, loan_term_months, monthly_prepayment)

    # Create a DataFrame from the loan schedule
    df = pd.DataFrame(schedule)

    # Export DataFrame to Excel
    output_file = 'loan_schedule.xlsx'
    df.to_excel(output_file, index=False)

    # Display summary
    print(f"Loan schedule has been generated and exported to '{output_file}'.")
    print("Summary:")
    print(summary)

Conclusion

Managing a loan can be made easier with a loan amortization schedule. This Python script demonstrates how to create a comprehensive loan schedule with prepayments using the Pandas library. By inputting your loan details into the example section, you can generate your personalized loan schedule and understand the impact of prepayments on interest savings and tenure reduction.

The provided script can be a valuable tool for borrowers looking to optimize their loan payments and understand the financial implications of making extra payments towards their loan. Additionally, this script can be extended and integrated into financial applications to provide loan tracking and repayment analysis to users.

Remember to always exercise caution and perform due diligence before making any financial decisions, as the terms and conditions of loans can vary.