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.
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).
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
Measure
Value
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 machine
finance
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
Type the commencement date, the number of months you concluded and the first monthly payment.
Enter the annual escalation, the initial direct costs paid and the incentive received at commencement.
Enter the annual discount rate and the class; the Schedule and Annual tabs follow at once.
On the Classification tab, type the payment, the term, the guarantee, the remaining economic life and the fair value.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.