How a fragile spreadsheet nearly killed a 9-unit deal – and the 9-tab architecture that brought it back.
A sponsor sent us their build-to-rent model.
Three tabs. Deal-specific data hard-coded throughout. A #REF! error in row 45 of the inputs sheet. No GP/LP waterfall. No sensitivity analysis. No tear sheet for investors. The exit cap rate appeared in seven different cells, all typed in manually.
The deal was a 9-unit BTR scheme – modest by institutional standards, but exactly the size where a sponsor needs every percent of return to make the equity story work. They were heading into LP conversations the following week.
The model wasn’t going to make it.
This is the story of how we rebuilt it. More importantly, it’s a field guide to the five things that actually break build-to-rent models – and how to fix them before they break a deal.
The Diagnostic
When we open a model, the first thing we look for isn’t the headline IRR. It’s the balance sheet check. Sources minus uses minus equity – does it equal zero?
In the original file, it didn’t. The miss was small, but it was there, hiding behind seven decimals of formatting. The kind of error that quietly compounds across periods and ends up explaining away half a percent of IRR.
The next thing we check is the refinance. In a BTR development, refinance is where the deal lives or dies. The construction loan pays itself off (and a chunk of equity) when the asset stabilizes and a permanent lender sizes a new loan against the new value. If the refi loan can’t cover the dev loan plus exit fees, the equity gets a capital call. Sponsors hate explaining capital calls. LPs hate hearing about them.
The original model showed refinance excess of negative $2.3 million. The sponsor was about to walk into a meeting with a model that – if anyone scrolled to row 80 of the pro forma – said “you owe more money.”
What Was Actually Wrong
Five things, in order of severity:
1. The on-cost yield was below the exit cap rate.
This is the cardinal sin of development. If stabilized NOI divided by total project cost is less than your exit cap rate, you have negative development spread. You’re paying $1.10 to create $1.00 of value. The original model had an on-cost yield below 3% and an exit cap of 6%. No amount of formatting fixes that.
2. Rents and GDVs implied two different cap rates.
The unit GDVs averaged $375,000. The rents averaged $1,900 per month. Those numbers don’t talk to each other. At a 30% expense ratio and a 6% exit cap, a $375K unit needs about $2,800 per month of rent to support its valuation. Either the rents were too low, the GDVs were too aggressive, or the exit cap was wrong. All three needed fixing.
3. There was no waterfall.
The “equity returns” line showed a single IRR for the entire equity stack. No GP/LP split. No preferred return. No promote calculation. For a deal being raised on a 70/30 promote above an 8% pref, this was a non-starter for any LP review.
4. The sensitivity analysis was an image from a previous deal.
Literally. A screenshot of a sensitivity table from a different project, pasted into the dashboard. The cap rate values didn’t even match the model’s own assumptions.
5. Charts were broken in three places.
Self-inflicted: the model had been opened in LibreOffice for a quick review, which silently strips chart XML on save. The sponsor didn’t know. Anyone opening the file in Excel saw blank chart placeholders where the dashboard visuals were supposed to be.
The Rebuild
We don’t patch broken models. We rebuild them. Patching gives you an unstable foundation. Rebuilding gives you something you can use again – for this deal and the next ten.
The architecture we shipped is the same one we use for every BTR project:
Cover. A one-page summary that opens the file. Project name, sponsor, headline returns, model checks. If anything is broken, this page says so before the reader scrolls anywhere else.
Guide. A six-section start-here panel. How to use the model. Common questions. Cell color legend. Contact info. Sponsors share these files with junior associates and LPs; the guide keeps everyone on the same page about which cells are inputs and which are outputs.
Dashboard. Headline metrics at a glance. Levered IRR, MOIC, profit on cost, equity contributed. Refi snapshot. Operating profile. Sensitivity quick view. Five model checks that flash green or red. This is the page that goes on screen during a meeting.
Inputs. Every assumption, in one place, in one column. Project info. Unit mix (beds, square feet, monthly rent, GDV per unit). Acquisition costs. Construction line items. Operating assumptions. Dev loan and refi terms. Exit cap and hold period. Waterfall structure. One blue-font column. Everything else in the model links back to it.
Sources & Uses. The deal pro forma at financing close. Total uses on the left. Dev loan and equity stack on the right. The dev loan uses a closed-form formula for capitalized interest – eliminating the circular reference and the “iterative calc must be on” warning that scares first-time readers.
Pro Forma. Annual operating model from Year 0 through exit. Twelve columns. Revenue build. Operating expenses. NOI. Debt service. Refinance mechanics. Sale proceeds in the exit year. Project and equity cash flows. Built so every formula copies cleanly across rows – the test we run is select a row, press Ctrl+\, and confirm Excel finds zero differences.
Returns. Project IRR. Levered IRR. GP/LP waterfall with preferred return and promote. LP and GP cash flows separated for IRR calculation. Two sensitivity tables: levered IRR vs exit cap, and levered IRR vs stabilized occupancy. Each table uses helper rows that rebuild the cash flow under each scenario – cleaner than Excel’s native data tables, which break when written programmatically.
Tear Sheet. A one-page investor handout. Three-column metrics dashboard. Annual cash flow summary. Investment highlights. Risk factors. Designed to print as a single PDF or be sent as an investor preview.
Disclaimer. Forward-looking statement language. Eight specific risk factors. Model limitations. The legal language sponsors need before sending the file to anyone outside the deal team.
Nine tabs. 1,255 formulas. Zero errors on validation.
The New Numbers
Once we rebalanced the inputs so on-cost yield matched exit cap – we increased rents, reduced project costs, and adjusted unit GDVs so the implied cap rate held together – the deal worked:
| Metric | Result |
|---|---|
| Project IRR (unlevered) | 11.8% |
| Project MOIC | 2.67× |
| Levered IRR | 16.1% |
| Equity MOIC | 3.78× |
| LP IRR | 15.2% |
| LP MOIC | 3.46× |
| GP IRR (incl. promote) | 22.2% |
| GP MOIC | 6.69× |
| GP Promote $ | $254K |
| Profit on cost | 23.6% |
| Stabilized DSCR | 2.5× |
| Refinance excess | +$113K (no capital call) |
The sponsor took the model into the LP meeting the following week.
What Transfers to Your Deals
If you’re modeling BTR projects yourself, five things matter more than the rest.
Match rents to GDVs to cap rates. These three numbers form a triangle. If they don’t agree, your model lies somewhere. Before you build anything else, work out whether your unit valuations are based on income (cap rate × NOI) or sale comps (price per square foot). If both, they should agree within 5%.
Stress-test refinance every time. Run exit cap from 4.5% to 8.0%. Run stabilized occupancy from 85% to 100%. If refinance excess goes negative in any realistic scenario, the deal needs more equity, less leverage, or a different exit assumption – before the LP finds it.
Build the waterfall before you build the dashboard. Sponsors waste hours on dashboard formatting before realizing their waterfall is wrong. The waterfall determines what numbers go on the dashboard. Reverse the order and you’ll rebuild twice.
Keep one column for inputs. Every assumption in one place. Every other cell in the model links back to it. Non-negotiable for an audit-proof model. The day someone says “what if the dev loan rate were 50bps higher” you want to change one cell, not seventeen.
Validate before you ship. Open the file in a fresh Excel session. Click through every tab. Confirm zero #REF!, #NUM!, or #VALUE! errors. If the model uses charts, save in Excel Desktop only – never in LibreOffice or Google Sheets if you intend to keep the chart XML.
The Template
The model we built for that sponsor is now available as a productized template. Same architecture. Same nine tabs. Same 1,255 formulas. The deal-specific data is stripped and replaced with a balanced sample deal that you can swap out for your own underwriting in about an hour.
If you’re underwriting a build-to-rent project and you don’t want to spend a week building the model from scratch – or you want a clean baseline to compare your existing model against – you can find the template at Etsy.
If you have a model that needs the same kind of rebuild, that’s also what we do. Send it to jonas.frac.cfo@gmail.com and we’ll take a look.