IFRS 16 Excel-based lease accounting tool

From March 2024 the tool is owned, maintained and supported by FinancialTemplateStore.com, hence this page is to consider advertising for them. You can access their store by clicking on the button on the right hand side or click here.

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.

The tool is user-friendly 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.

What is IFRS 16?

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.

Quick start guide

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”.

Short video showing the main mechanics of the tool