How To Build An IFRS 16 Lease Liability Schedule Without Breaking The Audit Trail

Most schedules do not fail in period 1. They fail when nobody can explain what changed in period 37.

Most lease liability schedules do not break on day one. They break later, when someone asks a simple question: what changed between one period and the next?

By period 37, the present value math is rarely the real problem. The problem is what happened around it. Payment timing assumptions shift. A discount rate changes mid-schedule without a documented event. Someone inserts an off-cycle row and does not tell anyone. The schedule still opens. The numbers still look plausible. But the trail is gone.

That is the real IFRS 16 risk for many finance teams. Not that they cannot produce a schedule, but that they cannot defend the path from contract inputs to each row that follows. This article is about how to build a lease liability schedule that stays reviewable - period by period, row by row, from commencement to end of lease.

I built an IFRS 16 calculator to handle this deterministically, but the underlying mechanics are worth understanding whether you use a calculator or build your own schedule. Let me walk through them.

What the schedule actually tracks

Under IFRS 16, the lease liability is the present value of future lease payments. At commencement you measure it once. After that, it unwinds through interest and reduces through payments. The schedule is just the period-by-period record of that movement.

Four things happen every period:

• Opening balance - the liability carried forward from last period
• Interest unwind - opening balance times the periodic discount rate
• Lease payment - the contractual cash outflow
• Closing balance - opening plus interest, minus the payment

Simple on paper. If the opening balance is $100,000, the periodic rate is 0.5%, and the lease payment is $8,000, the closing balance should be $92,500. A reviewer should be able to prove that from one row without rebuilding the file. The hard part is getting the first period right. Payment timing, discounting conventions, and commencement adjustments all interact before row one exists.

Where the audit trail starts

The initial lease liability is the present value of all future lease payments, discounted at the rate implicit in the lease - or your incremental borrowing rate if the implicit rate is not readily determinable.

It depends on four things:

• Lease term - the non-cancellable period, plus any extension options you are reasonably certain to exercise
• Payment amount and frequency - monthly, quarterly, semi-annual or annual
• Payment timing - in advance or in arrears
• Discount rate - annual rate, applied per period

Payment timing matters more than people expect. An in-advance payment at commencement reduces the initial liability straight away because the first payment is not discounted. An in-arrears schedule pushes every payment forward by one period. That single choice changes the present value, the interest profile, and every row that follows.

In Excel, the present value of each payment is:

PV = Payment / (1 + rate/n)^period

For in-advance leases, period 0 has a discount factor of 1.0 - the payment is already at present value.

A calculator that handles this deterministically removes the risk of a mistyped formula or an off-by-one period error. Excel export is available on Pro plans when you need the workbook, but the engine that powers the schedule is the same either way.

The period-by-period unwind

Once the initial liability is set, each period follows the same pattern. The interest rate stays at the original discount rate unless a modification or reassessment triggers a change. CPI and index remeasurements keep the original rate (IFRS 16.43). Modifications use a revised rate (IFRS 16.45).

Get these mixed up and the schedule drifts silently. I have seen teams apply a modification rate to a CPI event without realising it, then wonder why month 37 does not reconcile.

A clean schedule makes every period traceable. If month 37's interest is $412.18, the reviewer should be able to multiply the opening balance by the periodic rate and get $412.18 without rebuilding anything.

Where spreadsheets drift

The most common lease liability errors are not formula errors. The formulas are fine. The assumptions they rest on are wrong.

• Payment timing off by one period. An in-arrears schedule accidentally using in-advance discounting will understate the initial liability and shift every interest row.
• Discount rate changed mid-schedule with no modification event. The audit trail breaks. The schedule becomes unreproducible.
• Off-cycle payment rows inserted manually. A payment date the lease contract does not support. The schedule must stay on one cadence engine.
• Historical rows edited after the fact. Once a period boundary has passed, that row is immutable. Reopening it destroys the forward-only integrity of the model.
• Rounding mismatches. Using a rounded payment in the PV calculation but the unrounded payment in the schedule creates penny-level drift that compounds.

The IFRS Connect engine enforces eight determinism guardrails specifically to prevent this class of error. Contractual cadence only. No silent date correction. No reopening history. No inferred rate changes. Same-day events in explicit priority order. These are product controls, not IFRS 16 laws - but they keep the output reproducible.

What reviewers actually check

When an auditor looks at your lease liability schedule, they are not judging your discount rate. That is a management call. They are checking:

• Can I reproduce the initial present value from the stated inputs?
• Does the interest unwind correctly every period?
• Do closing balances roll forward without gaps?
• Are remeasurement events properly dated and explained?
• Is the final liability zero, or equal to the last payment?

A schedule that passes those checks with transparent, traceable rows is audit-ready. One that makes the reviewer unpack nested formulas or guess which rate was used in month 14 is not.

Payment timing - the most overlooked variable

Advance vs arrears is probably the single biggest source of schedule divergence between two people looking at the same lease.

Take 12 annual payments of $10,000 at 5%. The in-advance lease makes the first payment at commencement, so that payment adds nothing to the initial liability. The in-arrears lease discounts all 12 payments. Higher initial liability, more interest over the term, different ROU depreciation. Same lease, completely different numbers.

The IFRS 16 lease liability guide covers the measurement logic in more detail, and the broader calculation guide shows how the schedule ties back to the full model. The short version: confirm the payment timing before you build anything.

Deterministic means the same inputs always give the same output

Sounds obvious. Breakable in surprising ways.

A TODAY() function that shifts the discounting date each time the file opens. A manual override typed into what looks like a formula cell. A sort that reorders rows and breaks the dependency chain. A VLOOKUP returning the wrong value after someone inserts a column.

The IFRS Connect methodology page describes the deterministic controls the calculator applies. Single cadence engine. Explicit same-day ordering. Audit-visible constraint enforcement. The methodology is not an IFRS 16 requirement. It is a product control, chosen to keep the output reproducible.

If you are building your own schedule, the same discipline helps. Lock the inputs. Keep formulas explicit. Never use a formula cell as an override cell.

Building the schedule with confidence

The lease liability schedule is the foundation. Get it right and the ROU asset, journal entries, and disclosures fall into place. Get it wrong and everything downstream carries the error.

For a few simple leases, a locked Excel schedule can work. For multiple leases with modifications, CPI adjustments, and audit evidence requirements, silent drift becomes a real risk.

The IFRS 16 calculator generates the full liability schedule, ROU asset schedule, and journal entries from your inputs. Every row traceable. Every period reproducible. The engine is deterministic. The export workbook provides the evidence a reviewer expects.

Start with the inputs. Keep the cadence clean. Never reopen history. And make sure every row tells the reviewer how it got there.

This is general IFRS 16 education, not accounting advice. Review material judgments, assumptions and unusual lease terms with your adviser or auditor.