Build a Lease Liability Schedule

Since IFRS 16, a company that leases an office, a warehouse or a fleet of vans records most of those leases on its balance sheet: a lease liability for the payments it has committed to, and a right-of-use asset for the thing it is allowed to use. Both have to be calculated at the start of the lease and then rolled forward every month — the liability grows with interest and falls with each payment, and the asset is depreciated over the lease term. Finance teams usually keep a spreadsheet per lease for this, and each one is a small amortisation table with room for formula mistakes. This page builds the schedule for a portfolio of leases with pandas: the opening liability as the present value of the payments, a month-by-month unwind with payments in advance, the asset's depreciation, the current and non-current split the balance sheet needs, the month's journals and the maturity analysis for the notes — and checks the starting figure against Excel's own PV function. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy openpyxl

The input is a lease register with one row per lease: commencement date, term in months, the fixed monthly payment, paid at the start of each month, and the discount rate — the incremental borrowing rate the company would pay to borrow over a similar term. The month being closed is September 2026. The calculation ignores variable payments, options and remeasurements, which a real register would add as separate events.

Step 1 — The opening liability is the present value of the payments

import numpy as np
import pandas as pd

leases = pd.read_excel("leases.xlsx", parse_dates=["Commencement"])

def opening_liability(payment, annual_rate, months):
    r = annual_rate / 12
    t = np.arange(months)                       # payments in advance: the first is at time 0
    return float((payment / (1 + r) ** t).sum())

leases["Opening liability"] = [round(opening_liability(p, rate, n), 2)
                               for p, rate, n in zip(leases["Monthly payment"], leases["Discount rate"], leases["Term (months)"])]
leases["Total payments"] = leases["Monthly payment"] * leases["Term (months)"]
print(leases[["Lease", "Total payments", "Opening liability"]].to_string(index=False))

The liability at commencement is what the future payments are worth today, discounted at the lease's rate. With payments at the start of each month, the first payment is not discounted at all, the second is discounted by one month, and so on. The monthly rate is the annual rate divided by twelve, which is the common simplification; an effective monthly rate, (1 + r) ** (1/12) − 1, is the alternative some policies prefer, and the code only changes in one line. The head office lease, sixty payments of 12,500 — 750,000 in total — has an opening liability of 657,409.82; the difference of 92,590.18 is the interest that will be charged over five years. The warehouse lease, ten years at 21,000 a month, has a liability of 1,901,000.23 against payments of 2.52 million, and the vans 136,625.55 against 151,200.

Step 2 — Check the opening figure against Excel

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Lease", "Python", "Excel PV"])
for i, row in enumerate(leases.itertuples(index=False), start=2):
    ws.append([row.Lease, row._6, f"=PV({row._5}/12,{row._3},-{row._4},0,1)"])
wb.save("pv_check.xlsx")

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' pv_check.xlsx > /dev/null 2>&1
cat pv_check.csv

Excel's PV(rate, nper, pmt, fv, type) computes the same present value, and type = 1 tells it that payments are at the start of each period. Writing the formula next to the Python result and letting LibreOffice calculate it gives an independent check: the two columns agree to the penny for all three leases. It is worth keeping such a check for any number that will be audited — the reviewer can see that the Python matches the function they would use themselves.

Step 3 — Roll the liability and the asset forward

def schedule(lease):
    r, n, pay = lease["Discount rate"] / 12, lease["Term (months)"], lease["Monthly payment"]
    months = pd.period_range(lease["Commencement"], periods=n, freq="M")
    rows, balance = [], lease["Opening liability"]
    dep = lease["Opening liability"] / n                      # right-of-use asset = opening liability here
    for i, m in enumerate(months):
        opening = balance
        after_payment = opening - pay
        interest = round(after_payment * r, 2)
        balance = round(after_payment + interest, 2)
        rows.append({"Lease": lease["Lease"], "Month": m, "Opening": round(opening, 2), "Payment": pay,
                     "Interest": interest, "Closing": balance, "Depreciation": round(dep, 2),
                     "ROU closing": round(lease["Opening liability"] - dep * (i + 1), 2)})
    df = pd.DataFrame(rows)
    df.loc[df.index[-1], "Interest"] += round(-df["Closing"].iloc[-1], 2)    # absorb rounding so it ends at zero
    df.loc[df.index[-1], "Closing"] = 0.0
    return df

sched = pd.concat([schedule(l) for _, l in leases.iterrows()], ignore_index=True)
print(sched.groupby("Lease").agg(interest=("Interest", "sum"), payments=("Payment", "sum"), last=("Closing", "last")).round(2))
print(sched[(sched["Lease"] == "L-01") & (sched["Month"].astype(str).isin(["2025-01", "2025-02", "2029-12"]))]
      [["Month", "Opening", "Payment", "Interest", "Closing"]].to_string(index=False))

Each month the payment is made first, because it is due in advance, and interest is then charged on what remains at the monthly rate. The closing balance becomes next month's opening. Over the term, total interest equals total payments minus the opening liability, and the liability falls to zero with the last payment; rounding to pence each month leaves a few pence over at the end, which are absorbed into the final month's interest so the balance closes exactly at zero. The right-of-use asset starts at the opening liability — it would also include initial direct costs and payments made before commencement, if there were any — and is depreciated in equal monthly amounts over the term.

Step 4 — The month's figures, the balance sheet split and the maturity analysis

month = pd.Period("2026-09", freq="M")
now = sched[sched["Month"] == month].set_index("Lease")
in_12 = sched[sched["Month"] == month + 12].set_index("Lease")["Closing"].reindex(now.index, fill_value=0.0)
position = pd.DataFrame({"Interest": now["Interest"], "Depreciation": now["Depreciation"], "Payment": now["Payment"],
                         "Liability": now["Closing"], "Current": (now["Closing"] - in_12).round(2),
                         "ROU asset": now["ROU closing"]})
position["Non-current"] = (position["Liability"] - position["Current"]).round(2)
print(position.to_string())
print(position[["Interest", "Depreciation", "Payment", "Liability", "Current", "Non-current"]].sum().round(2).to_dict())

future = sched[sched["Month"] > month]
years_out = (future["Month"] - month).apply(lambda d: d.n) / 12
buckets = pd.cut(years_out, [0, 1, 5, 100], labels=["Within 1 year", "1 to 5 years", "Over 5 years"])
maturity = future.groupby(buckets, observed=False)["Payment"].sum()
print(maturity.to_dict(), "| total undiscounted", maturity.sum())

For the month being closed, each lease contributes interest expense, depreciation and a payment, and ends with a liability and an asset balance. The balance sheet needs the liability split into the part due within twelve months — current — and the rest. The cleanest measure is the reduction in the liability over the next twelve months: the closing liability now minus the closing liability twelve months from now, which is the principal that will be paid off in that time. The maturity analysis in the notes to the accounts uses a different basis — the undiscounted payments still to be made — grouped into within one year, one to five years and over five years. Its total is larger than the liability, because it includes the interest not yet charged. For September 2026 the three leases carry a liability of 2,280,581.66, of which 327,653.55 is current, while the undiscounted payments still due add up to 2,814,300 — 452,400 within a year, 1,416,900 in one to five years and 945,000 after that.

Step 5 — Journals and the workbook

totals = position.sum()
journals = pd.DataFrame([
    ("Interest expense — leases", totals["Interest"], 0.0), ("Lease liability", 0.0, totals["Interest"]),
    ("Lease liability", totals["Payment"], 0.0), ("Cash", 0.0, totals["Payment"]),
    ("Depreciation — right-of-use assets", totals["Depreciation"], 0.0), ("Right-of-use assets — accumulated depreciation", 0.0, totals["Depreciation"]),
], columns=["Account", "Debit", "Credit"]).round(2)
assert round(journals["Debit"].sum() - journals["Credit"].sum(), 2) == 0

with pd.ExcelWriter("leases_2026-09.xlsx") as xw:
    position.reset_index().to_excel(xw, sheet_name="Position", index=False)
    journals.to_excel(xw, sheet_name="Journals", index=False)
    maturity.rename("Undiscounted payments").reset_index().rename(columns={"index": "When"}).to_excel(xw, sheet_name="Maturity", index=False)
    sched.assign(Month=sched["Month"].astype(str)).to_excel(xw, sheet_name="Schedules", index=False)
print(journals.to_string(index=False))

Three journals record the month: interest increases the liability, the payment reduces it and leaves the bank, and depreciation reduces the asset. The debits and credits are asserted to balance before anything is written. The workbook holds the position by lease, the journals, the maturity table and the full schedules, which double as the audit trail — every month of every lease, from commencement to the final payment. Changes to a lease — a new payment amount after a rent review, an extension — are handled by recalculating the liability from that date with the new payments and the rate at that date, and recording the difference against the asset; the same function serves for that, called with the remaining payments.

Common pitfalls

• Payments in arrears by accident. In advance means the first payment is not discounted; set type = 1 in Excel.
• Rounding leftovers. Absorb the final pennies so the liability closes at exactly zero.
• Current portion as next year's payments. Use the principal reduction, not undiscounted payments.
• Maturity analysis discounted. It shows undiscounted payments, including future interest.
• No independent check. Compare the opening liability with PV in a spreadsheet.

Conclusion

A lease liability schedule starts with the present value of the payments at the lease's monthly rate, checked against Excel's PV, then each month deducts the payment in advance, adds interest on the remainder and depreciates the right-of-use asset straight line. From the schedules come the month's interest, depreciation and payment journals, the current and non-current split from the next twelve months' principal reduction, and the undiscounted maturity analysis for the notes.

Frequently asked questions

How do I calculate a lease liability in Python? Discount each payment at the monthly rate — for payments in advance the first is undiscounted — and sum them; that is the liability at commencement.

How do I split a lease liability into current and non-current? Take the closing liability now minus the closing liability twelve months later as current, and the remainder as non-current.

What Excel function checks the opening lease liability?PV(rate/12, months, -payment, 0, 1), where the final 1 means payments at the start of each period.

Why is the maturity analysis bigger than the liability? It shows undiscounted future payments, which include interest that has not been charged yet.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Loan Amortization Schedule in Excel with Python — the same mechanics for a loan.
• PMT, NPV and IRR in Python — present values in Python.
• Build a Fixed Asset Depreciation Schedule — depreciating the asset side.