Free Complex Real Estate Financial Model
This free real estate financial modelling course teaches students how to build a LBO cash-flow model from scratch via a live case study of a value-add investment opportunity. It is ideal for learners with some basic Excel and financial modelling training and who want to take their financial modelling skills to the next level. It is particularly relevant for candidates who are preparing for job interviews, financial modelling tests or assessment centres at a real estate private equity firm.
The Advanced Real Estate Financial Modelling Case Study
Course Overview - Advanced Real Estate Financial Model
Lesson 1: Model Setup 1 (Real Estate Financial Modelling Assumptions)
Lesson 2: Financial Model Setup 2 (Modelling Assumptions)
Lesson 3: Cash-Flow Calcs
Lesson 4: Real Estate Market Rents (ERVs)
Lesson 5: Void Period & Toggles
This free complex real estate financial modelling course builds on the free beginner financial modelling course. The real estate model covered in this course is very similar to the financial models that candidates are often asked to complete in assessment centers at private equity firms and the course is therefore highly relevant for candiates that are interested in a career in commerical real estate investing and who are preparing for financial modelling tests or assessment centers at real estate investment firms. It is suitable for participants with some financial modelling training although this is not a prerequisite.
The course is based on a live case study of a value-add real estate investment opportunity that includes two tenants on short term leases with a capex programme. It is taught via 15 easy-to-follow video lessons in which participants will learn how to build a real estate LBO financial model in Excel. The instructor of this course is Steffen Kluners, who learnt how to build real estate cash-flow models at Blackstone. Consequently, participants will learn how the best real estate investment firms build financial models and analyse real estate transactions. Students will gain access to the Excel based financial models and course materials upon providing the e-mail address after clicking the button above.
We set up the financial model by including two additional tabs, which are the assumptions and the rent roll tab. In these tabs we input the general assumptions, acquisition assumptions and leasing assumptions, which we will later use to forecast the cash-flows.
We continue inputting the main assumptions of this case study so that we can project the real estate cash-flows in the subsequent lessons. In this lesson we include timing, capex and financing assumptions as well as assumptions related to the exit valuation including exit valuation yields.
Lesson 1:
Financial Model Setup (1 of 2)
Lesson 3:
Calcs for Cash-Flows
We have now completed inputting the main assumptions for this real estate case study and in this lesson we include a new tab which is used to perform the main calculations for this cash-flow model. We will start by projecting the surface area of the building as well as the rental income of the in-place leases.
Lesson 2:
Financial Model Setup (2 of 2)
Lesson 4:
Market Rents (ERVs)
We are forecasting the market rents or ERVs (economic rental values) in the calc sheet. The market rents which later be used as a basis for calculating the rental income of the new lease after the capex programme is completed.
Lesson 5:
Void Periods & Void Toggles
Lesson 6: Total Void Costs
We are projecting the time during which the building falls vacant by using a void toggle. We then use the void toggle to compute the occupancy of the building.
This lesson is based on the calculations of the 5th lesson, in which we computed the void toggle. In this lesson we are calculating the void costs, which represent the operational costs (opex) of the building that are typically covered by the tenant but that are payable by the landlord when the building is vacant.
Lesson 7: New Rents
We are forecasting the rents of the new lease after completion of the capex programme. This is done by using the market prevailing market rents at the time the new lease is signed. The new rents are calculated on a per sqm basis as well as total rental income per quarter.
Lesson 8: The Cash-Flow Sheet
We start forecasting the cash-flows of the property based on the calculations in the previous lesson and we include market rents, in-place rents and the occupancy of the building by linking up the key summary statistics on the cash-flow page with the calcs sheet.
Lesson 9: Asset Cash-Flows
Lesson 10:
Unlevered Cash-Flows
& Unlevered Returns
We forecast the asset cash-flows of the building based on the calculations of the previous lessons and the assumption. We then project the rents, opex costs, NOI and the capex costs in this lesson as part of the asset cash-flows, which are used in the next lesson to compute the unlevered cash-flows.
We forecast the unlevered cash-flows and unlevered returns including the unlevered IRR. The unlevered cash-flows are calculated by combining the asset cash-flows with the acquisition costs and by calculating the asset valuation at the time of the sale. We will then use the unlevered cash-flows to forecast the levered cash-flows in the next lesson.
Lesson 12: Annual Cash-Flows
We have now completed the financial model and in this lesson, we summarise the quarterly cash-flows into annual cash-flows so that we can use the cash-flows as modelling outputs.
Lesson 11: Levered Cash-Flows & Levered Returns
We forecast the levered cash-flows and levered returns including the levered IRR. We will also build a debt repayment schedule and then forecast the levered cash-flows by combining the unlevered cash-flows with the debt repayment schedule.
Lesson 13: Formatting
We are improving the formatting of this financial model so that the outputs look professional. Some of the things we do include setting the print areas, adjusting the margins and including footers and page numbers.
We include a two variable and a one variable sensitivity table. In the two variable sensitivity table we show how returns change relative to changes of the market rents and exit yield and in the one variable sensitivity table we sensitise the returns relative to the purchase price. Finally, we backsolve the purchase price that Thanos can pay to achieve their target returns.
Lesson 15: Sensitivity Tables
We are include the Sources & Uses (S&Us) to the financial model to show how this investment opportunity is funded and how capital is deployed
Lesson 14: Sources & Uses
The REIA Courses
Are you ready to take your financial modelling and real estate investing skills to the next level?
This advanced real estate financial modelling course is an excellent starting point for candiates who are preparing for financial modelling tests and assessment centres. However, there are many financial modelling techniques as well as many concepts and theories of estate investing that are not yet covered here but that are discussed in great depth in the more advanced courses of the Real Estate Investment Academy (REIA).
These include a free Excel financial course (Applied Financial Modelling) and four additional paid courses that are structured as a holistic and complete package that covers all topics of real estate investment analysis and financial modelling. These courses are structured to provide participants with a competitive edge to propel their careers in commercial real estate investing.