IFRS 16 Lease Accounting 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 lessee perspective.
DOWNLOAD DETAILS:
Excel Models
Populated & Unpopulated
€49.00
User-friendly Excel calculation model to calculate accounting movements and balances for up to 15 lease agreements based on IFRS 16 requirements from a lessee perspective. These include lease liability balances, interest expense, right-of-use asset balances, depreciation charges, lease payments and months to maturity. The calculation model will automate the lease accounting calculations and key disclosures and intended as a supporting working file for the General Ledger lease agreement postings.
The calculation model summarises the balances and movements across all lease agreements for a presentation period of up to 5 years as selected by the user which is available on a monthly and annual basis. The calculation model also includes adjustments for extension, termination and purchase options.
KEY OUTPUTS
-
Lease liability control account on a monthly and annual basis and by lease agreement. Control account includes opening balance, additions, interest expense, lease payments and closing balance
-
Right-of-use asset control account on a monthly and annual basis and by lease agreement. Control account includes opening balance, additions, depreciation charge and closing balance
-
Months to maturity calculation on a monthly and annual basis and by lease agreement as well as overall weighted average.
-
Charts showing lease liability and right-of-use asset balances by month
-
Charts showing depreciation charge, interest expenses and lease payments by month
KEY INPUTS
Setup Inputs:
-
Name of business
-
Currency
-
First presentation year
-
Names of lease agreements
-
Lease discount rate
-
Lease useful life
-
Non-cancellable starting and ending periods
-
Extension period (if applicable) and probability of exercising extension option
-
Termination period (if applicable) and probability of exercising termination option
-
Probability of Exercising Purchase Option
Cash flow inputs:
-
Initial direct costs incurred (if applicable);
-
Fixed lease payments;
-
Variable lease payments (if applicable);
-
Exercise price of purchase option (if applicable);
-
Other cash flows (if applicable).
MODEL STRUCTURE
The model comprises of 7 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The tabs to be populated by the user are the input tabs which include ‘i_Setup’ for setup assumptions, ‘i_cashflows’ to input applicable lease cash flows. The calculation tabs takes the user-defined inputs to calculate and produce the outputs which are presented in the ‘o_summary’ and ‘o_Dashboard’ tabs.
System tabs include:
-
A 'Front Sheet' containing a disclaimer, instructions and contents;
-
A checks dashboard containing a summary of checks by tab.
KEY FEATURES
-
The model follows good practice financial modelling guidelines and includes instructions, line item explanations, checks and input validations;
-
The model allows for a maximum of 15 lease agreements (this can be extended if required).
-
The model allows the inclusion of extension, termination and purchase options with drop down to select probability option will be exercised;
-
The model includes 5 different cash flow types for each lease agreement including: initial direct costs, fixed lease payments, variable lease payments, exercise price of purchase option and any other cash flows;
-
The model allows for lease agreements with any starting and ending date to be included and summarises lease balance and movement results across a 5 year period as selected by the user;
-
Business name, currency, first presentation year and lease assumptions are fully customisable
-
The model includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors.