Lease accounting became a little more complex with the introduction of ASC 842 in recent years.
And while compliance is vital, it’s also a task that many accountants dread.
The complex spreadsheets and manual work take up valuable time. So, we put together some resources to help.
In this post, we share how to create ASC 842-compliant schedules in Excel. With our step-by-step process, you can create an operating lease schedule that meets the requirements under ASC 842.
Alternatively, you can skip the manual work and download our free lease amortization schedule template.
Let’s dive in.
Considerations before creating your lease amortization schedule
Before getting started, here are a few principles to consider:
- Keep it simple. The standard has enough complexity—don’t add to it.
- Pick an approach and stick with it. Users of the schedule will want consistency, and the Financial Accounting Standards Board (FASB) also calls for a consistent approach:
- 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 the beginning or end of the 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 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 an ASC 842-compliant operating lease amortization schedule
With the above considerations in mind, follow these seven steps to create an ASC 842-compliant operating lease amortization schedule in Excel.
1. Create input fields
Create the following input fields at the top of your spreadsheet to record key data that will 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 date and payment columns
Create the following columns for period, start dates, monthly payments, and single lease expense:
- Period number: Start with period 0 and no payment to represent the lease commencement. Then, sequentially number the periods.
- Period start date: Represents each sequential full month of the lease term
- Month’s 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
Create the following columns to capture liability effective interest:
- Interest accretion (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).
- Allocated to principal (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 interest accretion (interest accrued during the period).
4. Create ROU asset columns
Create the following columns for right-of-use (ROU) asset balance and ROU asset adjustment:
- Right-of-use asset balance: Prior period balance less asset adjustment
- ROU asset adjustment (ROU accumulated amortization): 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 other words, 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 ROU asset amounts
Enter the following amounts:
- Period 0 lease liability: Present value of lease payments. Calculate using the NPV formula in Excel:
- End of period payments: =npv(rate/12,period1pmt:finalperiodpmt)
- Beginning of period payments: =npv(rate/12,period2pmt:finalperiodpmt)+period1pmt
- Period 0 ROU asset: Lease liability, plus prepaid lease payments, plus initial direct costs, minus lease incentives
6. Include checks in your schedule
The following checks will help you determine if your schedule is working as intended:
- Lease liability should run down to 0 at the end of the lease.
- ROU asset 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. Pull journal entry amounts from the schedule
Now that your schedule is ready, you can pull the journal entry amounts:
- 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).
Building your own schedule gives you more insight into the calculations and data, but if you’re short on time (and what accountant isn’t?), you can download our ready-made lease amortization schedule template.
Learn more about ASC 842 lease templates
Find answers to common questions about lease amortization schedules.
Can you amortize lease commissions?
Yes, lease commissions can be amortized over the term of the lease. Lease commissions refer to the fees paid to a broker or other agent for arranging a lease agreement between the landlord and tenant.
Accounting standards generally require that lease commissions (initial direct costs) be recorded as an increase to the lessee’s ROU asset and amortized (expensed over time) over the term of the lease agreement. The amortization expense is recognized as a component of the lease expense.
Are operating leases amortized?
Yes, operating leases are amortized. Under an operating lease, the lessee (the person or company using the asset) does not take ownership of the asset at the end of the lease term. Instead, the lessor (the owner of the asset) retains ownership, and the lessee pays for the use of the asset over the lease term.
In an operating lease, the lessee typically records lease payments as lease expenses in their income statement on a straight-line basis over the term of the lease. This means that the total rent expense is spread evenly over each period of the lease, rather than being front-loaded or back-loaded.
Should leases be capitalized or expensed?
Leases should be capitalized if the term (or economic benefits) is greater than 12 months. Otherwise, it is a short term/low value lease that should be expensed.
It's important to note that there are certain exceptions and exemptions to the capitalization requirements under IFRS 16 and GAAP, such as short-term leases and leases of low-value assets.
How do you amortize ROU assets?
The amortization of ROU assets is the process of systematically reducing the value of the asset over time to reflect its decreasing value as the lease term progresses. ROU assets should be amortized using the straight-line method, allocating the cost of the asset over the lease term.
The amortization expense is recorded on the income statement as a non-cash expense, and the ROU asset is reduced on the balance sheet by the same amount.
Guarantee ASC 842 compliance with NetLease
A well-built lease amortization schedule can help you ensure compliance with standards like ASC 842. But as your lease portfolio grows, even the best Excel schedule turns time-consuming and tedious.
There’s an even better way to manage lease compliance: automation software.
Whether you're managing a few leases or hundreds, NetLease gives you the power to stay compliant with ASC 842, IFRS 16, GASB 87, and GASB 96 — right from NetSuite.
With automated compliance, comprehensive lease reporting, and customizable workflows, NetLease can help you cut your time spent managing leases in half.
Ready to take lease accounting out of spreadsheets? Explore Netgain’s lease accounting solutions, or request a personalized demo.
