ASC 842 Excel Template: Lease Liability and ROU Schedule

Quick Answer: An ASC 842 Excel template needs three parts: an inputs block (term, payment, timing, discount rate), the opening lease liability from =PV(rate/12, months, -payment, 0, 1), and a monthly table that accretes interest on the liability and amortizes the right-of-use asset. For an operating lease the right-of-use amortization is the plug that keeps total cost straight-line; for a finance lease it is a flat monthly amount and interest is booked on top.

A workable ASC 842 lease schedule in Excel is one inputs block plus one row per month. The opening liability is the present value of the remaining payments, the right-of-use asset starts from that number, and each row rolls both balances forward. One table serves operating and finance leases; only the asset amortization differs, and the payment column is what you tick against the bank statement.

What does the template need before any formulas?

Put every assumption in one place so the schedule never contains a typed number. A small Inputs sheet covers most leases:

The term is the noncancellable period plus renewal periods you are reasonably certain to take, so settle it with whoever signed the contract before you build anything. Variable payments tied to usage or an index change after commencement generally stay out of the liability and get expensed as incurred, which is why the template only carries fixed amounts.

How do you calculate the lease liability in Excel?

When the payment is level, one function does it. Microsoft documents the PV function as PV(rate, nper, pmt, [fv], [type]), where type 1 means payments fall at the start of each period, which is how most rent is paid.

=PV(Inputs!B4/12, Inputs!B2, -Inputs!B3, 0, Inputs!B5)

With the example inputs that returns 66,070.74. Thirty-six payments of 2,000 total 72,000, so 5,929.26 is the interest the schedule will release over three years.

Escalating rent breaks PV, because pmt must stay constant. Put each month's actual payment in the schedule's Payment column instead and discount the column directly:

=SUMPRODUCT(D2:D37 / (1 + Inputs!B4/12) ^ (A2:A37 - Inputs!B5))

Column A holds month numbers 1 to 36. With advance payments the exponent for month 1 is zero, so the first payment is undiscounted. Run this against the level example first; it should also return 66,070.74, which proves the formula before you trust it on a 3% annual bump.

The right-of-use asset at commencement is the liability, plus initial direct costs, plus prepaid rent, minus incentives received: =Liability + Inputs!B6 + Inputs!B7.

Building the monthly table for an operating lease

Lay the Schedule sheet out with one row per month and these columns:

1. A Month: 1 to 36.
2. B Date: =EDATE(Inputs!$B$1, A2-1).
3. C Opening liability: the PV result in row 2, then =F1 style links to the previous closing balance.
4. D Payment: the contractual amount for that month.
5. E Interest: =ROUND((C2-D2)*Inputs!$B$4/12, 2) for advance payments (interest runs on the balance left after paying), or =ROUND(C2*Inputs!$B$4/12, 2) for arrears.
6. F Closing liability: =C2-D2+E2.
7. G Straight-line lease cost: =SUM($D$2:$D$37)/Inputs!$B$2.
8. H ROU amortization: =G2-E2.
9. I Closing ROU asset: the opening asset less column H, running down.

Operating leases show a single lease cost in the income statement, level across the term. Column G is that level figure, and the right-of-use asset absorbs whatever interest does not. The first three rows of the example look like this:

With no initial direct costs or incentives, the asset and liability stay equal every month. When they differ, the gap is the unamortized costs or incentives, which is a handy check. Rounding leaves a few cents in the final row; force month 36's interest to whatever brings the closing liability to exactly zero rather than letting pennies sit on the balance sheet.

How is a finance lease schedule different?

Columns A to F stay identical, because the liability unwinds the same way whatever the classification. Only the asset side changes:

• ROU amortization becomes flat: =Inputs!$B$9/Inputs!$B$2, where B9 holds the opening ROU asset. In the example that is 1,835.30 a month. That assumes the usual case of amortizing over the lease term; if ownership transfers or a purchase option is reasonably certain, the asset's useful life governs instead.
• Interest expense is reported separately, straight from column E.
• Total expense is =E2+H2, which starts at 2,155.65 and falls each month as interest shrinks.

That front-loading is the visible difference between the two types. The operating version books 2,000 every month; the finance version books more early and less late, with the same 72,000 in total. A single IF on Inputs!B8 can switch column H between the two formulas, so one workbook serves both.

Which journal entries does the schedule feed?

Each line below pulls its amount from a cell, so month-end posting is copying numbers rather than computing them.

Many teams book the operating entry as one line to lease cost with the two credits, exactly as shown. If your ledger only allows two-line entries, split it into the accretion and the amortization, both debiting lease cost.

Which discount rate should go in cell B4?

Topic 842 asks first for the rate implicit in the lease. Lessees rarely know it, because it depends on the lessor's residual value assumptions, so most use their incremental borrowing rate: what they would pay to borrow, on a collateralized basis over a similar term, an amount equal to the payments. A bank quote for a secured term loan is a reasonable starting point, documented in the workpapers.

Entities that are not public business entities have an option to use a risk-free rate instead. FASB issued Update 2021-09, Discount Rate for Lessees That Are Not Public Business Entities, which changed how that election is made; read it, or ask your auditor, before choosing. A lower rate raises the liability, so the choice moves the balance sheet.

Leases you can leave off the schedule

A lessee may elect, by class of underlying asset, not to recognize leases with a term of 12 months or less that do not include a purchase option it is reasonably certain to exercise. Those payments are simply expensed straight-line. Keep a one-line register of them anyway, with the start date and term, so that a renewal pushing a lease past 12 months gets caught and moved onto the schedule.

Tying the schedule to the bank statement

Column D is a forecast until cash confirms it. Each month, find the rent debit on the bank statement and compare it with the scheduled payment:

• Amount differs: a CAM true-up, late fee or index increase. Variable pieces go to expense, not the liability.
• Date shifts across month-end: the liability is fine, but cash and accrued rent need a cutoff entry.
• Payment missing: check whether it cleared from a different account or a card.

If the statements are PDFs, convert them to Excel with a tool such as QuickBankConvert, filter the description column for the lessor, and use SUMIFS by month to set the actual payments beside column D. Accounts with dozens of property leases make this worth automating; for one office lease, reading the statement by eye is quicker. The same comparison belongs on your month-end closing checklist, next to the balance sheet tie-outs described in how to reconcile a balance sheet.

Checks before you rely on the numbers

• Sum of interest plus sum of ROU amortization equals total payments plus initial direct costs (operating) or the opening asset plus total interest (finance).
• The liability ends at zero in the final month, not one month early or late.
• Current portion of the liability equals the principal reduction scheduled for the next 12 months: =SUM of payments less interest over those rows.
• The ROU asset and liability carry into your balance sheet template as separate lines from other fixed assets and debt.
• A lease modification or a change in term is remeasured from the date it happens, on a fresh block of rows; never overwrite the months already posted.

Related guides

• Account reconciliation template in Excel for tying the lease liability to the ledger each month.
• Month-end closing checklist.
• Balance sheet template in Excel.