IFRS 16 Excel-based lease accounting tool

Complying with the new IFRS 16 lease accounting standard may seem daunting, especially given its complexities. To support businesses in this transition, I present my IFRS 16 Lease Accounting Tool – an Excel-based, macro-driven solution that simplifies the task.

The tool is designed with usability in mind, catering to businesses of all sizes. Its key strength lies in accurately calculating the lease liability and right-of-use asset values, while considering individual lease conditions such as discount rates, lease periods, and payment schedules. Although the tool currently does not support the generation of journal entries and foreign currency calculations, it delivers a robust foundation for the core IFRS 16 calculations.

My IFRS 16 Lease Accounting Tool also accommodates unlimited modifications in the lease contracts and aids in the preparation of disclosures as required under the IFRS 16 standard.

Distributed for free under an MIT license, the tool is not only user-friendly but also widely accessible, with clear instructions and guidance integrated within the application. If you’re a small business grappling with the intricacies of IFRS 16, or a larger organization seeking a cost-effective solution for managing your lease portfolio, our tool offers a helpful resource to streamline your lease accounting processes.

Note: Companies that have hundreds of leases, or have complex lease agreements (e.g. leases in foreign currency), should consider a transactional system.

IFRS 16 is a significant global financial reporting standard, introduced by the International Accounting Standards Board (IASB) that replaced IAS 17. It addresses lease accounting and was implemented to bring more transparency and comparability into financial reporting of lease transactions.

The central principle of IFRS 16 is that it requires lessees to recognize almost all leases on the balance sheet, as it eliminates the classification of leases as either operating leases or finance leases. This means that lessees must now recognize a lease liability reflecting future lease payments and a ‘right-of-use’ asset for virtually all lease contracts.

This standard dramatically changes the way lessees record leases in their financial statements, making the company’s financial leverage more apparent. In the income statement, it replaces the straight-line operating lease expense with a depreciation charge for the right-of-use asset and an interest expense on the lease liability, potentially impacting key financial metrics such as EBITDA and operating profit.

The file contains two sheets “Summary” and “LeaseTemplate“. The “LeaseTemplate” sheet is hidden by default and is only used as a template which is copied each time we add a new lease agreement to the model. I.e. when you open the file the first time you will only see the “Summary” sheet.

This model is built on the basis that one sheet = one lease agreement, e.g. if your company leases one building, one car and one machine you will end up with four sheets: 1. Summary, 2. Building leasing agreement, 3. Car leasing agreement and 4. Machine leasing agreement.

The “Summary” sheet summarizes key data from all sheets containing lease agreements and is also where you select the start- and end-dates for the current year-to-date period as well as adding new lease agreements.

• Click on “Add new agreement” to add a new empty lease template.
• Select “Period start” and “Period end” dates for the current YTD period.
• Click on “Update summary” to gather updated information from all lease sheets based on the “Period start” and “Period end” dates.
  • When clicked, the table starting on row 12 will be filled with key information from the lease sheets
• You can “jump” straight to each lease sheet by clicking there blue link in column B.

Each lease agreement will be a copy of the “LeaseTemplate” sheet as seen below.

The “LeaseTemplate” sheet comprises three main sections:

1. “Section 1” is where you enter all relevant information about the lease.
2. “Section 2” comprises detailed information per month from commencement date until ending date. “Section 2” will be built/updated when you click on the button “RUN ASSESSMENT” located at the bottom of “Section 1”
3. “Section 3” summarizes the key information as per the “Period start” and “Period end” dates you have selected on sheet “Summary”, i.e. this is the information that will be collected on sheet “Summary”.

It’s easy to start using the tool.

1. Download the Excel file located here

2. Right-click on the file, choose Properties (Sw: “Egenskaper”), and then select the Unblock (Sw: “Avblockera”) checkbox on the General (Sw: “Allmänt”) tab. If needed, read my blog post for additional information.

3. Open the Excel file

4. Enable Macros in the Excel file, visit Microsoft for instructions.

5. Now you are ready to start using the tool

Please send support tickets and improvement ideas to hello(at)lundgrensimon.com

Version 1.0 – 2023-06-20: First release

Version 3.0 – 2023-08-29: Added functionality to support variable payments that depends on a rate or index

MIT License

Copyright (c) 2023 Simon Lundgren

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.