Free Excel templates

ASC 842 Lease Accounting Model

Lease liability, right-of-use asset, a 240-month schedule, journal entries and the classification tests, for operating and finance leases.

Type one lease and the workbook measures it under ASC 842: the liability at commencement, the right-of-use asset after initial direct costs and incentives, the schedule month by month and year by year, the entries to post and the five classification criteria. The worked case is an office lease that starts at $4,996,066.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Lease_Accounting_ASC842_Model.xlsx (60 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Lease Accounting Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

6 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
The lease in nine yellow cells: dates, term, first payment, escalation, initial direct costs, incentive, rate and class.
Schedule
One row per month, up to 240: payment, liability, interest or accretion, lease cost, ROU amortization and balances.
Annual
Calendar years for the footnote and the roll-forward, rounded to the dollar, the last year absorbing the rounding.
Entries
Draft commencement entry and first-month entries for an operating or a finance lease.
Classification
Term test and present value test with the residual value guarantee counted in full, and a five-line sensitivity grid.
Checks
Seventeen checks: balances end at zero, totals tie, entries balance, and the figures of the book.

What the worked case returns

MeasureValue
Lease liability at commencement (office)$4,996,066
Right-of-use asset at commencement (office)$4,441,066
Straight-line monthly lease cost (office)$52,694.40
Lease cost, first year (office)$632,333
Present value of the residual value guarantee (machine)$27,524
Present value test (machine)97.0%
Class of the machinefinance

The office is an operating lease: one straight-line cost of $52,694.40 a month whatever the rent paid. The machine is a finance lease: its present value test reaches 97.0% only once the $40,000 residual value guarantee is counted in full, which is the step most homemade tests leave out.

How to use it on your own case

  1. Type the commencement date, the number of months you concluded and the first monthly payment.
  2. Enter the annual escalation, the initial direct costs paid and the incentive received at commencement.
  3. Enter the annual discount rate and the class; the Schedule and Annual tabs follow at once.
  4. On the Classification tab, type the payment, the term, the guarantee, the remaining economic life and the fair value.
  5. Check that the Checks tab reads ALL PASS before you post anything from the Entries tab.

Questions people ask about it

Does the model pay rent in advance or in arrears?

In advance: row 1 is the commencement month and its payment is made on day one. The annual rate is divided by 12 and interest runs on the balance after each payment.

Why is the residual value guarantee in the classification test but not in the liability?

The liability includes only the amount probable of being owed under the guarantee, $0 in the case. The present value test adds the full guaranteed amount not already in the payments, discounted from the end of the term: $27,524 for the machine.

Can it handle a modification or a reassessment?

Not directly: it measures one lease from commencement. For a remeasurement, start a new run at the effective date with the remaining payments, the new rate and the carried-forward asset, as the book shows chapter by chapter.

Go further

The free workbooks of The Lease Accounting Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Lease Accounting Handbook. See the book.