What is Real Estate Financial Modeling (REFM)? [Step-By-Step-Tutorial]
Vložit
- čas přidán 1. 07. 2024
- Learn more: breakingintowallstreet.com/re...
In this tutorial, you’ll learn what real estate financial modeling is, how we use it to make investment decisions, and you’ll see examples of simple acquisition and development models and the step-by-step process that applies to both of them.
www.mergersandinquisitions.co...
breakingintowallstreet.com/
"Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
Table of Contents:
1:18 Part 1: What is the Point of Real Estate Financial Modeling?
3:09 Part 2: Types of Deals, Properties, and Models
7:19 Part 3: Example of an Acquisition Model
12:50 Renovation Model Differences
15:42 Part 4: Example of a Development Model
20:19 Recap and Summary
In real estate financial modeling, also known as REFM, you analyze a property from the perspective of an Equity Investor (owner) or Debt Investor (Lender) and determine whether or not the Equity or Debt Investor should invest, based on the risks and potential returns.
For example, if you buy a multifamily property for $50 million and hold it for 5 years, could you earn a 12% annualized return on it? Is that range of returns (10 - 15%) plausible?
Types of Deals, Properties, and Models
There are three main ways you can invest in properties:
Way #1: Acquire an existing property, change little to nothing, and sell it - this is Acquisition Modeling.
Way #2: Acquire an existing property, change it significantly, and sell it - this is Renovation Modeling.
Way #3: Buy land, pay to develop a new property, find tenants, and then sell the property when it stabilizes - this is Development Modeling.
And then there are several different categories of properties.
The first category is office, industrial, and retail properties, which have businesses as customers and tend to have long-term leases with highly variable terms.
On the opposite end are hotels, where guests only stay a few nights, and where financial modeling is much closer to what you do for normal companies.
In the middle are multifamily properties, with 1-year leases that have very similar terms, and condominiums, which are often pre-sold to individuals and owned by individuals.
The Step-by-Step Process to Real Estate Financial Modeling
Step 1: Set up the Transaction Assumptions, including the property size, price or development costs, and exit details.
Step 2: For development models, project the Construction Period and draw on Debt and Equity over time to fund the development.
Step 3: Build the Operating Assumptions, which could be high-level or very granular depending on the property type.
Step 4: Build the Pro-Forma, including NOI, Adjusted NOI, Debt Service, and Cash Flow to Equity.
Step 5: Make the Returns Calculations, including the initial investment(s), cash flows over time, exit, and debt repayment.
Step 6: Make an Investment Decision based on your criteria and the model output in different cases.
In the acquisition model, we apply these steps by setting the property’s size based on number of units and average square feet per unit and setting a price based on a Cap Rate. Debt is based on the purchase price times the LTV.
The operating assumptions are very high-level and linked to per-unit and per-square-foot figures since the individual leases are so similar. We assume rental growth, a reduced discount to market rates, and increases in reimbursement rates and the vacancy rate over time, along with minor upgrades.
The Pro-Forma is fairly standard and starts with Base Rental Income, makes deductions and adjustments, deducts expenses, and ends with NOI, Adjusted NOI, and Cash Flow to Equity.
We then calculate the IRR and multiple and conclude that we’d need to analyze this in different cases and stress test it a bit more. A 15% IRR is quite good for a stabilized property, but we don’t know how well it holds up in downside scenarios.
In the development model example, we purchase land upfront and estimate the construction costs.
During the construction period, we distribute the land and construction costs over time, draw on Equity first to pay for them, then switch to Debt, and we capitalize interest and loan fees during the period.
The operating assumptions are based on tenant-by-tenant numbers since there are only two tenants.
The Pro-Forma is fairly standard, but the refinancing is a bit tricky since we need to get the property’s value a year after it takes place and then discount it back one year based on a 15% Discount Rate to determine the Permanent Loan amount.
In the Returns Calculations, we factor in upfront Equity draws, the refinancing, cash flows to equity, excess land sale, and exit and debt repayment at the end.
This one is probably a “no” since we just barely reach the 20% IRR in the base case, and we purchase too much land in the beginning. The waterfall structure also works against us.
![Working Capital and the Change in Working Capital in Valuation and Financial Modeling [REVISED]](http://i.ytimg.com/vi/tMgty8jwmHI/mqdefault.jpg)
Thank you for this channel!!! Been prepping up for Investment bank interviews!
Thanks for watching!
This is a yummy presentation. It's helpful. It makes sense and if informative. The desert part is the free content.
Thanks!
This was perfect! Thank you!
Thanks for watching!
Thank you so much for this overview! I am about to start a career in CRE and really enjoyed this insight!
Thanks for watching!
Wow.. Im gonna buy biws real estate course for sure.
Presentation + Teaching skill is top notch.
Thanks! Good luck and let us know if you have any questions.
Thank you for this tutorial and summary - I know you were trying to cover most of the material within 20min but I was wondering if you had in-depth videos on each of the topics / investments you discussed?
In our full Real Estate course, yes. There are 44 hours of video tutorials on all these topics and more. This channel is just for samples and quick overviews.
Thank you for this! Learned a lot. Just one question: In the simple acquisition model example, why are bad debt and concession combined into a single cell as a metric? From my understanding concessions is something you can control contrast to bad debt. Shouldn't they be apart? Thank you and excuse my ignorance haha!
You could separate them, but this is a simplified model, so we combined them and assumed that both bad debt and concessions tend to rise when the economy is bad. If they moved in different directions under different economic conditions, we might have separated them.
This is a great video! I wanted to learn to build my own CRE excel financial modeling for my deals with the formulas most used, can you recommend a cheap or free excel program for this online?
We have many free real estate tutorials in this channel and on M&I. If you want a paid course, we offer a Real Estate Modeling course on BIWS, though it is not "cheap," as we aim to be comprehensive.
Thank you for the great videos!
I have an question for Levearged IRR & Unleveraged at the Excel file you've attached.(Real-estate-Pro-forma)
I think terms of them should be switched. Is it right?
I thought Cashflow to Equity Investors (Unlevered IRR)
Leveraged IRR means the annualized return that equity investors earn, after factoring in debt and debt service. Unleveraged means, "Let's ignore Debt and pretend the property was purchased with 100% equity, so there is no Debt to reduce the upfront purchase price and there's also no Debt service during the holding period."
@@financialmodeling Really Thank you I should have checked the terms correctly..
Always Thanks for your videos.
Does excel help with calculating cap rates noi etc, or do you need these percentages before hand?
You need an outside source for the data.
I couldn't find this excel sheets in the link in the description. Where is it?
www.mergersandinquisitions.com/real-estate-financial-modeling/ "Real Estate Financial Modeling: Sample Excel (XLS) Files"
@@financialmodelingThat is really helpful, however I can not access to the excel now. DOES THE LINK STILL WORK? THANKS
why no 3 statement modeling for real estate?
You mean like financial statements?
From the linked article:
"Properties are much simpler than large companies, so if it’s feasible to simplify the financial statements like this for companies, it’s even more feasible to simplify them for properties.
It’s also simpler because Working Capital tends to be less important for properties, and you effectively use Cash Accounting rather than Accrual Accounting in the analysis.
Also, you usually ignore income taxes because properties tend to be owned by pass-through entities such as Partnerships, S Corporations, and REITs that do not pay corporate income taxes.
Finally, “other activities” on companies’ Cash Flow Statements are often minimal for properties, and Debt and Equity Issuances and Debt Service can be handled directly on the pro-forma.
To be clear: properties still have full financial statements.
It’s just that for financial modeling, valuation, and investment analysis, you almost always skip the full statements and focus on the pro-forma instead."
hi brian, where does PBSA fit in this?
Not sure what you are asking? Student housing is just a sub-asset class within real estate, similar to multifamily.
@@financialmodeling thanks for the clarification. That makes sense
Where is the excel model for this vid? thanks ..
www.mergersandinquisitions.com/real-estate-financial-modeling/
Awesome video! You give off cocaine-level energy
Thanks for watching!