A complete underwriting model for apartment buildings and other multi-unit residential deals, built for investors, sponsors and analysts who need to price a value-add acquisition quickly and defend the numbers to partners and lenders. It takes you from the rent roll to project IRR, LP and GP returns and equity multiple in one workbook.
What is included
Rent Roll: up to 12 unit types with unit counts, size, in-place and market rent, loss-to-lease, renovation premium, renovation cost and the pace of renovations.
Operating Budget: 8 other-income lines and 15 expense lines, each on its own basis (per unit, per square foot, percentage of effective gross income or fixed) and growth rate.
Acquisition and capital: purchase price, closing costs, capital reserve and a 5-line capital plan.
Debt: interest-only period followed by amortization, plus an optional refinance sized by loan-to-value or debt service coverage.
Pro Forma: a 10-year projection of income, expenses, NOI, debt service, cash flow, DSCR, sale at an exit cap rate and net proceeds, with project and unlevered IRR.
Waterfall: preferred return plus two IRR hurdles with promote, showing LP and GP distributions, IRR and equity multiple year by year.
Sensitivity: equity multiple by exit cap rate and purchase price.
Dashboard: headline KPIs, a 6-point scorecard against your targets, a verdict and 4 charts.
How to use it
1. Open the Start Here tab: a three-step quick start, a color key and a clickable list of every sheet.
2. Replace the example values in the yellow input cells with your own. Everything else is calculated, and formulas are left visible so you can audit every step.
3. Read the Dashboard. Every number on it traces back to an input, and the scorecard compares the plan with the targets you set.
4. Switch between the Base, Upside and Downside scenarios to see what really drives the result.
Technical notes
The model is a single .xlsx workbook for Microsoft Excel 2016 or newer and Microsoft 365 on Windows and Mac. It also works in Google Sheets. There are no macros, add-ins, external links or locked cells, and it is set up to print one page wide. It ships with a complete worked example so you can see how everything fits together before entering your own numbers.
Quality: the workbook is generated from code and must pass automated tests against hand-calculated cases with zero formula errors, and it is opened and recalculated in Microsoft Excel before release. We use AI-assisted tooling in our build and testing process; the structure, logic and checks are designed and reviewed by us.
This is a planning and analysis tool, not financial, tax, legal or investment advice.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Real Estate, Integrated Financial Model Excel: Multifamily Underwriting Model with GP/LP Waterfall Excel (XLSX) Spreadsheet, ModelDesk
|
Download our FREE Strategy & Transformation Framework Templates
Download our free compilation of 50+ Strategy & Transformation slides and templates. Frameworks include McKinsey 7-S, Balanced Scorecard, Disruptive Innovation, BCG Curve, and many more. |