Free Excel templates

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.

Lease_Adjusted_Leverage.xlsx (17 KB) · Lease_Adjusted_Leverage_Blank.xlsx (16 KB)

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.

Get the book on Amazon →

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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

Get the Library, $79 →

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.

What the worked case returns

MeasureMarlow (leases)Ashby (owns)
EBITDA, rent expensed$240.0m$360.0m
Rent$130.0m$0.0m
EBITDAR$370.0m$360.0m
Debt / EBITDA, unadjusted2.50x4.17x
Lease debt at 8x rent$1,040.0m$0.0m
Lease-adjusted leverage, (debt + 8x rent) / EBITDAR4.43x4.17x
Lease-adjusted net leverage4.16x3.89x
IFRS 16 lease liability$816.2m$0.0m
Leverage under IFRS 163.93x4.17x
Fixed charge cover, EBITDAR / (interest + rent)2.15x4.50x
Lender FCCR, after maintenance capex, taxes and amortization1.51x2.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

  1. 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.
  2. Set the rent multiple your counterparty uses. 8x is the traditional convention; check the credit agreement or the methodology you are comparing against.
  3. Enter the remaining lease term and the incremental borrowing rate from the lease note to get the IFRS 16 liability.
  4. 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.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Credit Analysis. The book is on Amazon.

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.