Kerbio
Spreadsheet · Version 1.0 · Excel file · Not on sale yet

HMO cash flow and yield, room by room

Put in the price, the works, each room's rent and how you'll fund it. It works out the cash flow after the mortgage, the cash left in, the occupancy you need to break even, and whether the same house would do better as a single let.

The HMO calculator sheet: verdict line, three tiles, inputs for the house, finance and rooms, and results for the HMO and a single let side by side
12rooms, each with its own rent
3ways to fund it: cash, mortgage, bridge then refinance
152LHA areas built in, as a floor for benefit tenants
Excelone-off purchase, yours to keep, no subscription

Watch: the HMO Calculator, step by step (1 min 23)

What it tells you

Cash flow a month

Rent from every room, less empty rooms, bills, management, repairs, insurance, council tax, cleaning, licence and compliance, then the mortgage interest.

Cash needed, cash left in

Price, stamp duty, buying costs, refurb and finance fees, less the mortgage. With a bridge, the most cash you're out before the refinance, and what comes back when you refinance on the finished value.

Yield, return and payback

Gross and net yield, return on the cash left in, and years to get your cash back.

Break-even occupancy

The share of room-months that must be filled to cover every cost including the mortgage, and how many rooms that is.

The same house as a single let

The same house let to one household, with its own rent, lighter refurb and lower costs, next to the HMO. The verdict says which wins and by how much a month.

Licence, Article 4 and LHA, in context

Pick your council to see what it publishes on additional and selective licensing and whether an HMO Article 4 direction was found. Pick your area to see the LHA shared accommodation rate, and price every room at it if you let to tenants on benefits. Public data, labelled, with the free Kerbio checker a click away.

A six room house, with a bridge

A £220,000 house with £45,000 of works to make six rooms, worth £290,000 finished. Bought on a six month bridge at 70% and refinanced at 75% of the finished value. Rooms at £525 to £600 with bills included, 92% occupancy.

  • Cash flow about £283 a month after a 6% HMO mortgage, with £78,291 left in (most cash out before the refinance: £139,796).
  • Needs 81% of room-months filled to break even: about five of the six rooms.
  • As a single let at £1,250 a month, the same house makes about £42 a month. The HMO wins by £241 a month, for £10,000 more cash left in.

Made-up numbers to show how it works. Payback on the cash left in is 23.1 years at these figures, which is the kind of number the sheet makes you look at.

The Sensitivity sheet: monthly cash flow by occupancy against average room rent, and a refurb overrun table

Licensing and Article 4, next to the numbers

Pick the council and the Council check sheet shows what it publishes on additional and selective licensing, in its own words, and whether an HMO Article 4 direction was found, with the source page and the date we read it. The LHA sheet lists the shared accommodation rate for 152 areas, labelled as a benefit cap, not a market rent.

Neither sheet changes your figures unless you ask it to: the LHA switch prices every room at the cap as a floor.

The Council check sheet: licensing status and Article 4 flag for each council, with sources

What's inside

Good fit
  • First HMO: pricing the rooms and the licence before you offer
  • Comparing an HMO conversion with a plain single let on the same house
  • Checking how much occupancy a deal can lose before it stops paying
Not what it's for
  • Retained interest, refurb bridges and maximum offers: that's the Deal Analyser
  • Planning or licensing decisions: check the postcode free on this site, then check with the council
  • Replacing a broker or accountant

Questions

Where do the room rents come from?

You type them in. The LHA shared accommodation rate for your area is shown beside them as a reference: it is the most housing benefit or Universal Credit will pay for a room in a shared house, a cap rather than a market rent. A switch lets you price every room at that rate as a floor.

Is my council's licence fee in it?

No. Fees vary a lot by council and by scheme, so the licence row is yours to fill; it starts with a placeholder and a note saying so. The Council check sheet shows what each council publishes about additional and selective licensing, with a link and the date we read it.

How does the bridging option work?

A bridging loan on the price, with monthly interest, an arrangement fee and exit costs for the months you set, then a mortgage at your loan to value on the finished value. Refurb is paid from your cash. It is a simple model: for retained interest and refurb bridges use the Deal Analyser.

How is break-even occupancy worked out?

Every cost that does not move with the rent (bills, insurance, council tax, cleaning, licence, compliance, mortgage interest) divided by the rent if full after management and repairs. Over 100% means it loses money even when full.

What do I need to use it?

Built as an Excel file (.xlsx) with standard formulas (INDEX/MATCH, SUMPRODUCT, IFERROR) and tested headless in LibreOffice against an independent model written separately from the spreadsheet. We have not yet opened this one in Microsoft Excel ourselves; the two earlier Kerbio spreadsheets use the same building blocks and run in Excel, Google Sheets and Numbers. If anything misbehaves, email us and we will fix it, replace it or refund you.

How was it tested?

Every result was recalculated for 10 random scenarios and the worked example, across all three finance options, and compared with an independent model written separately from the spreadsheet. All matched, with no formula errors. The test report is in the product's folder.

Is this financial, tax or planning advice?

No. It is a calculator for comparing deals. Licensing, planning and lender terms vary by council and lender; check the postcode on this site and then with the council.

Kerbio HMO Room Rent and Yield Calculator, version 1.0

Not on sale yet. The spreadsheet is built and tested; the price and the checkout are being set up. Leave your email and we will send one message when it is ready.

Made by Kerbio, a small independent UK tool for property investors. Free first: the HMO Article 4 postcode checker, licensing by council and LHA rates. Estimates for comparing deals, not financial, legal, tax or planning advice. LHA figures are benefit caps, not market rents. See our terms.