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.
Whether financing or operating, you can easily make an operating lease schedule that meets the requirements under ASC 842.
Key Takeaways:
- Keep it simple and consistent when creating a compliant schedule.
- Don't worry about payment timing beyond the beginning or end of the month.
- Input key data at the top of your spreadsheet to drive your amortization schedule results.
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.
FAQs
Does Microsoft have a lease template?
Yes, Microsoft has a lease template. Microsoft offers a variety of lease templates through its Microsoft office software suite. These templates can be used to create leases for commercial or residential properties, and they can be customized to create your own contract and minimize any potential problems between tenant and landlord.
Does Quickbooks And Google Sheets have an amortization schedule?
In QuickBooks, the availability of an amortization schedule depends on the version of the software being used. QuickBooks Desktop has a built-in loan amortization feature that allows you to create and track an amortization schedule.
QuickBooks Online, on the other hand, does not have this feature natively, but you can use a third-party app or a loan amortization calculator to create an amortization schedule and then import the data into QuickBooks Online.
In Google Sheets, there is no built-in function for creating an amortization schedule. However, you can use formulas and templates to create one. There are also several add-ons and templates available in the Google Sheets add-on store that you can use to create an amortization schedule.
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) to be recorded as an increase to the Lessee’s ROU Asset and amortized as a part of the 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 expense 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?
ROU (Right to use) assets should be amortized using the straight-line method. To amortize ROU asset, you need to allocate the cost of the asset over the lease term. The amortization of ROU asset is the process of systematically reducing the value of the asset over time to reflect its decreasing value as the lease term progresses.
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.
What are the disadvantages of amortization?
Disadvantages of amortization include the requirement to make regular payments, the borrower sometimes does not realize how much he/she is actually paying in interest, and the lack of flexibility in adjusting payments.
