Build an ASC 842 Lease Modification Tracker That Auto-Remeasures Liabilities and ROU Assets

Here’s a thing that sounds boring and absolutely is not: on 1 July 2024, one office lease in our example quietly grows from a $191,502.24 liability to a $308,503.39 one. Same building. Same tenant. Nobody moved. The landlord and the tenant simply agreed to keep going for another two years, and ASC 842 says that agreement resets the measurement of the whole lease.

Miss it and your balance sheet is 38% light on that lease. Catch it but rebuild the schedule the usual way — new tab, fresh PV, hardcoded rate — and there’s a very good chance you’ll get the liability right and the right-of-use asset wrong, which is the failure mode nobody spots, because the liability is the number everyone checks.

Grab a coffee. We’ll build the thing properly: the classic version that opens in any Excel back to 2007, then the 365 version that spills both schedules out of three cells and redraws itself the moment an input changes.

Who this quietly bites

You, if you’re a corporate accountant, a controller, in FP&A, or on the audit side of the table — and your company signs leases. Which is to say: nearly everyone.

The pattern is always the same. A 60-month office lease was set up beautifully at commencement. Someone built a lovely amortisation table. Eighteen months later the business decides to stay, the landlord wants more rent for the privilege, and the lease is extended by 24 months at $5,500 a month instead of $5,000. Your incremental borrowing rate has drifted from 5.5% to 6.2% in the meantime, because that’s what rates did.

Under ASC 842 that is a modification, and it triggers a full remeasurement of the lease liability and an adjustment to the right-of-use asset. Not a footnote. Not a memo. A remeasurement.

Here is what actually has to move on that one day:

Three numbers change, not one. And notice the ROU asset and the liability are not equal — they never were. They start $6,000 apart, because the broker’s commission at signing capitalises into the asset and touches the liability not at all. Any template that treats “the ROU asset” as a synonym for “the lease liability” is already wrong on day one; it just doesn’t show until something makes you look.

Why the usual formula gets it wrong

Search for a lease calculator and you’ll get something that asks for four things — term, payment, discount rate, commencement date — and hands back a gorgeous 60-row table.

It is perfect. On day one. It has no concept of day 547 — 1 July 2024, if you’re counting.

Because on day 547 you need five things it was never built to do:

1. Read the carrying liability at the modification date — not recompute it, read it, from the schedule you’ve been running.
2. Read the carrying ROU asset at the same instant, which is a different number.
3. Decide whether this modification is even a remeasurement at all, or a separate contract.
4. Re-discount the revised payments at the revised rate.
5. Recut the straight-line lease cost over the remaining term — the step that quietly disappears from every version of this I have ever been handed.

And there’s a sixth thing, which is the one that keeps me up at night: every wrong answer here still produces a schedule that looks completely finished. Columns tie. Balances trend down. Nothing goes red.

Let me show you exactly how expensive each near-miss is, all measured at the same instant on 1 July 2024:

That last near-miss is the one worth staring at. Commercial rent is paid in advance — on the first of the month, not the last. That makes it an annuity due, which means PV needs a trailing 1 and interest has to accrue on opening balance − payment, not on the full opening balance.

Get the interest base wrong while discounting correctly and the error is tiny per month — payment × rate, about $22.92 here — but it never nets out. Run it across all 60 periods and the schedule finishes with $1,578.52 still outstanding on a lease that has been fully paid. I’ve seen that number written off as “a rounding thing.” It is not a rounding thing.

The fix, two ways

The scenario, in full. A 60-month office lease commencing 1 January 2023. Rent $5,000 a month, paid in advance. Incremental borrowing rate 5.5%. Initial direct costs of $6,000 — the broker’s commission — paid at signing.

On 1 July 2024, after 18 payments, the parties extend the lease by 24 months. Rent rises to $5,500 a month for every remaining month, original and extended. The revised incremental borrowing rate is 6.2%.

First, three decisions no formula can make for you

Before a single cell gets typed, ASC 842 makes you answer three questions. Skip them and you’ll build an immaculate schedule for the wrong accounting.

1. Is this a separate contract? ASC 842-10-25-8 says a modification is a separate contract only if both are true: it grants an additional right of use, and the payments rise commensurately with the standalone price of only that additional right of use.

Here, (a) passes — 24 more months on the same floor is more right of use. But (b) fails, and this is the subtle bit: the rent on the 42 remaining original months also went from $5,000 to $5,500. The price increase isn’t confined to the extra time, so it isn’t commensurate with the standalone price of the extra time. One modified lease, then. Remeasure.

That test is two dropdowns and one formula. It is not, as the internet would have it, =IF(Mod_Type="Renewal","Remeasure","No Action") — a formula that only ever tells you what you already typed into it.

2. Does scope go up or down? This one matters enormously and gets flattened constantly. A scope increase or a term extension puts the entire movement in the liability into the ROU asset, with nothing through P&L. A scope decrease — you hand back half the floor — follows ASC 842-10-25-13 instead: reduce the ROU asset and the liability proportionately to the decrease, take the difference to profit or loss as a gain or loss, and only then remeasure whatever’s left.

Different rule, different journal entry, and a P&L line that simply does not exist in the increase pattern. One “modification engine” cannot silently cover both.

3. Operating or finance? Run the five ASC 842-10-25-2 criteria — ownership transfer, purchase option, major part of economic life, substantially all of fair value, specialised asset. On ordinary office space with decades of building life left, all five come back “no”, so: operating lease. One straight-line lease cost, no separate interest line on the face of the P&L.

Option 1 — Classic (backward-compatible functions, step by step)

Works in Excel 2007 onwards. Your auditor can open it, and so can the colleague still on a locked-down desktop build.

Step 1 — Put the inputs somewhere real and name them.

On an Inputs sheet: Start_Date (1 Jan 2023), Term_1 (60), Pmt_1 (5000), Rate_1 (5.5%), IDC (6000), Incentive (0), then Mod_Date (1 Jul 2024), Ext_Months (24), Pmt_2 (5500), Rate_2 (6.2%). Formulas → Define Name, one each. Named ranges are the difference between a formula you can audit and a formula you have to reverse-engineer.

Step 2 — Open the two balances. They are not the same number.

B3  =PV(Rate_1/12,Term_1,-Pmt_1,,1)
B4  =B3+IDC-Incentive
B5  =(Term_1*Pmt_1+IDC-Incentive)/Term_1

Lease liability $262,963.93. ROU asset $268,963.93. Straight-line lease cost $5,100.00 a month.

That trailing 1 in PV is the annuity-due flag. Without it you’d get $261,764.18 — $1,199.75 light, because you’d have told Excel the rent arrives at the end of each month.

B5 is the operating-lease cost. Total cost over the lease is every dollar of rent plus the initial direct costs — $306,000 — spread evenly across 60 months. Note that it is $5,100, not the $5,000 rent. It is supposed to differ.

Step 3 — Build the 60-row schedule.

Headers in row 8, data from row 9: Period, Month starting, Opening liability, Payment, Interest, Principal, Closing liability, Lease cost, ROU amortisation, Closing ROU.

A9  =1
B9  =EDATE(Start_Date,A9-1)
C9  =$B$3
D9  =Pmt_1
E9  =(C9-D9)*Rate_1/12
F9  =D9-E9
G9  =C9-F9
H9  =$B$5
I9  =$B$5-E9
J9  =$B$4-SUM($I$9:I9)

Row 10 differs in exactly two cells — A10 =A9+1 and C10 =G9, so the period counts up and each month opens where the last one closed. Everything else copies straight down. Select A10:J10, drag to row 68. Two of those rows deserve a second look:

E9 is the annuity-due interest base — (opening − payment) × rate. The rent is already gone by the time the month starts accruing.

I9 is the operating-lease trick, and it’s lovely once it clicks: the ROU asset absorbs whatever the interest doesn’t. The P&L charge is flat at $5,100 every month, interest declines as the balance falls, so amortisation rises to fill the gap. Month 1 amortises $3,917.67; month 18 amortises $4,226.29.

J9 anchors on $I$9 instead of chaining off the row above, so inserting a row can’t quietly corrupt the running total.

Here’s what the top of it looks like — payment is a flat $5,000 and lease cost a flat $5,100 on every row, so watch interest fall and ROU amortisation rise to meet it:

The two closing columns start $6,000 apart and finish the row-18 gap at $4,200 — that’s the unamortised slice of the broker’s commission, and it’s the whole reason these are two balances rather than one.

Step 4 — Run the separate-contract test for real.

Two cells you answer honestly, and one formula:

=IF(AND(Addl_ROU="Yes",Commensurate="Yes"),"Separate contract","Single modified lease")

Returns Single modified lease, for the reason we worked through above. Everything downstream keys off this cell, so if a future modification genuinely is a separate contract, the schedule stops remeasuring on its own.

Step 5 — Read both carrying amounts at the modification date.

On a second sheet, Classic Remeasured — these are its B3, B4, B5, not the ones from Step 2:

B3   =DATEDIF(Start_Date,Mod_Date,"M")
B4   =Term_1-B3
B5   =B4+Ext_Months
B9   =INDEX('Classic Original'!$G$9:$G$68,B3)
B10  =INDEX('Classic Original'!$J$9:$J$68,B3)
B11  =B10-B9

Which gives you: 18 payments made, 42 original months still to run, a revised term of 66 months, a carrying liability of $191,502.24, a carrying ROU asset of $195,702.24, and $4,200.00 of unamortised initial direct costs sitting between them.

DATEDIF counts whole elapsed months, which is exactly the count of payments already made — 18. And INDEX(...,18) returns period 18’s closing balance, which is the carrying amount at 1 July 2024 before that day’s rent goes out. Same instant as the remeasurement. That’s the trap from the callout above, closed.

Step 6 — Remeasure the liability at the revised rate.

B13  =IF(Mod_Test="Separate contract",B9,PV(Rate_2/12,B5,-Pmt_2,,1))

66 months of $5,500, discounted at 6.2%, still in advance: $308,503.39. Use the old 5.5% and you’d get $314,057.61 — over by $5,554.21 — because ASC 842-10-35-6 measures a modification at the rate on the day it happens, not the rate you signed at.

Step 7 — Adjust the ROU asset, and post the entry.

B14  =B13-B9
B15  =B10+B14

The adjustment is $117,001, taking the ROU asset to $312,703.39.

The whole movement lands in the asset. No gain, no loss, no expense — because scope increased. Hand back space instead and this entry is wrong.

Step 8 — Recut the straight-line cost. Everybody forgets this one.

B16  =(B5*Pmt_2+B11)/B5

Remaining rent of $363,000 plus the $4,200 of initial direct costs still riding on the ROU asset, spread over the 66 months that remain: $5,563.64 a month.

Not $5,500. If you just use the rent, you under-recognise $63.64 every month for 66 months — and $4,200 of initial direct costs sits on your balance sheet at the end of the lease with nothing left to amortise against.

Step 9 — Build the remeasured 66-row schedule.

Identical mechanics to Step 3 with three inputs swapped: opening balance $B$13, rate Rate_2, payment Pmt_2, cost $B$16, and the ROU running total anchored on $B$15. Rows 19 to 84. Period 19 opens at $308,503.39 and period 84 closes at $0.00, with the ROU asset landing on zero on the same row.

Option 2 — Excel 365 spill (dynamic-array equivalent)

Same maths, same answers to the cent — but the schedule stops being 600 individual formulas and becomes three.

Step 1 — The period and date columns.

A9  =SEQUENCE(Term_1)
B9  =EDATE(Start_Date,SEQUENCE(Term_1)-1)

Two spills, 60 rows each. Change Term_1 to 48 and they redraw themselves.

Step 2 — The whole money block, from one cell.

=LET(
   r,    Rate_1/12,
   n,    Term_1,
   p,    Pmt_1,
   lia0, $B$3,
   rou0, $B$4,
   slc,  $B$5,
   per,  SEQUENCE(n),
   clo,  SCAN(lia0, per, LAMBDA(a,i, a-(p-(a-p)*r))),
   opn,  VSTACK(lia0, DROP(clo,-1)),
   intr, (opn-p)*r,
   prin, p-intr,
   amrt, slc-intr,
   cost, per*0+slc,
   rclo, rou0-SCAN(0, amrt, LAMBDA(a,b, a+b)),
   HSTACK(opn, per*0+p, intr, prin, clo, cost, amrt, rclo)
)

Type that in C9 and eight columns spill across to J and down to row 68. Reading it top to bottom:

• clo is the engine. SCAN walks the period list carrying one running value — the liability — and each step applies exactly the rule from the classic build: closing = opening − (payment − interest). It’s a copy-down column with no cell references to break.
• opn is the closing column shifted down one row, with the opening balance stacked on top. VSTACK + DROP is the dynamic-array way of saying “the row above.”
• amrt is the operating-lease plug again: straight-line cost minus this month’s interest.
• rclo runs a second SCAN — this one a plain cumulative sum — to turn amortisation into a declining ROU balance.
• HSTACK bolts the eight columns together into one array.

Step 3 — The remeasured schedule is the same formula.

A19 =$B$3+SEQUENCE($B$5)
B19 =EDATE(Mod_Date,SEQUENCE($B$5)-1)
C19 =LET(r,Rate_2/12, n,$B$5, p,Pmt_2, lia0,$B$13, rou0,$B$15, slc,$B$16, ... )

Character for character identical to Step 2 apart from the six inputs at the top. That’s the real prize: one engine, pointed at two sets of assumptions. Change the extension from 24 months to 12 on the Inputs sheet and this schedule redraws itself at 54 rows. Nothing to drag. Nothing to delete.

The derived block above it is ordinary 365:

B9   =INDEX('365 Original'!$G$9:$G$68,B3)
B13  =IF(Mod_Test="Separate contract",B9,PV(Rate_2/12,B5,-Pmt_2,,1))
B14  =B13-B9
B16  =(B5*Pmt_2+B11)/B5

Typing it by hand you’d point at the spill instead. One catch worth knowing: the # only ever attaches to a spill’s anchor cell, never to a cell inside it — the money block spills from C9, so G9# is meaningless. You reach a column by its position within the block: =INDEX('365 Original'!C9#,B3,5) for the closing liability, =INDEX('365 Original'!C9#,B3,8) for the closing ROU. That version keeps working if the term ever changes length. The workbook writes the explicit ranges so it opens cleanly in every build of Excel; the answers are identical.

Step 4 — The journal entry, as an array.

=LET(adj, 'Classic Remeasured'!B14,
  VSTACK(
    HSTACK("Account","Debit","Credit"),
    HSTACK("Right-of-use asset", MAX(0,adj), MAX(0,-adj)),
    HSTACK("Lease liability",    MAX(0,-adj), MAX(0,adj))
  ))

VSTACK of HSTACKs gives you an actual three-column entry rather than a strip of text, and the MAX pair puts the amount on the correct side automatically — so a remeasurement that reduces the liability posts as a credit to the ROU asset without you touching it.

Where else this pattern pays for itself

The engine — carrying balance, revised rate, remeasure, adjust the asset, recut the cost — is the same shape for a lot of things ASC 842 throws at you:

• Exercising a renewal option you hadn’t included in the lease term. Technically a reassessment under ASC 842-10-35-1 rather than a modification, and the trigger is different — but the mechanics are identical: revised rate, remeasure, all of it to the ROU asset.
• Rate reassessments, when a change in the lease term or a purchase-option assessment forces you to revisit the discount rate.
• CPI-linked and index-based rent escalations, once the change is one you actually remeasure for rather than expense as it arises.
• IFRS 16 lessees, with one caveat: there’s no operating/finance split, so every lease amortises the asset straight-line and shows interest separately — the finance-lease column in the workbook.
• Any liability measured at present value that gets renegotiated mid-life — asset retirement obligations, deferred consideration, restructuring provisions. Same skeleton.

Download the workbook

Everything above, live, with both builds side by side and a Check sheet that proves they agree.

Eight sheets: Inputs (all yellow, all yours), Classic Original and Classic Remeasured, then 365 Original and 365 Remeasured running the same two schedules from three cells each. Journal Entries carries the remeasurement entry, a sample month either side of the modification, the separate partial-termination entry a scope decrease needs, and that same remeasurement entry rebuilt as one spilled VSTACK/HSTACK formula. Check ties the classic and 365 numbers to the cent, proves both schedules land on exactly zero, confirms that total lease cost recognised equals total cash plus initial direct costs ($459,000 either way), and prices all four wrong answers from the table above so you can watch them move.

The three ASC 842 tests are wired in as dropdowns. Answer the separate-contract test differently and the remeasurement switches itself off. Answer any classification criterion “Yes” and the ROU column flips from the operating-lease plug to straight-line finance-lease amortisation. Nothing in the file is hardcoded — change the term, the rent, the rate or the extension and all 126 schedule rows redraw.

Download the ASC 842 Lease Modification Tracker (.xlsx)

The 365 sheets need Microsoft 365 or Excel 2021+. The classic sheets open in anything back to 2007, which is usually what your auditor is running.

Takeaways

• A lease liability and an ROU asset are two balances, not one. Initial direct costs and incentives live in the asset and nowhere else. Any template that shows one column has already lost the plot.
• Answer three questions before you type a formula: separate contract or not, scope up or down, operating or finance. A modification also makes you reassess classification — it isn’t only a remeasurement.
• Rent is paid in advance. PV needs its trailing 1, and interest accrues on (opening − payment). Miss the second and a fully-paid 60-month lease still shows $1,578.52 outstanding.
• Both sides of the comparison must stand at the same instant. Period 18’s closing balance, not period 19’s — otherwise the liability comes out right and the ROU adjustment is $4,145.20 wrong, which is far worse.
• Recut the straight-line cost after remeasuring. $5,563.64, not the $5,500 rent. Use the rent and $4,200 of initial direct costs never amortises.
• A scope decrease is a different rule entirely — proportionate reduction of both balances with a gain or loss through P&L. One engine cannot silently cover both directions.
• On 365, LET + SEQUENCE + SCAN spill an entire schedule from three cells, and the remeasured version is the same formula with different inputs.
• Your move: open your current lease file and find the modification. If it lives in a tab called Renewal v3 FINAL with a hardcoded opening balance, rebuild it with this engine — then run the Check sheet’s four wrong answers against what you’ve been reporting. It takes ten minutes and it is a genuinely uncomfortable ten minutes.