What a mid-term change does to a lease workbook

A lease workbook is a good tool for a lease that does not change. Payments in a column, a discount rate in a cell, a present value, an amortisation table underneath. It is legible, it is checkable, and for a fixed five-year property lease it will serve you fine for five years.

Then a term extends, an index resets, or a cage is added, and the workbook stops being a record of a schedule and becomes a record of one version of a schedule.

Why a modification is not an edit

The instinct is to change the inputs and let the sheet recalculate. That produces a number, and the number is wrong in a specific way: it recalculates the whole life of the lease as if the new terms had always applied.

What actually needs to happen is that the schedule up to the change date stays exactly as it was, because that is what was recognised and posted, and a new measurement applies from the change date forward. Two regimes, one agreement, joined at a date.

A workbook can express that. It expresses it by becoming two workbooks, or by growing a second block underneath the first with its own inputs, its own present value, and a set of formulas that reference the closing position of the block above. Do that three times to one agreement and the file stops being legible to anyone who did not build it.

The four changes that arrive

Term extension or reduction. The most common. The remaining payments change, the remaining period changes, and the measurement is redone from the change date.

Consideration change. More space, more power, more devices, or less. Often bundled with a term change, which means both variables move at once.

Index or rate reset. A CPI-linked escalation, a benchmark review, a floating rate on a finance arrangement. These are the ones that arrive quietly, because nobody signs anything. A clause fires, the payment changes, and unless somebody is watching the clause the schedule silently diverges from what is being paid.

Option exercise. A renewal or termination option becomes reasonably certain, or stops being so. This one is a judgement, made by your accountants, and the system's job is to record when the judgement changed and what it changed to.

Where portfolios differ from leases

Everything above is manageable one agreement at a time. The reason lease workbooks fail is that modifications do not arrive one at a time.

An index reset is the clearest case. A CPI-linked escalation clause does not apply to one agreement, it applies to every agreement carrying that clause, on the same date, driven by a published figure. That is a portfolio operation: apply a new index to a population, remeasure each affected schedule, produce the aggregate journal, and record which agreements were touched and why.

In a workbook it is forty files, opened one at a time, each with a slightly different layout, over a week, in the middle of a close.

The same is true of a rate change applied across a class of agreements, or a policy change in how a component split is derived. The unit of work is the population, and the tool holds one record.

What a portfolio-safe version does

Four things, none of them exotic.

It keeps versions rather than overwriting. Every measurement is dated and retained. The current schedule is the latest version; prior periods still report under the version that was live at the time. Nothing is recalculated retrospectively unless somebody explicitly asks for it.

It records the trigger. A modification is an event with a cause: an amendment, a clause firing, a judgement changing. The cause is stored against the change, so the question "why did this schedule change in March" is a lookup rather than an investigation.

It operates on sets. Apply an index to every agreement with that clause. Remeasure a class. Produce one journal for the population and the per-agreement detail behind it.

It reconciles by construction. The sum of the schedules is the subledger, the subledger agrees to the ledger, and the difference is zero because the same data produced both, not because somebody checked.

What that leaves you doing

The judgements. Whether an option is reasonably certain to be exercised, what discount rate your policy specifies, how a component split is derived, whether a change is a modification or a new agreement. Those are yours and they should be.

What you stop doing is the mechanical part: rebuilding a schedule because a date passed, opening forty files because an index published, and reconstructing in March why a number changed the previous August.