A free care home valuation model in Excel, built from residents and hours
Occupancy from admissions and survival, the conversion of self-funders, fees, care hours and staff cost, EBITDARM, rent cover, value on the trade and on the lease, occupancy break-evens and debt capacity: ten sheets, no macros.
The file values a fictional 70-bed care home from its residents up. With 2.8 admissions a month and the leaving hazard of real stays, occupancy settles at 89.5%. Fees average 1,508 a week and revenue is 4.93 million; staff, built from care hours per resident, cost 55.8% of it. EBITDARM is 1.53 million, a 31.0% margin, covering the rent of 0.85 million 1.8 times. The home is worth 16.8 million on the trade and 14.2 million on the lease, 240,000 a bed, and the operator breaks even at 74.5% occupancy.
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 129 free models.
Care_Home_Valuation_Model.xlsx (126 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 file is one worked case from Senior Living and Healthcare Real Estate. 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: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Ten sheets. Blue cells are the inputs; every other sheet points to them.
Read me and Inputs
Beds, admissions, the leaving hazard by month of stay, fees, capital on admission, staff hours and pay, costs, rent, yields and debt terms.
Residents and Payor
Survival by month of stay, occupancy, and when each self-funder's capital falls below the limit.
Result
Fees, revenue, care hours, staff cost, EBITDARM, rent, rent cover and the two values.
Trajectory
Twenty-five years of revenue, costs and cover as fees, wages and rent move at different speeds.
Occupancy, Embargo and Fill and Build
What one point of occupancy is worth, break-even and covenant occupancy, debt capacity, an admissions embargo, and a new home filling up.
Checks
The book's headline figures against the formulas.
What the worked case returns
Measure
Value
Registered beds
70
Occupancy
89.5%
Revenue
4.93m
Staff as a share of revenue
55.8%
EBITDARM and margin
1.53m, 31.0%
Rent cover on EBITDARM
1.80x
Value on the trade
16.8m
Value on the lease
14.2m
Operator's break-even occupancy
74.5%
A care home has no leases, so occupancy is not an input: it is what admissions and length of stay produce. One point of occupancy here is worth about 31,600 of EBITDARM, an elasticity of 1.8, which is why the admissions rate is the first input to argue about.
How to use it on your own home
Enter beds, admissions a month and the leaving hazard on Inputs.
Enter fees and the capital distribution of self-funders.
Enter care hours per resident and pay, then read the Result sheet.
Check break-even and covenant occupancy on the Occupancy sheet.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference.
What is EBITDARM?
Earnings before interest, tax, depreciation, amortisation, rent and management charges: the home's trading profit before what it pays for the building and the operator. Rent cover is EBITDARM, or EBITDAR, divided by rent.
Can I use it for a real home?
You can reuse the structure. The home and every figure are fictional and set in a UK funding framework, and nothing here is investment advice.
The rest of the files
This template is one of the companion workbooks for Senior Living and Healthcare Real Estate: the others track the payor mix, build the rota and employer cost, and give a bid sheet. 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.
This template comes from Senior Living and Healthcare Real Estate. 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.