Multifamily Development Proforma Excel Template
The Multifamily Development Proforma Guide serves three purposes:
A Tutorial - A collection of blog posts, images, videos, and links for users using the Real Estate Development Proforma Model. Every facet of the model is explained and can be easily navigated with the hyperlinked table of contents below.
A Preview—This blog post will detail the tool for viewers who need a multifamily real estate development tool. Then, you can determine if it is a good fit for your business.
Knowledge - Regardless of your interest in pro forma software, this article will provide great insight into how a real estate development opportunity should be underwritten and analyzed.
Real Estate Development Proforma
Enable Macros
As the workbook opens, Excel will prompt you with a yellow warning bar at the top of the screen to “Enable Macros.” Click “Enable Content.”
If you don't see this bar pop up, you can follow these steps:
File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable Macros
Click Ok. Save the workbook on your computer, and then close it. Once you reopen it, macros will work.
Enable Iterative Calculations
The first thing you will need to do is enable “Iterative Calculations.”
For PC Users
File > Options > Formulas > Enable Iterative Calculations (check the box)
For MacBook Users
I have found this YouTube tutorial the most intuitive for Mac users if you need assistance enabling iterative calculations.
To learn more about iterative calculations and why they are necessary, please take two minutes to read about the relevance of circular references in development proforma.
Pre-Analysis Video Tutorial
Input Cells
In all of the Tactica tools, all brown text cells are your responsibility. Black text hosts formulas that you should not overwrite.
This tab is where you will make the majority of the assumptions.
General Property Information
Most of these assumptions are self-explanatory. Rentable SF and Units will be pulled from the “Unit Mix” tab, thus the black text.
Analysis Start: This should be the day you close funding to develop the deal.
Construction Financing
You will enter the construction timeline and loan terms. Construction loans are assumed to be interest-only. This section is where the cash (equity) requirement will populate. Make sure to read about how the Tactica real estate development proforma model accounts for investor equity, as this will have a material impact on the IRR calculations.
While the model doesn’t account for floating-rate debt, if you did get a variable-rate debt quote with an interest rate cap, you could underwrite the interest rate at the maximum capped interest rate amount.
Related: Why forecasting future interest rates based on the forward SOFR curve is dangerous.
If you’re eligible for a grant or other free funding source, you’d need to make a few minor adjustments to the model to account for this.
Construction Draw & Summary
A pivot table of the various construction costs and a chart detailing the monthly project spending during the construction timeline will populate here.
Permanent Financing
You will enter the loan terms for your permanent financing.
*Recently added a toggle for the interest-only assumption*
The Refinance Month is a brown cell that you can overwrite, but it is a formula that ties to the Stabilization Date, which I will cover in the next section. Naturally, most developers will refinance once they can get maximum proceeds. If there was an instance where you wanted to keep the construction loan for additional months or blow it out sooner, you could adjust this date.
The Stabilized NOI cell populates from the "Stabilized Operations" tab, which we will get to shortly.
Check out the linked tutorials if you want to know how to alter the model to handle HUD 221(d)(4) financing and potentially a BSPRA credit.
Lease-Up Summary
Pre-Leased at Certificate of Occupancy (CO): What percentage of units will you pre-lease? Pay attention to the timing of the targeted opening date. A high pre-leasing percentage is crucial in cold-weather climates if the opening occurs in the late-summer months. You don't want to have to try to stabilize the property in the winter! If the opening is in March, you'll have more wiggle room if the pre-leasing percentage is low.
Related: Learn a workaround for projects that have multiple CO dates (multiple phases)
Leases/Month Until Stabilization: How many leases do you think you can sign monthly?
Stabilized Occupancy: This will determine the Stabilization Date and Stabilization Month below. I think anywhere from 92% and up is acceptable. This ties to the refinance date, so the lower the stabilized occupancy, the sooner the construction loan will be refinanced.
Concessions-Lease-Up (months): Do you need to offer rental concessions to lease the property? If so, how many months per lease? This assumption is for lease-up only. Concessions will cease once the property stabilizes.
All the milestone dates are summarized in this section, expressed in dates and months.
Unit Mix Summary
The pie chart shows how many of each floorplan there will be. Aggregate unit rental metrics are summarized to the right. The bar chart at the bottom is more granular, showing each floorplan’s targeted rent and rent PSF.
These charts tie to the “Unit Mix” tab and will refresh when inputting your unit mix data there.
Cash Flow Summary
This simple chart provides a nice visual of NOI, CF after Debt and Debt Service Coverage Ratio (DSCR) over a theoretical 10-year hold.
The data that populates this chart is hosted on the “Lists & Amortization” tab. It will refresh automatically.
Residual Assumptions
Residual assumptions determine your net proceeds when selling the development in the future.
Stabilized Cap Rate/Increment: What is today's stabilized capitalization rate (cap rate) for a similar deal? I have 5% in this example. The increment is ten basis points (bps). If you were to sell this investment in ten years, the residual cap rate would be 5% + 10 bps x 10 years or 6.00%. Higher stabilized cap rates and increments/year are more conservative.
You can learn more about our methodology for calculating residual sale proceeds.
Cost of Sale: These would include brokerage fees, deed tax, and legal fees. This assumption is made as a percentage of Sales Price.
The rest of the data summarizes the reversion. It’s essential to digest these metrics and make sure they are realistic. The residual sales price is the most critical factor in a successful development project.
Project Level Metrics
From here, you can adjust the Hold Period and see how it affects:
Ensure the return on cost is considerably higher than the market cap rate to ensure financial feasibility.
Partnership Returns
Finally, the real estate development proforma model has five different partnership structures. You can select which one you want to use from the drop-down list, and the model will summarize both GP and LP investment return metrics.
It's important to note that the GP's IRR, equity multiple, and ROI do not include income from asset management, development, or residual sales fees. All partnership distribution models are linked to the hold period you select on this tab. While you can change the hold period on each partnership tab, it's important to link back to this tab so the graphs are accurate. There is an error check to help you with this.
Reboot
You will notice a reboot option to the right of the dashboard.
This last toggle is for emergencies. Due to the circular references in this workbook, there is potential for the model to go haywire.
Hopefully, this doesn't happen. If it does, the reboot is the antidote. If the cell ever glows read, you will need to switch "No" to "Yes." Whatever mistake you made, you now correct it. Above, I accidentally typed “yy” in the Construction Loan Amt cell. I will change it back to 80%.
After that, the cell will remain red until you change it back to "No." Then everything should be good.
Note: If you miss correcting this error immediately, the workbook may not be salvageable. Users neglect their mistakes and continue populating the workbook, ignoring the reboot altogether. Unfortunately, in these instances, the workbook could not be saved. You must reboot ASAP for it to work effectively!
Thankfully, there is a solution if you accidentally neglect the reboot. You’ll need to start over, but most of the work is already done. Opening a new version of the development model and copying/pasting all information from the corrupted workbook into the fresh, unaltered version (ensuring not to repeat the error) was a relatively quick and painless fix and only took less than 10 minutes.
Fees
You can input assumptions in for:
Construction Management Fees
Refi Fees
Disposition Fees
Asset Management Fees
Note: The asset management fee assumption is summarized on the "Project Summary" tab. You'd make your assumption on the "Returns Summary" tab, as it may be easier when you can see the operating details.
Tactica's proforma model doesn't include the predevelopment period. The proforma timer starts when the lender funds the construction loan, corresponding with the land purchase and cash equity funding. This simplifies the model significantly and works flawlessly for most private developers.
In this tab, you will enter the construction costs and formulate the draw schedule. You can forecast per square foot (PSF), per unit, or gross cost (chunk). Make sure you only choose one option per expense line. If you have multiple assumptions, the model will read from left to right and ignore anything to the right of the first entry.
I will then define each budgeted item as either a:
Land Cost (or land contribution)
Soft Cost
Hard Cost
FF&E
Impact Fee
Finance & Title
If you want to describe" Items" differently than the bullets above, you can amend the drop-down on the "Lists and Amortization" tab.
Directly to the right, you decide the draw timing in the "Method" Column (J). There are four options for drawing:
Upfront
Even
Bell-Curve
Custom
Upfront: You would draw 100% of the cost on the front end.
Even: The budgeted expense is drawn evenly over the construction period
Bell-Curve: The estimated expenditure will ramp up in the middle of the construction timeline and then fall toward the tail end. You will also have control over the steepness of the bell curve. I have it currently set at 2.5. The lower the number, the steeper the curve (cell N74)
Custom: If you select "Custom," all cells will be "0," you will need to overwrite them when you want to draw the funds. I commonly will use the custom option for expenses necessary for operating the property after construction, like marketing, operating reserves, and decorating.
The total will turn pink if the budgeted amount doesn't match the actual drawn amount. In some instances, this is normal. It can also act as an error check. The $300,000 in marketing expense will not be drawn during the 14-month construction period, so I am okay with the pink cell and the carryover balance. Always double-check to ensure things are added out when using the custom option.
If you switch from "custom" back to one of the other options, remember to drag the formula from above back over the previously custom cell. Otherwise, the cell will remain overwritten and inaccurate. To help you remember this step, any "Custom" overwritten cell will glow pink like the $65,000 and $100,000 spent in Month 14.
The financial model will calculate capitalized interest (the last line item of $735,450). It is not an assumption you make so you can leave it alone. There will always be a balance as the model assumes you draw and pay the accrued interest from the previous month the following month.
Operating Shortfall
If this cell is showing up as "-" you are in good shape.
Sometimes, it may be a negative number. A negative amount means there will be a period during the lease-up where cash flows from the property are not covering the debt service, and the construction loan is fully exhausted. For example, let’s say the operation shortfall is $70,000.
Note: The model doesn't track the negative operating cash flow if you refinanced before property stabilization (not recommended). It only tracks operating shortfall while the construction loan is still outstanding.
I would go up to the budget and add a line called “operating contingency” and set it at $75,000.
Notice I set the “method” to “Custom." I actually won’t allocate this expense at all during the construction period. Therefore, the $75,000 will carry over as a surplus in the “Balance” column.
Now, these funds will be available post-construction to help offset the negative cash flow early in the lease-up. The operating shortfall is back to $0, as shown in the two images above.
Final Construction Loan Balance
This sum details how much of the loan proceeds you utilize. A fast lease-up may allow you to get into positive cash flow sooner and lessen the dependence on loan draws post-construction.
Draw Order
The model assumes that you will draw on equity before debt. Using equity first is the most cost-effective option as it would allow you to hold off on paying interest longer. There are a few charts built-in depicting this data.
In the unit mix tab, you log the different floorplans, square footage, and targeted rents (hopefully verified by comps). You can add or delete rows as you see fit. There is a unit mix pie chart and bar chart depicting the rents PSF that will populate as you fill out the information.
To the right, there is a section dedicated to inclusionary zoning. I wrote a blog post related to this topic. Some municipalities will require the developer to include affordable units. If this is the case, you can add them to the right.
Market Rent Growth During Construction
You can also control market rent growth during the construction years. I’ve received feedback over the years that many developers are solving for the unit mix today. If the project isn’t delivered for 12 or 24 months, having a rent growth toggle would be helpful.
Warning: This assumption can have a significant impact on returns. Use it reasonably. A thorough rent comp survey and confirmation that rent levels at the property aren’t higher than the local cost of homeownership would be helpful.
Once the property receives its certificate of occupancy, rent will be locked until it leases up and is stabilized.
Commercial
If you have to underwrite a small commercial component, entering the commercial lease with the residential units may be a viable option.
It's time to start entering assumptions for all the operating revenues and expenses. Gross Potential Rent (GPR) populates from the "Unit Mix" tab. You must project the other line items.
Revenue Loss assumptions are as a percentage of GPR. These are the black text revenue line items:
Gain (Loss to Lease)
Concessions
Vacancy Loss
Bad Debt
Employee Discount
Model Unit Loss
If you don't need all the above line items, you can either set them at $0 and hide the row, or delete the row altogether.
For ancillary income, you must designate the quantity and what the monthly charge will be. Some ancillary income opportunities include:
Storage
Pet Rent
Parking
RUBS
Amenity Fee
For items you can't set a monthly price, such as damages, application fees, and lease break fees, you could set the quantity as the total unit amount and estimate the average monthly cost to back into what you think the average annual income will be. I think the annual income will be $32,400 from these items, and I got there by plugging in an average monthly charge of $15 per unit.
Commercial
I have used "Other" line items to underwrite a mixed-use component. I would only recommend doing this if the commercial element is a small percentage of the overall project. If it's significant, there may be more specialized underwriting consequences that you must account for. My rule is that if the commercial revenue is less than 5% of the total project revenue, you are safe to underwrite as "Other Income."
Operating Expenses
You underwrite expense items on a per-unit basis except for the management fee. This assumption is as a percentage of Effective Gross Income (EGI). Finally, you can set replacement reserves per unit, and the stabilized Net Operating Income (NOI) will populate.
RE Tax Note: You must determine the property reassessment as a percentage of Project Cost. You must also project the millage rate (typically, you use the current rate).
Developments can span multiple years, and full tax reassessment will differ depending on the county. You also must determine what percentage of the entire tax amount you will pay during the stabilization years as a percentage of Projected Stabilized (in green). Once the property is fully stabilized, you must determine an annual escalation amount. There should never be an entry in both the Stabilized and Escalator columns. Your grid should look similar to the one below.
You should have a good idea of what real estate taxes will be during the development months and budget for that expense appropriately.
I will generally use property tax comps to finalize the stabilized tax number. I detail how I do this specifically for new development in the second half of the linked article.
I also discuss property tax abatements in this blog post.
Starting in column "K," you will see the monthly financial performance. During the construction months, operating revenues/expenses will be $0. Once the construction period is over, revenues will begin ramping up, and operating expenses will also be phased in. A summary in rows 6-9 depicts the date, the occupancy (in units), and the occupancy percentage. Once the property accomplishes stabilization (per your assumption on the "Project" tab), the monthly analysis will cut off, and your stabilized assumptions to the left will be fully phased in.
Scrolling further to the right, you will see a summary of these cash flows presented annually. You cannot control the stabilized cash flows in the gray box above. You'll notice that the cells are blank in this example before the property stabilizes. This is because your previous lease-up assumptions dictate the cash flows until stabilization.
Important Notes About Cash Flow Logic
Expenses will phase in at your stabilized assumption; there is no build-up; the model assumes that your stabilized expense assumptions will be prevalent during lease-up
Rent will not increase until stabilization. In other words, the model assumes that if the lease-up takes two years and your targeted rent is $1,700, you will target $1,700 until stabilization.
The model will not work if a lease-up takes over five years (refi must occur before Year 7).
During the early days of lease-up, when operating cash flow is negative, it is assumed that the construction loan will be drawn upon to cover this loss. This summary is presented in rows 55 - 62.
Here, you can analyze the project-level returns of the real estate development. IRRs, equity multiples, and cash-on-cash returns (post-refi) are at the bottom of the sheet. The residual cap rate assumption you make on the "Project" tab determines the Residual Value.
The debt service expense and loan paydown factor is whether or not you are paying debt service on the construction loan or permanent financing. When calculating the IRRs and equity multiples, the model assumes that if you sold in the refinance year, you would forgo the refinance, sell, and pay down the construction loan. You wouldn't pay lender fees and likely burden yourself with yield maintenance if you are going to sell that same year.
Merchant Developers
A couple of notes for merchant developers. If your intended sale takes place during the “would be” refinance year (typically Year 2 or Year 3), you must check that either the refi takes place:
The last month of the year
A later year post-sale
If not, the annual debt service payment will be slightly off.
In the example image above, the refi would take when the property stabilizes in month 24. Since this is the last month of Year 2, the debt service line ($1,005,049) is accurate if you were to sell then. If the property were set to stabilize in month 20, you’d need to go to the project summary tab and manually adjust the refi date to occur in month 24 or later.
If you miss this step, the debt service in Year 2 will be nine months of construction loan payments and three months of permanent financing payments. This logic is exemplary (and intended) if you plan to hold the property for three or more years. It’s only inaccurate if you were to sell in Year 2 (although likely immaterial as the sale proceeds skew all return metrics heavily).
Once you move the refi out of the scope of the investment hold, the model will know to use the interest-only construction loan payment until sale.
Additional Application: Convert an underwriting template into a hybrid analysis that factors actual historical financial performance and updated future forecasts for optimal 'hold vs. sell' decision-making
Construction Draw at C.O.
The construction loan proceeds used to cover negative cash flow early in the lease-up will be considered cash flow, as this is what pays debt service when operations aren’t yet sufficient.
This tab summarizes construction and permanent financing terms, payments, balances, and the timing of blowing out the construction loan. You don’t need to adjust financing assumptions on this tab; it simply provides transparency on all debt metrics.
You can edit the brown text to the left to customize the drop-down lists on the "Budget & Draw" tab.
When analyzing new development opportunities, I have found that it is common to overlook the tax liability for the next buyer, which could drastically impact sale proceeds. I wrote a blog post detailing real estate tax sensitivity and how it should be analyzed. The bottom line is that the longer you are willing to hold the asset, the less real estate tax sensitivity there will be for a theoretical future buyer. This tab will show this evolving sensitivity over a ten-year hold period.
A thorough financial mode will allow you to stress a real estate deal without constantly altering assumptions. That is the purpose of the “Stress Tests” tab. You will be able to stress seamlessly:
Stabilized DSCR
Refinance Proceeds at Stabilization
Pre-leasing/Leasing Effect on IRR
Return on Cost
You can walk you through these stress tests and see how they should be utilized to maximize their effectiveness.
A separate tutorial describes deeper insight into the IRR on development projects and how Tactica’s "IRR Sources” analysis tabs work. This article will touch on underlying topics related to the fundamentals of IRR sources, specifically what real estate components contribute to the IRR.
The final step in development underwriting is determining the partnership distribution structure. In other words, how will the cash distribution be split between GP and LP investors? Five different options are built into the Tactica financial model.
Pari Passu
Profit Interest
Equity Multiple + Sale Kicker
I got into great depth, explaining each partnership distribution model in a separate blog post.
Each tab in this section will also track the sponsor fees (if applicable).
Partnership Summary Tab
The "Partnership Summary" tab summarizes all the different partnership structures (bulleted above) in one central place, so you do not have to jump back and forth between the tabs to compare them. This tab will lay out various metrics from both GP/LP perspectives, such as:
Investment Contributions
Net Cash Flow
IRR
Equity Multiple
Avg. Annual Return
Fees (for the sponsor)
There are no inputs on this tab. Its sole purpose is a more efficient comparison.
If you're interested in the inner-working of this tool to maximize its utility with advanced Excel knowledge, we recommend visiting these articles:
We plan to publish more posts about the commonly used Excel functions and the major analysis components within the Tactica models.
Multifamily Real Estate Development Proforma Conclusion
That concludes this tutorial. You can go directly to the sales page from here if you want to purchase. Before you buy, I am happy to answer any questions about the multifamily real estate development proforma model and its functionality. I want you to be sure it fits your business plan perfectly.
This intuitive development template will help you feel comfortable and confident with your analysis. You should be able to vet development sites quickly and accurately, and you can recycle this tool for all of your future underwriting projects.
We also offer free underwriting tools that are explained via tutorials in the links below:
Existing Properties: Proforma Excel Template
Development Opportunities: Back of Napkin Excel Template