Real estate development pro forma template

What the template works out

The example loaded above is a made-up 48-unit apartment building, broken down per unit in the multifamily development pro forma. Land costs $2,400,000, construction $9,600,000 in hard costs and $1,450,000 in soft costs, with 5% contingency on the hard costs. A 65% loan-to-cost construction loan at 7.5% funds the rest over an 18-month build and a 9-month lease-up.

From those inputs the pro forma gives a total development cost of $14,848,230, of which $918,230 is construction interest. Rents of $2,350 a month, less vacancy and $7,200 a unit in yearly operating costs, leave stabilized NOI of $983,520. That is a 6.62% yield on cost, +112 bps over the 5.5% exit cap rate. Sold at that cap rate, the building is worth $17,882,182, for a profit of $2.7M, a 22.6% levered IRR and a 1.51x equity multiple.

How the construction interest is worked out

Land is paid at closing, month 0. Hard costs and contingency follow an S-curve: slow during sitework, heaviest mid-build, slow again at finishes. Soft costs are spread evenly. Each month, equity pays first. In the example the $5,196,880 of equity runs out in month 7, and the loan funds every dollar after that.

Interest is charged monthly on the balance already drawn and added to the loan, so it compounds; the construction loan interest calculator shows the effect on a loan by itself. That creates the loop every development model has to deal with: the loan is 65% of total cost, total cost includes interest, and interest depends on the loan. The usual spreadsheet fix is Excel's iterative calculation, where one error can get stuck in the loop until you break it by hand. This template solves it directly. For each month in which the equity could run out there is exactly one total cost that balances, and only one of those months is consistent. The online version and the Excel file use the same method. We recalculated the Excel file in Microsoft Excel and compared it with the online calculator: the same total cost, interest, IRR and equity multiple for the example and for three edge cases: a 20% loan at 24% over 60 months of construction and 60 of lease-up, no loan at all, and a project worth less than it cost.

What it leaves out

It models one senior construction loan and one pool of equity. There is no mezzanine debt, preferred equity or investor waterfall, so if your deal splits profit between a sponsor and limited partners, you need a model with a waterfall on top of this. Loan fees and points are not separate lines; add them to soft costs. Interest during lease-up is added to the loan until the sale rather than paid from rental income, which overstates interest a little on a project that leases quickly.

The exit is a sale at stabilization, valued at NOI divided by the exit cap rate. Holding the building and refinancing out of the construction loan is not modelled yet, and neither are taxes.

Inputs worth a second look

• Hard costs. Use the contractor's bid or guaranteed maximum price, and keep contingency separate so you can see how much of it is left as the job goes on.
• Soft costs. Architecture, engineering, permits, impact fees, legal, insurance, property taxes during construction, the developer fee and lease-up marketing all belong here.
• Lease-up. Interest keeps accruing on the full balance until the sale, so a month of lease-up costs more interest than a month of construction: by then the whole loan is drawn.
• Exit cap rate. Use the cap rate you expect when you sell, 27 months out in the example, not the cap rate on today's trades.

From the pro forma to a budget and a draw schedule

Once the deal works, the same project carries over. The construction budget template builds its cost categories from this pro forma, and the draw schedule template plans one loan draw for every month the loan funds. A walk-through of the example with the monthly table is in the pro forma example.