Free Excel templates

A free assisted living occupancy and break-even calculator in Excel

Type your units, residents, rates and care fees, the Medicaid share and daily rate, direct care hours and their loaded cost, food and supplies, rent and other fixed costs: the sheet returns revenue per resident, the contribution of one resident, monthly EBITDA, the break-even in residents and occupancy, and what a shift toward Medicaid costs, with three checks.

The worked example is the book's fictional 48-unit residence on its FY2026 averages: revenue of $6,154.88 per resident a month, 2.81 direct care hours per resident-day at $23.31 and $13.20 of food and supplies leave a contribution of $3,761.05 per resident. Against $35,020 of rent and $98,975 of other fixed costs a month, the residence breaks even at 35.6 residents, 74.2 percent occupancy, and earns $37,133 a month at 45.5 residents. A Medicaid resident in place of a private one brings $2,691.96 less a month.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 147 free models.

Assisted_Living_Occupancy_and_Break_Even_Calculator.xlsx (12 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 book behind this model

This model goes with The Assisted Living Facility Owner, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 148 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: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

3 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Units and residents, revenue per resident actually earned, base rent, care fees by level, memory care, the private residents' shares, the Medicaid share, daily rate and room and board, direct care hours and their loaded hourly cost, food and supplies, rent or debt service, other fixed costs and the points of Medicaid share to test.
Results
Revenue per resident built from the rates, the contribution of one resident, monthly EBITDA, the break-even in residents and occupancy, the private charge, the Medicaid gap and the revenue a shift toward Medicaid would cost.
Checks
Three tests on negative entries, shares that must add to 100 percent, and residents against units with a contribution above zero, and an All checks cell.

What the worked case returns

MeasureValue
Revenue per resident a month built from the rates$6,230.92
Contribution per resident a month$3,761.05
Monthly EBITDA at the residents entered$37,133
Break-even residents35.6
Break-even occupancy (percent)74.2
Private assisted living charge, levels 1 to 3$6,138
One private resident replaced by a Medicaid resident, revenue a month$2,691.96
Revenue a month lost at the points of Medicaid share tested$1,225

Every figure is a teaching assumption of the book's fictional case, not a market norm or the rate of a real state, payer or landlord. The calculator treats direct care hours as variable per resident-day; a real schedule steps at thresholds, so the book also gives the break-even with the schedule held fixed (39.1 residents in the case).

How to use it on your own case

  1. Type your units, the residents in place on average and the revenue per resident a month you actually earned, without community fees.
  2. Type the base rent, the care fees by level, memory care and the shares of private residents at each, then the Medicaid share, daily rate and room and board.
  3. Enter direct care hours per resident-day, their loaded hourly cost, food and supplies per resident-day, rent or debt service and other fixed costs a month.
  4. Read the contribution, the monthly EBITDA, the break-even, the Medicaid gap and the checks.

Questions people ask about it

What occupancy does an assisted living facility need to break even?

Fixed costs, rent included, divided by the contribution of one resident, then by the units. In the book's case that is 35.6 residents of 48 with direct care treated as variable, and 39.1 with the staffing schedule held fixed.

How much revenue does one assisted living resident bring?

In the case a private assisted living resident pays $6,138 a month in calendar 2026, base rent plus the average care fee, and a Medicaid resident brings $3,446.04: room and board plus the waiver's daily rate.

Why does the calculator give a lower break-even than the book's schedule method?

It treats caregiver hours as rising and falling with each resident. A real schedule is set by thresholds and does not shrink when one resident leaves, so the fixed-schedule break-even is higher.

Is this calculator really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Assisted Living Facility Owner go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Assisted Living Facility Owner. See the book.