Build a Loan Amortization Schedule in Excel with Python

An amortization schedule is the table every loan comes with: one row per payment, showing how much of it is interest, how much reduces the balance, and what is left owed. Finance teams build them for equipment loans, leases and intercompany lending; operations teams build them to compare financing offers. Excel's PMT, IPMT and PPMT functions make a schedule easy to sketch but tricky to get exactly right: payments are rounded to cents, so the last one never quite clears the balance, and an overpayment changes every row after it. Building the schedule in Python makes those rules explicit, and writing it to Excel with live formulas lets the reader change the rate and watch it recalculate. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas xlsxwriter openpyxl

The example is a 24,000 equipment loan at 6.9% a year, repaid monthly over four years, with the first payment on 1 November 2026. Interest is charged monthly at one twelfth of the annual rate on the outstanding balance — the usual convention for such loans; mortgages in some countries compound differently, and the loan agreement always wins.

Step 1 — Calculate the payment

A fixed payment that repays a loan over n periods at periodic rate r is the annuity formula — the same thing Excel's PMT(rate, nper, pv) computes, without the sign convention that makes PMT return a negative number:

principal, annual_rate, months = 24_000, 0.069, 48
r = annual_rate / 12
payment = round(principal * r / (1 - (1 + r) ** -months), 2)
print(f"monthly payment: {payment:,.2f}")

The payment is rounded to cents because nobody pays 573.6046… a month. That rounding is tiny, but over 48 months it adds up to a few cents of difference in the final balance, which the schedule has to absorb somewhere — normally in the last payment.

Step 2 — Build the schedule row by row

Each row depends on the balance left by the previous one, so build the schedule with a loop. Round interest to cents each month, as a lender's system does, and make the last payment whatever clears the balance:

import pandas as pd

def schedule(principal, r, months, payment, first="2026-11-01", extra=0.0):
    rows, balance, start = [], principal, pd.Timestamp(first)
    for k in range(1, months + 1):
        interest = round(balance * r, 2)
        paid_off = min(payment + extra - interest, balance)
        rows.append({"No": k, "Date": start + pd.DateOffset(months=k - 1),
                     "Opening": balance, "Payment": round(interest + paid_off, 2),
                     "Interest": interest, "Principal": round(paid_off, 2)})
        balance = round(balance - paid_off, 2)
        rows[-1]["Closing"] = balance
        if balance == 0:
            break
    return pd.DataFrame(rows)

plan = schedule(principal, r, months, payment)
print(plan.head(3).to_string(index=False))
print(plan.tail(2).to_string(index=False))
print(f"total paid {plan['Payment'].sum():,.2f}, of which interest {plan['Interest'].sum():,.2f}")

The first payment is mostly interest — 138.00 of the 573.60 — and the share shifts towards principal every month, because interest is charged on a shrinking balance. The final payment is 573.40, twenty cents less than the others: the rounding difference, absorbed in the last row so the closing balance is exactly zero. Over the four years the loan costs 3,532.60 in interest.

pd.DateOffset(months=...) moves by calendar months, so a loan starting on the 31st lands on the last day of shorter months rather than drifting into the next month.

Step 3 — Overpayments

An extra 100 a month goes straight to principal, so the balance falls faster, less interest accrues, and the loan ends early. The same function handles it through the extra argument:

fast = schedule(principal, r, months, payment, extra=100)
saved = plan["Interest"].sum() - fast["Interest"].sum()
print(f"{len(fast)} payments instead of {len(plan)}, last on {fast['Date'].iloc[-1]:%d %b %Y}")
print(f"interest {fast['Interest'].sum():,.2f} — saving {saved:,.2f}")

Paying 673.60 instead of 573.60 clears the loan in 40 months instead of 48 and saves about 600 in interest. Comparing offers or overpayment strategies becomes a loop over parameters — the kind of table that is tedious to maintain by copying sheets in Excel. One-off lump sums work the same way with an extra that varies by month; pass a dictionary of month number to amount and look it up inside the loop.

Step 4 — Summarise and chart the balance

Readers want four numbers before the 48 rows: the payment, the total interest, the total repaid and the payoff date. Put them above the schedule, and add a chart of the balance:

summary = pd.Series({
    "Loan amount": principal,
    "Annual rate": annual_rate,
    "Monthly payment": payment,
    "Number of payments": len(plan),
    "Total interest": round(plan["Interest"].sum(), 2),
    "Total repaid": round(plan["Payment"].sum(), 2),
    "Final payment date": plan["Date"].iloc[-1].date(),
})
print(summary.to_string())
print(plan.loc[[11, 23, 35, 47], ["No", "Closing"]].to_string(index=False))

Step 5 — Write a workbook with live formulas

A schedule written as values is fine for a record. For a model that people will play with, write the inputs in cells and the schedule as formulas that refer to them, so changing the rate recalculates every row:

import xlsxwriter

wb = xlsxwriter.Workbook("loan_schedule.xlsx")
ws = wb.add_worksheet("Schedule")
bold = wb.add_format({"bold": True})
money = wb.add_format({"num_format": "#,##0.00"})
pct = wb.add_format({"num_format": "0.00%"})
date = wb.add_format({"num_format": "dd mmm yyyy"})

inputs = [("Loan amount", principal, money), ("Annual rate", annual_rate, pct), ("Months", months, None)]
for i, (label, value, fmt) in enumerate(inputs):
    ws.write(i, 0, label, bold)
    ws.write(i, 1, value, fmt)
ws.write(3, 0, "Payment", bold)
ws.write_formula(3, 1, "=ROUND(PMT(B2/12,B3,-B1),2)", money)
ws.write_row(5, 0, ["No", "Date", "Opening", "Payment", "Interest", "Principal", "Closing"], bold)

for k in range(1, months + 1):
    row = 5 + k                      # zero-based row index; Excel row number is row + 1
    x = row + 1
    ws.write(row, 0, k)
    ws.write_datetime(row, 1, plan["Date"].iloc[k - 1].to_pydatetime(), date)
    ws.write_formula(row, 2, "=B1" if k == 1 else f"=G{x - 1}", money)
    ws.write_formula(row, 4, f"=ROUND(C{x}*$B$2/12,2)", money)
    ws.write_formula(row, 3, f"=IF(A{x}=$B$3,C{x}+E{x},$B$4)", money)
    ws.write_formula(row, 5, f"=D{x}-E{x}", money)
    ws.write_formula(row, 6, f"=ROUND(C{x}-F{x},2)", money)
ws.set_column("A:A", 12)
ws.set_column("B:G", 13)
ws.freeze_panes(6, 0)
wb.close()
print("written")

The payment formula uses PMT with a negative loan amount, which flips Excel's sign convention so the payment shows as a positive number. The last row's payment is the opening balance plus interest, mirroring the Python rule that the final payment clears the loan. To check the formulas, let LibreOffice recalculate a copy and compare it with the Python schedule. There is a catch: xlsxwriter stores 0 as the cached result of every formula, and LibreOffice trusts cached results in .xlsx files by default, so it would simply show zeros. Saving the file once through openpyxl drops the cached values, which forces LibreOffice to calculate:

from openpyxl import load_workbook

load_workbook("loan_schedule.xlsx").save("loan_schedule_nocache.xlsx")

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated loan_schedule_nocache.xlsx > /dev/null 2>&1
ls recalculated

ws = load_workbook("recalculated/loan_schedule_nocache.xlsx", data_only=True)["Schedule"]
sheet = pd.DataFrame(ws.iter_rows(min_row=7, max_row=6 + months, min_col=3, max_col=7, values_only=True),
                     columns=["Opening", "Payment", "Interest", "Principal", "Closing"])
diff = (sheet - plan[["Opening", "Payment", "Interest", "Principal", "Closing"]]).abs().max().max()
print(f"payment cell {ws['B4'].value}, largest difference from the Python schedule: {diff:.6f}")

Every cell matches, so the live workbook and the Python calculation agree. Store Formula Results with xlsxwriter shows how to write the Python values as cached results so the file displays numbers even in viewers that do not calculate.

Common pitfalls

• Unrounded interest. Lenders round interest to cents each period; a schedule that does not will drift by cents from the statement.
• Forgetting the final adjustment. With a rounded payment, the last payment must clear whatever balance is left, or the schedule ends with a few cents outstanding or a negative balance.
• Excel's sign convention. PMT returns a negative payment for a positive loan; negate the present value or the result.
• Annual rate used as monthly. Divide by the number of payments per year — and check the agreement for how it defines the periodic rate.
• Day-count conventions. Some loans charge interest on actual days (actual/365); for those, interest is balance × rate × days / 365, and months of different lengths give different interest.

Conclusion

Compute the payment with the annuity formula and round it to cents, then build the schedule row by row: interest on the opening balance rounded to cents, principal as payment minus interest, and a final payment that clears the balance exactly. Put the totals and payoff date above the table, model overpayments with an extra amount per period, and write a workbook whose schedule is live formulas driven by input cells — checked against the Python schedule by recalculating it once.

Frequently asked questions

How do I calculate a loan payment in Python? Use the annuity formula: payment = principal × r / (1 − (1 + r)^−n), where r is the periodic rate and n the number of payments. It gives the same result as Excel's PMT without the negative sign.

Why doesn't my amortization schedule end at exactly zero? The payment is rounded to cents, so the schedule accumulates a few cents of difference. Make the final payment equal to the remaining balance plus that month's interest, which clears the loan exactly.

How do extra payments change the schedule? Add the extra amount to the principal paid each month. The balance falls faster, less interest accrues, and the schedule ends early; stop the loop when the balance reaches zero.

Can the Excel version recalculate when I change the rate? Yes, if the schedule is written as formulas that refer to input cells for the amount, rate and term. Changing an input then recalculates every row when Excel opens or edits the file.

Related

• Up one level: Building Finance and Operations Reports — the shared shape of finance reports.
• PMT, NPV, IRR and XIRR Excel Functions in Python — the financial functions behind the schedule.
• Goal Seek in Python Instead of Excel — finding the rate behind a given payment.
• Build a Fixed Asset Depreciation Schedule — the other schedule finance teams maintain.