Whether financing or operating, you can easily make an operating lease schedule that meets the requirements under ASC 842.
In this post, we’ll share how to make ASC 842-compliant schedules in Excel, while also providing a ready-made template to make the transition as smooth as possible for you.
Alternatively, you can skip the trouble and transition for free with NetLease Go Free.
Here are a few principles to consider before getting started:
1. Keep it simple. The standard has enough complexity—don’t add to it.
2. Pick an approach and stick with it—users want consistency.
- ASC 250-10-45-1 States: “A presumption exists that an accounting principle once adopted shall not be changed in accounting for events and transactions of a similar type. Consistent use of the same accounting principle from one accounting period to another enhances the utility of financial statements for users by facilitating analysis and understanding of comparative accounting data.”
- In addition, per ASC 250 Accounting Changes and Error Correction Definitions: “A change in the method of applying an accounting principle also is considered a change in accounting principle.”
3. Don’t worry about payment timing beyond the beginning or end of the month.
4. Don’t forget, we’re making assumptions in our application of 842.
5. The rate applied has the biggest impact on present value—much bigger than exactly when a payment is made within the period.
- For example, on a 60-month lease, the difference between the beginning and end of the period has less than a 0.2% impact in incremental borrowing rate to arrive at the same present value. As a reference point, keep in mind the treasury risk-free rate increased by twice that amount in the first two months of 2021 for a five-year lease.
In summary, your key decisions are (1) picking an appropriate rate and (2) choosing beginning or end of period for payments
How to create a simple, compliant schedule:
1. Create input fields at the top of your spreadsheet to record the following key data that drive your amortization schedule results
- Incremental Borrowing Rate (percentage): Annual discount rate to be applied to the lease.
- Prepaid Lease Payments: Any amounts that were prepaid in periods prior to the commencement month of the lease (i.e., not in the periods of the schedule you create).
- Initial Direct Costs: Incremental costs of obtaining the lease that would not have been incurred if the lease had not been obtained.
- Lease Incentives: Any incentives received prior to the period of commencement (note that any incentives expected to be received after commencement should be reflected in that period’s payment amount).
2. Create Period, Dates, Payments, & Single Lease Expense Columns
- Period #: Start with period 0 and no payment to represent the lease commencement. Then sequentially number the periods.
- Month: Represents each sequential full month of the lease term.
- Payment: Input the monthly payments for each period of the lease term. If there are multiple payments, add them together. If there are any periods with rent abatement or rent holiday, put $0 in the payment field.
- Single Lease Expense (amount applied to all periods): Sum all period payments, add prepaid lease payments, add initial direct costs and deduct lease incentives. Divide that amount by the total number of periods (months) for your lease.
3. Create Liability Effective Interest Columns
- Liability Accretion (interest accretion): Calculate the amount of interest that would be applied against the liability using the effective interest method (period beginning balance X rate/12).
- Liability Reduction (allocated to principal): Represents the reduction of liability and is calculated as your current month payment less liability accretion.
- Lease Liability Balance: Your ending balance equals the prior period ending balance, less payments received, plus liability accretion (interest accrued during the period).
4. Create ROU and ROU adjustment Accounts
- Right of Use Asset Balance: Prior Period Balance less Asset Adjustment.
- ROU Asset Adjustment: This is the least intuitive field to include, but it’s the amount to apply against the ROU asset that balances your journal entry. In words we can all relate to, this is the ROU "plug" amount. Calculated as Single Lease Expense, plus Allocated to Principal, minus current month's payment.
5. Input Period 0 Calculated Lease Liability and Right of Use Asset amounts
- Period 0 Lease Liability = Present value of Lease Payments. Using Excel, calculate using the NPV formula.
- End of Period Payments: =npv(rate/12,period1pmt:finalperiodpmt)
- Beginning of Period Payments: =npv(rate/12,period2pmt:finalperiodpmt)+period1pmt
- Period 0 Right of Use Asset = Lease Liability, plus prepaid lease payments, plus initial direct costs, minus lease incentives.
6. Include some checks to your schedule
- Lease liability should run down to 0 at the end of the lease.
- Right of Use should run down to 0 at the end of the lease.
- The total of your lease expense for the full lease should equal the sum of all the monthly payments, prepayments and initial direct costs, less lease incentives.
7. Journal entry amounts are then pulled right from the schedule:
- Lease Commencement Initial Balance Entry from Period 0: Debit ROU Asset and credit Lease Liability.
- Monthly Amortization Entry: debit Lease Expense, debit Allocated to Principal, credit Payment and credit ROU Asset Adjustment (plug).
Give it a try on your own, or download our FREE ASC 842 Lease Amortization Schedule spreadsheet template. Keep in mind that our software solution, Netlease, automates all of these steps and delivers the required disclosures for ASC 842, IFRS 16 and GASB 87 compliance.
Or, try it for free. NetLease Go Free is free forever and can manage up to three leases.
More helpful ASC 842 links:
- Are you prepared to account for embedded links? Here's our guide so you're not surprised.
- Are spreadsheets really the right call for ASC 842? Here's why spreadsheets are not the way to go long-term.
- Want to transition for free? Here's our guide for free compliance.