If you’re looking for a practical IFRS 16 lease calculation example in Excel, you’ve come to the right place. This page gives you a free, ready-to-use IFRS 16 Excel template with a full amortisation schedule, ROU asset depreciation, journal entries, and annual P&L impact all in one spreadsheet. No sign-up required. Just download and start using it today.
What Is IFRS 16 and Why Does It Matter?
IFRS 16 is the international lease accounting standard issued by the International Accounting Standards Board (IASB), effective from 1 January 2019. It replaced the old IAS 17 standard and fundamentally changed the way lessees account for leases.
Under the old rules, many leases were kept “off balance sheet” as operating leases — meaning a company could lease a fleet of vehicles, an office building, or heavy machinery without it appearing as debt on the balance sheet. IFRS 16 ended that. Now, almost every lease must be recognised on the balance sheet as a Right-of-Use (ROU) asset and a corresponding lease liability.
This has a real impact on a company’s financial ratios debt-to-equity, EBITDA, interest cover, and affects how investors, lenders, and auditors read the financial statements. Understanding and correctly calculating your lease figures under IFRS 16 is no longer optional. It’s essential.
Key insight: Under IFRS 16, a lessee no longer recognizes a single straight-line “rent expense.” Instead, it records depreciation of the ROU asset (in operating expenses) and finance charges (in finance costs), meaning the total P&L charge is front-loaded in the early years of the lease.
IFRS 16 Summary and Key Principles
Before diving into the Excel model, it helps to understand the five key pillars of IFRS 16 lease accounting. Here’s a clean summary:
| Principle | What It Means in Practice | Standard Ref |
|---|---|---|
| Initial Recognition | On the commencement date, recognise a ROU asset and a lease liability equal to the present value of future lease payments. | Para. 22 |
| Discount Rate | Use the rate implicit in the lease if readily determinable; otherwise use the lessee’s incremental borrowing rate (IBR). | Para. 26 |
| ROU Asset Measurement | Initially = lease liability + initial direct costs + prepaid payments − lease incentives received. | Para. 24 |
| Depreciation | Depreciate the ROU asset over the shorter of the lease term and the asset’s useful life — usually straight-line. | Para. 31 |
| Interest Unwinding | Increase the lease liability by finance charges (opening balance × periodic rate) and reduce it by cash payments made. | Para. 36 |
| Practical Expedients | Short-term leases (≤12 months) and low-value asset leases may be expensed straight-line — no balance sheet recognition required. | Para. 5 |
3What’s Inside the Free Excel Template?
This IFRS 16 automated Excel model is built with professional-grade formatting and 100% dynamic formulas. You change the yellow input cells, everything else calculates automatically. Here’s what each sheet does:
| Inputs & Assumptions | Amortization Schedule |
| Enter your lease date, term, payment amount, IBR, and advance/arrears setting. Initial recognition calculates instantly. | Full 60-period IFRS 16 amortization schedule in Excel, opening liability, payment, finance charge, principal, and closing balance. |
| ROU Asset Depreciation | P&L & Balance Sheet |
| Straight-line depreciation schedule for the Right-of-Use asset over the full lease term with impairment column. | Annual summary of depreciation, interest expense, ROU net book value, and current vs non-current lease liability. |
| Journal Entries | How to Use Guide |
| IFRS 16 journal entries example with live values, commencement date, monthly payment, finance accrual, and depreciation. | Step-by-step instructions built right into the spreadsheet so anyone on your team can use it confidently. |
IFRS 16 Lease Calculation: Step-by-Step Example
Let’s walk through a real worked example using the numbers pre-loaded in the Excel template. This is the same IFRS 16 lease calculation example that drives every formula in the spreadsheet.
The Lease Details
- Lessee: ABC Manufacturing Ltd
- Asset: Industrial Machinery
- Lease Term: 60 months (5 years)
- Monthly Payment: $5,000 paid in advance
- Commencement Date: 1 January 2024
- Incremental Borrowing Rate (IBR): 6% per annum (0.5% per month)
Step 1 — Calculate the Present Value (Initial Lease Liability)
Because payments are made in advance (annuity-due), the present value formula is:
PV = Payment × [(1 − (1 + r)^−n) / r] × (1 + r)
PV = 5,000 × [(1 − (1.005)^−60) / 0.005] × 1.005
PV = 5,000 × 51.7256 × 1.005
PV ≈ $259,897
This $259,897 is both your initial lease liability and your initial ROU asset cost on the commencement date (assuming no initial direct costs or lease incentives).
Step 2 — Build the IFRS 16 Amortisation Schedule
Each month, the IFRS 16 amortisation schedule Excel model calculates:
- Finance Charge = (Opening Liability − Payment) × Monthly IBR
- Principal Repaid = Payment − Finance Charge
- Closing Liability = Opening Liability − Principal Repaid
By month 60, the closing liability reaches exactly $0.00 — confirming the schedule balances perfectly.
Step 3 — Depreciate the ROU Asset
Monthly depreciation = $259,897 ÷ 60 months = $4,331.62 per month on a straight-line basis.
Over 5 years, the ROU asset is fully depreciated to nil, matching the end of the lease term.
IFRS 16 Journal Entries Example
One of the most searched topics in lease accounting is how to actually record the entries in the books. Here are the IFRS 16 journal entries for our worked example — all of which are included with live values in the Excel template:
| Ref | Account | Debit ($) | Credit ($) |
|---|---|---|---|
| JE 1 Day 1 Recognition |
Dr Right-of-Use Asset | 259,897 | |
| Cr Lease Liability | 259,897 | ||
| JE 2 Monthly Payment |
Dr Lease Liability | 5,000 | |
| Cr Cash / Bank | 5,000 | ||
| JE 3 Finance Charge |
Dr Finance Charge (P&L) | 1,274 | |
| Cr Lease Liability | 1,274 | ||
| JE 4 Monthly Depreciation |
Dr Depreciation Expense (P&L) | 4,332 | |
| Cr Accumulated Depreciation — ROU Asset | 4,332 |
These entries repeat each month. The finance charge amount reduces over time as the liability unwinds. Which is the fundamental difference between IFRS 16 and the old straight-line rent expense model.
How to Use the IFRS 16 Lease Liability Calculator Excel
Using this lease liability calculator Excel is straightforward. Here’s the process in four quick steps:
- Step 1: Go to the Inputs & Assumptions tab and fill in the yellow cells — commencement date, lease term, payment amount, IBR, and whether payments are made in advance or arrears.
- Step 2: Check the Amortisation Schedule tab to see the full month-by-month breakdown of your lease liability.
- Step 3: Review the ROU Asset Depreciation tab for straight-line depreciation figures you’ll need for your financial statements.
- Step 4: Use the P&L & Balance Sheet Impact tab to get the annual figures for your reporting disclosures and ratio analysis.
The entire model is built on IFRS 16 spreadsheet with formulas, no macros, no VBA, no add-ins required. It works on any version of Excel or Google Sheets (with minor adjustments).
7IFRS 16 vs IAS 17: What Changed?
If you’re still familiar with the old IAS 17 operating lease model, here’s a quick comparison to show why IFRS 16 changes the numbers significantly:
| Item | IAS 17 (Old) | IFRS 16 (New) |
|---|---|---|
| Balance Sheet | No asset or liability for operating leases | ROU asset + lease liability recognised |
| P&L — Early Years | Straight-line rent expense only | Higher total charge (depreciation + front-loaded interest) |
| P&L — Later Years | Same rent expense throughout | Lower total charge (interest reduces as liability unwinds) |
| EBITDA Impact | Rent reduces EBITDA | Rent replaced by depreciation (below EBITDA) — EBITDA improves |
| Leverage Ratios | No impact on debt | Lease liability increases reported debt |
Frequently Asked Questions
Q1: What is the incremental borrowing rate (IBR) under IFRS 16?
The IBR is the rate of interest your company would have to pay to borrow funds over a similar term and with similar collateral to obtain an asset of similar value. In simple terms — it’s your company’s borrowing cost. If you’re unsure, your bank or finance team can advise on an appropriate rate. Typical rates range from 3% to 10% per annum depending on the company and market conditions.
Q2: Does IFRS 16 apply to all leases?
No. IFRS 16 includes two practical expedients that allow lessees to keep leases off the balance sheet: (1) Short-term leases with a term of 12 months or less at the commencement date, and (2) Low-value asset leases where the underlying asset is of low value when new (IASB guidance suggests below approximately $5,000 USD). These can simply be expensed on a straight-line basis.
Q3: Can I use this Excel template for multiple leases?
Yes. The template is designed for one lease at a time, so for multiple leases you can duplicate the file or duplicate the sheets within the workbook. For larger portfolios with dozens or hundreds of leases, a dedicated IFRS 16 software solution may be more efficient — but for most small-to-medium businesses, this Excel model covers everything you need.
Q4: Is this IFRS 16 Excel template free to use commercially?
Yes — the template is completely free to download and use for personal and commercial purposes. You’re welcome to use it for your own company’s accounts or to support clients in your accounting practice.
Q5: What’s the difference between an annuity-due and an annuity-immediate under IFRS 16?
An annuity-due means payments are made at the beginning of each period (payments in advance). An annuity-immediate means payments are made at the end of each period (payments in arrears). This affects both the PV calculation and the finance charge accrual. The Excel template handles both, simply set “Payments in Advance?” to Yes or No in the inputs.
