Building a Lease Maturity Analysis Table in pandas

Problem Statement #Link to this section

You have a portfolio of lease payment schedules — one row per payment per lease, each row carrying a lease id, a payment date, an undiscounted amount, and the discounted liability the lease carries at the reporting date — and you need the single maturity-analysis table the notes require: undiscounted future payments bucketed into Year 1 through Year 5 and a "thereafter" line, followed by an imputed-interest reconciliation row that lands on the discounted lease liability. pandas is the natural tool for the grouping and the pivot, but it fights you on two fronts. First, its default numeric type is binary float, which cannot represent cents exactly and will drift a long undiscounted sum off the reconciliation. Second, the obvious way to bucket by year — the payment's calendar year — is wrong whenever the reporting date is not 31 December. This page builds the table correctly: Decimal-backed sums for money, and a bucket anchored to whole years from the reporting date rather than the calendar. It is a focused companion to the maturity analysis and disclosure tables topic, which specifies the accounting behind every step here.

Standard Anchor #Link to this section

Two provisions govern this table directly. ASC 842-20-50-6 requires a maturity analysis of lease liabilities showing undiscounted cash flows for each of the first five years and a total thereafter, reconciled to the discounted lease liabilities on the balance sheet. IFRS 16.58 requires the same maturity analysis applying the liquidity-risk banding of IFRS 7.39 and B11, presented separately from other financial liabilities. Both require the buckets to be undiscounted and both require a bridge to the discounted liability. That bridge is imputed interest — the difference between the undiscounted total and the liability — and it equals the remaining interest the effective-interest method will accrue, so it is a figure the schedule already holds rather than a new estimate.

Algorithm Specification #Link to this section

Each future payment $P_t$ is assigned to a disclosure bucket by the whole number of years between the reporting date and the payment date:

$$b=\lfloor \frac{\text{days}(\text{reporting date}\to \text{payment date})}{365}\rfloor +1$$

so a payment inside the first twelve months lands in bucket 1. Buckets 1 through 5 are reported individually; every payment with $b>5$ collapses into "thereafter". The bucket totals and the reconciliation are

$$U_b=\sum _{t:\text{bucket}(t)=b}{P}_t,\sum _{b=1}^5{U}_b+\text{Thereafter}-I_{\text{imputed}}=L$$

where $L$ is the discounted lease liability and $I_{\text{imputed}}={\sum }_t{P}_t-L$ is the imputed-interest reconciling line. The terminal check the code must pass is that the undiscounted total minus imputed interest equals $L$ exactly.

Annotated Python Snippet #Link to this section

The snippet groups a long payment frame into buckets and appends the reconciliation. Money is kept as Decimal objects inside object-dtype columns so pandas never coerces cents to float; aggregation uses a Decimal-seeded sum rather than the default. It ends with a terminal assertion that the undiscounted total minus imputed interest equals the discounted liability.

import pandas as pd
from decimal import Decimal, ROUND_HALF_UP

CENT = Decimal("0.01")
REPORTING_DATE = pd.Timestamp("2026-12-31")


def bucket(pay_date: pd.Timestamp) -> str:
    """Whole years from the reporting date, 1-based; >5 collapses to thereafter."""
    days = (pay_date - REPORTING_DATE).days       # NOT the calendar-year difference
    b = days // 365 + 1                           # day 0-364 -> 1, 365-729 -> 2, ...
    return f"year_{b}" if b <= 5 else "thereafter"


def dsum(series) -> Decimal:
    """Exact Decimal sum; avoids float coercion of the amount column."""
    return sum(series, Decimal("0"))


# payments: one row per future payment; amount is Decimal, liability is the
# per-lease discounted balance at the reporting date (ASC 842-20-50-6 / IFRS 16.58)
payments["b"] = payments["pay_date"].map(bucket)
order = [f"year_{y}" for y in range(1, 6)] + ["thereafter"]
buckets = payments.groupby("b")["amount"].apply(dsum).reindex(order, fill_value=Decimal("0"))

undiscounted_total = dsum(buckets)
liability = dsum(payments.drop_duplicates("lease_id")["liability"])   # one L per lease
imputed_interest = undiscounted_total - liability                     # residual bridge

table = buckets.copy()
table["undiscounted_total"] = undiscounted_total
table["imputed_interest"] = imputed_interest
table["lease_liability"] = liability
table = table.map(lambda x: x.quantize(CENT, rounding=ROUND_HALF_UP))  # present at cents

# Terminal reconciliation gate: undiscounted total − imputed interest = liability
assert table["undiscounted_total"] - table["imputed_interest"] == table["lease_liability"]
print(table)

The reconciliation runs on every build, not just in a notebook, so a portfolio whose payments and liabilities have drifted apart fails loudly before the table is published. Quantizing only the presented figures, after summing in full Decimal precision, keeps the bucket total and the liability from disagreeing by a stray cent.

Correct vs. Incorrect Bucketing #Link to this section

The choice that breaks most first attempts is how a payment's year is derived. Bucketing by the payment's calendar year is only correct when the reporting date happens to be 31 December; for any other period end it splits the first twelve months across two buckets.

Both approaches produce six lines, so the error is invisible unless you test a non-December reporting date — which is exactly why interim and non-calendar fiscal-year filers are where this defect surfaces.

Gotcha / Failure Mode: float Coercion Silently Breaks the Reconciliation #Link to this section

If the amount column is left as the default float64, pandas coerces every payment to binary floating point, and a multi-year undiscounted sum drifts by fractions of a cent. The reconciliation then fails — or, worse, passes against a liability that was also computed in float and drifted the same way, hiding the error until an auditor re-derives in exact arithmetic.

Before (WRONG — float dtype, drift accumulates):

payments["amount"] = payments["amount"].astype(float)   # cents no longer exact
buckets = payments.groupby("b")["amount"].sum()         # binary drift across the term
# assert later fails by a fraction of a cent, or ties to an equally drifted liability

After (correct — Decimal preserved end to end):

payments["amount"] = payments["amount"].map(Decimal)    # exact cents, object dtype
buckets = payments.groupby("b")["amount"].apply(dsum)   # Decimal-seeded exact sum
# undiscounted total − imputed interest == liability, to the cent

Keep the amount and liability columns as Decimal from ingestion onward, and never call .sum() on them without a Decimal seed, so the reconciliation reflects true cents rather than float artifacts.

Frequently Asked Questions #Link to this section

Why not just group by the payment's calendar year?

Because the maturity buckets are measured from the reporting date, not the calendar. Grouping by calendar year is correct only when the period ends 31 December; for a 30 June year end it splits the first twelve months across Year 1 and Year 2, understating the Year 1 total. Anchoring the bucket to whole years since the reporting date — days // 365 + 1 — gives the correct twelve-month bands for any period end.

How do I add the imputed-interest row without recomputing interest?

Compute it as a residual: the undiscounted total minus the discounted lease liability. That residual equals the remaining interest the amortization schedule will accrue, so there is nothing to re-estimate. Append it as a row and subtract it from the undiscounted total; the result must equal the liability. If you already carry the schedule's remaining interest, assert the two agree as an extra control.

Do I need Decimal if pandas is just summing?

Yes. The default float64 dtype cannot represent most cent amounts exactly, so a long undiscounted sum drifts and the reconciliation fails — or ties to an equally drifted liability, hiding the error until an exact re-derivation. Keep money as Decimal in object-dtype columns and aggregate with a Decimal-seeded sum so the table reflects true cents.

Related #Link to this section

• Sibling: Building a lease amortization schedule in pandas — produces the per-lease schedules whose future payments and remaining interest this table aggregates.
• Parent: Maturity analysis and disclosure tables — the accounting specification behind the bucketing and reconciliation built here.
• Section: Lease disclosure and financial statement reporting — how this table fits alongside the weighted-average, cost, and rollforward disclosures.