Multifamily Redevelopment Proforma Excel Template
The Tactica Multifamily Redevelopment Underwriting Guide serves three purposes:
A Tutorial - A collection of blog posts, images, videos, and links for users using the Tactica Redevelopment 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 repositioning underwriting template, and you can determine if it is a good fit for your business.
Knowledge - Regardless of your interest in an underwriting tool, this article will provide great insight into how an extensive renovation should be underwritten and analyzed.
Redevelopment Model Contents
Redevelopment Primer
Unlike the value-add strategy, which is a more "linear" endeavor from an underwriting standpoint, this tool will allow investors to get much more detailed with their rehab assumptions. Users can control the rehab down to the individual unit if desired (although this level of detail isn't necessary).
This blog post aims to show you how to accurately organize your renovation assumptions to depict your business plan. Tactica defines “redevelopment” opportunities as properties that are:
Distressed physically or from an occupancy standpoint
Not eligible for agency or bank financing due to the condition of the apartment complex
Displays dismal financial trend lines
Needing significant capital investment for unit interiors, common areas, and exteriors
When you want to take over the property, renovate it, and stabilize it as quickly as possible with the intent to sell or refinance at stabilization, the following steps will be perfect for your real estate analysis.
You can check out how Tactica differentiates Redevelopment and Value-Add business plans.
Pre-Analysis Requirements
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.” To do so, go to:
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 read about the relevance of circular references in development proformas for two minutes.
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.
Investment Summary
This tab serves two significant purposes. It is where you make the majority of your assumptions and also serves as a dashboard that can be printed and emailed to partners and investors. The dashboard will display and summarize various other components found in different tabs. The hope is that by spending five minutes on this tab, you can formulate a solid understanding of the investment viability of a particular multifamily asset.
General Property Info
Start by keying all the general property details. About half of the cells in this section will populate from the data inputted on other tabs.
Picture of Property
This is where you can import a picture of the property or a map of its location. It’s pretty self-explanatory.
Unit Mix Summary
This pie chart is a simple summary of the unit mix of the apartment building. This chart will update automatically when you populate the “Renovation Inputs” tab.
Financing
For financing in “Year 0,” you must enter the terms of your bridge/construction loan or interest-only bank loan/agency loan. You can select whether the loan proceeds are calculated using only the purchase price (LTV) or the purchase price plus construction costs (LTC).
Note: 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.
I explain the “Interest Accrual” and “Interest Reserve” Assumption in great detail in the “CAPEX Plan” tutorial.
Upon completing the renovation, the model expects you to refinance, blow out the “Year 0” financing, and slap on an agency or bank loan. Your inputs in the refinance section will determine the “LTV” of the loan and the “Loan Amount” cells above, which I will touch on shortly.
Related: See how I used this proforma model to explore the consequences of a ‘cash-in’ refinance.
Summary of Rents & Renovation Plan
The “Reno Inputs” tab also populates these charts. The renovation data is summarized at a high level to give you an overview of how the potential repositioning effort is projected to play out.
Project-Level Metrics
This section hosts arguably the two most essential toggles of the analysis—the purchase price and investment hold period. In real-time, you can see how various pricing metrics are affected by a change in price and hold, most notably the stabilized “Yield on Cost” highlighted in blue (Stabilized NOI / Price + Construction Costs).
The model calculates different cap rates, including the stabilized cap rate post-renovation. You can select via a dropdown list what cap rates you want to be displayed.
Refinance Metrics
This section is where you make four refinance assumptions:
The cap rate you forecast at refinancing.
The LTV of the refinance loan.
Refinance month
Refi/Reposition fee (if prevalent)
You have the flexibility to change the refi month. However, it must be 36 months or less to conform to the renovation analysis.
The model will calculate the valuation at refinancing, the total refi proceeds, and how much investor equity is left in the project post-refinance.
Important Note: The IRR is a time-sensitive metric and assumes refi proceeds are paid to investors at the end of the proforma year. For example, if you refinance in month 25, the model will assume you pay out the proceeds to investors at the end of Year 3 (or 11 months later). This is the same logic I use in the Multifamily Development Model and assures refinance cash flows are accounted for conservatively.
If you charge a “Repositioning Fee,” you can enter this information as well (as a percentage of total loan proceeds).
Sources & Uses
This section summarizes all costs that will encumber the investment opportunity and how it will be funded (via debt and equity). One cell is dedicated to "Other Costs" in additional costs (must be entered as a negative).
Interest Reserve selection in the “Financing” section will determine if construction interest is factored into the sources and uses. Above, it is $0 because I elected “No” for the “Interest Reserve” assumption.
Loan Costs are also determined by your assumption in the “Financing” section.
Escrow & Closing Costs and Acquisition Fee assumptions are made off to the right, outside of the view of the dashboard frame:
Cash Operating Reserve is calculated automatically if there are any negative cash flow years during the renovation plan (years 1 - 3).
Residual Assumptions
Summary of residual assumptions.
Estimating the residual sales value is a crucial aspect of any real estate project. This data is summarized here. You will be able to see the reversion pricing metrics along with the appreciation that has taken place over the hold period. The back-end sales price is determined by the residual cap rate, which is a manual input you will make off to the right:
You would enter the residual cap rate today and its expected annual increases. In this example, if you think the market cap rate is 5.25% and will grow at 0.10% annually in ten years, the residual cap rate would be 6.25% (5.25% + 0.10%*10).
You can learn more about our methodology for calculating residual sale proceeds.
You will also be responsible for entering a brokerage commission and other closing costs (as a percentage of sales price). If you are syndicating equity and charge a residual sales fee, you would also enter that amount in his section.
The “Loan Paydown,” “Sale Costs,” and “Residual Sales Fee” are all summarized as negative numbers, and the total sales proceeds are totaled at the bottom.
Partnership Returns
Finally, the value-add model has five different partnership structures built in. 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 and equity multiple do not include any income from asset management, acquisition fees, or residual sales fees. All partnership distribution models are linked to the hold period set on this tab. While you can change the hold period for each scenario, linking back to this tab is essential so the graphs are accurate. There is an error check to help you with this.
I would also recommend checking out how to maximize Excel’s Goal Seek analysis tool. Understanding Goal Seek and unleashing its potential on this tab could help you unlock solutions that may have gone unnoticed and ultimately give you ideas to persuade investors, convince lenders, or negotiate with a seller.
Renovation Inputs
I have two blog posts that walk you through the “Renovation Inputs” tab of the financial model.
Small Properties
For a more granular analysis (optimal for properties under 50 units), you want to plan the renovation down to individual units or group similar floorplans.
Planning a Multifamily Repositioning Investment Strategy
The analysis gives you two different ideas on populating the unit inputs.
Large Properties
I also wrote a tutorial article for more significant properties that will summarize how to knock out hundreds of unit renovations and give you an idea to enter the information fast.
Planning a 100+ Unit Multifamily Repositioning
This blog post shows how you can underwrite quickly without sacrificing accuracy using the “Error Check” feature.
Bonus: If you can add more units in a common area or combine smaller units to re-rent them as one large unit, check out the hyperlinked articles. They will touch on the Redevelopment Model towards the end of each article.
Renovation Summary
This tab has no assumptions; it is simply an excellent summary of your renovation. It details rental metrics, the renovation timeframe, vacancy projections, return on cost projections, critical cash flows, and a CAPEX summary.
CAPEX Plan
The emphasis of this worksheet is allocating CAPEX for common areas, deferred maintenance, and other miscellaneous nonrecurring costs such as:
Hallways
Plumbing
HVAC
Exteriors
Project Management
Construction Contingencies
Marketing (Lease-Up)
Interest Expense (Optional)
I have an article on planning all construction and miscellaneous costs unrelated to unit interiors.
Real Estate Taxes
The property tax calendar will vary from state to state. That is okay, though, because aside from slight nuances in different taxing jurisdictions, there are two crucial things that you must account for as an investor.
How much reassessment risk is there?
Which year will this reassessment risk hit the proforma?
There is a process you should follow when underwriting property taxes, and I have a separate article that serves as a guide to underwriting multifamily property tax.
I also dedicated an article to properly underwriting instances where the property tax calendar doesn't align perfectly with the proforma investment calendar. The timing of the sale can impact how you underwrite the proforma property tax. For states with multiple tax payments due throughout the year, it is crucial to understand "staggering" and how to use it to depict real estate tax exposure accurately.
Financials
You'll make assumptions for other revenue line items and expenses in this tab. This tab is broken down into three parts:
Historical Financials
Proforma Assumptions
Proforma Cash Flows
Historical Financials
This tab is where you will enter the property's historical profit and loss (P&L) data.
Again, all brown and orange (for the negative numbers) are your responsibility. Hopefully, you were provided the current Trailing 12 Month Financials (T12) broken down monthly and the prior two years of financial information. If this is not the case, you can hide or delete unnecessary columns. For example, if no 2017 operating information were available, I would likely hide that column so it didn't distract me.
The Trailing Three (T3) Header is tinted pink, so you know to enter only three months of data. You could do a “T6”, a “T2”, or your preferred viewpoint. Just update the header and enter the corresponding months' worth of data. A “multiply factor” cell is above the “T3/T12” column. If you enter “T3,” the model will know to multiply by 4 to annualize the data. If you were going off of a T6, the model would know to multiply by 2.
The process will be slightly different if you're underwriting a pre-stabilized project.
This is a brown cell because it technically can be overwritten. The cell does, however, have a formula.
You can delete any unnecessary rows. I hide them, but removing them is fine and will not affect any calculations adversely.
After you key the historical data, you can see the annualized T3/T12 broken down in a few different ways. Many investors love breaking down line items on a "per unit" basis. I am one of them. Looking at revenue items as a % of GPR is helpful. Looking at expense items as a % of EGI is common, especially for Management Fees.
The green columns show the year-over-year historical trends. Historical EGI has been all over the place, which has caused the NOI to fluctuate wildly.
Proforma Assumptions
Now, it's time to enter your proforma assumptions. Let's start with Year 1 (FY1).
Year 1 Assumptions
For each line item, the model allows three underwriting assumption options. You can make your assumption by:
Column 1: Increasing/Decreasing as % over the latest historical amount (T3/T12 amount in this case)
Column 2: As a % of GPR for revenue and as a % of EGI for expenses
Column 3: Per unit
The gray cells indicate that you don't need to do anything. These assumptions are flowing in from another tab. Assumptions for:
Gross Potential Rent
Proforma Rent-Value-Add
Vacancy Loss (General)
Vacancy Loss (Renovations)
The information is flowing in from the “Reno Inputs” tab. Same with Real Estate Taxes in the operating expenses.
Note: Utilities can be grouped as one line item or broken out individually (water, gas, electricity, etc). It’s your call.
Year 2 Assumptions
Again, you can make assumptions by:
Column 1: Increasing/decreasing as % over the FY1 amounts
Column 2: As a % of GPR for revenue and as a % of EGI for expenses
Column 3: Per unit
My assumptions are similar to the ones I made in Forecast Year 1 (FY1). Reserves will continue to be $300 per unit each year.
Note: Excel will read the assumptions from left to right. Look at the administrative expenses assumptions. If I entered:
Excel would use the 3% growth over FY1, ignoring 25% and $110,000. For clarity, ensure you only have one assumption entered for each line item (excluding gray cells, which are calculated automatically).
Ancillary Income Phase-In
When you want to phase in other income items so they tie to the property occupancy, you can use the grid below to help expedite the process.
You can tie the “occupancy percentage” to the “per-unit” potential to ramp up the other income (or expenses) over the renovation timeline. I cover this process extensively in the tutorial video below.
Year 3+ Assumptions
After FY2, the Years 3-11 assumption will be much more inflexible. Every line item except for Vacancy Loss, Bad Debt, and Management Fee is made as a percentage increase (or decrease) over the prior year. Vacancy and Bad Debt assumptions are a percentage of GPR. Management Fee will be a percentage of EGI. This approach is noted in the model, so you do not forget.
Proforma Cashflows
After all, you enter all assumptions; you can take a look at the cash flows off to the right:
Returns Summary
The Returns Summary is all about analyzing the numbers and calculating the return metrics. This tab will summarize the following:
Cap Rates (historical, proforma, and tax-adjusted)
Leveraged IRRs
All Cash Returns
IRRs and equity multiples are calculated for various holding periods. For example, a 7-year hold would produce a leveraged IRR of 16.69% and a 10-year hold would have a leveraged IRR of 15.65%.
The “All Cash IRR” and “All Cash Returns” are standard metrics to analyze because they show you how the investment would look if you didn't utilize debt. It's crucial to compare the “Leveraged” vs. “All Cash” metrics to ensure that the debt is accretive and boosts your yields. In many cases, deals are over-leveraged and don't lift the returns much but exponentially more risk.
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
Renovate & Flip
Here are a couple of notes for the renovate and flip investors. 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 that year
A later year post-sale
If not, the annual debt service payment will be slightly off during the sale year.
Once you move the refi out of the scope of the investment hold, the model will know to use the interest-only bridge loan payment until sale.
Asset Management
There is one assumption you will need to make on this tab.
Asset Management: If you plan on charging an asset management fee (as a percentage of EGI), you make that assumption here.
This tab is more visual in nature and is where I spend much time trying to figure out if a deal makes sense. If you downloaded the Free Multifamily Template, the presentation on this tab is identical to the “Valuation” tab.
Refi Debt
The “Debt” tab hosts the amortization table for the new loan at refinancing. This tab doesn't require you to do anything.
IRR Sources
IRR is a prevalent return metric for investors to gauge the upside of an investment opportunity. What many don't realize is that the IRR is compromised of five sources:
Initial Investment Recovery
Year 1 Cash Flow
Cash Flow Increases Year-over-Year
Refinance
Appreciation
Principal Loan Balance Reduction
The Tactica value-add model measures the allocation of each source. The more deals you are underwriting, the better feel you will gain for risk/reward correlation with the distribution off IRR.
Check out the article, IRR For Real Estate Development, to better understand how IRR works and how you should look at it for your investments. While the blog is written for a theoretical development project, very similar concepts will apply as both development and redevelopment have a refinance component.
Partnership Distributions
The final step in value-add underwriting is determining the partnership distribution structure. In other words, what will the cash distribution split between GP and LP investors? Five different options are built into the Tactica financial model.
Pari Passu
Profit Interest
Preferred Return + Profit Interest
Equity Multiple + Sale Kicker
I got into great depth, explaining each partnership distribution option in a separate blog post.
Excel Resources
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.
Video: Underwriting a Distressed 78-unit MF Property
Summarizing the Redevelopment Model
This page will be updated in real-time with text, images, videos, and links explaining the new facets as updates continue to be implemented. If you purchase the Redevelopment Model, I will constantly update you via email when the new versions are released. If you are ever confused about something, I recommend checking here to see if your doubt can be resolved.
If you are looking for a new financial model but are not ready to commit, I recommend checking out our Free Multifamily Underwriting Template. It shares a lot of the same capabilities as Tactica’s paid tools and will give you a good feel for the paid tools. You can register below to receive the free version.
