ASC 842 & IFRS 16 Operating Lease Accounting Excel Template (ROU Asset & Lease Liability Calculator)
01_Lease_Portfolio_Summary — Interactive Spreadsheet
| # | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ASC 842 & IFRS 16 LEASE ACCOUNTING MASTER SCHEDULE | |||||||||||||||
| 2 | US GAAP (ASC 842) & IFRS 16 Lead Workpaper | Entity: Atlas Global Enterprises Inc. | Period: FY2026 Q1 | GL Tie-Out: BALANCED / ZERO VARIANCE | |||||||||||||||
| 3 | ||||||||||||||||
| 4 | ACTIVE LEASES | TOTAL UNDISCOUNTED COMMITMENTS | TOTAL PV LEASE LIABILITY | TOTAL ROU ASSET CARRYING VALUE | CURRENT LIABILITY PORTION (<12M) | |||||||||||
| 5 | 6 | $8,929,200.00 | $7,492,604.25 | $7,535,604.25 | $1,091,332.29 | |||||||||||
| 6 | ||||||||||||||||
| 7 | SECTION 1: CORPORATE LEASE PORTFOLIO REGISTER & BALANCE SHEET CARRYING VALUES | |||||||||||||||
| 8 | LEASE IDENTIFICATION & SCOPE | VALUATION PARAMETERS | CASH FLOWS | INITIAL BALANCE SHEET VALUATION | CARRYING VALUE ALLOCATION | |||||||||||
| 9 | Lease ID | Asset Description & Location | Lessor / Counterparty | Asset Class | ASC 842 Classification | Commence Date | Term (Mos) | Lessee IBR (%) | Base Monthly Rent ($) | Annual Escal. (%) | Initial PV Liability ($) | Initial ROU Asset ($) | Direct Costs ($) | Undiscounted Rent ($) | Current Portion (<12M) ($) | Non-Current Portion ($) |
| 10 | LS-101 | Corporate Headquarters (Floors 12-14) | Brookfield Office Properties | Real Estate - Office | Operating Lease | 2026-01-01 | $2,052,576.67 | $2,067,576.67 | $2,310,000.00 | $372,543.76 | $1,680,032.91 | |||||
| 11 | LS-102 | Regional Logistics Hub (75,000 sq ft) | Prologis Industrial REIT | Logistics Facility | Operating Lease | 2025-07-01 | $1,824,346.57 | $1,832,846.57 | $2,184,000.00 | $216,221.81 | $1,608,124.76 | |||||
| 12 | LS-103 | Commercial Delivery Fleet (12 Vans) | Penske Truck Leasing Corp | Fleet Vehicles | Operating Lease | 2025-10-01 | $317,923.94 | $319,123.94 | $345,600.00 | $97,714.18 | $220,209.76 | |||||
| 13 | LS-104 | Enterprise Data Center Server Racks | Equinix Infrastructure Ltd | IT Hardware | Finance Lease | 2026-01-01 | $622,711.81 | $626,211.81 | $681,600.00 | $142,377.97 | $480,333.84 | |||||
| 14 | LS-105 | Automated CNC Manufacturing Cell | Caterpillar Financial Services | Heavy Equipment | Finance Lease | 2024-06-01 | $625,290.33 | $628,090.33 | $708,000.00 | $110,335.48 | $514,954.85 | |||||
| 15 | LS-106 | Retail Flagship Storefront (Metropolis) | Simon Property Group Inc | Commercial Retail | Operating Lease | 2026-02-01 | $2,049,754.93 | $2,061,754.93 | $2,700,000.00 | $152,139.09 | $1,897,615.84 | |||||
| 16 | TOTAL ACTIVE LEASE PORTFOLIO | $7,492,604.25 | $7,535,604.25 | $43,000.00 | $8,929,200.00 | $1,091,332.29 | $6,401,271.96 | |||||||||
| 17 | ||||||||||||||||
| 18 | GENERAL LEDGER (GL) BALANCE SHEET RECONCILIATION & TIE-OUT VERIFICATION | |||||||||||||||
| 19 | GL Account 2300 (Operating Lease Liability - Total) | $7,492,604.25 | $0.00 | PASSED / GL TIED | ||||||||||||
| 20 | GL Account 1750 (Right-of-Use Asset - Operating Leases) | $7,535,604.25 | $0.00 | PASSED / GL TIED | ||||||||||||
| 21 | GL Account 2050 (Current Portion of Lease Liabilities) | $1,091,332.29 | $0.00 | PASSED / GL TIED | ||||||||||||
| 22 | GL Account 2310 (Long-Term Portion of Lease Liabilities) | $6,401,271.96 | $0.00 | PASSED / GL TIED | ||||||||||||
Free Instant Download
No email signup • Unlocked formulas
Built-In Excel Formulas & Calculation Logic
Unlocked native Microsoft Excel functions pre-programmed inside this workbook:
=ROUND(PV(Lessee_IBR/12, Term_Months, -Monthly_Payment, 0, 0), 2) Calculates the initial carrying value of the lease liability by discounting all contractual future lease payments to present value using the lessee's monthly Incremental Borrowing Rate (IBR).
=ROUND(Initial_Lease_Liability + Initial_Direct_Costs + Prepaid_Rent - Lease_Incentives, 2) Establishes the opening balance of the ROU Asset by adding initial direct costs (brokerage, legal fees) and advance lease payments to the initial lease liability, less any tenant improvement allowances or lessor incentives.
=ROUND(Beginning_Liability * (Annual_IBR / 12), 2) Accretes interest on the outstanding lease liability balance for each monthly period using the effective interest method under ASC 842 and IFRS 16.
=ROUND(Straight_Line_Operating_Expense - Interest_Accretion, 2) Determines the periodic amortization of the ROU Asset for operating leases under US GAAP ASC 842, ensuring total operating lease expense remains uniform (straight-line) across the entire lease term.
=SUM(OFFSET(Principal_Reduction_Range, 1, 0, 12, 1)) Dynamically sums the expected principal reduction over the next 12 monthly periods to correctly segregate the current portion of lease liabilities from non-current liabilities on the balance sheet.
Full Workbook Print Layout Preview (.XLSX)
Overview & Industry Standard Alignment
Transitioning to US GAAP ASC 842 and IFRS 16 represents one of the most substantial regulatory changes to corporate financial statements in modern history. Previously, operating leases were relegated to obscure footnote disclosures. Under current standards, virtually all commercial real estate leases, vehicle fleets, IT servers, and industrial machinery contracts exceeding 12 months must be capitalized directly onto the corporate balance sheet as Right-of-Use (ROU) Assets with corresponding Lease Liabilities. While dedicated enterprise lease accounting software platforms demand steep annual subscriptions, complex onboarding, and multi-month implementation cycles, this professional Excel model provides corporate controllers and accounting teams with complete audit-grade transparency. Every calculation formula—from compound monthly discounting to straight-line expense matching—remains visible, verifiable, and free of proprietary black-box calculations. Engineered according to rigorous Big-4 public accounting methodologies, this workbook establishes a seamless audit trail. It unites multi-contract portfolio registers, 60-month monthly rollforward waterfalls, auto-balancing general ledger closing entries, and mandatory ASC 842-20-50 maturity analysis disclosures within a single interconnected model.
Practical Operational Use Cases:
Corporate Real Estate & Facility Lease Accounting
Capitalize commercial office headquarters, regional distribution warehouses, retail storefronts, and industrial plants under ASC 842 with escalating rental payment terms, tenant improvement allowances, and initial direct legal costs.
Fleet Vehicle & Machinery Equipment Schedules
Model commercial delivery van fleets, manufacturing CNC machines, and heavy construction equipment leases, running the 5-part classification test to distinguish between operating and finance (capital) leases.
Month-End Close Accruals & ERP Journal Entries
Streamline monthly closing cycles by copying auto-generated journal entries into NetSuite, SAP, QuickBooks Enterprise, or Microsoft Dynamics 365 with perfect Debit-Credit balance checks.
Annual Audit Preparation & Footnote Maturity Disclosures
Provide external CPA auditors with complete mathematical transparency, prior-year rollforward ties, and the mandatory 5-year undiscounted contractual commitments maturity schedule (ASC 842-20-50-4).
Designed For:
What’s Inside the Workbook (Tab-by-Tab Breakdown)
Worksheet 1
Worksheet 2
Worksheet 3
Worksheet 4
Practitioner Tips & Best Practices
-
✓
Establishing the Lessee Incremental Borrowing Rate (IBR): Under ASC 842, lessees must use the rate implicit in the lease when readily determinable; otherwise, establish an IBR based on the company's collateralized borrowing rate for a similar term and economic environment.
-
✓
Handling Variable Lease Payments: Payments based on an index or rate (such as CPI) must be included in initial liability measurement using the index rate at commencement. Variable payments based on performance or asset usage (such as percentage rent) are expensed in the period incurred and excluded from the liability.
-
✓
Applying the Short-Term Lease Practical Expedient: Do not capitalize leases with a maximum possible term of 12 months or less that contain no purchase option. Electing this expedient allows straight-line expensing without balance sheet recognition.
-
✓
Audit Trail Readiness: Maintain signed lease agreements, commencement acceptance letters, and treasury yield documentation directly linked to the workpaper's audit tickmark references to ensure a friction-free annual financial audit.
Frequently Asked Questions
What is the primary difference between ASC 842 and IFRS 16 lease accounting?
Under US GAAP ASC 842, lessees classify leases into two categories: Operating Leases and Finance Leases. Operating leases recognize a single, straight-line lease expense on the income statement, keeping EBITDA unchanged. Under IFRS 16, all leases are treated under a single finance lease model where the lessee recognizes depreciation on the ROU asset and interest expense on the liability separately, which increases reported EBITDA.
How is the Right-of-Use (ROU) Asset initially calculated?
The initial ROU asset equals the initial present value of the lease liability, plus any lease payments made to the lessor before or at commencement (prepaid rent), plus any initial direct costs incurred by the lessee (such as broker commissions or legal drafting fees), minus any lease incentives received from the lessor (such as tenant improvement allowances).
How do you calculate the present value of lease payments in Excel?
Use Excel's native PV function: `=PV(rate/12, nper, -pmt, [fv], [type])`. Divide the annual Incremental Borrowing Rate (IBR) by 12 for the monthly rate, set `nper` to the lease term in months, enter monthly payment with a negative sign, and set `type` to 0 for payments made at the end of the month or 1 for payments made in advance at the beginning of the month.
Why does an operating lease have straight-line expense if liability interest decreases over time?
Under ASC 842 operating lease rules, total operating lease expense is recognized on a straight-line basis over the lease term. Because effective interest accretion decreases as the lease liability is paid down, ROU asset amortization is calculated as a plug (Straight-Line Expense minus Interest Accretion). As interest decreases, ROU amortization increases, ensuring the total periodic income statement charge remains constant.
How should current versus non-current lease liabilities be classified on the balance sheet?
The current portion of a lease liability is the amount of principal reduction expected to be paid over the immediate subsequent 12 months. The remaining liability balance is classified as non-current (long-term). This workbook automates this segregation across all 60 monthly periods using dynamic rolling summation formulas.
Can this Excel spreadsheet handle lease modifications or rent escalations?
Yes. The amortization waterfall schedule on Sheet 2 explicitly accommodates contractual rent escalations (such as annual 2.5% increases) across each 12-month tranche. If a lease modification occurs (such as a term extension or partial termination), you can overwrite future cash flow rows to re-measure the remaining liability and adjust ROU asset carrying value accordingly.