A free lease-adjusted leverage calculator in Excel: EBITDAR, rent multiple and IFRS 16
Two conventions for the same borrower, the rent multiple and the discounted lease liability, with fixed charge coverage, a sensitivity to the multiple and a peer that owns its sites: six sheets, no macros.
The lease-adjusted leverage formula adds rent-like debt to financial debt and divides by earnings before rent: (debt + 8 × annual rent) / EBITDAR. The file applies it to Marlow Stores, a fictional retailer with $600 million of debt, $240 million of EBITDA and $130 million of rent. Reported leverage is 2.50x. Lease-adjusted, it is 4.43x, above Ashby Stores, a peer that owns its shops and borrows 4.17x. Under IFRS 16 the same leases become an $816.2 million liability and leverage reads 3.93x.
Get the template
The worked case and an empty copy, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The blank file keeps the formulas and checks with every input cleared: one column for your borrower, one for its peer.
The book behind this model
This file is one worked case from Credit Analysis. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Inputs are blue type on a yellow fill and sit on one sheet; everything else is a formula.
Inputs
Two companies side by side, one that leases and one that owns: revenue, EBITDA with rent expensed, total rent and its short-term and variable part, debt, cash, interest, maintenance capex, cash taxes and scheduled amortization. Then the rent multiple, the remaining lease term, the incremental borrowing rate and the multiples for the sensitivity.
Leverage
EBITDAR, lease debt at the multiple, lease-adjusted gross and net debt, then unadjusted and lease-adjusted leverage, interest cover, fixed charge cover and a lender-style fixed charge coverage ratio.
IFRS16
The capitalized rent, the lease liability as the present value of the remaining payments, EBITDA with that rent removed from costs, leverage on that basis, the lease interest and the multiple of rent the liability implies.
Sensitivity
Lease-adjusted leverage for both companies from 5x to 10x rent, and the multiple at which the two are equally levered.
Checks
Tie-outs on EBITDAR, the discounting and the sensitivity, and every figure on this page set against the model.
Lender FCCR, after maintenance capex, taxes and amortization
1.51x
2.21x
On reported numbers Marlow looks the safer credit by 1.67 turns. Put the leases back and it is the more levered of the two, by 0.27 turns, and its fixed charge cover is less than half of Ashby’s.
The two conventions do not agree. IFRS 16 discounts nine years of $120 million of capitalized rent at 6.0% to $816.2 million, 6.80x the rent, against $1,040.0 million at 8x, and leaves $10 million of short-term and variable rent in costs. Leverage reads 3.93x instead of 4.43x.
The ranking turns on the multiple: below 7.24x rent Marlow looks less levered than Ashby, above it more. Say which convention a covenant or a comparison uses before reading the number.
How to use it on your own numbers
Enter the borrower’s EBITDA with rent expensed, total rent and its short-term and variable part, debt, cash and interest on Inputs. If the accounts are already under IFRS 16, subtract the lease payments from reported EBITDA first.
Set the rent multiple your counterparty uses. 8x is the traditional convention; check the credit agreement or the methodology you are comparing against.
Enter the remaining lease term and the incremental borrowing rate from the lease note to get the IFRS 16 liability.
Put a peer in the second column, ideally one that owns its sites, and read Sensitivity for the multiple at which the comparison flips.
Questions people ask about it
What is the lease adjusted leverage formula?
Lease-adjusted leverage = (total debt + lease debt) / EBITDAR, where EBITDAR is EBITDA plus rent expense and lease debt is either a multiple of annual rent, traditionally 8x, or the lease liability on the balance sheet. For Marlow: (600 + 8 x 130) / (240 + 130) = 1,640 / 370 = 4.43x.
What is EBITDAR?
Earnings before interest, taxes, depreciation, amortization and rent. Adding rent back puts a company that leases its sites on the same footing as one that owns them, whose cost of occupancy sits in depreciation and interest instead.
Rent multiple or IFRS 16: which is right?
Neither is a fact; both are conventions. The multiple is simple and comparable across accounting regimes. The IFRS 16 liability reflects the actual remaining term and discount rate, so a company with short leases shows less lease debt. Use the one your covenant or comparison uses, and say which.
What is a fixed charge coverage ratio?
Earnings available to pay fixed charges divided by those charges. The simple form is EBITDAR / (interest + rent): 2.15x for Marlow. Lenders often deduct maintenance capex and cash taxes from the numerator and add scheduled amortization to the denominator, which gives 1.51x.
Does it use macros or circular references?
No. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Can I use it on a real company?
You can reuse the structure. Both companies and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Credit Analysis: the full set adds the add-back bridge with a switch on every line, both covenants and which one binds, the downside carried through to cash and the recovery, and expected loss against the margin. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.