Hotel underwriting model
Occupancy, ADR, RevPAR, GOP, FF&E reserve, debt yield. Build a hotel’s revenue from occupancy and ADR, carry it through a departmental operating statement to GOP, EBITDA and NOI after the FF&E reserve, size a senior loan to loan to cost, DSCR and debt yield with a mezzanine layer on top, and read the levered return and its sensitivity to occupancy and the exit cap rate.
Built in: Private equity, Investment banking
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: hotel underwriting model.Updated 22 October 2026
Who builds it, and for whatThe model a hotel investment team at a real estate fund, a lodging REIT or a hotel owner builds before bidding, and the one a hotel lender reads before writing a term sheet. It answers what the hotel earns once the renovation is done and occupancy settles, how much of that income the operator, the brand and the furniture reserve take before the owner sees any of it, how much a lender will advance against income that is resold every night, and what the equity earns before and after the sponsor’s promote.
Every sheet below, in your browser. Sign in free to change the inputs and watch it recalculate
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | |||||||||||||||
| 2 | $m unless stated. ADR and spend per occupied room in dollars; costs per key in $ thousands a year. | |||||||||||||||
| 4 | Year | Unit | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | Y8 | Y9 | Y10 | Y11 | Notes | |
| 5 | Timing | |||||||||||||||
| 6 | Year | # | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | ||
| 7 | Hold period | years | 5 | Whole years, 1 to 10. Sold at the end of this year on the next year’s NOI. | ||||||||||||
| 8 | Hold period used | years | 5 | The input, rounded and held within 1 to 10. Checks flag any difference. | ||||||||||||
| 9 | Owned during the year | flag | 0 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 10 | Sale year | flag | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 12 | Scenario | |||||||||||||||
| 13 | Scenario | # | 1 | 1 = base, 2 = upside, 3 = downside. Sets stabilised occupancy, Year 1 ADR and the exit cap rate. | ||||||||||||
| 15 | Rooms, occupancy and ADR | |||||||||||||||
| 16 | Keys | rooms | 200 | Rooms available to sell each night. | ||||||||||||
| 17 | Days in the year | days | 365 | |||||||||||||
| 18 | Occupancy, Year 1 | % | 62.0% | The year of the renovation and relaunch. Ramps in a straight line to the stabilised rate. | ||||||||||||
| 19 | First stabilised year | year | 3 | Whole years, 1 to 10. Occupancy reaches the stabilised rate in this year. | ||||||||||||
| 20 | First stabilised year used | year | 3 | The input, rounded and held within 1 to 10. Checks flag any difference. | ||||||||||||
| 21 | Stabilised occupancy, base | % | 72.0% | |||||||||||||
| 22 | Stabilised occupancy, upside | % | 75.0% | |||||||||||||
| 23 | Stabilised occupancy, downside | % | 68.0% | |||||||||||||
| 24 | Stabilised occupancy, selected scenario | % | 72.0% | |||||||||||||
| 25 | ADR, Year 1, base | $ | 180.00 | Average daily rate: rooms revenue per occupied room night. | ||||||||||||
| 26 | ADR, Year 1, upside | $ | 188.00 | |||||||||||||
| 27 | ADR, Year 1, downside | $ | 172.00 | |||||||||||||
| 28 | ADR, Year 1, selected scenario | $ | 180.00 | |||||||||||||
| 29 | ADR growth, % a year | % | 3.0% | |||||||||||||
| 31 | Other operated departments | |||||||||||||||
| 32 | Food and beverage revenue per occupied room, Year 1 | $ | 85.00 | Restaurants, bar, banqueting and room service. | ||||||||||||
| 33 | Other operated departments revenue per occupied room, Year 1 | $ | 12.00 | Spa, parking and other outlets the hotel runs itself. | ||||||||||||
| 34 | Rentals and other income, % of rooms revenue | % | 2.0% | Leased shop space, commissions, cancellation and attrition fees. No departmental cost. | ||||||||||||
| 35 | Growth in spend per occupied room and in costs, % a year | % | 3.0% | Applies to F&B and other spend per occupied room, costs per key and fixed charges. | ||||||||||||
| 37 | Departmental and undistributed expenses | |||||||||||||||
| 38 | Rooms expense, % of rooms revenue | % | 25.0% | Housekeeping, front desk, laundry, amenities, reservation and travel agent commissions. | ||||||||||||
| 39 | Food and beverage expense, % of F&B revenue | % | 72.0% | Cost of food and drink and the kitchen and service staff. | ||||||||||||
| 40 | Other operated departments expense, % of their revenue | % | 50.0% | |||||||||||||
| 41 | Administrative and general, per available room, Year 1 | $k/key | 6.50 | Largely fixed: management, finance, HR, IT and security. | ||||||||||||
| 42 | Sales and marketing, % of total revenue | % | 7.5% | Includes the brand’s marketing and loyalty programme charges. | ||||||||||||
| 43 | Property operations and maintenance, per available room, Year 1 | $k/key | 3.50 | |||||||||||||
| 44 | Utilities, per available room, Year 1 | $k/key | 2.80 | |||||||||||||
| 46 | Management fees and fixed charges | |||||||||||||||
| 47 | Base management fee, % of total revenue | % | 3.0% | |||||||||||||
| 48 | Incentive management fee, % of adjusted GOP above the owner’s priority | % | 10.0% | Adjusted GOP is GOP less the base fee. | ||||||||||||
| 49 | Owner’s priority, % of purchase price and PIP | % | 8.0% | The annual return the owner keeps before the operator earns an incentive fee. | ||||||||||||
| 50 | Property tax, Year 1 | $m | 0.5 | Fixed charge. Grows at the cost growth rate. | ||||||||||||
| 51 | Insurance, Year 1 | $m | 0.3 | Fixed charge. Grows at the cost growth rate. | ||||||||||||
| 53 | FF&E reserve | |||||||||||||||
| 54 | FF&E reserve, % of total revenue | % | 2.0% | 3.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | 4.0% | Low while the renovated rooms are new, then the 4% lenders and brands expect. | ||
| 56 | Purchase and property improvement plan | |||||||||||||||
| 57 | Purchase price | $m | 38.0 | Paid at closing (Year 0 column). | ||||||||||||
| 58 | Closing costs, % of purchase price | % | 2.0% | |||||||||||||
| 59 | Property improvement plan (PIP), per key | $k/key | 20.00 | The renovation the brand requires as a condition of the licence. Funded at closing. | ||||||||||||
| 60 | PIP, total | $m | 4.0 | |||||||||||||
| 62 | Senior loan | |||||||||||||||
| 63 | Maximum loan to cost (LTC) | % | 60.0% | On the purchase price, closing costs and PIP. | ||||||||||||
| 64 | Minimum debt service coverage ratio (DSCR) | x | 1.3x | Year 1 NOI / annual senior debt service. | ||||||||||||
| 65 | Minimum debt yield | % | 10.0% | Year 1 NOI / senior loan. | ||||||||||||
| 66 | Senior interest rate, % a year | % | 6.5% | |||||||||||||
| 67 | Amortisation period | years | 25 | |||||||||||||
| 68 | Origination fee, % of senior loan | % | 1.0% | Paid at closing, a use of funds. | ||||||||||||
| 70 | Mezzanine loan | |||||||||||||||
| 71 | Mezzanine loan, % of total cost | % | 10.0% | Set to 0 for no mezzanine. Interest only, repaid at sale. | ||||||||||||
| 72 | Mezzanine interest rate, % a year | % | 11.0% | |||||||||||||
| 74 | Exit | |||||||||||||||
| 75 | Exit cap rate, base | % | 7.5% | On NOI after the FF&E reserve, for the year after the sale. | ||||||||||||
| 76 | Exit cap rate, upside | % | 7.0% | |||||||||||||
| 77 | Exit cap rate, downside | % | 8.0% | |||||||||||||
| 78 | Exit cap rate, selected scenario | % | 7.5% | |||||||||||||
| 79 | Selling costs, % of sale price | % | 2.0% | |||||||||||||
| 81 | Equity waterfall | |||||||||||||||
| 82 | Sponsor co-investment, % of equity | % | 10.0% | Treated as investor capital in tiers 1 and 3. | ||||||||||||
| 83 | Preferred return, % a year, compounding | % | 8.0% | Accrues on unreturned capital and unpaid preferred return. | ||||||||||||
| 84 | Sponsor promote, % of profit | % | 20.0% | After capital and preferred return: 100% catch-up, then this split. | ||||||||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Hotel underwriting model: the workbook
Excel workbook with working formulas, no macros and no external links. Work with it above first; it is the same model as the preview.
- The worked model
- The workbook above as an Excel file: 9 sheets and 1,673 live formulas, all 8 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves, so you can rebuild a step and compare.
- The starter
- The same inputs and layout with the hotel operating statement: departmental P&L to NOI left blank for you to build, each blank cell marked. Free with any account. It has no worked answer to check yourself against; the worked model is that answer.
What the base case says
- RevPAR, first stabilised year ($)
- 137.49
- GOP margin, first stabilised year
- 35.2%
- Levered IRR
- 15.3%
- NOI, Year 1
- $2.7m
- NOI, first stabilised year
- $3.5m
- Senior loan
- $25.7m
- Senior DSCR, first stabilised year
- 1.7x
- Unlevered IRR
- 10.2%
- Equity multiple
- 2.0x
Read from the workbook as served, every input at its default. Periods: Y0, Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8, Y9, Y10, Y11. The figures are invented and move with whatever you type in.
What this model is
An underwriting model for a 200-key full-service hotel, bought and renovated under a brand property improvement plan (PIP), held for five years and sold on forward NOI at an exit cap rate.
A hotel is an operating business inside a building. There are no leases: every room is sold again every night, so income moves with the economy week by week. Revenue starts from occupancy and the average daily rate (ADR), whose product is RevPAR, the industry’s headline measure; restaurants, bars and other outlets follow the guests who are in the building.
Costs are reported department by department, then the undistributed costs of running the building, to gross operating profit (GOP). Much of the undistributed cost is fixed per room, so GOP margin rises and falls faster than revenue. An operator runs the hotel for a base fee on revenue and an incentive fee on profit above the owner’s priority.
Furniture, fixtures and equipment wear out on a cycle of a few years, so lenders and buyers deduct an FF&E reserve, usually 4% of revenue, before NOI. Because hotel income can fall faster than a valuer’s cap rate moves, lenders size on debt yield as well as on cost and coverage: NOI over the loan says how much income stands behind each dollar lent, whatever the building is said to be worth.
Outputs are RevPAR, GOP and NOI by year, the senior loan and its tests, unlevered and levered returns, the split of equity cash flow between investors and the sponsor, and two board-style sensitivity tables.
Seats: Private equity, Investment banking.
Careers that do this work: infrastructure & real assets, private equity, equity research, asset management.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Occupancy, ADR and RevPAR build
Room nights, rate and revenue by department
Assumptions, rows 16–29 · Assumptions, rows 32–35 · Revenue, rows 6–13 · Revenue, rows 16–24
Hotel operating statement: departmental P&L to NOI
Departments, undistributed costs, GOP, fees, EBITDA
Assumptions, rows 38–44 · Assumptions, rows 47–51 · Operating Statement, rows 6–10 · Operating Statement, rows 13–18 · Operating Statement, rows 21–26 · Operating Statement, rows 29–31 · Operating Statement, rows 34–38 · Operating Statement, rows 41–43 · Operating Statement, rows 46–47 · Operating Statement, rows 56–60
FF&E reserve
Furniture, fixtures and equipment, set aside before NOI
Assumptions, row 54 · Operating Statement, rows 50–53
Sources and uses
What the deal costs and who funds it
Assumptions, rows 57–60 · Debt, rows 6–11 · Debt, rows 14–20
Loan sizing: LTV, DSCR and debt yield
The lender lends to the tightest of three constraints
Assumptions, rows 63–68 · Debt, rows 23–34 · Sensitivity, rows 19–26 · Sensitivity, rows 29–50
Mortgage debt schedule
Constant payment, interest, principal, balloon
Assumptions, rows 71–72 · Debt, rows 37–43 · Debt, rows 46–50 · Debt, rows 53–57
Property returns
Unlevered and levered IRR, multiple, cash-on-cash, exit cap sensitivity
Assumptions, rows 6–10 · Assumptions, rows 75–79 · Returns, rows 6–11 · Returns, rows 14–19 · Returns, rows 22–29 · Sensitivity, rows 5–8 · Sensitivity, rows 11–16 · Sensitivity, rows 53–98
Equity waterfall: preferred return, catch-up, promote
How the levered cash is split between investors and sponsor
Assumptions, rows 82–84 · Waterfall, rows 6–8 · Waterfall, rows 11–16 · Waterfall, rows 19–21 · Waterfall, rows 24–26 · Waterfall, rows 29–36
What you should be able to explain
- Why RevPAR, occupancy times ADR, is the headline measure, and why a point of occupancy and a dollar of rate do not move profit by the same amount.
- How the departmental statement works: each department’s revenue less its own costs, then the undistributed costs of the building, to gross operating profit.
- Why GOP margin moves faster than revenue: administration, maintenance and utilities are largely fixed per room, so a hotel has operating leverage that a leased building does not.
- What the operator is paid: a base fee on revenue whatever happens, and an incentive fee only on profit above the owner’s priority return.
- Why NOI is struck after an FF&E reserve, and why hotel lenders lean on debt yield: income can fall faster than a valuer’s cap rate moves.
What a reviewer looks for
- Revenue grown as one line from a growth rate, so a fall in occupancy and a fall in rate look the same.
- Every cost as a percentage of revenue, which removes the operating leverage that makes hotels volatile.
- NOI struck before the FF&E reserve, which overstates value at the cap rate and the loan a lender will make.
- The incentive fee charged on all of GOP, or forgotten, when it is paid only above the owner’s priority.
- The loan sized on stabilised income in a year the hotel is still ramping, so the first year’s coverage fails.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Annual periods. The hotel is bought and the PIP funded at closing (Year 0 column), and it is sold at the end of the hold period, an input of 1 to 10 whole years (base case 5). The sale is priced on the next year’s NOI, which is why the sheets run to Year 11; returns columns after the sale year read zero.
- Occupancy starts at the Year 1 rate and rises in a straight line to the stabilised rate by the first stabilised year. ADR grows at its own rate; spend per occupied room, costs per key and fixed charges grow at the cost growth rate.
- The operating statement follows the order of the hotel industry’s uniform system of accounts: departmental revenue and expenses, undistributed operating expenses, gross operating profit, management fees, fixed charges, EBITDA. NOI is EBITDA less the FF&E reserve; the cap rates, the loan tests and the returns read NOI.
- The incentive fee is a share of adjusted GOP (GOP less the base fee) above the owner’s priority, a fixed annual amount set as a return on the purchase price and PIP. It is nil in any year adjusted GOP falls short.
- The senior loan is sized on Year 1 NOI to the lowest of loan to cost, DSCR and debt yield, and is a constant-payment mortgage with interest on the opening balance, repaid at sale. The mezzanine loan is interest only and repaid at sale. Debt service is paid from NOI; any shortfall is a negative levered cash flow, funded by equity.
- Waterfall terms: the sponsor co-investment is treated as investor capital. Tier 1 pays investors their capital and a compounding preferred return through one hurdle balance; tier 2 pays the sponsor a 100% catch-up to its promote share of all profit distributed; tier 3 splits the rest at the promote share.
- The sensitivity tables hold the financing as sized and recompute the hotel’s income from the operating statement’s own fixed and variable lines, so the centre of each table equals the model.
Build it yourself
The starter workbook
The operating statement has been cleared from departmental revenue to NOI, except the FF&E reserve rows. Link the departmental revenue, build the departmental expenses, the undistributed costs and GOP, the base and incentive fees, the fixed charges and EBITDA, then NOI after the FF&E reserve, so that the loan, the returns and the waterfall calculate again. The Checks sheet shows when everything ties.
Blanks: Hotel operating statement: departmental P&L to NOI. Free with any account. Compare with the worked model when you are done: download above.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~3 min
IRR by Eye
Say the IRR for any multiple and hold before the interviewer finishes the sentence, and the multiple for any IRR.
Read · Guide · 13 min
Real Estate Financial Modelling: Why Corporate Finance Mechanics Break Here
Read · Guide · 11 min
Credit and Covenant Modelling: What a Lender Actually Tests
Read · Guide · 11 min
Financial Modelling Best Practices: The Conventions That Make a Model Auditable
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: RevPAR, Average Daily Rate (ADR), Gross Operating Profit (GOP), Furniture, Fixtures and Equipment Reserve, Property Improvement Plan (PIP), Net Operating Income (NOI), Cap Rate (Capitalisation Rate), Debt Yield, Loan to Cost (LTC), DSCR (Debt Service Coverage Ratio), Mezzanine Debt, Stabilisation, Cash-on-Cash Return, Promote (Real Estate).
Questions about this model
What is RevPAR, and why not just use occupancy?
RevPAR is rooms revenue per available room night, which is occupancy times the average daily rate. In the base case the first stabilised year runs at 72% occupancy and an ADR of $190.96, a RevPAR of $137.49. Occupancy alone hides price: a hotel can fill rooms by discounting. RevPAR captures both, which is why owners, operators and lenders compare hotels on it.
Why does a hotel have an FF&E reserve?
Furniture, fixtures and equipment in guest rooms wear out on a cycle of a few years, and a hotel that stops replacing them loses rate. So owners set aside a share of revenue every year, 4% once the renovated rooms age, and lenders and buyers deduct it before NOI. Here it rises from 2% in the year after the renovation to 4% from Year 3.
Why do hotel lenders care so much about debt yield?
Because hotel income is resold every night and can fall within months, while valuations and cap rates move slowly. Debt yield, NOI over the loan, says how much income stands behind each dollar lent whatever the building is said to be worth. Here the senior loan is sized on Year 1 NOI, still in the ramp: loan to cost binds in the base case, and in the downside the lower income makes coverage bind instead.
How does this differ from the property acquisition model?
The acquisition model buys income locked in by leases and reads it lease by lease. A hotel has no leases: it is an operating business inside a building, with departments, a management company and a brand. The two share the loan sizing, the mortgage, the returns and the waterfall, so the differences you see are the ones that make a hotel a hotel.
What does it cost?
Nothing to preview: the whole workbook is on this page, and a free account works with it online, every formula included. Every starter workbook downloads with a free account. L3VLUP Plus ($4.99/month) keeps one worked model each billing month, yours for good, and L3VLUP Pro ($25/month) keeps every one.
Related models
Real estate · Core
Property acquisition model
Rent roll, NOI, cap rate, three-constraint loan, waterfall
Real estate · Advanced
Real estate development model
Budget, construction loan, lease-up, refinance, development spread
Real estate · Core
REIT model: FFO, AFFO and NAV
FFO, AFFO, NAV per share and the cost of new equity