Whether financing or operating, you can easily make an operating lease schedule that meets the requirements under ASC 842.
Here are a few principles to consider before getting started.
We're excited to share how to make ASC 842 compliant spreadsheet but also want to provide you with a spreadsheet template of what are covering in this post.
- Keep it simple. The standard has enough complexity—don’t add to it
- Pick an approach and stick with it—users want consistency, along with materiality
- 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.”
- Additionally, 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”
- Don’t worry about payment timing beyond beginning or end of month
- Don’t forget we’re making assumptions in our application of 842
- 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 beginning and end of period has less than 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 2 months of 2021 for a 5-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)
- Prepaid Lease Payments
- Initial Direct Costs
- Lease Incentives
> Any incentives received prior to the period of commencement (not that any expected to be received after commencement should be reflect in that period’s payment amount)
2. Create Period, Dates, Payments, & Single Lease Expense Columns
- Period # > Start with period 0 and no date 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 lease term. If multiple payments, add together, or mark 0 when no payment/rent holiday
- Single Lease Expense (amount applied to all periods) > Sum all period payments, add prepaid lease payments and 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 > 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 > 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
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 of the monthly payments, prepayments, and initial direct costs, less lease incentives.
Journal entry amounts are then pulled right from the schedule:
- Lease Commencement Initial Balance Entry from Period 0 ROU Asset and Lease Liability
- Monthly Amortization Entry from Lease Expense, Allocated to Principal, Payment, and ROU Asset Adjustment (plug)
Give it a try on your own, or download our FREE ASC 842 Lease Amortization Schedule spreadsheet template, so you don’t have to start with a blank spreadsheet. Keep in mind that Netlease automates all of these steps and delivers the required disclosures for ASC 842, IFRS 16 and GASB 87 compliance.
You can also get our free version of NetLease Go, which is free forever and can manage up to 5 leases.