Development, cash flow and fund underwriting
17 worksheets. Contains models from Unit Mix to DCF, Class III loans to LP/GP Waterfall.
From Excel file,
ArriveComplete modeling logic.
How do I connect the rent, cost, financing and fund terms of real estate projects into cash flow, valuation and investor returns.
Which files correspond to this display?
Loan underwriting and pricing
5 worksheets. Contains cost, NOI, cap-rate valuation, leverage limit, DSCR, interest reserve, pricing and term sheet.
Actual work and execution record
17 worksheets. Includes financing follow-up, budget updates, drawdowns, insurance, vendors, accounts payable and administrative execution.
The following dismantles the worksheets, fields and formulas from the provided documents; the project name, address, amount, contact person and account information have been removed. Original Excel remains local.
Development model: 17 tables, what are each responsible for?
First sort out the project assumptions and rent base, then establish construction expenditure, stable period income, debt and exit cash flow, and finally allocate project income to LP and GP.
| Worksheet | What's in the file | How to use it for modeling |
|---|---|---|
| Executive Summary | Summarize investment scale, equity requirements, investor returns, timeline and project focus. | Read the results of Project Assumptions and OZ Fund Equity Waterfall to form an investment summary. |
| Property Information | Basic information, pictures and locations of properties and development projects. | Provide project background; the public page does not display specific addresses or transaction entities. |
| Project Assumptions | House type, area, rent, development cost, construction and stable period, financing conditions, valuation and exit assumptions. | Gather adjustable inputs in one table for reference in income, withdrawals, loans and cash flow calculations. |
| Stabilization Period Income | Rent by unit type, occupancy progress, stable period income and expenses. | Multiply the single-family rent by the number of households to establish the income from rental to stable operation. |
| Projected Cash Flows | Construction expenses, rent, NOI, exit price, three types of financing, interest and principal, and two layers of cash flow. | Calculate the unlevered cash flow of the asset layer first, and then superimpose loan issuance, repayment, financing fees and debt services to obtain the equity cash flow. |
| Assumption Checklist | Supporting materials and gaps for tenant, rent, cost, operating and financing assumptions. | Record the basis for assumptions, lack of information, impact on cash flow or debt, and follow up with the person in charge. |
| OZ Fund - Open Items | Fund structure, terms and issues requiring subsequent decisions. | Connect undetermined matters to model assumptions; discussed matters are not considered finalized. |
| Finanical Information Report | Summary of Sources & Uses, Construction Loan and Financial Information. | Check the source and purpose of funds together to assist in financing discussions. |
| DCF Analysis | NOI, terminal value, discount factor, asset and equity NPV, sensitivity matrix and discount rate derivation. | First calculate the present value of operating cash flow and net sales income, then compare it with development investment, and check the impact of exit capitalization rate and discount rate. |
| Unit Mix | House type, number of units, area, single-family rent and area unit price. | Establish the underlying details of rental income to avoid directly replacing the rental schedule with a total income. |
| Perm Loan Amort. Template | The opening balance, monthly payment, principal, interest and ending balance of a long-term loan. | Connect the debt service and exit balances of amortized loans to equity cash flows. |
| Construction Draw Schedule | Budget allocated by type of work and monthly withdrawal calendar. | The monthly date is generated from the construction start date, and the construction cost is allocated to the actual fund usage period. |
| Construction budget | General contracting, earthwork, structure, mechanical and electrical budget and cost per household. | Separate hard costs, soft costs and reserves, and then summarize development fund requirements. |
| OZ Fund Equity Waterfall | LP/GP capital contribution, capital return, priority return of arrears, catch-up, promote and residual income. | Process each tier of distributable cash sequentially; summarize investor cash flows, calculate XIRR, multiples and check remaining cash. |
| Fund Terms Assumptions | LP/GP investment ratio, management fee, priority return, distribution ratio and holding period. | Fund terms are used as the driving input of waterfall; the tax items in the file belong to the modeling assumptions. |
| GP Equity | GP investment, management fees, joint investment returns and promotion. | Distinguish between GP's own capital return and management/performance compensation, and show the sources of GP's income. |
| Const Loan Amort. Template | Loan amount, interest rate, term and repayment calculation of construction loan. | As a debt calculation support table, check with project fund use and repayment assumptions. |
How do I connect these tables?
I first sort out the number of apartments, area and unit rent by unit type, and then calculate the monthly rent and annual income. The development model calculates the single-family monthly rent as area × rent per square foot, which is then multiplied by the number of units.
Project Assumptions! D7 = E7 × C7; the income summary uses the rent of each apartment type × the number of units of each apartment type.
I separate vacancy/credit losses and operating costs from revenue, which include items such as taxes, insurance, maintenance, and management. This can explain whether the change in NOI comes from the revenue side or the expense side.
RTL UW Model: B38 = B28 − B37; that is, NOI = Effective Gross Income − Operating Expenses.
I distinguish between land, hard costs, soft costs, contingency and financing charges. Construction Draw Schedule spreads the budget by type of work and month, rather than assuming that the construction money will be spent all at once.
Draw schedule uses the construction start date and EOMONTH generation date; Projected Cash Flows!F40 refers to the construction withdrawals of the corresponding month, and represents the expenditure as a negative number.
I first calculate unlevered cash flow consisting of NOI, land, construction/soft costs and net sales proceeds, then add in acquisitions/refinancing, construction and long-term loan originations, fees, interest, principal and exit repayments.
Projected Cash Flows: row 48 is Unlevered Cash Flow; rows 61-78 are financing and debt services; row 81 is Levered Cash Flow.
I calculate acquisition LTV, ARLTV, and LTC separately and convert each limit into an allowable loan amount. Loans cannot be decided solely on the most lenient basis.
UW Model!B67 = MIN(B64, B65, B66): Take the minimum value among the loan amounts corresponding to the three leverage constraints. B62 = B51 / B47; B63 = B51 / B20.
I compare interest-only and amortized loans separately, checking the affordability of exit financing through NOI / annual debt service; construction carry is estimated using the average withdrawn balance.
B81 = B51 × B80 × B54 / 12; B82 = B81 × B55. The annual amortized debt service uses −PMT(monthly interest rate, total number of months, loan amount) × 12.
I used NOI and exit cap rate to derive the terminal value, then deducted the cost of sale and calculated the present value based on the discount rate. The DCF page displays both asset-level and equity-level results, as well as sensitivities to discount rate × exit cap rate and leverage × required equity return.
Core relationship: Terminal Value = next issue NOI / Exit Cap Rate after exit; Present Value = Cash Flow / (1 + Discount Rate)^number of issues. The document also contains the derivation of two discount rates, WACC and risk premium build-up.
I connect fund terms to project cash flows, dealing with capital return, unpaid priority return, GP catch-up and residual promotion in order. After deducting the allocated cash at each level, you can proceed to the next level.
Waterfall!C47 = B47 + C45 − C46: Give priority to rolling back the arrears. C61 = MIN(C59, MAX(C60,0)): Pay the outstanding amount only with available cash. C185 = C181 − C182 − C183: Check the total allocation balance.
RTL model: from project information to loan terms.
| Worksheet | What's in the file | How to use it for modeling |
|---|---|---|
| UW Model | Cost basis → Rent and fees → NOI → Cap-rate valuation → Loan size → Leverage test → DSCR → Interest reserve → scorecard. | Convert borrower's project information into loan underwriting results. |
| Construction Budget | Cost by job type, cost per household, hard cost proportion, soft cost and carry/contingency. | Summarize the cost denominator of the loan and distinguish between the actual construction budget and reserves. |
| Guidelines Ref | References to internal conditions organized by strategy and borrower experience level. | Provide restrictions for leverage testing; the public page does not display internal approval thresholds. |
| Term Sheet | Interest rates, fees, terms, first day lending, construction reserves, total loan amount and leverage tests. | Organize selected inputs and results from the underwriting model into financing terms. |
| Pricing Engine | SOFR, floor, basic spread, leverage adjustment, all-in rate, interest and fee estimates. | Select the spread according to product and experience level, and then combine the interest rates; it needs to be checked with the final underwriting interest rate and term sheet. |
How to make a pricing engine?
First take MAX (SOFR, SOFR Floor) to get the effective base interest rate, then select spread according to product and experience level, and add leverage adjustment. All-in rate = Effective Base Rate + Adjusted Spread / 10,000. The output from the Pricing Engine needs to be reconciled with the loan rates, fees, and Term Sheet in the UW Model.
Work record: After the model, what did I do?
This document captures the actual execution: updating underwriting budgets, comparing financing, preparing lender conditions, following up on drawdowns and closings, to insurance, vendor and payment processes.
BIDBUY Log / By Business
Organize actions, responsible persons, completion records and follow-up matters according to work and business categories.
Loan checklist
Track lender conditions, income/physical documents, insurance, title, loan and account information.
Ramp setup / vendor setup / vendor by account
Record system configuration, supplier invitation, data collection, and supplier's corresponding project account.
Vendor ACH / Bills created
Record payment information, invoice, expiration, payment status, approver and accounting classification. Public pages do not display any account, supplier or invoice information.
RNR COI / Lending COI projects
Track insurance certificates, additional insured, certificates required for specific items, and coverage gaps.
Builders’ License / construction draw / FCI Account
Track license status, construction withdrawals, WIP, funding progress, loan accounts and payment follow-up.
audit / Closing Check / claims
Organize follow-up records of audit, delivery and claim materials. The subject of the claim, the lawsuit and the amount are not displayed.
How to feed work back to the model?
I use the updated construction budget to re-underwrite, supplement bad debt, concessions, rent losses and operating expenses, and then check the funding connection between construction loans, land refinancing and long-term loans. Changes in construction quotation, construction period and lease-up will affect cash flow and required equity; financing, withdrawal and delivery lists are responsible for implementing the capital plan in the model into data and execution.
Items to be reviewed in the original document
The development template has #REF! references and external workbook links; some fund tier labels and descriptions need to be checked against the actual formulas. RTL's Acquisition LTV formula uses land value as the denominator, but the null check references another cost cell, which also requires a consistency check. This page shows the modeling structure and method. These references to be reviewed are not used to publish the project revenue results, and the original workbook is not modified.