IFRS 16 lease accounting monthly close with Kestra and DuckDB

id: lease-accounting-ifrs16-close
namespace: company.team

description: |
  Monthly IFRS 16 lease close: measure every lease liability as the present value of its
  remaining payments at the incremental borrowing rate, depreciate the right-of-use assets,
  remeasure index-linked leases, derecognize early terminations with their gain or loss,
  expense short-term and low-value leases, roll both balances forward, disclose the maturity
  analysis and the current portion, post the journals and lock the period.

inputs:
  - id: period
    type: STRING
    displayName: Period (YYYY-MM)
    defaults: "2026-08"
    validator: ^\d{4}-(0[1-9]|1[0-2])$

  - id: scenario
    type: SELECT
    displayName: Demo scenario
    description: ISSUES adds a lease without a borrowing rate, one with a negative
      payment and one with no term, which must block the close.
    values:
      - CLEAN
      - ISSUES
    defaults: CLEAN

  - id: approved_by
    type: STRING
    displayName: Approved by
    description: Required to close a period with a remeasurement or a termination.
    defaults: ""

variables:
  lock_key: lease_close
  pack_dir: "leases/{{ inputs.period }}"

concurrency:
  limit: 1

triggers:
  - id: third_business_day
    type: io.kestra.plugin.core.trigger.Schedule
    description: The 3rd of each month, for the month before. Disabled until the
      lease register is wired in.
    cron: "0 7 3 * *"
    disabled: true

tasks:
  - id: period_control
    type: io.kestra.plugin.core.output.OutputValues
    values:
      last_closed: "{{ (kv(vars.lock_key, errorOnMissing=false) ?? {'period': ''}) |
        jq('.period') | first }}"
      locked_liability: "{{ (kv(vars.lock_key, errorOnMissing=false) ?? {'liability':
        'NULL'}) | jq('.liability') | first }}"
      locked_rou: "{{ (kv(vars.lock_key, errorOnMissing=false) ?? {'rou_net': 'NULL'})
        | jq('.rou_net') | first }}"
      previous_period: "{{ (inputs.period ~ '-01') | date('yyyy-MM-dd') | dateAdd(-1,
        'MONTHS') | date('yyyy-MM') }}"

  - id: period_gate
    type: io.kestra.plugin.core.flow.If
    condition: >-
      {{ outputs.period_control.values.last_closed != ''
         and (outputs.period_control.values.last_closed >= inputs.period
              or outputs.period_control.values.last_closed != outputs.period_control.values.previous_period) }}
    then:
      - id: period_refused
        type: io.kestra.plugin.core.execution.Fail
        errorMessage: >-
          {{ outputs.period_control.values.last_closed >= inputs.period
             ? 'Period ' ~ inputs.period ~ ' is already closed (last closed: ' ~ outputs.period_control.values.last_closed ~ ').'
             : 'Period ' ~ outputs.period_control.values.previous_period ~ ' must be closed before ' ~ inputs.period ~ ' (last closed: ' ~ outputs.period_control.values.last_closed ~ ').' }}

  - id: measure
    type: io.kestra.plugin.jdbc.duckdb.Queries
    description: >
      One DuckDB session: the lease register (demo generator or your data),
      payment schedules, the liability at every month end as the present value
      of the remaining payments, right-of-use depreciation, remeasurements,
      terminations, the period roll-forward, the maturity analysis, blockers and
      journals.
    outputFiles:
      - register
      - movements
      - maturity
      - journal
      - blockers
    fetchType: FETCH_ONE
    sql: |
      SET VARIABLE p_start = DATE '{{ inputs.period }}-01';

      -- =======================================================================
      -- Lease register. Replace with your lease system export. Months are counted
      -- from the commencement month (month 1). cpi_from_month and cpi_change
      -- describe an index-linked payment change; terminated_after_month an early
      -- termination at the end of that month.
      -- =======================================================================
      CREATE TABLE leases (lease_id VARCHAR, asset_class VARCHAR, description VARCHAR, commencement DATE, term_months INT,
                           timing VARCHAR, monthly_payment DECIMAL(14, 2), annual_escalation DOUBLE, ibr DOUBLE,
                           cpi_from_month INT, cpi_change DOUBLE, terminated_after_month INT, exemption VARCHAR);
      INSERT INTO leases VALUES
        ('L01', 'Property', 'Head office, Paris', DATE '2022-01-01', 120, 'ARREARS', 42000.00, 0.03, 0.042, 57, 0.034, NULL, NULL),
        ('L02', 'Property', 'Warehouse, Lille', DATE '2023-04-01', 84, 'ARREARS', 28500.00, 0.025, 0.051, NULL, NULL, NULL, NULL),
        ('L03', 'Property', 'Retail store, Lyon', DATE '2024-07-01', 60, 'ADVANCE', 15800.00, 0, 0.060, NULL, NULL, NULL, NULL),
        ('L14', 'Equipment', 'Forklift fleet', DATE '2025-02-01', 60, 'ARREARS', 4200.00, 0, 0.062, NULL, NULL, NULL, NULL),
        ('L15', 'IT', 'Data centre colocation', DATE '2025-10-01', 36, 'ARREARS', 9600.00, 0, 0.055, NULL, NULL, NULL, NULL),
        ('L16', 'Property', 'Regional office, Nantes', DATE '2026-09-01', 72, 'ARREARS', 12500.00, 0.02, 0.058, NULL, NULL, NULL, NULL),
        ('L17', 'Property', 'Pop-up store, 9 months', DATE '2026-06-01', 9, 'ADVANCE', 6000.00, 0, 0.060, NULL, NULL, NULL, 'SHORT_TERM'),
        ('L18', 'IT', 'Office printers', DATE '2025-01-01', 48, 'ARREARS', 180.00, 0, 0.065, NULL, NULL, NULL, 'LOW_VALUE');
      -- Ten company cars, monthly in advance. L09 is handed back early, at the end of October 2026.
      INSERT INTO leases
      SELECT 'L' || lpad((3 + i)::VARCHAR, 2, '0'), 'Vehicles', 'Company car ' || i,
             (DATE '2024-01-01' + INTERVAL (i * 3) MONTH)::DATE, CASE WHEN i % 2 = 0 THEN 48 ELSE 36 END, 'ADVANCE',
             (640 + i * 34)::DECIMAL(14, 2), 0, 0.068 + (i % 4) * 0.002, NULL, NULL,
             CASE WHEN i = 6 THEN date_diff('month', (DATE '2024-01-01' + INTERVAL (i * 3) MONTH)::DATE, DATE '2026-10-01') + 1 END, NULL
      FROM range(1, 11) t(i);
      {% if inputs.scenario == 'ISSUES' %}
      INSERT INTO leases VALUES
        ('L19', 'Equipment', 'Packing line (no rate yet)', DATE '2026-05-01', 60, 'ARREARS', 3100.00, 0, NULL, NULL, NULL, NULL, NULL),
        ('L20', 'Vehicles', 'Van (credit note keyed as payment)', DATE '2026-03-01', 36, 'ADVANCE', -500.00, 0, 0.07, NULL, NULL, NULL, NULL),
        ('L21', 'Property', 'Storage unit (term missing)', DATE '2026-02-01', 0, 'ARREARS', 900.00, 0, 0.06, NULL, NULL, NULL, NULL);
      {% endif %}

      CREATE TABLE blockers AS
      SELECT 'MISSING_IBR' AS code, lease_id AS ref, description || ': no incremental borrowing rate' AS detail FROM leases WHERE ibr IS NULL AND exemption IS NULL
      UNION ALL SELECT 'NON_POSITIVE_PAYMENT', lease_id, description || ': payment ' || monthly_payment FROM leases WHERE monthly_payment <= 0
      UNION ALL SELECT 'INVALID_TERM', lease_id, description || ': term ' || term_months || ' months' FROM leases WHERE term_months IS NULL OR term_months <= 0
      UNION ALL SELECT 'SHORT_TERM_TOO_LONG', lease_id, description || ': exempt as short-term with a term of ' || term_months || ' months' FROM leases WHERE exemption = 'SHORT_TERM' AND term_months > 12;

      CREATE TABLE valid AS
      SELECT *, pow(1 + ibr, 1.0 / 12) - 1 AS r,
             date_diff('month', commencement, getvariable('p_start')) + 1 AS t_p
      FROM leases WHERE lease_id NOT IN (SELECT ref FROM blockers);

      -- =======================================================================
      -- Payments. Payment k falls at month end k (arrears) or month end k-1
      -- (advance; payment 1 is made at commencement). The index change applies
      -- to every payment from cpi_from_month.
      -- =======================================================================
      CREATE TABLE payments AS
      SELECT v.lease_id, k, CASE WHEN v.timing = 'ADVANCE' THEN k - 1 ELSE k END AS tau,
             round(v.monthly_payment * pow(1 + v.annual_escalation, (k - 1) // 12), 2)::DECIMAL(14, 2) AS pay_old,
             round(v.monthly_payment * pow(1 + v.annual_escalation, (k - 1) // 12)
                   * CASE WHEN v.cpi_from_month IS NOT NULL AND k >= v.cpi_from_month THEN 1 + v.cpi_change ELSE 1 END, 2)::DECIMAL(14, 2) AS pay_new
      FROM valid v, range(1, v.term_months + 1) t(k);

      -- =======================================================================
      -- Liability at every month end t: the present value of the payments still
      -- to come. This is the effective interest method in closed form, so it
      -- cannot drift. "new" uses the payments after the index change.
      -- =======================================================================
      CREATE TABLE liab AS
      SELECT v.lease_id, t,
             round(coalesce(sum(p.pay_old * pow(1 + v.r, -(p.tau - t))) FILTER (WHERE p.tau > t), 0), 2)::DECIMAL(14, 2) AS l_old,
             round(coalesce(sum(p.pay_new * pow(1 + v.r, -(p.tau - t))) FILTER (WHERE p.tau > t), 0), 2)::DECIMAL(14, 2) AS l_new
      FROM valid v, range(0, v.term_months + 1) m(t), payments p
      WHERE p.lease_id = v.lease_id AND v.exemption IS NULL
      GROUP BY v.lease_id, t;

      CREATE TABLE sched AS
      SELECT v.lease_id, l.t, v.cpi_from_month AS m, v.term_months AS n, v.r,
             -- The index change takes effect with the first changed payment (IFRS 16.42(b)), so the
             -- remeasurement sits at the start of month m: l_pre is the balance closing a month,
             -- l_post the balance the next month starts from.
             CASE WHEN v.cpi_from_month IS NOT NULL AND l.t >= v.cpi_from_month - 1 THEN l.l_new ELSE l.l_old END AS l_post,
             CASE WHEN v.cpi_from_month IS NOT NULL AND l.t > v.cpi_from_month - 1 THEN l.l_new ELSE l.l_old END AS l_pre,
             coalesce((SELECT sum(CASE WHEN v.cpi_from_month IS NOT NULL AND l.t >= v.cpi_from_month THEN p.pay_new ELSE p.pay_old END)
                       FROM payments p WHERE p.lease_id = v.lease_id AND p.tau = l.t), 0) AS paid
      FROM valid v JOIN liab l USING (lease_id);

      -- Right-of-use asset: initial liability plus payments made at commencement,
      -- straight-line over the term. The remeasurement adjusts the asset and is
      -- depreciated over the remaining months.
      CREATE TABLE rou AS
      WITH base AS (
        SELECT s.lease_id, s.n, s.m,
               max(s.l_post) FILTER (WHERE s.t = 0) + max(s.paid) FILTER (WHERE s.t = 0) AS cost0,
               coalesce(max(s.l_post - s.l_pre) FILTER (WHERE s.t = s.m - 1), 0) AS delta
        FROM sched s GROUP BY s.lease_id, s.n, s.m
      )
      SELECT b.lease_id, s.t, b.cost0, b.delta,
             b.cost0 + CASE WHEN b.m IS NOT NULL AND s.t >= b.m - 1 THEN b.delta ELSE 0 END AS cost,
             b.cost0 + CASE WHEN b.m IS NOT NULL AND s.t > b.m - 1 THEN b.delta ELSE 0 END AS cost_pre,
             (CASE WHEN b.m IS NULL OR s.t < b.m THEN round(b.cost0 * s.t / b.n, 2)
                   ELSE round(b.cost0 * (b.m - 1) / b.n, 2)
                        + round((b.cost0 - round(b.cost0 * (b.m - 1) / b.n, 2) + b.delta) * (s.t - b.m + 1) / (b.n - b.m + 1), 2) END)::DECIMAL(14, 2) AS acc_dep
      FROM base b JOIN sched s USING (lease_id);

      -- =======================================================================
      -- The period, lease by lease.
      -- =======================================================================
      CREATE TABLE movements AS
      SELECT v.lease_id, v.asset_class, v.description, v.t_p AS lease_month,
             CASE WHEN v.t_p = 1 THEN 0 ELSE prev.l_pre END AS opening_liability,
             CASE WHEN v.t_p = 1 THEN prev.l_post ELSE 0 END AS additions,
             CASE WHEN v.t_p > 1 THEN prev.l_post - prev.l_pre ELSE 0 END AS remeasurement,
             cur.l_pre - prev.l_post + cur.paid AS interest,
             cur.paid AS payments,
             CASE WHEN v.terminated_after_month = v.t_p THEN cur.l_pre ELSE 0 END AS terminated_liability,
             CASE WHEN v.terminated_after_month = v.t_p THEN 0 ELSE cur.l_pre END AS closing_liability,
             CASE WHEN v.t_p = 1 THEN prev.paid ELSE 0 END AS paid_at_commencement,
             CASE WHEN v.t_p = 1 THEN 0 ELSE rp.cost_pre - rp.acc_dep END AS opening_rou,
             CASE WHEN v.t_p = 1 THEN rp.cost ELSE 0 END AS rou_additions,
             CASE WHEN v.t_p > 1 THEN rp.cost - rp.cost_pre ELSE 0 END AS rou_remeasurement,
             rc.acc_dep - rp.acc_dep AS depreciation,
             CASE WHEN v.terminated_after_month = v.t_p THEN rc.cost_pre - rc.acc_dep ELSE 0 END AS terminated_rou,
             CASE WHEN v.terminated_after_month = v.t_p THEN 0 ELSE rc.cost_pre - rc.acc_dep END AS closing_rou,
             rc.cost_pre AS rou_cost, rc.acc_dep AS rou_accumulated_depreciation,
             -- Independent check of the interest: opening balance times the monthly rate.
             round(prev.l_post * v.r, 2) AS interest_check,
             -- Principal falling due within 12 months: the current portion.
             CASE WHEN v.terminated_after_month = v.t_p THEN 0
                  ELSE cur.l_pre - coalesce((SELECT l_pre FROM sched s12 WHERE s12.lease_id = v.lease_id AND s12.t = least(v.t_p + 12, v.term_months)), 0) END AS current_portion
      FROM valid v
      JOIN sched prev ON prev.lease_id = v.lease_id AND prev.t = v.t_p - 1
      JOIN sched cur ON cur.lease_id = v.lease_id AND cur.t = v.t_p
      JOIN rou rp ON rp.lease_id = v.lease_id AND rp.t = v.t_p - 1
      JOIN rou rc ON rc.lease_id = v.lease_id AND rc.t = v.t_p
      WHERE v.exemption IS NULL AND v.t_p BETWEEN 1 AND v.term_months
        AND (v.terminated_after_month IS NULL OR v.t_p <= v.terminated_after_month);

      CREATE TABLE exempt AS
      SELECT lease_id, description, exemption, monthly_payment AS expense
      FROM valid WHERE exemption IS NOT NULL AND t_p BETWEEN 1 AND term_months;

      INSERT INTO blockers
      SELECT 'INTEREST_CHECK', lease_id, 'Interest ' || interest || ' against ' || interest_check || ' from the opening balance and rate'
      FROM movements WHERE abs(interest - interest_check) > 0.05;

      -- Undiscounted payments still to come, for the maturity analysis.
      CREATE TABLE maturity AS
      SELECT CASE WHEN p.tau - v.t_p <= 12 THEN '1. Within 1 year' WHEN p.tau - v.t_p <= 60 THEN '2. 1 to 5 years' ELSE '3. More than 5 years' END AS bucket,
             count(DISTINCT p.lease_id) AS leases, sum(CASE WHEN v.cpi_from_month IS NOT NULL AND p.k >= v.cpi_from_month AND v.t_p >= v.cpi_from_month - 1 THEN p.pay_new ELSE p.pay_old END) AS undiscounted_payments
      FROM payments p JOIN valid v USING (lease_id) JOIN movements mv USING (lease_id)
      WHERE p.tau > v.t_p AND v.exemption IS NULL AND (v.terminated_after_month IS NULL OR v.terminated_after_month > v.t_p)
      GROUP BY 1 ORDER BY 1;

      -- =======================================================================
      -- Journals.
      -- =======================================================================
      CREATE TABLE journal AS
      SELECT row_number() OVER () AS line, * FROM (
        SELECT '1680' AS account, 'Right-of-use assets' AS account_name, sum(rou_additions)::DECIMAL(14, 2) AS debit, 0::DECIMAL(14, 2) AS credit, 'New leases' AS memo FROM movements HAVING sum(rou_additions) <> 0
        UNION ALL SELECT '2600', 'Lease liabilities', 0, sum(additions), 'New leases' FROM movements HAVING sum(additions) <> 0
        UNION ALL SELECT '1000', 'Cash', 0, sum(paid_at_commencement), 'New leases: paid at commencement' FROM movements HAVING sum(paid_at_commencement) <> 0
        UNION ALL SELECT '1680', 'Right-of-use assets', greatest(sum(rou_remeasurement), 0), greatest(-sum(rou_remeasurement), 0), 'Remeasurement for index change' FROM movements HAVING sum(rou_remeasurement) <> 0
        UNION ALL SELECT '2600', 'Lease liabilities', greatest(-sum(remeasurement), 0), greatest(sum(remeasurement), 0), 'Remeasurement for index change' FROM movements HAVING sum(remeasurement) <> 0
        UNION ALL SELECT '7400', 'Interest on lease liabilities', sum(interest), 0, 'Interest' FROM movements
        UNION ALL SELECT '2600', 'Lease liabilities', 0, sum(interest), 'Interest' FROM movements
        UNION ALL SELECT '2600', 'Lease liabilities', sum(payments), 0, 'Lease payments' FROM movements
        UNION ALL SELECT '1000', 'Cash', 0, sum(payments), 'Lease payments' FROM movements
        UNION ALL SELECT '6600', 'Depreciation of right-of-use assets', sum(depreciation), 0, 'Depreciation' FROM movements
        UNION ALL SELECT '1690', 'Right-of-use accumulated depreciation', 0, sum(depreciation), 'Depreciation' FROM movements
        UNION ALL SELECT '2600', 'Lease liabilities', sum(terminated_liability), 0, 'Termination ' || string_agg(lease_id, ', ') FROM movements WHERE terminated_liability <> 0 HAVING count(*) > 0
        UNION ALL SELECT '1690', 'Right-of-use accumulated depreciation', sum(rou_accumulated_depreciation), 0, 'Termination ' || string_agg(lease_id, ', ') FROM movements WHERE terminated_rou <> 0 OR terminated_liability <> 0 HAVING count(*) > 0
        UNION ALL SELECT '1680', 'Right-of-use assets', 0, sum(rou_cost), 'Termination ' || string_agg(lease_id, ', ') FROM movements WHERE terminated_rou <> 0 OR terminated_liability <> 0 HAVING count(*) > 0
        UNION ALL SELECT CASE WHEN sum(terminated_liability - terminated_rou) >= 0 THEN '7210' ELSE '6910' END,
                         CASE WHEN sum(terminated_liability - terminated_rou) >= 0 THEN 'Gain on lease termination' ELSE 'Loss on lease termination' END,
                         greatest(-sum(terminated_liability - terminated_rou), 0), greatest(sum(terminated_liability - terminated_rou), 0), 'Termination ' || string_agg(lease_id, ', ')
          FROM movements WHERE terminated_rou <> 0 OR terminated_liability <> 0 HAVING count(*) > 0 AND sum(terminated_liability - terminated_rou) <> 0
        UNION ALL SELECT '6610', 'Short-term and low-value lease expense', sum(expense), 0, 'Exempt leases: ' || string_agg(lease_id, ', ') FROM exempt HAVING count(*) > 0
        UNION ALL SELECT '1000', 'Cash', 0, sum(expense), 'Exempt leases: ' || string_agg(lease_id, ', ') FROM exempt HAVING count(*) > 0
      );

      INSERT INTO blockers
      SELECT 'JOURNAL_OUT_OF_BALANCE', 'journal', 'debits ' || sum(debit) || ', credits ' || sum(credit) FROM journal HAVING sum(debit) <> sum(credit);
      INSERT INTO blockers
      SELECT 'ROLLFORWARD_DOES_NOT_TIE', lease_id,
             'liability ' || (opening_liability + additions + remeasurement + interest - payments - terminated_liability - closing_liability)
             || ', right-of-use ' || (opening_rou + rou_additions + rou_remeasurement - depreciation - terminated_rou - closing_rou)
      FROM movements
      WHERE opening_liability + additions + remeasurement + interest - payments - terminated_liability <> closing_liability
         OR opening_rou + rou_additions + rou_remeasurement - depreciation - terminated_rou <> closing_rou;

      COPY (SELECT * EXCLUDE (r, t_p) FROM valid ORDER BY lease_id) TO '{{ outputFiles.register }}' (HEADER, DELIMITER ',');
      COPY (SELECT * FROM movements ORDER BY lease_id) TO '{{ outputFiles.movements }}' (HEADER, DELIMITER ',');
      COPY maturity TO '{{ outputFiles.maturity }}' (HEADER, DELIMITER ',');
      COPY journal TO '{{ outputFiles.journal }}' (HEADER, DELIMITER ',');
      COPY blockers TO '{{ outputFiles.blockers }}' (HEADER, DELIMITER ',');

      SELECT
        (SELECT count(*) FROM movements) AS leases_on_balance_sheet,
        (SELECT count(*) FROM exempt) AS exempt_leases,
        (SELECT coalesce(string_agg(lease_id || ' ' || description, ', '), '') FROM movements WHERE additions <> 0) AS new_leases,
        (SELECT coalesce(string_agg(lease_id || ' ' || remeasurement, ', '), '') FROM movements WHERE remeasurement <> 0) AS remeasurements,
        (SELECT coalesce(string_agg(lease_id || ' gain/loss ' || (terminated_liability - terminated_rou), ', '), '') FROM movements WHERE terminated_liability <> 0) AS terminations,
        (SELECT sum(opening_liability) FROM movements) AS opening_liability,
        (SELECT sum(additions) FROM movements) AS additions,
        (SELECT sum(remeasurement) FROM movements) AS remeasurement,
        (SELECT sum(interest) FROM movements) AS interest,
        (SELECT sum(payments) FROM movements) AS payments,
        (SELECT sum(terminated_liability) FROM movements) AS terminated_liability,
        (SELECT sum(closing_liability) FROM movements) AS closing_liability,
        (SELECT sum(current_portion) FROM movements) AS current_portion,
        (SELECT sum(closing_liability) - sum(current_portion) FROM movements) AS non_current_portion,
        (SELECT sum(opening_rou) FROM movements) AS opening_rou,
        (SELECT sum(depreciation) FROM movements) AS depreciation,
        (SELECT sum(closing_rou) FROM movements) AS closing_rou,
        (SELECT coalesce(sum(expense), 0) FROM exempt) AS exempt_expense,
        (SELECT string_agg(bucket || ' ' || undiscounted_payments, ', ' ORDER BY bucket) FROM maturity) AS maturity_detail,
        coalesce((SELECT sum(opening_liability) FROM movements) - {{ outputs.period_control.values.locked_liability }}::DECIMAL(14, 2), 0) AS liability_restatement,
        coalesce((SELECT sum(opening_rou) FROM movements) - {{ outputs.period_control.values.locked_rou }}::DECIMAL(14, 2), 0) AS rou_restatement,
        (SELECT count(*) FROM blockers) AS blockers,
        (SELECT coalesce(string_agg(code || ' ' || ref || ': ' || detail, '; ' ORDER BY code, ref), '') FROM blockers) AS blocker_detail;

  - id: result
    type: io.kestra.plugin.core.output.OutputValues
    values:
      r: "{{ (outputs.measure.outputs | last).row | toJson }}"

  - id: log_close
    type: io.kestra.plugin.core.log.Log
    message: |
      Lease close {{ inputs.period }} ({{ inputs.scenario }}).
      {{ outputs.result.values.r | jq('"\(.leases_on_balance_sheet) leases on balance sheet, \(.exempt_leases) exempt (expense \(.exempt_expense)). New: \(.new_leases). Remeasured: \(.remeasurements). Terminated: \(.terminations)."') | first }}
      {{ outputs.result.values.r | jq('"Liability \(.opening_liability) + new \(.additions) + remeasurement \(.remeasurement) + interest \(.interest) - payments \(.payments) - terminations \(.terminated_liability) = \(.closing_liability) (current \(.current_portion), non-current \(.non_current_portion))."') | first }}
      {{ outputs.result.values.r | jq('"Right-of-use \(.opening_rou) -> \(.closing_rou), depreciation \(.depreciation). Opening restated against the last close: liability \(.liability_restatement), right-of-use \(.rou_restatement)."') | first }}
      {{ outputs.result.values.r | jq('"Maturity of undiscounted payments: \(.maturity_detail). Blockers: \(.blockers). \(.blocker_detail)"') | first }}

  - id: evidence
    type: io.kestra.plugin.core.namespace.UploadFiles
    namespace: "{{ flow.namespace }}"
    filesMap:
      "{{ render(vars.pack_dir) }}/register.csv": "{{ outputs.measure.outputFiles.register }}"
      "{{ render(vars.pack_dir) }}/movements.csv": "{{ outputs.measure.outputFiles.movements }}"
      "{{ render(vars.pack_dir) }}/maturity-analysis.csv": "{{ outputs.measure.outputFiles.maturity }}"
      "{{ render(vars.pack_dir) }}/journal.csv": "{{ outputs.measure.outputFiles.journal }}"
      "{{ render(vars.pack_dir) }}/blockers.csv": "{{ outputs.measure.outputFiles.blockers }}"

  - id: decide
    type: io.kestra.plugin.core.flow.Switch
    value: >-
      {%- set r = outputs.result.values.r -%} {%- if (r | jq('.blockers') |
      first) > 0 -%}BLOCKED {%- elseif (r | jq('.liability_restatement') |
      first) != 0 or (r | jq('.rou_restatement') | first) != 0 -%}RESTATED {%-
      elseif ((r | jq('.remeasurements') | first) != '' or (r |
      jq('.terminations') | first) != '') and inputs.approved_by == ''
      -%}NEEDS_APPROVAL {%- else -%}READY{%- endif -%}
    cases:
      READY:
        - id: lock_period
          type: io.kestra.plugin.core.kv.Set
          description: The closing balances become the opening balances the next close
            must start from.
          key: "{{ vars.lock_key }}"
          kvType: JSON
          value: |
            {"period": "{{ inputs.period }}", "closed_at": "{{ now() }}", "execution_id": "{{ execution.id }}",
             "liability": {{ outputs.result.values.r | jq('.closing_liability') | first }},
             "current_portion": {{ outputs.result.values.r | jq('.current_portion') | first }},
             "rou_net": {{ outputs.result.values.r | jq('.closing_rou') | first }},
             "approved_by": "{{ inputs.approved_by }}"}

        - id: log_closed
          type: io.kestra.plugin.core.log.Log
          message: "Leases for {{ inputs.period }} closed. Liability {{
            outputs.result.values.r | jq('.closing_liability') | first }},
            right-of-use {{ outputs.result.values.r | jq('.closing_rou') | first
            }}. Evidence in {{ render(vars.pack_dir) }}."

      NEEDS_APPROVAL:
        - id: approval_required
          type: io.kestra.plugin.core.execution.Fail
          errorMessage: >-
            The period has a remeasurement ({{ outputs.result.values.r |
            jq('.remeasurements') | first }}) or a termination ({{
            outputs.result.values.r | jq('.terminations') | first }}). Review {{
            render(vars.pack_dir) }}/movements.csv and run again with
            approved_by.

      RESTATED:
        - id: restated
          type: io.kestra.plugin.core.execution.Fail
          errorMessage: >-
            The opening balances no longer match the last close: liability off
            by {{ outputs.result.values.r | jq('.liability_restatement') | first
            }}, right-of-use by {{ outputs.result.values.r |
            jq('.rou_restatement') | first }}. The register changed for a closed
            period. Correct it or reopen the period.

    defaults:
      - id: blocked
        type: io.kestra.plugin.core.execution.Fail
        errorMessage: "Leases for {{ inputs.period }} not closed: {{
          outputs.result.values.r | jq('.blocker_detail') | first }}"

Since IFRS 16, almost every lease is on the balance sheet: a lease liability, and a right-of-use asset depreciated over the term. The month-end entries are mechanical. The parts that go wrong are the events and the reconciliation:

a rent indexed to inflation changes,
a car is handed back early,
a new office starts,
the auditors want the liability rolled forward, split into current and non-current, and its undiscounted maturities disclosed.

This flow runs the whole close from the lease register and refuses to lock a month that does not tie.

It runs with no setup and no network. DuckDB holds a register of 20 leases:

offices, a warehouse and a store, with annual escalation, paid in arrears or in advance;
ten company cars, one of them handed back early;
forklifts and a colocation contract;
a head office lease indexed to CPI, revised in September 2026;
a new regional office starting in September 2026;
a 9-month pop-up store and office printers, which are exempt.

This blueprint was created by zkasuran.

Measurement

Item	Rule
Liability at each month end	Present value of the payments still to come, at the monthly equivalent of the incremental borrowing rate. This is the effective interest method in closed form, so the schedule cannot drift
Interest	Closing - opening + payments. It is checked independently against the opening balance times the monthly rate
Payments in advance	The payment at commencement goes into the right-of-use asset, not the liability
Right-of-use asset	Initial liability + payments at commencement, straight-line over the term
Index-linked payment change	Remeasured at the original rate when the changed payment takes effect (IFRS 16.42(b)). The asset is adjusted by the same amount and depreciated over the remaining months
Early termination	The liability and the asset are derecognized at month end, with the difference as gain or loss
Short-term (12 months or less) and low-value	Payments expensed (IFRS 16.6)
Current portion	Principal falling due within 12 months
Maturity analysis	Undiscounted remaining payments within 1 year, 1 to 5 years, and beyond (IFRS 16.58)

Controls

Blocks on a lease without a borrowing rate, a non-positive payment, a missing term, or a "short-term" lease longer than 12 months.
Blocks when the interest check fails, a lease's liability or asset does not roll forward, or the journal does not balance.
Restatement check: the opening balances recomputed from today's register must equal the closing balances the last close stored in KV. If they differ, the register changed for a closed month, and the flow stops.
A remeasurement or a termination needs approved_by.

How it works

period_control and period_gate refuse a closed period or one whose previous period is open, and read the last closing balances.
measure (io.kestra.plugin.jdbc.duckdb.Queries) builds the payments of every lease and the liability and asset at every month end. It then computes the period's movements lease by lease, the maturity analysis, the blockers and the journal.
log_close prints both roll-forwards. evidence stores register.csv, movements.csv (one row per lease), maturity-analysis.csv, journal.csv and blockers.csv.
decide (io.kestra.plugin.core.flow.Switch):
- READY: locks the period with the closing liability, current portion and right-of-use asset.
- Otherwise it fails with the reason: BLOCKED, RESTATED or NEEDS_APPROVAL.

Tested end to end

On Kestra 2.0.3 OSS, in this order.

Run	Inputs	Result
1	2026-08	15 leases on balance sheet, 2 exempt. Liability 5,356,171.14 + interest 20,997.29 - payments 115,832.75 = 5,261,335.68, current 1,096,300.93. Right-of-use depreciation 98,361.49. Closed
2	2026-09	New office L16 (798,306.81) and L01 CPI remeasurement (+100,013.54, rent +3.4%): stops for approval
3	same, `approved_by`	Closed. Liability 6,054,422.70, right-of-use 5,431,838.08
4	2026-10	L09 handed back: liability 23,879.62, asset 23,646.07, gain 233.55. Stops for approval, then closes
5	2026-10 again / 2026-12	Refused
6	2026-11 `ISSUES`	Blocked by the 3 issues: L19 without a borrowing rate, L20 with a payment of -500.00, L21 with no term
7	2026-11, warehouse rent changed to 29,500 in the register	`RESTATED`: liability off by 42,279.74, right-of-use by 37,071.12 against the September close
8	2026-11	Closed

I checked the closing liability of every lease for August, September and October against a separate month-by-month amortization loop in Python: interest on the balance, minus the payment, with the remeasurement at the start of September. There were 0 differences above 0.02.

Problems found while testing

The remeasurement landed a month early. The first version closed August with the CPI-revised liability, because the revision takes effect at the start of September, which is the same instant as the end of August. The schedule now keeps two balances at each month end: the one closing the month (l_pre) and the one the next month starts from (l_post). The difference between them is the remeasurement.
Floating point broke the roll-forward. Differences of 1e-10 failed the tie-out until every amount was cast to DECIMAL(14, 2).
The termination memo listed every lease. A missing filter. It now names only the terminated lease.

Inputs

Input	Default	Purpose
`period`	`2026-08`	`YYYY-MM`, validated
`scenario`	`CLEAN`	Demo only
`approved_by`	empty	Needed for remeasurements and terminations

Use it with your data

Replace leases with your lease register:
- commencement (first of a month), term in months, timing (ADVANCE or ARREARS), monthly payment, annual escalation and borrowing rate;
- for an index change: the month it takes effect and the change;
- for an early termination: its last month;
- SHORT_TERM or LOW_VALUE for exempt leases.
Map the accounts (1680, 1690, 2600, 6600, 6610, 7400, 7210, 6910) to your chart of accounts.

Things to know

Initial direct costs, lease incentives, restoration provisions, purchase options and residual value guarantees are not modelled. Add them to the initial measurement of the asset or liability.
A modification that changes the scope or the consideration needs a new discount rate (IFRS 16.44-46). This flow handles index-linked changes, which keep the original rate.
Reopening a period is deliberate: delete the KV key lease_close.

Close IFRS 16 leases each month with remeasurements, terminations and the maturity analysis

Measurement

Controls

How it works

Tested end to end

Problems found while testing

Inputs

Use it with your data

Things to know

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Close IFRS 16 leases each month with remeasurements, terminations and the maturity analysis

Measurement

Controls

How it works

Tested end to end

Problems found while testing

Inputs

Use it with your data

Things to know

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Business Blueprints

Generate and Deliver an AI-Powered Daily News Digest from RSS Feeds via Slack and Email

AI Slack Chatbot with Per-User Conversation Memory Using OpenAI and KV Store

Sync Postgres records to Algolia index

Automated Customer Feedback Sentiment Analysis and Reporting

Automate AML transaction monitoring and velocity alerts using DuckDB

Classify and Route Customer Support Emails with AI and Flow Switch

More Data Blueprints

Build an ETL Pipeline with Airbyte Cloud and dbt Cloud for SaaS Analytics

Build an ETL Pipeline with Parallel Airbyte Syncs and dbt Core Transformations

Generate and Execute SQL Queries from Natural Language Using OpenAI GPT-4o

Airflow DAG Run SLA Gate

AI Synthetic Relational Data Generator and Referential Integrity Gate

Build an ETL Pipeline with Airbyte Cloud and dbt Core for SaaS Analytics

New to Kestra?