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

Who this quietly bites

If you’re in corporate accounting, FP&A, or audit, you’ve seen this: a 60-month office lease gets renewed at month 36. The discount rate changes from 5.5% to 6.2%. The monthly payment jumps from $5,000 to $5,500. Under ASC 842, this is a modification — and it triggers a full remeasurement of both the lease liability and the right-of-use (ROU) asset.

Most companies handle this by creating a brand-new Excel tab, manually rebuilding the amortization schedule from month 37 forward, and hardcoding the new rate. One case study from Virgin Voyages documented 5+ hours per lease just preparing journal entries and reconciling schedules. Multiply that by 50 leases, and you’re looking at a full week of manual work every quarter.

Why the usual formula gets it wrong

• Lease term
• Monthly payment
• Discount rate
• Commencement date

1. Calculate the remaining liability balance at the modification date
2. Determine if the modification requires a new discount rate
3. Rebuild the schedule for the new term
4. Calculate the ROU asset adjustment
5. Generate journal entries for the difference

The standard lease calculator you find online asks for:It spits out a beautiful 60-month amortization table. Perfect — on day one. But on day 1,095 (month 37), when the renewal hits, that calculator is useless. You have to:

None of the free templates do this. The paid ones barely do it. And if you have 50 leases, you’re rebuilding 50 schedules by hand.

The fix, two ways

The scenario: A 60-month lease at $5,000/month, 5.5% discount rate, starting January 1, 2023. On July 1, 2024 (month 19), the lease is renewed for an additional 24 months. The new rate is 6.2%, and the new payment is $5,500/month.

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

**Step 1: Build the original amortization schedule.**Create columns: Period, Date, Opening Liability, Interest, Payment, Principal, Closing Liability.

In cell B3 (Opening Liability):

=PV(0.055/12,60,-5000,,1)

Result: $262,089

In cell D3 (Interest):

=B3*0.055/12

In cell E3 (Payment):

=5000

In cell F3 (Principal):

=E3-D3

In cell G3 (Closing Liability):

=B3-F3

In cell B4 (next period opening):

=G3

Drag all formulas down 60 rows.

Step 2: Flag the modification.

In a separate cell:

=IF(OR(Mod_Type="Renewal",Mod_Type="Rate Change", Mod_Type="Scope Change"),"Remeasure","No Action")

Step 3: Calculate the opening balance at modification date.

Look up the closing liability at month 18 (June 30, 2024):

=INDEX(G3:G62,18)

Result: approximately $182,450

Step 4: Calculate the new liability.

Remaining term = 60 - 18 + 24 = 66 months.

=PV(0.062/12,66,-5500,,1)

Result: approximately $312,800

Step 5: Calculate the ROU adjustment.

=New_Liability - Current_Liability

=312800 - 182450

= $130,350

Step 6: Build the remeasured schedule manually. Create a new 66-row table starting with $312,800 as opening liability, 6.2% rate, $5,500 payment. The difference between the new liability and the old remaining liability is your ROU adjustment journal entry.

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

Step 1: Auto-build the original schedule with SCAN. In cell B3:

=LET(r,0.055/12,n,60,pmt,5000,PV(r,n,-pmt,,1))

In cell B4 (spilled 60 rows):

=SCAN(B3,SEQUENCE(60),
  LAMBDA(acc,per,
    LET(int_amt,acc*r,
      princ,pmt-int_amt,
      acc-princ)))

Where r and pmt are named ranges.

This single formula builds the entire closing liability column.

Step 2: Auto-calculate the modification date balance.

In cell F3:

=LET(mod_date,DATE(2024,7,1),
  period_num,DATEDIF(DATE(2023,1,1),mod_date,"M"),
  INDEX(B4#,period_num))

Step 3: Calculate the new liability with LET.

In cell G3:

=LET(new_r,0.062/12,new_n,66,new_pmt,5500,
  PV(new_r,new_n,-new_pmt,,1))

Step 4: Build the remeasured schedule in one formula.

In cell H3 (spilled 66 rows):

=LET(open_bal,F3,new_r,0.062/12,
  new_pmt,5500,
  SCAN(open_bal,SEQUENCE(66),
    LAMBDA(acc,per,
      LET(int_acc,acc*new_r,
        princ,new_pmt-int_acc,
        acc-princ))))

Step 5: Generate the journal entry automatically.

In cell J3:

=LET(new_liability,G3,current_liability,F3,
  rou_adjustment,new_liability-current_liability,
  HSTACK("Dr. ROU Asset",rou_adjustment,
    "Cr. Lease Liability",rou_adjustment))

Where else this pattern pays for itself

This same engine handles:

• Lease terminations — partial or full, with early termination penalties
• Rate reassessments — when the IBR changes due to credit rating shifts
• Scope reductions — when you give back half the office space
• Index-based rent escalations — CPI adjustments that trigger remeasurement

Any lease event that changes the liability balance can be captured, calculated, and journalized automatically.

Download the workbook

The workbook includes:

• Original and remeasured amortization schedules
• Automatic modification classification (renewal, rate change, scope change, termination)
• ROU asset adjustment calculator
• Journal entry generator for each modification type
• Side-by-side classic and 365 versions

Download the ASC 842 Lease Modification Tracker Workbook (.xlsx)

Takeaways

• Every free lease calculator is inception-only. None handle mid-lease modifications.
• The classic approach requires PV, INDEX, and manual schedule rebuilding.
• Excel 365’s SCAN lets you rebuild the entire amortization schedule in one formula.
• Your action: If your lease accounting lives in static inception calculators, add a modification event tab with this engine. It turns 5 hours per lease into 5 minutes.