IFRS 16 Lease Accounting for Lessors Excel Calculation Model
Originally published: 20/11/2023 11:01
Publication number: ELQ-25103-1
View all versions & Certificate

IFRS 16 Lease Accounting for Lessors Excel Calculation Model

Excel calculation model to calculate accounting movements and balances for up to 15 lease agreements based on IFRS 16 requirements from a lessor perspective.

Purpose of the Model

Our Excel calculation model is designed with a user-friendly interface to meet the IFRS 16 requirements from a lessor's perspective. It streamlines calculations for up to 15 lease agreements, automating accounting movements and balances essential for lease management. The model efficiently handles lease received balances (Net investment in the lease), interest income, revenue, cost of sales, inventory (Equipment held for lease), residual value, and months to maturity. It automates lease accounting calculations, journal entries, and crucial disclosures, serving as a vital working file for General Ledger lease agreement postings.

Key Outputs

This versatile model delivers comprehensive outputs, not confined to any specific industry:
• Monthly and annual lease receivable control accounts by lease agreement, showcasing opening and closing balances, additions, interest income, lease payments, and residual value movements.
• Calculation of months to maturity per lease agreement and weighted averages.
• Internal Rate of Return (IRR) by lease.
• Visual representations via charts illustrating lease receivable progression and breakdown of costs and income per lease.
• Journal entries for selected periods, including additions, lease payments, interest income, and residual value movements.

Key Inputs

The inputs are structured for ease of use, comprising setup inputs and cash flow item inputs. Highlights include:
• Setup Inputs: Customizable fields like business name, currency, lease agreement names, non-cancellable periods, extension and termination options, accounting codes, and posting periods.
• Cash Flow Inputs: Detailed inputs covering costs of leased assets, fair values, initial direct costs, fixed and variable lease payments, residual values, and other receipts/payments.

Model Structure

The model consists of 8 tabs for inputs ('i_'), calculations ('c_'), outputs ('o_’), and system functionalities. Users populate the 'i_Setup' and 'i_cashflows' tabs, which then feed into calculation tabs to generate results showcased in 'o_summary', 'o_journals', and 'o_Dashboard' tabs. System tabs include a Front Sheet with instructions and a checks dashboard summarizing validation checks.

Key Features

This tool offers numerous user-friendly and efficient functionalities:
• Adherence to best financial modeling practices with thorough instructions, line item explanations, checks, and validations.
• Capacity to handle up to 15 lease agreements, extendable if necessary.
• Flexibility for inclusion of extension and termination options with selectable probability.
• Support for 8 distinct cash flow types per lease, facilitating comprehensive lease analysis.
• Customizable parameters like business name, currency, presentation years, and lease assumptions.
• Checks dashboard for easy error identification and resolution.

About Projectify

We are financial modeling experts with experience in leading business modeling teams at top firms. Our goal is to provide robust, user-friendly models following best practices, aiding businesses in critical financial planning and analysis. We prioritize customer satisfaction and welcome feedback to continuously improve our models.

This Best Practice includes
1 Blank Excel Model and 1 Excel Model with Populated Example

Acquire business license for $55.00

Add to cart

Add to bookmarks


Further information

Calculation and visualisation of accounting movements and balances for a number of lease agreements from a lessor perspective

0.0 / 5 (0 votes)

please wait...