A free student housing pro forma model template in Excel, with a full worked scheme
Six room types, the rate card against what is actually collected, RevPAB on three bases, the cost base per bed, the capital cycle and a ten-year exit: nineteen linked sheets, one fictional 520-bed scheme, no macros.
The file models a 520-bed student accommodation scheme in Leeds across six room types. The rate card advertises £192.98 a week; once an early-booking discount, late discounting and agent commission are taken out, revenue per available bed over the academic year is £173.61, a gap of 10.0% that no yield in a marketing pack discloses. Net operating income is £2,698,619 on a 65.7% margin, and ten years of cash flow plus a three-step exit price the building at £41,728,378, 10.2% below its £46.5 million asking price. Every one of those numbers is a formula you can move.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Nineteen sheets, in the order the calculation runs. Blue text on a pale blue fill is an input; yellow fill is the carrying assumption of the sheet, the one to argue about first; black text is a formula.
Read me
What the workbook computes, the inputs to argue about first, and the conventions used throughout.
The tape
Six room types: beds, advertised rate per week, contract weeks and area, and the weighted-average rate card.
Assumptions
Occupancy at the census, the rate-to-occupancy elasticity and the exit yield, the three inputs that carry the whole model, plus every other driver.
The rate card
The three concessions that separate the advertised rate from the collected rate: early booking, late discounting and agent commission.
RevPAB
Revenue per available bed on three bases: the rate card, the collected rate, and academic RevPAB across the contract weeks.
The summer
The six-week summer programme as a separate business, its own margin, and the two taxes it attracts that the academic year does not.
The cost base
The cost per bed line by line, the management fee on gross revenue, net operating income, irrecoverable VAT, and the energy short position.
Capital expenditure
The refurbishment cycle, ongoing capital items and the summer the cycle consumes, annualised, with the yield after all three.
Ten-year cash flow
Year by year revenue, costs, net income, capital expenditure and cash flow, with the refurbishment landing where the cycle actually puts it.
The exit and the value
The three-step exit, the price that leaves once debt and sale costs are counted, and the discount to the asking price.
Rate and occupancy
The trade between rate and occupancy at the modelled elasticity, and the thresholds at which a discount stops paying for itself.
What a point is worth
The net operating income and value of one point of occupancy, and the discount rate that buys it.
The nomination
What a nomination agreement over a share of the building costs in value, and the yield movement needed to offset it.
City and competitor
Beds already consented against students in the town, and the supply still to land.
Debt
The loan on both the asking price and the bid, sized against the same net operating income.
Scenarios
A scenario engine that re-runs the ten-year value for every sensitivity, shock and inversion the book names.
Affordability and the hold
What the rent is against local income, and how the hold period changes the answer.
Checks
269 figures the book prints set against what the model computes, with a verdict on each line. The sheet ends ALL OK.
What the worked scheme shows
Measure
Value
Beds, across six room types
520
Advertised rate, weighted average
£192.98/week
Collected rate, weighted average
£181.35/week
RevPAB, academic
£173.61/week
Gap, advertised rate to RevPAB
10.0%
Net operating income
£2,698,619
Margin
65.7%
Net initial yield after capital expenditure
4.69% (vs 5.45% quoted)
Price that leaves (all-in present value)
£41,728,378
Discount to the asking price
10.2%
Read the first and last pairs of lines together. A rate card of £192.98 a week collects only £173.61 once the three concessions are counted, a gap of 10.0% that no headline yield discloses. Net operating income of £2,698,619 carries a healthy 65.7% margin, but capital expenditure quoted at a 5.45% net initial yield brings it down to 4.69%, and the refurbishment cycle alone accounts for about half of that gap. Ten years of cash flow and a three-step exit price the building at £41,728,378, 10.2% below its £46.5 million asking price, which is the number a bid should start from, not the yield on the cover page.
How to use it on your own scheme
Overwrite the tape: beds, advertised rate, contract weeks and area by room type. Everything downstream recalculates.
Read Assumptions before anything else: occupancy at the census and the rate-to-occupancy elasticity carry the whole model, and neither is a measurement, it is a price the operator chose to pay.
Check RevPAB on both bases before quoting a yield: academic RevPAB divides by contract weeks, RevPAB over the year divides by 52, and a pack that quotes one without naming which is not comparable to another.
Read Capital expenditure before the exit: on this scheme the refurbishment cycle alone costs about 50 basis points of yield, before the ongoing items and the summer it consumes.
Keep the Checks sheet: once you change an input it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this student housing model really free?
Yes. It is a companion file to a book. There is no sign-up, no email address and no paid version.
Does it use macros or circular references?
No macros and no circular reference: occupancy, the rate-to-occupancy elasticity and the exit yield are inputs on one sheet, and every other figure is a formula reading from them. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why is RevPAB lower than the rate card?
Three concessions turn the £192.98 advertised rate into a £181.35 collected rate: an early-booking discount taken by 42% of beds, late discounting taken by 23%, and agent commission on the 31% of beds let through an agent, 6.0% off the rate card in total. Revenue per available bed is lower again, at £173.61, a gap of 10.0% from the rate card, because it is measured across every contract week rather than only the weeks a bed is actually let.
Can I use it for a real scheme?
You can reuse the structure. The scheme, the city 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 Student Housing Real Estate: the full set adds
the letting campaign tracker week by week, the operating cost benchmark, and the bid sheet with the four inversions of the pricing chapter. Free, like this one.